Spring JDBCTemplate 实战大全：从 CRUD 到高级技巧

星辰聊技术
123 阅读9分钟

一、查询操作

  1. 基本 CRUD 操作 query() ：执行查询操作

List users = jdbcTemplate.query("SELECT * FROM users", new BeanPropertyRowMapper<>(User.class)); 1. queryForObject() ：查询单个对象

int count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class); 1. queryForObject() ：查询JavaBean对象（多列映射）

// 使用RowMapper映射到JavaBean String sql = "SELECT id, name, email FROM users WHERE id = ?"; User user = jdbcTemplate.queryForObject( sql, new BeanPropertyRowMapper<>(User.class), 123);

// 或者使用Lambda表达式 User user = jdbcTemplate.queryForObject( sql, (rs, rowNum) -> { User u = new User(); u.setId(rs.getLong("id")); u.setName(rs.getString("name")); u.setEmail(rs.getString("email")); return u; }, 123);

分页查询实现

public List findUsersByPage(int pageNum, int pageSize) { String sql = "SELECT * FROM users LIMIT ? OFFSET ?"; int offset = (pageNum - 1) * pageSize; return jdbcTemplate.query(sql, new Object[]{pageSize, offset}, new BeanPropertyRowMapper<>(User.class)); }

主要的方法签名有：

