Java Spring - RowMapper ResultSet - Integer/null values

Question

I have a Java SE 8 Spring 4.1.6-RELEASE application, where I am implementing the org.springframework.jdbc.core.RowMapper<T> interface, and I had some questions about the java.sql.ResultSet interface that is passed in its T mapRow(ResultSet rs, int rowNum) method.

When I inspect the ResultSet class, I see a bunch of methods to get column values back:

╔══════════════╦═════════════════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ Return Type  ║       Method        ║                                                                   Return (javadoc, se 8)                                                                   ║
╠══════════════╬═════════════════════╬════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╣
║ String       ║ getString           ║ the column value; if the value is SQL NULL, the value returned is null                                                                                     ║
║ boolean      ║ getBoolean          ║ the column value; if the value is SQL NULL, the value returned is false                                                                                    ║
║ byte         ║ getByte             ║ the column value; if the value is SQL NULL, the value returned is 0                                                                                        ║
║ short        ║ getShort            ║ the column value; if the value is SQL NULL, the value returned is 0                                                                                        ║
║ int          ║ getInt              ║ the column value; if the value is SQL NULL, the value returned is 0                                                                                        ║
║ long         ║ getLong             ║ the column value; if the value is SQL NULL, the value returned is 0                                                                                        ║
║ float        ║ getFloat            ║ the column value; if the value is SQL NULL, the value returned is 0                                                                                        ║
║ double       ║ getDouble           ║ the column value; if the value is SQL NULL, the value returned is 0                                                                                        ║
║ BigDecimal   ║ getBigDecimal       ║ the column value; if the value is SQL NULL, the value returned is null                                                                                     ║
║ byte[]       ║ getBytes            ║ the column value; if the value is SQL NULL, the value returned is null                                                                                     ║
║ Date         ║ getDate             ║ the column value; if the value is SQL NULL, the value returned is null                                                                                     ║
║ Time         ║ getTime             ║ the column value; if the value is SQL NULL, the value returned is null                                                                                     ║
║ Timestamp    ║ getTimestamp        ║ the column value; if the value is SQL NULL, the value returned is null                                                                                     ║
║ InputStream  ║ getAsciiStream      ║ a Java input stream that delivers the database column value as a stream of one-byte ASCII characters; if the value is SQL NULL, the value returned is null ║
║ Reader       ║ getCharacterStream  ║ a java.io.Reader object that contains the column value; if the value is SQL NULL, the value returned is null in the Java programming language              ║
║ InputStream  ║ getBinaryStream     ║ a Java input stream that delivers the database column value as a stream of uninterpreted bytes; if the value is SQL NULL, the value returned is null       ║
║ <T> T        ║ getObject           ║ an instance of type holding the column value                                                                                                               ║
╚══════════════╩═════════════════════╩════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Is the genernal expectation / practice to call:

rs.getObject("COLUMN_NAME", Boolean.class);

rs.getObject("COLUMN_NAME", Byte.class);

rs.getObject("COLUMN_NAME", Short.class);

rs.getObject("COLUMN_NAME", Integer.class);

rs.getObject("COLUMN_NAME", Long.class);

etc., for all of the primitive types? As everything else returns null for the instance of SQL NULL.

If so, what's the point of having all the methods for the different types when the typed Object method is there?

Also, what are the pros/cons of each approach?

1. Using getInt(String columnLabel):

   Integer resultingActionId = rs.getInt("RESULTING_ACTION_ID");
   if (rs.wasNull) {
       resultingActionId = null
   }

2. Using getObject(String columnLabel) and casting to Integer:

   Integer resultingActionId = (Integer) rs.getObject("RESULTING_ACTION_ID");

3. Using getObject(String columnLabel, Class type):

   Integer resultingActionId = rs.getObject("RESULTING_ACTION_ID", Integer.class);

For instance, I noticed the org.springframework.jdbc.core.JdbcTemplate used to have queryForLong, queryForInt, etc. methods for getting a single value from a single row query and replaced them all in favor of a typed queryForObject method.

