服务端开发

Appearance

Spring JDBC 核心类详解：告别原生 JDBC 的繁琐操作 🚀 ​

为什么需要 Spring JDBC？ ​

想象一下，你正在用原生 JDBC 开发一个简单的用户管理系统。每次数据库操作都需要：

  1. 手动获取数据库连接
  2. 创建 PreparedStatement
  3. 设置参数
  4. 执行 SQL
  5. 处理结果集
  6. 手动关闭资源
  7. 处理各种异常

这样的代码不仅冗长，还容易出错。Spring JDBC 的出现就是为了解决这些痛点！

IMPORTANT

Spring JDBC 的核心哲学：让开发者专注于业务逻辑，而不是底层的资源管理和异常处理。

Spring JDBC 核心架构 ​

1. JdbcTemplate：Spring JDBC 的核心 💎 ​

1.1 JdbcTemplate 解决的核心问题 ​

kotlin
// 传统 JDBC 代码 - 繁琐且容易出错
fun getUserCount(): Int {
    var connection: Connection? = null
    var statement: PreparedStatement? = null
    var resultSet: ResultSet? = null
    
    try {
        connection = dataSource.connection 
        statement = connection.prepareStatement("SELECT COUNT(*) FROM users") 
        resultSet = statement.executeQuery() 
        
        return if (resultSet.next()) {
            resultSet.getInt(1) 
        } else {
            0
        }
    } catch (e: SQLException) {
        throw RuntimeException("数据库操作失败", e) 
    } finally {
        // 手动关闭资源 - 容易遗漏
        resultSet?.close() 
        statement?.close() 
        connection?.close() 
    }
}
kotlin
// Spring JdbcTemplate - 简洁优雅
@Repository
class UserRepository(private val jdbcTemplate: JdbcTemplate) {
    
    fun getUserCount(): Int {
        return jdbcTemplate.queryForObject( 
            "SELECT COUNT(*) FROM users", 
            Int::class.java
        )!!
    }
}

TIP

对比可以看出，JdbcTemplate 将原本 20+ 行的代码简化为 3 行，同时自动处理了资源管理和异常转换！

1.2 JdbcTemplate 的核心功能 ​

查询操作（SELECT） ​

kotlin
@Repository
class ActorRepository(private val jdbcTemplate: JdbcTemplate) {
    
    // 1. 查询单个值
    fun getActorCount(): Int {
        return jdbcTemplate.queryForObject( 
            "SELECT COUNT(*) FROM t_actor", 
            Int::class.java
        )!!
    }
    
    // 2. 带参数的查询
    fun getActorCountByName(firstName: String): Int {
        return jdbcTemplate.queryForObject( 
            "SELECT COUNT(*) FROM t_actor WHERE first_name = ?",
            arrayOf(firstName),
            Int::class.java
        )!!
    }
    
    // 3. 查询单个对象
    fun getActorById(id: Long): Actor? {
        return jdbcTemplate.queryForObject( 
            "SELECT first_name, last_name FROM t_actor WHERE id = ?",
            arrayOf(id)
        ) { rs, _ ->
            Actor(
                rs.getString("first_name"),
                rs.getString("last_name")
            )
        }
    }
    
    // 4. 查询对象列表
    fun getAllActors(): List<Actor> {
        return jdbcTemplate.query( 
            "SELECT first_name, last_name FROM t_actor"
        ) { rs, _ ->
            Actor(
                rs.getString("first_name"),
                rs.getString("last_name")
            )
        }
    }
    
    // 5. 复用 RowMapper - 最佳实践
    private val actorRowMapper = RowMapper<Actor> { rs, _ ->
        Actor(
            rs.getString("first_name"),
            rs.getString("last_name")
        )
    }
    
    fun findAllActorsOptimized(): List<Actor> {
        return jdbcTemplate.query( 
            "SELECT first_name, last_name FROM t_actor", 
            actorRowMapper
        )
    }
}

更新操作（INSERT、UPDATE、DELETE） ​

kotlin
@Repository
class ActorRepository(private val jdbcTemplate: JdbcTemplate) {
    
    // 1. 插入数据
    fun insertActor(firstName: String, lastName: String): Int {
        return jdbcTemplate.update( 
            "INSERT INTO t_actor (first_name, last_name) VALUES (?, ?)",
            firstName, lastName
        )
    }
    
