一、问题背景
相信大家在项目一定使用过jdbcTemplate操作数据库，因业务需求，需要查询集合对象List<Entity>，于是看到jdbcTemplate有个queryForList方法，就直接用了，殊不知，这里有个大坑等着我去跳。
错误写法：
StringBuilder sql = new StringBuilder();
sql.append("SELECT refBizId,memberId,applyMobile FROM table where xxxx");
List<Entity> list = jdbcTemplate.queryForList(sql, Entity.class);
提测之后，发现查询就报错了，喜提bug。
堆栈日志:
org.springframework.jdbc.IncorrectResultSetColumnCountException: Incorrect column count: expected 1, actual 3
at org.springframework.jdbc.core.SingleColumnRowMapper.mapRow(SingleColumnRowMapper.java:92)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:93)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:60)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:703)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:639)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:690)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:717)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:767)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:192)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList(NamedParameterJdbcTemplate.java:250)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForList(NamedParameterJdbcTemplate.java:257)
二、错误原因
直接debug查看源码报错处mapRow方法。nrOfColumn含义为查询列的数量，这里我们看到取值为3，对应我们的sql语句 SELECT refBizId,memberId,applyMobile 发现确实为3个字段。
通过查看源码注释，其实发现已经提示我们只能查询单列了，实际源码中会判断if (nrOfColumns != 1)，只允许查询1个字段，否则会抛IncorrectResultSetColumnCountException异常。
三、正确姿势
方法一：
如果仅需查询单个字段，可以使用queryForList方法。
jdbcTemplate.queryForList(sql, params, Integer.class); // 数据库字段对应是int类型
jdbcTemplate.queryForList(sql, params, String.class); // 数据库字段对应是varchar类型
方法二：
使用query(String sql, RowMapper<T> rowMapper)方法，自己实现RowMapper接口。
/**
* Execute a query given static SQL, mapping each row to a result object
* via a RowMapper.
* <p>Uses a JDBC Statement, not a PreparedStatement. If you want to
* execute a static query with a PreparedStatement, use the overloaded
* {@code query} method with {@code null} as argument array.
* @param sql the SQL query to execute
* @param rowMapper a callback that will map one object per row
* @return the result List, containing mapped objects
* @throws DataAccessException if there is any problem executing the query
* @see #query(String, Object[], RowMapper)
*/
<T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException;
这里给出1.7和1.8两种实现：
jdk1.7:
list = jdbcTemplate.query(sql.toString(), params, new RowMapper<Apply>() {
@Override
public CashFinalApply mapRow(ResultSet rs, int rowNum) throws SQLException {
Apply apply = new Apply();
apply.setRefBizId(rs.getString("refBizId"));
apply.setMemberId(rs.getString("memberId"));
apply.setApplyMobile(rs.getString("applyMobile"));
return applypply;
}
});
jdk1.8:
list = jdbcTemplate.query(sql.toString(), params, (rs, rowNum) -> {
Apply apply = new Apply();
apply.setRefBizId(rs.getString("refBizId"));
apply.setMemberId(rs.getString("memberId"));
apply.setApplyMobile(rs.getString("applyMobile"));
return applypply;
}
});
方法三（推荐）：
使用public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)方法，BeanPropertyRowMapper实现了RowMapper接口，使用该构造函数初始化BeanPropertyRowMapper对象。
public class BeanPropertyRowMapper<T> implements RowMapper<T>{}
/**
* Create a new {@code BeanPropertyRowMapper}, accepting unpopulated
* properties in the target bean.
* <p>Consider using the {@link #newInstance} factory method instead,
* which allows for specifying the mapped type once only.
* @param mappedClass the class that each row should be mapped to
*/
public BeanPropertyRowMapper(Class<T> mappedClass) {
initialize(mappedClass);
}
实际使用：
// guava ImmutableMap 你也可以使用new HashMap
Map<String, Object> params = ImmutableMap.of("memberId", memberId);
List<Entity> list = jdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(Entity.class));
好了，到这里我们把这个坑填上了，江湖再见~
