Java Spring - RowMapper ResultSet - Integer / null values

Question

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

When I check the ResultSet class, I see a bunch of methods to return the column values:

  ╔═════════════╦═════════════════════════════════ ════════════════════════════════════════════════ ════════════════════════════════════════════════ ═ ═ ═
 ║ 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 general expectation / practice expected:

 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 primitive types? Because everything else returns null for the SQL NULL instance SQL NULL .

If so, what is the point of having all the methods for different types when a typed Object method exists?

Also, what are the advantages / disadvantages of each approach?

• Using getInt(String columnLabel) :

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

• Using getObject(String columnLabel) and pronunciation on Integer :

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

• Using getObject(String columnLabel, Class type) :

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

For example, I noticed that org.springframework.jdbc.core.JdbcTemplate had queryForLong , queryForInt , etc. methods to get a single value from a single query string and replaced them all in favor of the typed queryForObject method.

Thanks!

Answer 1

If you look at java.sql.ResultSet , you can see that you do not need to be so explicit. In fact, if you do not have a Mapper type for a connection that allows you to use the getObject method, it will not work ( java.sql.ResultSet.getObject ).

I don’t know if this will help you, but I managed to find a RowMapper that is perfect 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 understand that this is a lot of code, but hopefully you can see what has been done here. The actual implementation of RowMapper is actually designed to host all the "dirty work" for extracting your object from string information.

As long as your database is configured correctly, and you make it so that NOT NULL is in the required columns, you will never run into the problem of pulling out an empty row. Although I believe it would be impractical to check for a null response from your ResultSet, you still just end up throwing an exception if the column should have a value.