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!

Answer 1

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.