"地瓜哥"博客网

公司原有项目中，因为以前赶时间，有些地方做的稍微粗糙了一些，某些地方存在 N+1查询问题。最近这两个月再做一个新项目。所有，以前踩过的坑，希望这次能尽量避免。比如 N+1查询问题。

公司技术方面，Dao 层使用的是 Spring JdbcTemplate；数据库使用的是 MySQL。所以，关于这次的主题，D瓜哥 也只关注 Spring JdbcTemplate 和 MySQL。

从出坑到入坑

讲述解决办法之前，D瓜哥 先说明一下背景问题：一个订单 Order 中，会有多个订单元素 OrderItem，在查看订单列表时，需要根据订单 ID 查询相关的订单元素信息。查询方式有两种：一、在循环中，每次循环根据一个订单 ID 查询一个订单对应的订单元素信息。这就是典型的 N+1查询问题。另外一种方式，根据查询出来的所有的订单 ID 一次查询出所有相关的订单元素信息。显然，第二种方式要比第一种方式效率上高很多。这是基本的背景问题。下面来说明一下 D瓜哥 在解决这个问题过程中犯的错误以及更好的解决办法。

看到上述问题时，D瓜哥的第一反应就是使用 IN 就可以很好地解决。开始噼里啪啦写代码：

String sql = " SELECT * " +
        " FROM `order_item` " +
        " WHERE order_id IN ( ? )";

List<OrderItem> orderItemList = getJdbcTemplate().query(sql, new BeanPropertyRowMapper<OrderItem>(OrderItem.class), orderItemArray);

结果一运行，代码妥妥地报错了：一个问号只能识别一个参数。没办法，D瓜哥 也是到公司后首次使用 Spring JdbcTemplate，就把代码改成了下面这个方式：

String sql = " SELECT * " +
        " FROM `order_item` " +
        " WHERE order_id IN ( %s )";

sql = String.format(sql, Joiner.on(",").join(skuIds)); // <1>

List<OrderItem> orderItemList = getJdbcTemplate()
    .query(sql, new BeanPropertyRowMapper<OrderItem>(OrderItem.class));

1. 这里使用了 Google Guava 工具类。可以在 “Guava 小技巧三则” 中做更多了解。

代码妥妥地出结果了。大家觉得这个代码有什么问题吗？

对 JDBC 和数据库查询比较有了解地一眼就能看出来：这里把参数直接编码到了 SQL 中，每次使用，只要参数不一样，数据库都需要对 SQL 重新解析一下，不能享受解析缓存带来的优化结果。怎么办呢？

昨天下午，从同事那里知道了： NamedParameterJdbcTemplate 这个东东，后来查了一下相关资料，这个玩意果然能完美地解决了上次问题。下面来说明了这个玩意地搞。

配置方法

正式开始说明之前，先说明一下 Spring 中的相关配置。如下：

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="dataSource" />
</bean>

<bean id="namedParameterJdbcTemplate"
      class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="jdbcTemplate"/>
</bean>

这里省略了数据源的配置。请大家自行补充。

这里重点说明一下为什么使用构造器注入，并且注入 jdbcTemplate？

其实，这个问题很容易回答，查看一下 NamedParameterJdbcTemplate 类的源代码，你就会发现，这个类一共有两个构造函数，分别接受的参数是 DataSource 和 JdbcOperations ( JdbcTemplate是这个接口的一个实现类)。那么，就必须使用构造器注入了。而在接受 DataSource 的构造函数的实现如下：

public NamedParameterJdbcTemplate(DataSource dataSource) {
  Assert.notNull(dataSource, "DataSource must not be null");
  this.classicJdbcTemplate = new JdbcTemplate(dataSource);
}

这里需要重新创建一个 JdbcTemplate，D瓜哥认为，既然使用了 Spring 了，就把尽可能多的对象都交给 Spring 来管理。所以，D瓜哥偏向使用注入 JdbcTemplate 这种方式。

查询

废话不多说，直接上代码：

String sql = " SELECT * " +
        " FROM order_item " +
        " WHERE order_id IN (  rderIds )";

SqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("orderIds", orderIds);

List<OrderItem> orderItemList = getNamedParameterJdbcTemplate()
    .query(sql, parameters, new BeanPropertyRowMapper<OrderItem>(OrderItem.class));

代码还可以写成这样子：

String sql = " SELECT * " +
        " FROM order_item " +
        " WHERE order_id IN (  rderIds )";

Map<String, Object> parameters = Maps.newHashMap();
parameters.addValue("orderIds", orderIds);

List<OrderItem> orderItemList = getNamedParameterJdbcTemplate()
    .query(sql, parameters, new BeanPropertyRowMapper<OrderItem>(OrderItem.class));

D瓜哥查看了一下 Spring 的文档。这里的参数，不仅可以传 Key-Value 这样的类似 Map 的结构。还可以直接传一个对象。D瓜哥就直接照抄 Spring 文档中的代码了：

public class Actor {
    private Long id;
    private String firstName;
    private String lastName;

    public Long getId() {
        return id;
    }

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    // Setter 方法省略
}



public int countOfActors(Actor exampleActor) {
    // 注意： 具名参数必须和上面的 Actor 属性名相匹配。
    String sql = "select count(*) from T_ACTOR where first_name = :firstName and last_name = :lastName";
    SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(exampleActor);
    return getNamedParameterJdbcTemplate().queryForObject(sql, namedParameters, Integer.class);
}

这点还是很牛逼的！

批量更新

在查看 Spring 文档时，D瓜哥还有一个收获，就是可以使用这个东西进行批量更新。下面的代码结合上面代码中使用的 Actor 类。具体如下：

public int[] batchUpdate(final List<Actor> actors) {
    SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(actors.toArray());
    int[] updateCounts = getNamedParameterJdbcTemplate().batchUpdate(
            "update t_actor set first_name = :firstName, last_name = :lastName where id = :id",
            batch);
    return updateCounts;
}

返回记录是每条更新语句更新的数据行数。

这里再留一个问题吧：能否使用 NamedParameterJdbcTemplate 进行批量的插入操作？这个回头再研究研究。

参考资料

1. Data access with JDBC

2. NamedParameterJdbcTemplate vs JdbcTemplate – Stack Overflow