    // 2. 更新数据
    fun updateActorName(id: Long, lastName: String): Int {
        return jdbcTemplate.update( 
            "UPDATE t_actor SET last_name = ? WHERE id = ?",
            lastName, id
        )
    }
    
    // 3. 删除数据
    fun deleteActor(id: Long): Int {
        return jdbcTemplate.update( 
            "DELETE FROM t_actor WHERE id = ?",
            id
        )
    }
    
    // 4. 批量操作示例
    fun batchInsertActors(actors: List<Actor>): IntArray {
        return jdbcTemplate.batchUpdate( 
            "INSERT INTO t_actor (first_name, last_name) VALUES (?, ?)"
        ) { ps, actor ->
            ps.setString(1, actor.firstName)
            ps.setString(2, actor.lastName)
        }
    }
}

其他操作 ​

kotlin
@Repository
class DatabaseRepository(private val jdbcTemplate: JdbcTemplate) {
    
    // 1. 执行 DDL 语句
    fun createTable() {
        jdbcTemplate.execute( 
            "CREATE TABLE mytable (id INTEGER, name VARCHAR(100))"
        )
    }
    
    // 2. 调用存储过程
    fun refreshActorsSummary(unionId: Long) {
        jdbcTemplate.update( 
            "CALL SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
            unionId
        )
    }
}

1.3 JdbcTemplate 最佳实践 ​

配置方式 ​

kotlin
@Repository
class JdbcActorRepository(dataSource: DataSource) : ActorRepository {
    
    private val jdbcTemplate = JdbcTemplate(dataSource) 
    
    override fun findAllActors(): List<Actor> {
        return jdbcTemplate.query(
            "SELECT first_name, last_name FROM t_actor",
            actorRowMapper
        )
    }
}
kotlin
@Configuration
class DatabaseConfig {
    
    @Bean
    fun dataSource(): DataSource {
        return HikariDataSource().apply { 
            jdbcUrl = "jdbc:h2:mem:testdb"
            username = "sa"
            password = ""
        }
    }
    
    @Bean
    fun jdbcTemplate(dataSource: DataSource): JdbcTemplate {
        return JdbcTemplate(dataSource) 
    }
}

NOTE

线程安全性：JdbcTemplate 实例是线程安全的，可以在多个 DAO 之间共享同一个实例。

2. NamedParameterJdbcTemplate：告别问号参数 🎯 ​

2.1 解决的问题 ​

传统的 JdbcTemplate 使用 ? 占位符，当参数较多时容易出错：

kotlin
// 参数顺序容易搞错
jdbcTemplate.update(
    "UPDATE users SET name = ?, email = ?, age = ?, city = ? WHERE id = ?",
    name, email, age, city, id 
)
kotlin
// 参数名称明确，不易出错
namedParameterJdbcTemplate.update(
    "UPDATE users SET name = :name, email = :email, age = :age, city = :city WHERE id = :id",
    mapOf(
        "name" to name,
        "email" to email, 
        "age" to age,
        "city" to city,
        "id" to id
    )
)

2.2 使用方式 ​

kotlin
@Repository
class UserRepository(dataSource: DataSource) {
    
    private val namedParameterJdbcTemplate = NamedParameterJdbcTemplate(dataSource)
    
    // 1. 使用 Map 传递参数
    fun getUserByName(firstName: String): Int {
        val sql = "SELECT COUNT(*) FROM t_user WHERE first_name = :firstName"
        val params = mapOf("firstName" to firstName) 
        
        return namedParameterJdbcTemplate.queryForObject(
            sql, params, Int::class.java
        )!!
    }
    
    // 2. 使用 MapSqlParameterSource
    fun getUserByNameOptimized(firstName: String): Int {
        val sql = "SELECT COUNT(*) FROM t_user WHERE first_name = :firstName"
        val params = MapSqlParameterSource("firstName", firstName) 
        
        return namedParameterJdbcTemplate.queryForObject(
            sql, params, Int::class.java
        )!!
    }
    
    // 3. 使用 BeanPropertySqlParameterSource - 最强大的方式
    fun insertUser(user: User): Int {
        val sql = """
            INSERT INTO t_user (first_name, last_name, email, age) 
            VALUES (:firstName, :lastName, :email, :age)
        """.trimIndent()
        
        val params = BeanPropertySqlParameterSource(user) 
        
        return namedParameterJdbcTemplate.update(sql, params)
    }
}

// 用户实体类
data class User(
    val firstName: String,
    val lastName: String,
    val email: String,
    val age: Int
)