Thanks!

Pevzner

Answer 1

TLDR

• Use resultSet.getXXX(String columnLabel) if no SQL NULL allowed, or you only care about getting back a primitive (no object, boxed or array types)
• Use resultSet.getObject(String columnLabel, Class type) if value could be SQL NULL but DO NOT unbox if type is a boxed type e.g. Integer as you could get NullPointerException since primitives can't be null
• Use resultSet.getObject(String columnLabel) with casting if you're running Java 6

Details:

If a column does not allow SQL NULL, or your code strictly needs or expects a primitive i.e. no object types (this includes boxed types e.g. Integer, primitive arrays which are actually objects e.g. byte[]) then use the specific get method:

// Integer is 0 if value is SQL NULL
int i = resultSet.getInt(column);

// This is fine but Integer will never be null e.g. Integer.valueOf(0) if SQL NULL
// This may or may not be what you want
Integer j = resultSet.getInt(column);

As far as I can tell, the reason for this method is convenience as it handles SQL NULL gracefully where you must have a primitive since primitives cannot be null e.g. return 0 for int.

If the column does allow SQL NULL, or your code expects a object type (including boxed types or arrays) then use resultSet.getObject with the type's class, but DO NOT unbox boxed types when using this method:

// Integer will be null on SQL NULL but that's OK for boxed types
Integer i = resultSet.getObject(column, Integer.class);

// Throws NullPointerException on SQL NULL since primitives can't be null
int unbox = resultSet.getObject(column, Integer.class);

// Integer will be 0 since getInt returns 0 on SQL NULL
Integer autobox = resultSet.getInt(column);

This method is also useful for additional SQL types that are supported but do not have a specific get method i.e. are beyond the minimum specified in JDBC specification JSR-221:

// No getUUID method but it's supported by Postgres so no need for custom mapping
UUID uuid = resultSet.getObject(column, UUID.class);

But note this method wasn't added until Java 7, so if you were on Java 6 you would need cast it:

UUID uuid = (UUID) resultSet.getObject(column);

So unless you really don't know or care about the return type, there's no reason to use this unless you prefer it stylistically, or you need to run on Java 6.

Lastly my strong suggestion is to be judicious when using (or unboxing) boxed types when working with JDBC to avoid unexpected NullPointerException. Either avoid SQL NULL in your schema e.g. default to 0 if you can, or explicitly check/convert if you can't be sure.

Pepsinate

Answer 2

If you take a look at java.sql.ResultSet, you can see you don't need to be so explicit. Actually, unless you have a typeMapper for you connection which allows you to use the getObject method, it will not work (java.sql.ResultSet.getObject).

I don't know if it would help you, but I managed to find a RowMapper of my own that worked great for my needs.

private class ShabaUserMapper implements RowMapper<ShabaUser>
{
    @Override
    public ShabaUser mapRow( ResultSet rs, int rowNum ) throws SQLException
    {
        Collection<SimpleGrantedAuthority> roles = new ArrayList<SimpleGrantedAuthority>();

        String auths = rs.getString( "role" );

        roles.add( new SimpleGrantedAuthority( auths ) );

        ShabaUser user = new ShabaUser( rs.getString( "username" ), rs.getString( "password" ),
                rs.getBoolean( "enabled" ), rs.getString( "first_name" ),
                rs.getString( "last_name" ), rs.getString( "email" ),
                rs.getString( "date_joined" ), rs.getString( "last_online" ), true, true, true,
                roles );

        // Can be null!
        Integer awesomeness = rs.getInt( "awesomeness" );
        if ( rs.wasNull() )
        {
            awesomeness = null;
        }

        user.setAwesomeness( awesomeness );

        return user;
    }
}

private class ShabaUserListExtractor implements ResultSetExtractor<List<ShabaUser>>
{
    private final ShabaUserMapper rowMapper;

    private int                   rowsExpected;

