java jdbctemplate update_关于 Spring JdbcTemplate 的一些总结

关于 Spring JdbcTemplate 的一些总结

一个小问题的思考

起因

当前项目中一直使用的都是 SpringData JPA ，即 public interface UserRepository extends JpaRepository 这种用法；

考虑到 SpringData JPA 确实有一定的局限性，在部分查询中使用到了 JdbcTemplate 进行复杂查询操作；

由于本人16年也曾使用过 JdbcTemplate，古语温故而知新，所以做此总结梳理。

首先列出同事的做法：

public class xxx{

xxx method(){

...

List list = jdbcTemplate.query(sql, new WishDTO());

...

}

}

@Data

public class WishDTO implements RowMapper, 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 List queryForList(String sql, Class elementType)

即 将代码改为：

public class xxx{

xxx method(){

...

List 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 List queryForList(String sql, Class elementType) throws DataAccessException {

return query(sql, getSingleColumnRowMapper(elementType));

}

其本质是还是调用public List query(String sql, RowMapper rowMapper)，只是将 Class elementType 封装成一个 RowMapper 实现实例;

protected RowMapper getSingleColumnRowMapper(Class 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.

*

*

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 等基础类型；

解决方案

使用 BeanPropertyRowMapper 进行封装 ；

即 将代码改为：

public class xxx{

xxx method(){

...

List list = jdbcTemplate.query(sql, new BeanPropertyRowMapper(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.

*

*

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.

*

*

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.

*

*

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".

*

*

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.

*

*

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 实现类。

JdbcTemplate

Query

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() {

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 actors = this.jdbcTemplate.query(

"select first_name, last_name from t_actor",

new RowMapper() {

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 findAllActors() {

return this.jdbcTemplate.query( "select first_name, last_name from t_actor", new ActorMapper());

}

private static final class ActorMapper implements RowMapper {

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;

}

}

Updating (INSERT, UPDATE, and DELETE)

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

Other

this.jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

NamedParameterJdbcTemplate