TIP

BeanPropertySqlParameterSource 会自动将对象的属性名映射为 SQL 参数名，非常适合实体对象的批量操作！

3. JdbcClient：统一的现代化 API ✨ ​

3.1 为什么需要 JdbcClient？ ​

Spring 6.1 引入了 JdbcClient，提供了更加现代化和流畅的 API：

kotlin
@Repository
class ModernUserRepository(dataSource: DataSource) {
    
    private val jdbcClient = JdbcClient.create(dataSource) 
    
    // 1. 位置参数查询
    fun countUsersByFirstName(firstName: String): Int {
        return jdbcClient
            .sql("SELECT COUNT(*) FROM t_user WHERE first_name = ?") 
            .param(firstName) 
            .query(Int::class.java) 
            .single() 
    }
    
    // 2. 命名参数查询
    fun countUsersByFirstNameNamed(firstName: String): Int {
        return jdbcClient
            .sql("SELECT COUNT(*) FROM t_user WHERE first_name = :firstName") 
            .param("firstName", firstName) 
            .query(Int::class.java) 
            .single() 
    }
    
    // 3. 对象映射查询
    fun getAllUsers(): List<User> {
        return jdbcClient
            .sql("SELECT first_name, last_name, email, age FROM t_user") 
            .query(User::class.java) 
            .list() 
    }
    
    // 4. 单个对象查询
    fun getUserById(id: Long): User? {
        return jdbcClient
            .sql("SELECT first_name, last_name, email, age FROM t_user WHERE id = ?") 
            .param(id) 
            .query(User::class.java) 
            .optional() 
            .orElse(null)
    }
    
    // 5. 自定义 RowMapper
    fun getUsersWithCustomMapping(): List<User> {
        return jdbcClient
            .sql("SELECT first_name, last_name, email, age FROM t_user") 
            .query { rs, _ ->
                User(
                    rs.getString("first_name"),
                    rs.getString("last_name"),
                    rs.getString("email"),
                    rs.getInt("age")
                )
            } 
            .list() 
    }
    
    // 6. 更新操作
    fun insertUser(user: User): Int {
        return jdbcClient
            .sql("INSERT INTO t_user (first_name, last_name, email, age) VALUES (?, ?, ?, ?)") 
            .param(user.firstName) 
            .param(user.lastName) 
            .param(user.email) 
            .param(user.age) 
            .update() 
    }
    
    // 7. 使用对象作为参数源
    fun insertUserWithParamSource(user: User): Int {
        return jdbcClient
            .sql("INSERT INTO t_user (first_name, last_name, email, age) VALUES (:firstName, :lastName, :email, :age)") 
            .paramSource(user) 
            .update() 
    }
}

3.2 JdbcClient 的优势 ​

NOTE

JdbcClient 的设计理念

  • 🔗 流畅的链式调用：更符合现代 Kotlin/Java 编程习惯
  • 🎯 统一的 API：不再需要区分 JdbcTemplate 和 NamedParameterJdbcTemplate
  • 🚀 更好的类型安全：编译时就能发现更多错误
  • 📦 自动对象映射：支持 Record、Data Class 等现代 Java/Kotlin 特性

4. 异常处理：SQLExceptionTranslator 🛡️ ​

4.1 为什么需要异常转换？ ​

原生 JDBC 抛出的 SQLException 是检查异常，且错误信息不够友好。Spring 提供了统一的异常体系：

4.2 自定义异常转换器 ​

kotlin
// 自定义异常转换器
class CustomSQLErrorCodesTranslator : SQLErrorCodeSQLExceptionTranslator() {
    
    override fun customTranslate(
        task: String, 
        sql: String?, 
        sqlEx: SQLException
    ): DataAccessException? {
        
        return when (sqlEx.errorCode) {
            -12345 -> DeadlockLoserDataAccessException(task, sqlEx) 
            -54321 -> DuplicateKeyException("重复的主键", sqlEx) 
            else -> null // 使用默认转换器
        }
    }
}

// 在 Repository 中使用自定义转换器
@Repository
class CustomUserRepository(dataSource: DataSource) {
    
    private val jdbcTemplate = JdbcTemplate(dataSource).apply {
        exceptionTranslator = CustomSQLErrorCodesTranslator().apply { 
            this.dataSource = dataSource
        }
    }
    