    public ShabaUserListExtractor()
    {
        this( new ShabaUserMapper(), 0 );
    }

    public ShabaUserListExtractor( ShabaUserMapper rowMapper, int rowsExpected )
    {
        Assert.notNull( rowMapper, "RowMapper is required" );
        this.rowMapper = rowMapper;
        this.rowsExpected = rowsExpected;
    }

    @Override
    public List<ShabaUser> extractData( ResultSet rs ) throws SQLException
    {
        HashMap<String, ShabaUser> results = ( this.rowsExpected > 0
                                                                    ? new HashMap<String, ShabaUser>(
                                                                            rowsExpected )
                                                                    : new HashMap<String, ShabaUser>() );
        int rowNum = 0;
        while ( rs.next() )
        {
            ShabaUser user = rowMapper.mapRow( rs, rowNum++ );

            if ( results.containsKey( user.getUsername() ) )
            {
                ShabaUser inUser = results.get( user.getUsername() );
                ArrayList<GrantedAuthority> combinedAuthorities = new ArrayList<GrantedAuthority>();

                combinedAuthorities.addAll( inUser.getAuthorities() );
                combinedAuthorities.addAll( user.getAuthorities() );

                results.put( user.getUsername(),
                    createUserDetails( user.getUsername(), user, combinedAuthorities ) );
            } else
            {
                results.put( user.getUsername(), user );
            }
        }

        return new ArrayList<ShabaUser>( results.values() );
    }
}

I realize this is a lot of code, but hopefully you can see what was accomplished here. The actual RowMapper implementation is actually meant to house all the "dirty work" for extracting your object from row information.

So long as your database is setup correctly and you make it so NOT NULL is on required columns, you will never run into the problem of pulling out a row that is empty. Though I suppose it wouldn't hurt to check for a null response from your ResultSet, you'll still just end up throwing an exception anyways if the column should of had a value.

Sashasashay

I have a lot of cases where I want a null Integer, mainly if I have an optional foreign key, this is done through a nullable Integer type in the database. I was looking at docs.oracle.com/javase/8/docs/api/java/sql/…, and it says "Retrieves the value of the designated column in the current row of this ResultSet object and will convert from the SQL type of the column to the requested Java data type, if the conversion is supported. If the conversion is not supported or null is specified for the type, a SQLException is thrown."... – Pevzner

... I took this to mean it will try a class cast from the JDBC driver result, which should work for a numeric value being cast to an Integer. – Pevzner

In that case, I would at least attempt that your first solution. The wasNull() method is there so I would assume that is best practice: Integer resultingActionId = rs.getInt("RESULTING_ACTION_ID"); if (rs.wasNull) { resultingActionId = null } – Sashasashay

You don't want that to stay 0 otherwise. Which is how that is handled in the case of the SQL_NULL for an integer type. – Sashasashay

That's what I had as example 1 of pulling an Integer for comparing different ways of dealing with nulls in my question; to pull the value from ResultSet.getInt(String columnLabel) and check ResultSet.wasNull() right after. I want to know what the difference between that Integer awesomeness = (Integer) rs.getObject("awesomeness"); and Integer awesomeness = rs.getObject("awesomeness", Integer.class); is from a best-practices / known-practices / efficiency / executed code standpoint. – Pevzner

I see now. Forgive me. Best practice would definitely to use neither of those methods for primitive types of course. For any other custom types, you technically want to use the latter rs.getObject("column", ClassName.class). Using this method ensures type safety which is always the better alternative to casting. HOWEVER, the caveat is that most implementations of ResultSet don't support this method and allow only the former method therein relying on your SQL connector for a map of SQL types to java.sql.Structs. So technically you don't have a choice. – Sashasashay

JdbcRowSetImpl for instance has this in its method body: public <T> T getObject( String paramString, Class<T> paramClass ) throws SQLException { throw new SQLFeatureNotSupportedException( "Not supported yet." ); } – Sashasashay