SpringBoot_NamedParameterJdbcTemplate介绍及使用

张三疯不疯

已于 2024-03-16 15:53:02 修改

阅读量950

点赞数 21

分类专栏： springboot 文章标签： spring boot JdbcTemplate 动态sql

于 2024-03-11 14:35:37 首次发布

本文链接：https://blog.csdn.net/Myron_007/article/details/136617787

版权

springboot 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

文章目录

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 + " (");

        // 构建列名(注意列名需要,驼峰转下划线)
        Map<String, Object> underscoreColumnValues = convertKeysToUnderscore(columnValues);
        underscoreColumnValues.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());
        // 注意处理日期格式, 暂时使用 "yyyy-MM-dd HH:mm:ss" 转成Date对象,如:"2024-03-08 21:00:00 "
        MapSqlParameterSource parameters = buildParameters(columnValues);
        // 执行插入操作
        namedParameterJdbcTemplate.update(sql.toString(), parameters);
    }

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(toUnderscore(columnName)).append(" = :").append(columnName).append(", "));

        // 删除最后的逗号和空格
        sql.delete(sql.length() - 2, sql.length());

        sql.append(" WHERE id = :id");

        // 添加 ID 参数
        columnValues.put("id", id);

        // 执行更新操作
        MapSqlParameterSource parameters = buildParameters(columnValues);
        namedParameterJdbcTemplate.update(sql.toString(), parameters);
    }

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 -> convertKeysToCamelCase(item))
                //时间格式转日期字符串
                .map(item -> convertDataType(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 -> convertKeysToCamelCase(item))
                .map(item -> convertDataType(item))
                .collect(Collectors.toList());

        // 封装结果和总条数
        Map<String, Object> result = Map.of("rows", convertedList, "total", total, "pageIndex", page, "pageSize", pageSize);

        return result;
    }

4.6 实战完整源码

@Service
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(toUnderscore(columnName)).append(" = :").append(columnName).append(", "));

        // 删除最后的逗号和空格
        sql.delete(sql.length() - 2, sql.length());

        sql.append(" WHERE id = :id");

        // 添加 ID 参数
        columnValues.put("id", id);

        // 执行更新操作
        MapSqlParameterSource parameters = buildParameters(columnValues);
        namedParameterJdbcTemplate.update(sql.toString(), parameters);
    }


    public void insert(String tableName, Map<String, Object> columnValues) {
        StringBuilder sql = new StringBuilder("INSERT INTO " + tableName + " (");

        // 构建列名(注意列名需要,驼峰转下划线)
        Map<String, Object> underscoreColumnValues = convertKeysToUnderscore(columnValues);
        underscoreColumnValues.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());
        // 注意处理日期格式, 暂时使用 "yyyy-MM-dd HH:mm:ss" 转成Date对象,如:"2024-03-08 21:00:00 "
        MapSqlParameterSource parameters = buildParameters(columnValues);
        // 执行插入操作
        namedParameterJdbcTemplate.update(sql.toString(), parameters);
    }


    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 -> convertKeysToCamelCase(item))
                //时间格式转日期字符串
                .map(item -> convertDataType(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 -> convertKeysToCamelCase(item))
                .map(item -> convertDataType(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 -> convertKeysToCamelCase(item)).collect(Collectors.toList());
    }


    private Map<String, Object> convertDataType(Map<String, Object> item) {
        Map<String, Object> convertedItem = new HashMap<>();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        for (Map.Entry<String, Object> entry : item.entrySet()) {
            if (entry.getValue() instanceof Date) {
                // 如果值是时间类型，则转换为指定格式的时间字符串
                convertedItem.put(entry.getKey(), dateFormat.format((Date) entry.getValue()));
            } else {
                convertedItem.put(entry.getKey(), entry.getValue());
            }
        }

        return convertedItem;
    }


    private LocalDateTime parseDateTime(String dateTimeString, String dataFormat) {
        return LocalDateTime.parse(dateTimeString, DateTimeFormatter.ofPattern("yyyyMMddHHmmss"));
    }

    private MapSqlParameterSource buildParameters(Map<String, Object> columnValues) {
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        for (Map.Entry<String, Object> entry : columnValues.entrySet()) {
            if (entry.getValue() instanceof String && isDateString((String) entry.getValue())) {
                // 如果值是时间字符串，则转换为时间对象
                parameters.addValue(entry.getKey(), parseDateString((String) entry.getValue()));
            } else {
                parameters.addValue(entry.getKey(), entry.getValue());
            }
        }
        return parameters;
    }

    private boolean isDateString(String value) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            dateFormat.parse(value);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    private Date parseDateString(String value) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            return dateFormat.parse(value);
        } catch (ParseException e) {
            throw new IllegalArgumentException("Invalid date format: " + value);
        }
    }

    /**
     * 下划线转驼峰
     * @param inputMap
     * @return
     */
    public static Map<String, Object> convertKeysToCamelCase(Map<String, Object> inputMap) {
        Map<String, Object> outputMap = new HashMap<>();
        for (Map.Entry<String, Object> entry : inputMap.entrySet()) {
            String underscoreKey = toCamelCase(entry.getKey());
            outputMap.put(underscoreKey, entry.getValue());
        }
        return outputMap;
    }

    /**
     * 驼峰转下划线
     * @param inputMap
     * @return
     */
    public static Map<String, Object> convertKeysToUnderscore(Map<String, Object> inputMap) {
        Map<String, Object> outputMap = new LinkedHashMap<>();
        for (Map.Entry<String, Object> entry : inputMap.entrySet()) {
            String underscoreKey = toUnderscore(entry.getKey());
            outputMap.put(underscoreKey, entry.getValue());
        }
        return outputMap;
    }

    public static String toCamelCase(String s) {
        if (s == null) {
            return null;
        }

        s = s.toLowerCase();

        StringBuilder sb = new StringBuilder(s.length());
        boolean upperCase = false;
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);

            if (c == '_') {
                upperCase = true;
            } else if (upperCase) {
                sb.append(Character.toUpperCase(c));
                upperCase = false;
            } else {
                sb.append(c);
            }
        }

        return sb.toString();
    }


    /**
     * 将驼峰命名转换为下划线命名
     */
    public static String toUnderscore(String s) {
        if (s == null) {
            return null;
        }

        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < s.length(); i++) {
            char c = s.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append("_").append(Character.toLowerCase(c));
            } else {
                sb.append(c);
            }
        }

        return sb.toString();
    }



}

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已经在接近了。

张三疯不疯

关注

21
点赞
踩
17

收藏

觉得还不错? 一键收藏
1
评论
SpringBoot_NamedParameterJdbcTemplate介绍及使用

本文通过具体业务低代码技术需求出发，分析JdbcTemplate特点和解决动态sql问题的局限性，介绍了NamedParameterJdbcTemplate技术特点，并通过实战演示NamedParameterJdbcTemplate在解析Map生成通用接口操作方法。
复制链接

扫一扫