Spring Boot JdbcTemplate in 语句注意事项

描述

在多条件查询情况下，拼接SQL语句时使用到了in关键词不管参数传入List集合还是Array数组，等到的结果集都是空。

// MySQL

List<String> orderIds = new ArrayList<>();
orderIds.add("abc123456789");
orderIds.add("xyz123456789");

String sql = "select id, ordr_id from test_order where order_id in (?)";

Object[] args = new Object[1];
args[0] = orderIds;

List<OrderTest> orders = jdbcTemplate.query(sql, args, BeanPropertyRowMapper.newInstance(OrderTest.class));

原因

MySQL和PostgreSQL两种数据库情况不一样。

MySQL

JdbcTemplate在替换in的占位符?时，将List或Array替换为普通字符使用单引号标注，即'xxx'，执行的SQL类似以下语句，而in语句对于不满足条件的查询将返回空集合。

select id, ordr_id from test_order where order_id in ('abc,def,xyz')

若单纯传入的参数为"1,2,3,4,5"，则将格式化为in ('1,2,3,4,5')，而MySQL的in条件将只匹配改字符串的第一个逗号前的条件，即等同于in (1)

PostgreSQL

对于PostgreSQL使用in条件将会报错。

解决

MySQL

可以直接使用in(%s)和String.format()，格式化in条件。

推荐使用NamedParameterJdbcTemplate代替JdbcTemplate。

PostgreSQL

可以使用order_id = any(?)来代替order_id in (?)。

//PostgreSQL

List<String> orderIds = new ArrayList<>();
orderIds.add("abc123456789");
orderIds.add("xyz123456789");

String sql = "select id, ordr_id from test_order where order_id = any(?)";

Object[] args = new Object[1];
args[0] = orderIds;

List<OrderTest> orders = jdbcTemplate.query(sql, args, BeanPropertyRowMapper.newInstance(OrderTest.class));

将转为

select id, ordr_id from test_order where order_id = any('{abc123456789, xyz123456789}')