    fun updateUser(id: Long, name: String) {
        try {
            jdbcTemplate.update(
                "UPDATE users SET name = ? WHERE id = ?", 
                name, id
            )
        } catch (e: DeadlockLoserDataAccessException) {
            // 处理死锁异常
            logger.warn("检测到死锁，正在重试...", e) 
            // 实现重试逻辑
        } catch (e: DuplicateKeyException) {
            // 处理重复键异常
            logger.error("用户名已存在", e) 
            throw BusinessException("用户名已存在")
        }
    }
}

5. 实战案例：完整的用户管理系统 🎯 ​

让我们通过一个完整的用户管理系统来展示 Spring JDBC 的强大功能：

完整的用户管理系统实现
kotlin
// 1. 用户实体
data class User(
    val id: Long? = null,
    val firstName: String,
    val lastName: String,
    val email: String,
    val age: Int,
    val createdAt: LocalDateTime = LocalDateTime.now()
)

// 2. 用户仓储接口
interface UserRepository {
    fun save(user: User): User
    fun findById(id: Long): User?
    fun findAll(): List<User>
    fun findByEmail(email: String): User?
    fun update(user: User): Boolean
    fun deleteById(id: Long): Boolean
    fun count(): Long
}

// 3. Spring JDBC 实现
@Repository
class JdbcUserRepository(dataSource: DataSource) : UserRepository {
    
    private val jdbcClient = JdbcClient.create(dataSource)
    
    // 用户映射器
    private val userRowMapper = RowMapper<User> { rs, _ ->
        User(
            id = rs.getLong("id"),
            firstName = rs.getString("first_name"),
            lastName = rs.getString("last_name"),
            email = rs.getString("email"),
            age = rs.getInt("age"),
            createdAt = rs.getTimestamp("created_at").toLocalDateTime()
        )
    }
    
    override fun save(user: User): User {
        val keyHolder = GeneratedKeyHolder()
        
        jdbcClient
            .sql("""
                INSERT INTO users (first_name, last_name, email, age, created_at) 
                VALUES (:firstName, :lastName, :email, :age, :createdAt)
            """.trimIndent())
            .paramSource(user)
            .update(keyHolder)
        
        val generatedId = keyHolder.key?.toLong() 
            ?: throw DataAccessException("Failed to retrieve generated key")
        
        return user.copy(id = generatedId)
    }
    
    override fun findById(id: Long): User? {
        return jdbcClient
            .sql("SELECT * FROM users WHERE id = ?")
            .param(id)
            .query(userRowMapper)
            .optional()
            .orElse(null)
    }
    
    override fun findAll(): List<User> {
        return jdbcClient
            .sql("SELECT * FROM users ORDER BY created_at DESC")
            .query(userRowMapper)
            .list()
    }
    
    override fun findByEmail(email: String): User? {
        return jdbcClient
            .sql("SELECT * FROM users WHERE email = ?")
            .param(email)
            .query(userRowMapper)
            .optional()
            .orElse(null)
    }
    
    override fun update(user: User): Boolean {
        val rowsAffected = jdbcClient
            .sql("""
                UPDATE users 
                SET first_name = :firstName, last_name = :lastName, 
                    email = :email, age = :age 
                WHERE id = :id
            """.trimIndent())
            .paramSource(user)
            .update()
        
        return rowsAffected > 0
    }
    
    override fun deleteById(id: Long): Boolean {
        val rowsAffected = jdbcClient
            .sql("DELETE FROM users WHERE id = ?")
            .param(id)
            .update()
        
        return rowsAffected > 0
    }
    
    override fun count(): Long {
        return jdbcClient
            .sql("SELECT COUNT(*) FROM users")
            .query(Long::class.java)
            .single()
    }
}

// 4. 用户服务
@Service
@Transactional
class UserService(private val userRepository: UserRepository) {
    
    fun createUser(user: User): User {
        // 检查邮箱是否已存在
        userRepository.findByEmail(user.email)?.let {
            throw BusinessException("邮箱已存在: ${user.email}")
        }
        
        return userRepository.save(user)
    }
    
    fun getUserById(id: Long): User {
        return userRepository.findById(id) 
            ?: throw EntityNotFoundException("用户不存在: $id")
    }
    
    fun getAllUsers(): List<User> {
        return userRepository.findAll()
    }
    
    fun updateUser(user: User): User {
        val existingUser = getUserById(user.id!!)
        
        // 检查邮箱冲突（排除自己）
        userRepository.findByEmail(user.email)?.let { existing ->
            if (existing.id != user.id) {
                throw BusinessException("邮箱已被其他用户使用: ${user.email}")
            }
        }
        
        if (!userRepository.update(user)) {
            throw DataAccessException("更新用户失败")
        }
        
        return user
    }
    
