Java Spring - RowMapper ResultSet - 整数/空值

Question

我有一个 Java SE 8 Spring 4.1.6-RELEASE 应用程序，我正在其中实现 org.springframework.jdbc.core.RowMapper<T> 界面，我对 java.sql.ResultSet 有一些疑问在其 T mapRow(ResultSet rs, int rowNum) 中传递的接口(interface)方法。

当我检查 ResultSet类，我看到了一堆方法来取回列值:

╔══════════════╦═════════════════════╦════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗║ 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);

等等，对于所有的原始类型？由于其他一切都返回 null例如 SQL NULL .

如果是这样，当类型化对象方法存在时，拥有不同类型的所有方法有什么意义？

此外，每种方法的优缺点是什么？

1. 使用 getInt(String columnLabel) :

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

2. 使用 getObject(String columnLabel)并转换到Integer :

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

3. 使用 getObject(String columnLabel, Class type) :

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

例如，我注意到 org.springframework.jdbc.core.JdbcTemplate 曾经有 queryForLong , queryForInt 等方法，用于从单行查询中获取单个值并将它们全部替换为有利于类型化的 queryForObject方法。

谢谢!

Answer 1

如果你看一下java.sql.ResultSet ，你可以看到你不需要那么明确。实际上，除非你有一个允许你使用 getObject 方法的连接类型映射器，否则它不会工作(java.sql.ResultSet.getObject)。

我不知道它是否对你有帮助，但我设法找到了一个我自己的 RowMapper，它非常适合我的需要。

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() );
    }
}

我知道这是很多代码，但希望您能看到这里完成了什么。实际的 RowMapper 实现实际上是为了容纳所有从行信息中提取对象的“脏工作”。

只要您的数据库设置正确，并且在必需的列上设置了 NOT NULL，您就永远不会遇到提取空行的问题。虽然我认为检查 ResultSet 的空响应不会有什么坏处，但如果该列应该有一个值，您仍然会最终抛出异常。