关于SpringJdbcTemplate的一些总结

关于 Spring JdbcTemplate 的一些总结

一个小问题的思考

起因

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

成都创新互联公司坚持“要么做到，要么别承诺”的工作理念，服务领域包括：成都网站建设、做网站、企业官网、英文网站、手机端网站、网站推广等服务，满足客户于互联网时代的金林网站设计、移动媒体设计的需求，帮助企业找到有效的互联网解决方案。努力成为您成熟可靠的网络建设合作伙伴！

考虑到 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，结合网上的一些资料，大概知道了是什么原因了，分析如下；

```java
    public  List queryForList(String sql, Class elementType) throws DataAccessException {
        return query(sql, getSingleColumnRowMapper(elementType));
    }

其本质是还是调用public <T> List<T> query(String sql, RowMapper<T> rowMapper)，只是将 Class<T> 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 等基础类型；

网上的参考资料：https://blog.csdn.net/qq_40147863/article/details/86035595

解决方案

使用 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

https://docs.spring.io/spring/docs/5.1.8.RELEASE/spring-framework-reference/data-access.html#jdbc-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

https://docs.spring.io/spring/docs/5.1.8.RELEASE/spring-framework-reference/data-access.html#jdbc-NamedParameterJdbcTemplate