    fun deleteUser(id: Long) {
        if (!userRepository.deleteById(id)) {
            throw EntityNotFoundException("用户不存在: $id")
        }
    }
}

// 5. REST 控制器
@RestController
@RequestMapping("/api/users")
class UserController(private val userService: UserService) {
    
    @PostMapping
    fun createUser(@RequestBody @Valid user: User): ResponseEntity<User> {
        val createdUser = userService.createUser(user)
        return ResponseEntity.status(HttpStatus.CREATED).body(createdUser)
    }
    
    @GetMapping("/{id}")
    fun getUserById(@PathVariable id: Long): ResponseEntity<User> {
        val user = userService.getUserById(id)
        return ResponseEntity.ok(user)
    }
    
    @GetMapping
    fun getAllUsers(): ResponseEntity<List<User>> {
        val users = userService.getAllUsers()
        return ResponseEntity.ok(users)
    }
    
    @PutMapping("/{id}")
    fun updateUser(
        @PathVariable id: Long, 
        @RequestBody @Valid user: User
    ): ResponseEntity<User> {
        val updatedUser = userService.updateUser(user.copy(id = id))
        return ResponseEntity.ok(updatedUser)
    }
    
    @DeleteMapping("/{id}")
    fun deleteUser(@PathVariable id: Long): ResponseEntity<Void> {
        userService.deleteUser(id)
        return ResponseEntity.noContent().build()
    }
}

6. 性能优化与最佳实践 ⚡ ​

6.1 连接池配置 ​

kotlin
@Configuration
class DatabaseConfig {
    
    @Bean
    @ConfigurationProperties("spring.datasource.hikari")
    fun dataSource(): HikariDataSource {
        return HikariDataSource().apply {
            // 连接池配置
            maximumPoolSize = 20
            minimumIdle = 5
            connectionTimeout = 30000
            idleTimeout = 600000
            maxLifetime = 1800000
            
            // 性能优化
            isAutoCommit = false
            transactionIsolation = "TRANSACTION_READ_COMMITTED"
        }
    }
}

6.2 批量操作优化 ​

kotlin
@Repository
class OptimizedUserRepository(private val jdbcTemplate: JdbcTemplate) {
    
    // 批量插入 - 高性能
    fun batchInsertUsers(users: List<User>): IntArray {
        return jdbcTemplate.batchUpdate(
            "INSERT INTO users (first_name, last_name, email, age) VALUES (?, ?, ?, ?)",
            users,
            100 // 批次大小
        ) { ps, user ->
            ps.setString(1, user.firstName)
            ps.setString(2, user.lastName)
            ps.setString(3, user.email)
            ps.setInt(4, user.age)
        }
    }
    
    // 分页查询优化
    fun findUsersWithPagination(page: Int, size: Int): List<User> {
        val offset = page * size
        
        return jdbcTemplate.query(
            "SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?",
            arrayOf(size, offset),
            userRowMapper
        )
    }
}

总结 🎉 ​

Spring JDBC 通过以下核心组件，彻底改变了 Java 数据库编程的体验：

组件主要功能适用场景
JdbcTemplate基础 JDBC 操作，位置参数简单查询，传统项目
NamedParameterJdbcTemplate命名参数，更清晰的 SQL复杂查询，参数较多的场景
JdbcClient现代化流畅 API，统一接口新项目，现代化开发
SQLExceptionTranslator异常转换，统一异常体系异常处理，错误诊断

TIP

选择建议：

  • 🆕 新项目：优先选择 JdbcClient，API 更现代化
  • 🔄 现有项目：可以逐步从 JdbcTemplate 迁移到 JdbcClient
  • 🎯 复杂查询：使用 NamedParameterJdbcTemplate 或 JdbcClient 的命名参数
  • 🛡️ 异常处理：根据需要自定义 SQLExceptionTranslator

Spring JDBC 让我们能够：

  • ✅ 专注业务逻辑，而不是底层资源管理
  • ✅ 享受类型安全，编译时发现更多错误
  • ✅ 提升开发效率，减少样板代码
  • ✅ 保持高性能，充分利用数据库特性

现在，你已经掌握了 Spring JDBC 的精髓，可以开始构建高效、优雅的数据访问层了！🚀