基于Spring Boot+MS SQL，能否用Kotlin Exposed调用存储过程并映射结果到对象？

Absolutely! You can absolutely call stored procedures using Kotlin Exposed when working with Spring Boot and MS SQL, and mapping the returned results to your custom objects is totally feasible. Let me walk you through how to handle different common scenarios:

1. Basic Execution (No Result Mapping) #

If your stored procedure is meant for operations like inserts/updates (no return result set), you can leverage Exposed's transaction block and JDBC's CallableStatement directly:

transaction {
    val connection = this.connection
    // Replace with your procedure name and parameters
    val callableStmt = connection.prepareCall("{CALL UpdateUserStatus(?, ?)}")
    // Set input parameters (index starts at 1)
    callableStmt.setInt(1, 123) // User ID
    callableStmt.setString(2, "ACTIVE") // New status
    callableStmt.execute()
}

2. Mapping Result Sets to Custom Objects #

For stored procedures that return a result set, you can parse the ResultSet into your data classes easily. First, define your target data class:

data class UserProfile(
    val userId: Int,
    val fullName: String,
    val joinDate: LocalDate,
    val isActive: Boolean
)

Then, within a transaction, execute the procedure and map the results:

transaction {
    val connection = this.connection
    val callableStmt = connection.prepareCall("{CALL GetUserProfilesByRole(?)}")
    callableStmt.setString(1, "ADMIN") // Input parameter: role name
    
    val resultSet = callableStmt.executeQuery()
    val profiles = mutableListOf<UserProfile>()
    
    while (resultSet.next()) {
        val profile = UserProfile(
            userId = resultSet.getInt("user_id"),
            fullName = resultSet.getString("full_name"),
            joinDate = resultSet.getDate("join_date").toLocalDate(),
            isActive = resultSet.getBoolean("is_active")
        )
        profiles.add(profile)
    }
    
    profiles // Return the mapped list
}

3. Cleaner Mapping with Exposed's DSL #

To align with Exposed's style, you can define a dummy Table that mirrors your stored procedure's result schema, then use Exposed's built-in row parser:

// Dummy table matching the stored procedure's output columns
object UserProfileResult : Table() {
    val userId = integer("user_id")
    val fullName = varchar("full_name", length = 100)
    val joinDate = date("join_date")
    val isActive = bool("is_active")
}

// Then in your transaction:
transaction {
    val connection = this.connection
    val callableStmt = connection.prepareCall("{CALL GetAllActiveUsers()}")
    val resultSet = callableStmt.executeQuery()
    
    // Create a parser to map ResultSet rows to UserProfile
    val rowParser = UserProfileResult.rowParser { userId, fullName, joinDate, isActive ->
        UserProfile(userId, fullName, joinDate, isActive)
    }
    
    // Parse the entire result set into a list
    val activeUsers = resultSet.parse(rowParser).toList()
}

4. Handling Output Parameters #

If your stored procedure uses output parameters (e.g., returning a count or status code), you can register them explicitly:

transaction {
    val connection = this.connection
    val callableStmt = connection.prepareCall("{CALL GetTotalUsers(?)}")
    // Register output parameter (index, SQL type)
    callableStmt.registerOutParameter(1, Types.INTEGER)
    callableStmt.execute()
    
    val totalUsers = callableStmt.getInt(1)
    println("Total registered users: $totalUsers")
}

Quick Notes to Keep in Mind #

• Make sure your MS SQL JDBC driver is properly included in your Spring Boot project's dependencies (it’s usually auto-configured if you use Spring Boot starters for SQL).
• If you’re using Spring's @Transactional annotations, ensure Exposed is configured to use Spring’s transaction manager for consistency.
• Double-check parameter types (input/output) match what your stored procedure expects to avoid runtime errors.

内容的提问来源于stack exchange，提问作者Michal