T queryForObject(String sql, Class requiredType) T queryForObject(String sql, Class requiredType, Object... args) T queryForObject(String sql, RowMapper rowMapper) T queryForObject(String sql, RowMapper rowMapper, Object... args) T queryForObject(String sql, Object[] args, Class requiredType) T queryForObject(String sql, Object[] args, RowMapper rowMapper)

  1. 多表关联查询处理 public List findOrdersWithUsers() { String sql = "SELECT o.*, u.name as user_name, u.email as user_email " + "FROM orders o JOIN users u ON o.user_id = u.id";

    return jdbcTemplate.query(sql, (rs, rowNum) -> { Order order = new Order(); order.setId(rs.getLong("id")); order.setOrderDate(rs.getDate("order_date"));

     User user = new User();
 user.setName(rs.getString("user_name"));
 user.setEmail(rs.getString("user_email"));
 
 return new OrderWithUser(order, user);

    }); }

  2. 复合主键处理 public User findById(CompositeKey key) { String sql = "SELECT * FROM users WHERE department_id = ? AND employee_id = ?"; return jdbcTemplate.queryForObject(sql, new Object[]{key.getDepartmentId(), key.getEmployeeId()}, new BeanPropertyRowMapper<>(User.class)); }

  3. 复杂查询构建 public class UserQueryCondition { private String name; // 姓名模糊查询条件 private Integer minAge; // 最小年龄条件 private Integer maxAge; // 最大年龄条件 private String email; // 邮箱精确查询条件 private Boolean active; // 是否激活状态 private Date createTimeFrom; // 创建时间范围-开始 private Date createTimeTo; // 创建时间范围-结束

    // getter 和 setter 方法 //setter... }

public List findUsersByCondition(UserQueryCondition condition) { String sql = "SELECT * FROM users WHERE 1=1"; List params = new ArrayList<>();

if (condition.getName() != null) {
    sql += " AND name LIKE ?";
    params.add("%" + condition.getName() + "%");
}
if (condition.getMinAge() != null) {
    sql += " AND age >= ?";
    params.add(condition.getMinAge());
}

return jdbcTemplate.query(sql, params.toArray(), 
    new BeanPropertyRowMapper<>(User.class));

}

  1. 大数据量处理(流式查询) List adultUsers; try (Stream stream = jdbcTemplate.queryForStream( "SELECT * FROM users", new BeanPropertyRowMapper<>(User.class))) {

    adultUsers = stream.filter(u -> u.getAge() > 18) .collect(Collectors.toList()); }

// 现在可以使用adultUsers列表 adultUsers.forEach(user -> { // 处理每个成年用户 });

//------------------------------------------------------------ public Map<String, List> groupUsersByDepartment() { String sql = "SELECT * FROM users";

try (Stream<User> userStream = jdbcTemplate.queryForStream(sql, 
    new BeanPropertyRowMapper<>(User.class))) {
    
    return userStream
        .filter(u -> u.getDepartment() != null)
        .collect(Collectors.groupingBy(User::getDepartment));
}

}

  1. 动态 SQL 构建 public List searchUsers(Map<String, Object> params) { String baseSql = "SELECT * FROM users"; List values = new ArrayList<>();

    if (!params.isEmpty()) { baseSql += " WHERE"; boolean first = true;

     if (params.containsKey("name")) {
     baseSql += first ? " name LIKE ?" : " AND name LIKE ?";
     values.add("%" + params.get("name") + "%");
     first = false;
 }
 
 if (params.containsKey("minAge")) {
     baseSql += first ? " age >= ?" : " AND age >= ?";
     values.add(params.get("minAge"));
 }

    }

    return jdbcTemplate.query(baseSql, values.toArray(), new BeanPropertyRowMapper<>(User.class)); }

  2. 数据库特定功能支持 to_tsvector 和 to_tsquery 是 PostgreSQL 提供的全文搜索(Full Text Search)功能的核心函数，用于实现高效的文本搜索功能。

    3. 基本语法结构：

    SELECT * FROM table_name WHERE to_tsvector([配置], text_column) @@ to_tsquery([配置], search_query)

    使用实例：

    public List findUsersWithFullTextSearch(String keyword) { // PostgreSQL 的全文检索语法 String sql = "SELECT * FROM users WHERE to_tsvector(name) @@ to_tsquery(?)"; return jdbcTemplate.query(sql, new Object[]{keyword}, new BeanPropertyRowMapper<>(User.class)); }

    1. 自定义 SQL 生成器 public class SqlBuilder { private final StringBuilder sql = new StringBuilder(); private final List parameters = new ArrayList<>(); private boolean whereAdded = false;

      public SqlBuilder select(String columns) { sql.append("SELECT ").append(columns); return this; }

      public SqlBuilder from(String table) { sql.append(" FROM ").append(table); return this; }

      public SqlBuilder where(String condition, Object... params) { if (!whereAdded) { sql.append(" WHERE "); whereAdded = true; } else { sql.append(" AND "); } sql.append(condition); Collections.addAll(parameters, params); return this; }

      public SqlBuilder orderBy(String column, boolean ascending) { sql.append(" ORDER BY ").append(column) .append(ascending ? " ASC" : " DESC"); return this; }

      public String getSql() { return sql.toString(); }

      public Object[] getParameters() { return parameters.toArray(); } }

      // 使用示例 SqlBuilder builder = new SqlBuilder() .select("*") .from("users") .where("age > ?", 18) .where("status = ?", "ACTIVE") .orderBy("create_time", false);

      List users = jdbcTemplate.query( builder.getSql(), builder.getParameters(), new BeanPropertyRowMapper<>(User.class));

      1. 动态批处理大小调整 /**

      • 自适应批处理大小的JdbcTemplate扩展 */ public class AdaptiveBatchJdbcTemplate extends JdbcTemplate { private int currentBatchSize = 100; // 初始批次大小 private long lastAdjustmentTime = System.currentTimeMillis();

        public AdaptiveBatchJdbcTemplate(DataSource dataSource) { super(dataSource); }

        /**

        • 执行自适应批处理插入

        • @param dataList 要插入的数据列表 */ public void adaptiveBatchInsert(List dataList, BatchOperation operation) { int totalSize = dataList.size(); int processed = 0;

          while (processed < totalSize) { // 计算当前批次大小 int actualBatchSize = Math.min(currentBatchSize, totalSize - processed);

           // 获取当前批次数据
 List<T> batchData = dataList.subList(processed, processed + actualBatchSize);
 
 // 执行批处理
 long startTime = System.currentTimeMillis();
 int[] result = batchUpdate(
     operation.getSql(),
     new BatchPreparedStatementSetter() {
         @Override
         public void setValues(PreparedStatement ps, int i) throws SQLException {
             operation.setValues(ps, batchData.get(i));
         }

         @Override
         public int getBatchSize() {
             return batchData.size();
         }
     });
 
 // 调整批次大小
 long duration = System.currentTimeMillis() - startTime;
 adjustBatchSize(duration, actualBatchSize);
 
 System.out.printf("已处理: %d/%d, 当前批次大小: %d, 耗时: %dms%n",
     processed + actualBatchSize, totalSize, currentBatchSize, duration);
 
 processed += actualBatchSize;

          } }

        /**

        • 根据执行时间动态调整批次大小 */ private void adjustBatchSize(long duration, int processedSize) { long now = System.currentTimeMillis(); if (now - lastAdjustmentTime > 60000) { // 每分钟最多调整一次 if (duration > 5000) { // 执行时间过长，减少批次大小 currentBatchSize = Math.max(10, currentBatchSize / 2); System.out.println("【调整】减小批次大小至: " + currentBatchSize); } else if (duration < 100 && processedSize == currentBatchSize) { // 执行时间很短且当前批次是满载，增加批次大小 currentBatchSize = Math.min(1000, currentBatchSize * 2); System.out.println("【调整】增大批次大小至: " + currentBatchSize); } lastAdjustmentTime = now; } }

        /**

        • 批处理操作接口 */ public interface BatchOperation { String getSql(); void setValues(PreparedStatement ps, T item) throws SQLException; } }

      //--------------------------------------------------------使用 // 1. 创建自适应批处理模板 AdaptiveBatchJdbcTemplate adaptiveTemplate = new AdaptiveBatchJdbcTemplate(dataSource);

      // 2. 准备测试数据 List users = generateTestUsers(5000); // 生成5000个测试用户

      // 3. 执行批处理插入 adaptiveTemplate.adaptiveBatchInsert(users, new AdaptiveBatchJdbcTemplate.BatchOperation() { @Override public String getSql() { return "INSERT INTO users(name, age, email) VALUES(?, ?, ?)"; }

      @Override
public void setValues(PreparedStatement ps, User user) throws SQLException {
    ps.setString(1, user.getName());
    ps.setInt(2, user.getAge());
    ps.setString(3, user.getEmail());
}

      });

      1. 大数据量分批次处理避免内存溢出 /**

      • 大数据量批处理服务示例 */ public class LargeDataProcessor {

        private final JdbcTemplate jdbcTemplate;

        // 批处理大小，可根据实际情况调整 private static final int DEFAULT_BATCH_SIZE = 1000;

        public LargeDataProcessor(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; }

        /**

        • 处理用户表中的所有数据（分批次）

        • 使用事务确保处理过程的原子性 */ @Transactional public void processAllUsers() { int batchSize = DEFAULT_BATCH_SIZE; int offset = 0; List batch;

          // 记录处理开始时间 long startTime = System.currentTimeMillis(); int totalProcessed = 0;

          do { // 1. 分页查询数据 batch = fetchUsersBatch(batchSize, offset);

           // 2. 处理当前批次数据
 if (!batch.isEmpty()) {
     processUserBatch(batch);
     totalProcessed += batch.size();
 }
 
 // 3. 更新偏移量
 offset += batchSize;

          } while (!batch.isEmpty()); // 4. 循环直到没有数据

          // 记录处理结果 long duration = System.currentTimeMillis() - startTime; System.out.printf("处理完成！共处理 %d 条记录，耗时 %d 毫秒%n", totalProcessed, duration); }

        /**

        • 分页查询用户数据

        • @param limit 每页大小

        • @param offset 偏移量

        • @return 用户列表 */ private List fetchUsersBatch(int limit, int offset) { String sql = "SELECT id, name, email, age FROM users ORDER BY id LIMIT ? OFFSET ?";

          // 使用BeanPropertyRowMapper自动映射结果到User对象 return jdbcTemplate.query( sql, new Object[]{limit, offset}, new BeanPropertyRowMapper<>(User.class) ); }

        /**

        • 处理单个批次的用户数据

        • @param batch 当前批次的用户列表 */ private void processUserBatch(List batch) { // 这里可以替换为实际的业务逻辑 // 示例：简单地打印处理进度 System.out.printf("正在处理批次，大小: %d, 第一个用户ID: %d%n", batch.size(), batch.get(0).getId());

          // 实际业务处理示例： // 1. 数据转换 // 2. 调用外部服务 // 3. 写入其他系统等 for (User user : batch) { // 处理每个用户的逻辑 processSingleUser(user); } }

        /**

        • 处理单个用户
        • @param user 用户对象 */ private void processSingleUser(User user) { // 示例处理逻辑： // 1. 数据校验 // 2. 业务处理（如：年龄分类） // 3. 更新用户（示例） }

        /**

        • 根据年龄返回年龄段分类 */ private String getAgeGroup(int age) { if (age < 18) return "未成年"; if (age < 30) return "青年"; if (age < 50) return "中年"; return "老年"; }

        /**

        • 更新用户年龄段信息 */ private void updateUserAgeGroup(long userId, String ageGroup) { String sql = "UPDATE users SET age_group = ? WHERE id = ?"; jdbcTemplate.update(sql, ageGroup, userId); }

        /**

        • 用户实体类 */ public static class User { private long id; private String name; private String email; private int age; private String ageGroup;

          // getters and setters } }

      1. 结果集处理 RowMapper：自定义结果集映射 List users = jdbcTemplate.query("SELECT * FROM users", (rs, rowNum) -> { User user = new User(); user.setId(rs.getLong("id")); user.setName(rs.getString("name")); return user; });

      ResultSetExtractor：复杂结果集处理 Map<Long, User> userMap = jdbcTemplate.query("SELECT * FROM users", rs -> { Map<Long, User> map = new HashMap<>(); while (rs.next()) { User user = new User(); user.setId(rs.getLong("id")); user.setName(rs.getString("name")); map.put(user.getId(), user); } return map; });

      1. 自定义 RowMapper 复用 public class GenericRowMapper implements RowMapper { private final Class targetClass;

        public GenericRowMapper(Class targetClass) { this.targetClass = targetClass; }

        @Override public T mapRow(ResultSet rs, int rowNum) throws SQLException { T obj = targetClass.newInstance(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
    String columnName = metaData.getColumnLabel(i);
    Object value = rs.getObject(i);
    
    try {
        Field field = targetClass.getDeclaredField(columnName);
        field.setAccessible(true);
        field.set(obj, value);
    } catch (NoSuchFieldException e) {
        // 忽略不存在的字段
    }
}

return obj;

        } }

      // 使用示例 List users = jdbcTemplate.query( "SELECT * FROM users", new GenericRowMapper<>(User.class));

      二、增删改操作

      1. 更新操作 update() ：执行增删改操作 jdbcTemplate.update("INSERT INTO users(name, age) VALUES(?, ?)", "张三", 25);

      2. 批处理操作 batchUpdate() ：执行批量操作 public int[] batchInsertUsers(List users) { List<Object[]> batchArgs = new ArrayList<>(); for (User user : users) { batchArgs.add(new Object[]{ user.getName(), user.getAge(), user.getEmail() }); }

        return jdbcTemplate.batchUpdate( "INSERT INTO users(name, age, email) VALUES(?, ?, ?)", batchArgs); }

      3. 命名参数支持 NamedParameterJdbcTemplate：使用命名参数而非位置参数 Map<String, Object> params = new HashMap<>(); params.put("name", "张三"); params.put("age", 25); namedJdbcTemplate.update("INSERT INTO users(name, age) VALUES(:name, :age)", params);

      4. 高级批处理 批量插入并返回生成的主键 public int[] batchInsert(List users) { return jdbcTemplate.batchUpdate( "INSERT INTO users(name, age) VALUES(?, ?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { User user = users.get(i); ps.setString(1, user.getName()); ps.setInt(2, user.getAge()); } @Override public int getBatchSize() { return users.size(); } }); }

      5. 自定义类型处理 处理 JSON 等复杂类型 //-------------------自定义类型处理器 jdbcTemplate.update( "INSERT INTO products(id, details) VALUES(?, ?::jsonb)", product.getId(), new SqlParameterValue(Types.OTHER, product.getDetailsJson()));

      //-------------------使用Java对象自动转换 public void insertProductWithObject(Product product) { String sql = "INSERT INTO products(id, details) VALUES(?, ?::jsonb)";

      // 使用Jackson将对象转为JSON字符串
ObjectMapper mapper = new ObjectMapper();
String json = mapper.writeValueAsString(product.getDetails());

jdbcTemplate.update(sql, 
    product.getId(),
    new SqlParameterValue(Types.OTHER, json));

      }

      //---------------------查询并解析JSON public Product getProductWithJsonDetails(long id) { String sql = "SELECT id, name, details FROM products WHERE id = ?";

      return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
    Product product = new Product();
    product.setId(rs.getLong("id"));
    product.setName(rs.getString("name"));
    
    // 解析JSON字段
    String json = rs.getString("details");
    ObjectMapper mapper = new ObjectMapper();
    ProductDetails details = mapper.readValue(json, ProductDetails.class);
    product.setDetails(details);
    
    return product;
}, id);

      }

      //-------------------------电商产品服务案例 @Repository public class ProductRepository { private final JdbcTemplate jdbcTemplate; private final ObjectMapper objectMapper;

      public ProductRepository(JdbcTemplate jdbcTemplate, ObjectMapper objectMapper) {
    this.jdbcTemplate = jdbcTemplate;
    this.objectMapper = objectMapper;
}

@Transactional
public void saveProduct(Product product) {
    String sql = "INSERT INTO products(id, name, price, attributes) VALUES(?, ?, ?, ?::jsonb)";
    
    try {
        String jsonAttributes = objectMapper.writeValueAsString(product.getAttributes());
        
        jdbcTemplate.update(sql,
            product.getId(),
            product.getName(),
            product.getPrice(),
            new SqlParameterValue(Types.OTHER, jsonAttributes));
    } catch (JsonProcessingException e) {
        throw new DataAccessException("Failed to serialize product attributes", e) {};
    }
}

public Product findById(long id) {
    String sql = "SELECT id, name, price, attributes FROM products WHERE id = ?";
    
    return jdbcTemplate.queryForObject(sql, (rs, rowNum) -> {
        Product product = new Product();
        product.setId(rs.getLong("id"));
        product.setName(rs.getString("name"));
        product.setPrice(rs.getBigDecimal("price"));
        
        try {
            ProductAttributes attributes = objectMapper.readValue(
                rs.getString("attributes"), 
                ProductAttributes.class);
            product.setAttributes(attributes);
        } catch (JsonProcessingException e) {
            throw new DataAccessException("Failed to deserialize attributes", e) {};
        }
        
        return product;
    }, id);
}

public List<Product> findByAttribute(String key, String value) {
    String sql = """
        SELECT id, name, price 
        FROM products 
        WHERE attributes @> ?::jsonb
        """;
    
    String jsonCriteria = String.format("{\"%s\": \"%s\"}", key, value);
    
    return jdbcTemplate.query(sql, 
        (rs, rowNum) -> {
            Product product = new Product();
            product.setId(rs.getLong("id"));
            product.setName(rs.getString("name"));
            product.setPrice(rs.getBigDecimal("price"));
            return product;
        },
        jsonCriteria);
}

      }

      1. 乐观锁实现(本控制) @Transactional public boolean updateWithOptimisticLock(User user) { int affected = jdbcTemplate.update( "UPDATE users SET name = ?, age = ?, version = version + 1 " + "WHERE id = ? AND version = ?", user.getName(), user.getAge(), user.getId(), user.getVersion());

        if (affected == 0) { throw new OptimisticLockingFailureException("数据已被其他事务修改"); } return affected > 0; }

      三、 存储过程操作

      1. 调用存储过程 call() ：调用存储过程 jdbcTemplate.call(conn -> { CallableStatement cs = conn.prepareCall("{call proc_name(?, ?)}"); cs.setString(1, param1); cs.registerOutParameter(2, Types.INTEGER); return cs; }, Collections.emptyList());

      2. 存储过程高级调用 带输出参数的存储过程调用 public int callCalculateUserStatsSafe(int userId) throws DataAccessException { try { SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate) .withProcedureName("calculate_user_stats") // 可选的架构名称（某些数据库需要） .withSchemaName("public") // 声明返回结果集（如果有） .returningResultSet("result_set", new BeanPropertyRowMapper<>(UserStat.class)) .declareParameters( new SqlParameter("user_id", Types.INTEGER), new SqlOutParameter("result", Types.INTEGER), // 可以声明更多参数 new SqlOutParameter("message", Types.VARCHAR));

         // 执行调用
 Map<String, Object> out = jdbcCall.execute(userId);
 
 // 获取多个输出参数
 int result = (int) out.get("result");
 String message = (String) out.get("message");
 List<UserStat> stats = (List<UserStat>) out.get("result_set");
 
 log.debug("存储过程返回消息: {}", message);
 return result;

        } catch (DataAccessException e) { log.error("调用存储过程calculate_user_stats失败，用户ID: {}", userId, e); throw new ServiceException("统计计算失败", e); } }

      四、DDL操作

      1. 执行 DDL 语句 execute() ：执行任意 SQL 语句 jdbcTemplate.execute("CREATE TABLE users (id INT, name VARCHAR(100))");
      2. 数据库元数据操作(表结构信息)

      /**

      • 打印数据库所有表的结构元数据信息

      • 包括表名和每张表的所有列名及数据类型 */ public void printTableMetaData() { // 使用JdbcTemplate的execute方法执行连接回调 jdbcTemplate.execute((ConnectionCallback) conn -> { // 1. 获取数据库元数据对象 DatabaseMetaData metaData = conn.getMetaData();

         /*
  * 2. 获取所有表的信息
  * 参数说明：
  * - catalog: 数据库目录名，null表示忽略
  * - schemaPattern: 模式名称模式，null表示忽略
  * - tableNamePattern: 表名模式，"%"表示所有表
  * - types: 表类型数组，这里只查询普通表
  */
 ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
 
 // 3. 遍历所有表
 while (tables.next()) {
     // 获取表名（TABLE_NAME是标准元数据列名）
     String tableName = tables.getString("TABLE_NAME");
     System.out.println("Table: " + tableName);
     
     /*
      * 4. 获取当前表的所有列信息
      * 参数说明：
      * - catalog: 数据库目录名，null表示忽略
      * - schemaPattern: 模式名称模式，null表示忽略
      * - tableNamePattern: 表名，使用当前遍历到的表名
      * - columnNamePattern: 列名模式，null表示所有列
      */
     ResultSet columns = metaData.getColumns(null, null, tableName, null);
     
     // 5. 遍历当前表的所有列
     while (columns.next()) {
         // 打印列名和数据类型（TYPE_NAME是标准元数据列名）
         System.out.println("  Column: " + columns.getString("COLUMN_NAME") + 
                          " Type: " + columns.getString("TYPE_NAME") +
                          " Size: " + columns.getInt("COLUMN_SIZE") +
                          " Nullable: " + (columns.getInt("NULLABLE") == DatabaseMetaData.columnNullable ? "YES" : "NO"));
     }
     
     // 6. 关闭列结果集
     columns.close();
 }
 
 // 7. 关闭表结果集
 tables.close();
 
 // 返回null因为ConnectionCallback需要返回Void类型
 return null;

        }); }

avatar
文章
阅读
粉丝