当前项目中一直使用的都是 SpringData JPA
,即 public interface UserRepository extends JpaRepository<User, Serializable>
这种用法;
考虑到 SpringData JPA
确实有一定的局限性,在部分查询中使用到了 JdbcTemplate
进行复杂查询操作;
由于本人16年也曾使用过 JdbcTemplate
,古语温故而知新,所以做此总结梳理。
首先列出同事的做法:
public class xxx{ xxx method(){ ... List<WishDTO> list = jdbcTemplate.query(sql, new WishDTO()); ... } } @Data public class WishDTO implements RowMapper<WishDTO>, Serializable { String xxx; Long xxx; Date xxx; BigDecimal xxx; @Override public WishDTO mapRow(ResultSet rs, int rowNum) { WishDTO dto = new WishDTO(); Field[] fields = dto.getClass().getDeclaredFields(); for (Field field : fields) { try { field.setAccessible(true); field.set(dto, rs.getObject(field.getName())); } catch (Exception e) { e.printStackTrace(); } } return dto; } }
个人感觉让 WishDTO 再实现实现一遍 RowMapper 有点麻烦,毕竟 WishDTO 实体类的所有字段都是需要赋值的,并没有定制化需求。
所以想着有没有更好地写法,然后就翻了一下 jdbcTemplate 的方法,找到了一个 自认为
满足自己这个需求的方法:
public <T> List<T> queryForList(String sql, Class<T> elementType)
public class xxx{ xxx method(){ ... List<WishDTO> list = jdbcTemplate.queryForList(sql, WishDTO.class); ... } } @Data public class WishDTO implements Serializable { String xxx; Long xxx; Date xxx; BigDecimal xxx; }
一切看起来都很完美,但执行却报错了: Incorrect column count: expected 1, actual 13
经过一番对源码进行debug,结合网上的一些资料,大概知道了是什么原因了,分析如下;
public <T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException { return query(sql, getSingleColumnRowMapper(elementType)); }
其本质是还是调用 public <T> List<T> query(String sql, RowMapper<T> rowMapper)
,只是将 Class<T> elementType
封装成一个 RowMapper
实现实例;
protected <T> RowMapper<T> getSingleColumnRowMapper(Class<T> requiredType) { return new SingleColumnRowMapper<>(requiredType); }
现在我们可以看一下 SingleColumnRowMapper 类的描述:
/** * {@link RowMapper} implementation that converts a single column into a single * result value per row. Expects to operate on a {@code java.sql.ResultSet} * that just contains a single column. * * <p>The type of the result value for each row can be specified. The value * for the single column will be extracted from the {@code ResultSet} * and converted into the specified target type. */
其实从类名也可以看出,这是一个 RowMapper 的 简单实现,且仅能接收一个字段的数据,如 String.class 和 Integer.class 等基础类型;
网上的参考资料: https://blog.csdn.net/qq_4014...
使用 BeanPropertyRowMapper 进行封装 ;
即 将代码改为:
public class xxx{ xxx method(){ ... List<WishDTO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<WishDTO>(WishDTO.class)); ... } } @Data public class WishDTO implements Serializable { String xxx; Long xxx; Date xxx; BigDecimal xxx; }
接下来看一下 BeanPropertyRowMapper
的类描述:
/** * {@link RowMapper} implementation that converts a row into a new instance * of the specified mapped target class. The mapped target class must be a * top-level class and it must have a default or no-arg constructor. * * <p>Column values are mapped based on matching the column name as obtained from result set * meta-data to public setters for the corresponding properties. The names are matched either * directly or by transforming a name separating the parts with underscores to the same name * using "camel" case. * * <p>Mapping is provided for fields in the target class for many common types, e.g.: * String, boolean, Boolean, byte, Byte, short, Short, int, Integer, long, Long, * float, Float, double, Double, BigDecimal, {@code java.util.Date}, etc. * * <p>To facilitate mapping between columns and fields that don't have matching names, * try using column aliases in the SQL statement like "select fname as first_name from customer". * * <p>For 'null' values read from the database, we will attempt to call the setter, but in the case of * Java primitives, this causes a TypeMismatchException. This class can be configured (using the * primitivesDefaultedForNullValue property) to trap this exception and use the primitives default value. * Be aware that if you use the values from the generated bean to update the database the primitive value * will have been set to the primitive's default value instead of null. * * <p>Please note that this class is designed to provide convenience rather than high performance. * For best performance, consider using a custom {@link RowMapper} implementation. */
其作用就是讲一个Bean class 转化成相对应的 Bean RowMapper 实现类。
https://docs.spring.io/spring...
int rowCount = this.jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class); int countOfActorsNamedJoe = this.jdbcTemplate.queryForObject("select count(*) from t_actor where first_name = ?", Integer.class, "Joe"); String lastName = this.jdbcTemplate.queryForObject("select last_name from t_actor where id = ?", new Object[]{1212L}, String.class); Actor actor = this.jdbcTemplate.queryForObject( "select first_name, last_name from t_actor where id = ?", new Object[]{1212L}, new RowMapper<Actor>() { public Actor mapRow(ResultSet rs, int rowNum) throws SQLException { Actor actor = new Actor(); actor.setFirstName(rs.getString("first_name")); actor.setLastName(rs.getString("last_name")); return actor; } }); List<Actor> actors = this.jdbcTemplate.query( "select first_name, last_name from t_actor", new RowMapper<Actor>() { public Actor mapRow(ResultSet rs, int rowNum) throws SQLException { Actor actor = new Actor(); actor.setFirstName(rs.getString("first_name")); actor.setLastName(rs.getString("last_name")); return actor; } }); --- public List<Actor> findAllActors() { return this.jdbcTemplate.query( "select first_name, last_name from t_actor", new ActorMapper()); } private static final class ActorMapper implements RowMapper<Actor> { public Actor mapRow(ResultSet rs, int rowNum) throws SQLException { Actor actor = new Actor(); actor.setFirstName(rs.getString("first_name")); actor.setLastName(rs.getString("last_name")); return actor; } }
this.jdbcTemplate.update( "insert into t_actor (first_name, last_name) values (?, ?)", "Leonor", "Watling"); this.jdbcTemplate.update( "update t_actor set last_name = ? where id = ?", "Banjo", 5276L); this.jdbcTemplate.update( "delete from actor where id = ?", Long.valueOf(actorId));
this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
https://docs.spring.io/spring...