SpringBoot_NamedParameterJdbcTemplate介绍及使用

2024-03-12 12:12
文章标签 java 使用 springboot 介绍 spring namedparameterjdbctemplate

本文主要是介绍SpringBoot_NamedParameterJdbcTemplate介绍及使用，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

    • 1.背景
    • 2.需求分析
    • 3.技术分析
      • 3.1 JdbcTemplate
      • 3.2 NamedParameterJdbcTemplate
      • 3.3 JdbcTemplate与NamedParameterJdbcTemplate 对比总结
    • 4. 实战：简易通用curd接口开发
      • 4.1 通用新增接口
      • 4.2 通用修改接口
      • 4.3 通用删除接口
      • 4.4 通用列表接口
      • 4.5 通用分页查询接口
      • 4.6 实战完整源码
    • 5. 其他使用对比总结
      • namedParameterJdbcTemplate_update操作示例
      • namedParameterJdbcTemplate_sql生成工具

1.背景

最近开始研究一些低代码后端实现的功能，因为想采用不生成具体代码方式(对比代码生成器 + 持续构建是实际生成代码方式)，来实现业务对象常规功能。因此不能用传统依赖实体类实现映射的orm框架，如mybatis。需要借助更底层的jdbc操作来实现通用的操作。但最原生jdbc写法在设置参数和处理数据结果操作又太过繁琐。在借用spring的JdbcTemplate工具时候，发现了NamedParameterJdbcTemplate可以实现这种特殊需求。本文在此做一个记录和总结。

2.需求分析

首先，具体技术需求分析如下：

  1. 如果不通过具体类来实现任意数据模型的curd，除了指定具体的modelId(可以是表名)必须使用类似Map<String,Object>的通用数据结构。key指定数据字段名称value代表值。
  2. 引擎需要自动识别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对于实现，目标功能存在以下缺陷

  1. 使用占位符，通过位置索引传递参数，而对于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 对比总结

  1. JdbcTemplate使用占位符，通过位置索引传递参数。NamedParameterJdbcTemplate使用命名参数，通过参数名传递参数。
  2. 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 通用分页查询接口

分页接口相比列表接口需要额外处理以下细节

  1. 接受分页入参(pageIndex,pageSize)
  2. 计算记录总数(用于前端用于计算总页数)
  3. 封装分页结果
{"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 {@Autowiredprivate 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 dateLocalDateTime 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已经在接近了。

这篇关于SpringBoot_NamedParameterJdbcTemplate介绍及使用的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/801220。

相关文章

Java实现本地缓存的常用方案介绍

Java实现本地缓存的常用方案介绍

《Java实现本地缓存的常用方案介绍》本地缓存的代表技术主要有HashMap,GuavaCache,Caffeine和Encahche,这篇文章主要来和大家聊聊java利用这些技术分别实现本地缓存的方... 目录本地缓存实现方式HashMapConcurrentHashMapGuava CacheCaffe
阅读更多...
SpringBoot整合Sa-Token实现RBAC权限模型的过程解析

SpringBoot整合Sa-Token实现RBAC权限模型的过程解析

《SpringBoot整合Sa-Token实现RBAC权限模型的过程解析》：本文主要介绍SpringBoot整合Sa-Token实现RBAC权限模型的过程解析,本文给大家介绍的非常详细,对大家的学... 目录前言一、基础概念1.1 RBAC模型核心概念1.2 Sa-Token核心功能1.3 环境准备二、表结
阅读更多...
MySQL 事务的概念及ACID属性和使用详解

MySQL 事务的概念及ACID属性和使用详解

《MySQL事务的概念及ACID属性和使用详解》MySQL通过多线程实现存储工作,因此在并发访问场景中,事务确保了数据操作的一致性和可靠性,下面通过本文给大家介绍MySQL事务的概念及ACID属性和... 目录一、什么是事务二、事务的属性及使用2.1 事务的 ACID 属性2.2 为什么存在事务2.3 事务
阅读更多...
eclipse如何运行springboot项目

eclipse如何运行springboot项目

《eclipse如何运行springboot项目》：本文主要介绍eclipse如何运行springboot项目问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目js录当在eclipse启动spring boot项目时出现问题解决办法1.通过cmd命令行2.在ecl
阅读更多...
Java中的Closeable接口及常见问题

Java中的Closeable接口及常见问题

《Java中的Closeable接口及常见问题》Closeable是Java中的一个标记接口,用于表示可以被关闭的对象,它定义了一个标准的方法来释放对象占用的系统资源,下面给大家介绍Java中的Clo... 目录1. Closeable接口概述2. 主要用途3. 实现类4. 使用方法5. 实现自定义Clos
阅读更多...
Jvm sandbox mock机制的实践过程

Jvm sandbox mock机制的实践过程

《Jvmsandboxmock机制的实践过程》：本文主要介绍Jvmsandboxmock机制的实践过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录一、背景二、定义一个损坏的钟1、 Springboot工程中创建一个Clock类2、 添加一个Controller
阅读更多...
使用Python实现网页表格转换为markdown

使用Python实现网页表格转换为markdown

《使用Python实现网页表格转换为markdown》在日常工作中,我们经常需要从网页上复制表格数据,并将其转换成Markdown格式,本文将使用Python编写一个网页表格转Markdown工具,需... 在日常工作中，我们经常需要从网页上复制表格数据，并将其转换成Markdown格式，以便在文档、邮件或
阅读更多...
Python使用pynput模拟实现键盘自动输入工具

Python使用pynput模拟实现键盘自动输入工具

《Python使用pynput模拟实现键盘自动输入工具》在日常办公和软件开发中,我们经常需要处理大量重复的文本输入工作,所以本文就来和大家介绍一款使用Python的PyQt5库结合pynput键盘控制... 目录概述：当自动化遇上可视化功能全景图核心功能矩阵技术栈深度效果展示使用教程四步操作指南核心代码解析
阅读更多...
SpringBoot实现文件记录日志及日志文件自动归档和压缩

SpringBoot实现文件记录日志及日志文件自动归档和压缩

《SpringBoot实现文件记录日志及日志文件自动归档和压缩》Logback是Java日志框架,通过Logger收集日志并经Appender输出至控制台、文件等,SpringBoot配置logbac... 目录1、什么是Logback2、SpringBoot实现文件记录日志，日志文件自动归档和压缩2.1、
阅读更多...
MQTT SpringBoot整合实战教程

MQTT SpringBoot整合实战教程

《MQTTSpringBoot整合实战教程》：本文主要介绍MQTTSpringBoot整合实战教程,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考... 目录MQTT-SpringBoot创建简单 SpringBoot 项目导入必须依赖增加MQTT相关配置编写
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2