SpringBoot_NamedParameterJdbcTemplate介绍及使用
文章目录
1.背景
最近开始研究一些低代码后端实现的功能，因为想采用不生成具体代码方式(对比代码生成器 + 持续构建是实际生成代码方式)，来实现业务对象常规功能。因此不能用传统依赖实体类实现映射的orm框架，如mybatis。需要借助更底层的jdbc操作来实现通用的操作。但最原生jdbc写法在设置参数和处理数据结果操作又太过繁琐。在借用spring的JdbcTemplate工具时候，发现了NamedParameterJdbcTemplate可以实现这种特殊需求。本文在此做一个记录和总结。
2.需求分析
首先，具体技术需求分析如下：
- 如果不通过具体类来实现任意数据模型的curd，除了指定具体的modelId(可以是表名)必须使用类似Map<String,Object>的通用数据结构。key指定数据字段名称value代表值。
- 引擎需要自动识别Map有多少key构造具体的sql模板
以上是初步设想的技术思路。
3.技术分析
在springboot项目实现以上的功能，优先使用spring-jdbc自带的模板工具。介绍NamedParameterJdbcTemplate 之前先回顾下JdbcTemplate
3.1 JdbcTemplate
- JdbcTemplate是Spring框架对JDBC的简化封装，用于执行SQL语句和处理SQL查询结果。
- 它是Spring JDBC模块的核心类，提供了许多便捷的方法，减少了样板代码，简化了JDBC的使用。
- JdbcTemplate主要使用占位符（?）来表示参数，并通过索引进行参数的设置。
- 它的方法签名通常包含SQL语句和一个Object[]数组，用于传递SQL语句中的参数值。
简单代码示例：
JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
List<User> users = jdbcTemplate.query(sql, new Object[]{"john_doe", "secret"}, new BeanPropertyRowMapper<>(User.class));
通过示例代码分析可以发现,jdbcTemplate对于实现，目标功能存在以下缺陷
- 使用占位符，通过位置索引传递参数，而对于Map来说，它key的是无序的，因此不适用。
3.2 NamedParameterJdbcTemplate
什么是NamedParameterJdbcTemplate?
NamedParameterJdbcTemplate是JdbcTemplate的扩展，它使用命名参数而不是占位符，使得SQL语句更易读、更易维护
有如下特点：
- 使用命名参数时，SQL语句中的参数以冒号（:）开头，而且不再按顺序传递，而是通过SqlParameterSource对象来传递参数。
- NamedParameterJdbcTemplate支持命名参数的原因是，它提供了与具名参数相关的方法，例如query(String sql, SqlParameterSource paramSource, RowMapper rowMapper)。
代码示例：
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
String sql = "SELECT * FROM users WHERE username = :username AND password = :password";
Map<String, Object> params = new HashMap<>();
params.put("username", "john_doe");
params.put("password", "secret");
List<User> users = namedParameterJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(User.class));
3.3 JdbcTemplate与NamedParameterJdbcTemplate 对比总结
- JdbcTemplate使用占位符，通过位置索引传递参数。NamedParameterJdbcTemplate使用命名参数，通过参数名传递参数。
- NamedParameterJdbcTemplate相对于JdbcTemplate在SQL语句的可读性和可维护性上有优势，尤其是当有很多参数时。
利用NamedParameterJdbcTemplate的参数名命的特性，作为低代码通用sql操作类，具有天然的便利性。
PS: 就性能而言，这两个类相似，选择其中之一对性能的影响不太大。
总体而言，如果SQL查询涉及少量参数且顺序很简单，使用JdbcTemplate可能就足够了。然而，如果你有许多参数的复杂查询，或者如果你注重SQL代码的可读性和可维护性，那么NamedParameterJdbcTemplate可能是更好的选择.
4. 实战：简易通用curd接口开发
通过NamedParameterJdbcTemplate实现通用数据模型实现基础的增删改查，这里模型暂时直接指定表名和列数据
- 请求: tableName,Map<String, Object> columnValues
4.1 通用新增接口
public void insert(String tableName, Map<String, Object> columnValues) {
StringBuilder sql = new StringBuilder("INSERT INTO " + tableName + " (");
// 构建列名
columnValues.keySet().forEach(columnName -> sql.append(columnName).append(", "));
// 删除最后的逗号和空格
sql.delete(sql.length() - 2, sql.length());
sql.append(") VALUES (");
// 构建参数占位符
columnValues.keySet().forEach(columnName -> sql.append(":").append(columnName).append(", "));
// 删除最后的逗号和空格
sql.delete(sql.length() - 2, sql.length());
sql.append(")");
System.out.println(sql.toString());
// 执行插入操作
namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource(columnValues));
}
4.2 通用修改接口
修改接口相比新增接口需要指定主键ID
public void update(String tableName, Long id, Map<String, Object> columnValues) {
StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET ");
// 构建 SET 子句
columnValues.forEach((columnName, columnValue) ->
sql.append(columnName).append(" = :").append(columnName).append(", "));
// 删除最后的逗号和空格
sql.delete(sql.length() - 2, sql.length());
sql.append(" WHERE id = :id");
// 添加 ID 参数
columnValues.put("id", id);
// 执行更新操作
namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource(columnValues));
}
4.3 通用删除接口
public void delete(String tableName, Long id) {
String sql = "DELETE FROM " + tableName + " WHERE id = :id";
namedParameterJdbcTemplate.update(sql, Map.of("id", id));
}
4.4 通用列表接口
默认查询接口获取key使用原始数据列的key(数据库多为下划线), 不适合作为通用的数据接口交互，因此需要转小写驼峰
/**
* 查询表中所有数据
* @param tableName
* @return
*/
public List<Map<String, Object>> findAll(String tableName) {
String sql = "SELECT * FROM " + tableName;
return namedParameterJdbcTemplate.queryForList(sql, Map.of()).stream().map(item -> convertKeysToUnderscore(item)).collect(Collectors.toList());
}
4.5 通用分页查询接口
分页接口相比列表接口需要额外处理以下细节
- 接受分页入参(pageIndex,pageSize)
- 计算记录总数(用于前端用于计算总页数)
- 封装分页结果
{
"pageIndex": 1,
"pageSize": 10,
"rows": [
{
"name": "zhangsan",
"age": 20
}
],
"total": 1
}
实现
public Map<String, Object> findByPage(String tableName, int page, int pageSize) {
String countSql = "SELECT COUNT(*) FROM " + tableName;
Long total = namedParameterJdbcTemplate.queryForObject(countSql, new MapSqlParameterSource(), Long.class);
int offset = (page - 1) * pageSize;
String sql = "SELECT * FROM " + tableName + " LIMIT :pageSize OFFSET :offset";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("pageSize", pageSize);
parameters.addValue("offset", offset);
List<Map<String, Object>> resultList = namedParameterJdbcTemplate.queryForList(sql, parameters);
List<Map<String, Object>> convertedList = resultList.stream()
.map(item -> convertKeysToUnderscore(item))
.collect(Collectors.toList());
// 封装结果和总条数
Map<String, Object> result = Map.of("rows", convertedList, "total", total, "pageIndex", page, "pageSize", pageSize);
return result;
}
4.6 实战完整源码
public class AutoRepositoryService {
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public void update(String tableName, Long id, Map<String, Object> columnValues) {
StringBuilder sql = new StringBuilder("UPDATE " + tableName + " SET ");
// 构建 SET 子句
columnValues.forEach((columnName, columnValue) ->
sql.append(columnName).append(" = :").append(columnName).append(", "));
// 删除最后的逗号和空格
sql.delete(sql.length() - 2, sql.length());
sql.append(" WHERE id = :id");
// 添加 ID 参数
columnValues.put("id", id);
// 执行更新操作
namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource(columnValues));
}
public void insert(String tableName, Map<String, Object> columnValues) {
StringBuilder sql = new StringBuilder("INSERT INTO " + tableName + " (");
// 构建列名
columnValues.keySet().forEach(columnName -> sql.append(columnName).append(", "));
// 删除最后的逗号和空格
sql.delete(sql.length() - 2, sql.length());
sql.append(") VALUES (");
// 构建参数占位符
columnValues.keySet().forEach(columnName -> sql.append(":").append(columnName).append(", "));
// 删除最后的逗号和空格
sql.delete(sql.length() - 2, sql.length());
sql.append(")");
System.out.println(sql.toString());
// 执行插入操作
namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource(columnValues));
}
public void delete(String tableName, Long id) {
String sql = "DELETE FROM " + tableName + " WHERE id = :id";
namedParameterJdbcTemplate.update(sql, Map.of("id", id));
}
/**
* 查询表中所有数据
* @param tableName
* @return
*/
public List<Map<String, Object>> findAll(String tableName) {
String sql = "SELECT * FROM " + tableName;
return namedParameterJdbcTemplate.queryForList(sql, Map.of()).stream().map(item -> convertKeysToUnderscore(item)).collect(Collectors.toList());
}
public Map<String, Object> findByPage(String tableName, int page, int pageSize) {
String countSql = "SELECT COUNT(*) FROM " + tableName;
Long total = namedParameterJdbcTemplate.queryForObject(countSql, new MapSqlParameterSource(), Long.class);
int offset = (page - 1) * pageSize;
String sql = "SELECT * FROM " + tableName + " LIMIT :pageSize OFFSET :offset";
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("pageSize", pageSize);
parameters.addValue("offset", offset);
List<Map<String, Object>> resultList = namedParameterJdbcTemplate.queryForList(sql, parameters);
List<Map<String, Object>> convertedList = resultList.stream()
.map(item -> convertKeysToUnderscore(item))
.collect(Collectors.toList());
// 封装结果和总条数
Map<String, Object> result = Map.of("rows", convertedList, "total", total, "pageIndex", page, "pageSize", pageSize);
return result;
}
/**
* 根据查询条件查询数据
* @param tableName
* @param conditions
* @return
*/
public List<Map<String, Object>> findAllByConditions(String tableName, List<Map<String, Object>> conditions) {
StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM ").append(tableName);
MapSqlParameterSource parameterSource = new MapSqlParameterSource();
if (conditions != null && !conditions.isEmpty()) {
sqlBuilder.append(" WHERE ");
for (int i = 0; i < conditions.size(); i++) {
Map<String, Object> condition = conditions.get(i);
String keyname = (String) condition.get("keyname");
Object value = condition.get("value");
String dataType = (String) condition.get("dataType");
String dataFormat = (String) condition.get("dataFormat");
if (i > 0) {
sqlBuilder.append(" AND ");
}
if ("S".equals(dataType)) {
sqlBuilder.append(keyname).append(" = :").append(keyname);
parameterSource.addValue(keyname, value);
} else if ("D".equals(dataType)) {
// 日期使用数组代表范围查询（大于小于）
if (value instanceof List && ((List<?>) value).size() == 2) {
String startKey = keyname + "_start";
String endKey = keyname + "_end";
sqlBuilder.append(keyname).append(" BETWEEN :").append(startKey).append(" AND :").append(endKey);
Object startDateObj = ((List<?>) value).get(0);
Object endDateObj = ((List<?>) value).get(1);
if (startDateObj instanceof String && endDateObj instanceof String) {
LocalDateTime startDate = LocalDateTime.parse((String) startDateObj, DateTimeFormatter.ofPattern("yyyyMMddHHmm"));
LocalDateTime endDate = LocalDateTime.parse((String) endDateObj, DateTimeFormatter.ofPattern("yyyyMMddHHmm"));
parameterSource.addValue(startKey, startDate);
parameterSource.addValue(endKey, endDate);
}
// 日期使用一个字符串代表精确查询
} else if (value instanceof String) {
// Exact match for a single date
LocalDateTime exactDate = parseDateTime((String) value, dataFormat);
sqlBuilder.append(keyname).append(" = :").append(keyname);
parameterSource.addValue(keyname, exactDate);
}
// TODO 只有大于或者小于怎么处理待定
}
}
}
String sql = sqlBuilder.toString();
System.out.println(sql);
return namedParameterJdbcTemplate.queryForList(sql, parameterSource).stream().map(item -> convertKeysToUnderscore(item)).collect(Collectors.toList());
}
private LocalDateTime parseDateTime(String dateTimeString, String dataFormat) {
return LocalDateTime.parse(dateTimeString, DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
}
/**
* 将Map中的键从驼峰转换为下划线
*/
public static Map<String, Object> convertKeysToUnderscore(Map<String, Object> inputMap) {
Map<String, Object> outputMap = new HashMap<>();
for (Map.Entry<String, Object> entry : inputMap.entrySet()) {
String underscoreKey = MyronStringUtils.toCamelCase(entry.getKey());
outputMap.put(underscoreKey, entry.getValue());
}
return outputMap;
}
}
5. 其他使用对比总结
NamedParameterJdbcTemplate除了上面用于map操作sql，用于有实体类的对象。相比JdbcTemplate也方便很多，主要key比占位符通过位置索引更加灵活。
namedParameterJdbcTemplate_update操作示例
namedParameterJdbcTemplate写法如下
public int insertApiSql(DynamicApiInfo dynamicApiInfo) {
return namedParameterJdbcTemplate.update("INSERT INTO DYNAMIC_API_INFO (content, id, path, type, name) VALUES (:content, :id, :path, :type, :name)", new BeanPropertySqlParameterSource(dynamicApiInfo));
}
注意这里使用了BeanPropertySqlParameterSource 类包装业务对象
BeanPropertySqlParameterSource 是 Spring Framework 提供的一个实用类，用于将 Java 对象的属性映射为命名参数。在 JDBC 操作中，特别是在使用 NamedParameterJdbcTemplate 时，它可以方便地将一个 Java 对象的属性值映射到 SQL 语句中的命名参数。
为了对比，JdbcTemplate写法如下(参数较多时候，顺序非常容易写错)
public void insertDynamicApiInfo(DynamicApiInfo dynamicApiInfo) {
String sql = "INSERT INTO DYNAMIC_API_INFO (content, id, path, type, name) VALUES (?, ?, ?, ?, ?)";
// 直接传递参数对象数组
jdbcTemplate.update(sql,
dynamicApiInfo.getContent(),
dynamicApiInfo.getId(),
dynamicApiInfo.getPath(),
dynamicApiInfo.getType(),
dynamicApiInfo.getName()
);
}
namedParameterJdbcTemplate_sql生成工具
虽然BeanPropertySqlParameterSource 解决了，设置参数的便利性，但是实际上相比orm框架体验还不够好，因为还是要写sql，还有设置“:id”这样的变量，还是挺麻烦的。笔者是比较偷懒的人，借鉴我们通过map可以构造sql语句，实际上我们通过对象反射取到类的字段信息，这样就可以自动打印sql模板了。
代码如下：
public class DynamicSqlGenerator {
public static void generateInsertStatement(Object obj) {
Class<?> clazz = obj.getClass();
Field[] fields = clazz.getDeclaredFields();
String tableName = convertCamelToSnakeCase(clazz.getSimpleName());
String columns = Arrays.stream(fields)
.filter(field -> !java.lang.reflect.Modifier.isStatic(field.getModifiers())) // 过滤掉静态属性
.map(Field::getName)
.map(DynamicSqlGenerator::convertCamelToSnakeCase)
.collect(Collectors.joining(", "));
String values = Arrays.stream(fields)
.filter(field -> !java.lang.reflect.Modifier.isStatic(field.getModifiers())) // 过滤掉静态属性
.map(field -> ":" + field.getName())
.collect(Collectors.joining(", "));
String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columns, values);
System.out.println(sql);
}
public static void generateUpdateStatement(Object obj) {
Class<?> clazz = obj.getClass();
Field[] fields = clazz.getDeclaredFields();
String tableName = convertCamelToSnakeCase(clazz.getSimpleName());
String setClause = Arrays.stream(fields)
.filter(field -> !java.lang.reflect.Modifier.isStatic(field.getModifiers())) // 过滤掉静态属性
.map(field -> convertCamelToSnakeCase(field.getName()) + " = :" + field.getName())
.collect(Collectors.joining(", "));
String sql = String.format("UPDATE %s SET %s WHERE <condition>", tableName, setClause);
System.out.println(sql);
}
public static void generateSelectStatement(Object obj) {
Class<?> clazz = obj.getClass();
Field[] fields = clazz.getDeclaredFields();
String tableName = convertCamelToSnakeCase(clazz.getSimpleName());
String columns = Arrays.stream(fields)
.filter(field -> !java.lang.reflect.Modifier.isStatic(field.getModifiers())) // 过滤掉静态属性
.map(Field::getName)
.map(DynamicSqlGenerator::convertCamelToSnakeCase)
.collect(Collectors.joining(", "));
String conditions = Arrays.stream(fields)
.filter(field -> !java.lang.reflect.Modifier.isStatic(field.getModifiers()))
.map(field -> convertCamelToSnakeCase(field.getName()) + " = :" + field.getName())
.collect(Collectors.joining(" AND ")); // 使用 "AND" 连接所有条件
String sql = String.format("SELECT %s FROM %s WHERE %s", columns, tableName, conditions);
System.out.println(sql);
}
private static String convertCamelToSnakeCase(String input) {
return input.replaceAll("([a-z0-9])([A-Z])", "$1_$2").toLowerCase();
}
public static void main(String[] args) {
DynamicApiInfo dynamicApiInfo = new DynamicApiInfo();
generateInsertStatement(dynamicApiInfo);
generateUpdateStatement(dynamicApiInfo);
generateSelectStatement(dynamicApiInfo);
}
}
sql输出效果:
INSERT INTO dynamic_api_info (id, path, type, name, content, meta_id) VALUES (:id, :path, :type, :name, :content, :metaId)
UPDATE dynamic_api_info SET id = :id, path = :path, type = :type, name = :name, content = :content, meta_id = :metaId WHERE <condition>
SELECT id, path, type, name, content, meta_id FROM dynamic_api_info WHERE id = :id AND path = :path AND type = :type AND name = :name AND content = :content AND meta_id = :metaId
ps: 如果生成的sql不是硬编码,而是在内存中动态生成,那么实际上要做的功能和orm已经在接近了。