Kotlin操作MariaDB匹配数据后无法写入表的问题修复求助

数据老陈

2026-6-20

问题：Kotlin代码中MariaDB插入数据失败排查与修复

我编写了如下Kotlin代码，用于将输入数据与正则表达式匹配：匹配成功的数据存入对应分类表，未匹配的数据存入non-matched表。运行后匹配逻辑正常，但数据无法写入MariaDB的对应表中。我怀疑以下两行INSERT语句存在问题：
val insertNonMatchedSQL = "INSERT INTO `non-matched` (data) VALUES (?)"
connection.prepareStatement("INSERT INTO `$tableName` (data) VALUES (?)")
请问VALUES(?)的写法是否正确？该如何修复才能让数据成功存入数据库表？

完整代码：

import java.io.File
import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.Statement
import com.google.gson.Gson
import com.google.gson.reflect.TypeToken

data class InputData(val data: List<String>)
data class RegexPatterns(val regex_patterns: Map<String, String>)

fun main() {
    // Load input data from input.json
    val inputFile = File("src/main/resources/input.json")
    val inputData: InputData = Gson().fromJson(inputFile.reader(), InputData::class.java)

    // Load regex patterns from regex.json
    val regexFile = File("src/main/resources/regex.json")
    val regexType = object : TypeToken<RegexPatterns>() {}.type
    val regexPatterns: RegexPatterns = Gson().fromJson(regexFile.reader(), regexType)

    // MariaDB connection details
    val jdbcUrl = "jdbc:mariadb://localhost:3306/sensitive_data"
    val username = "root"
    val password = "your_password"

    // Connect to MariaDB
    val connection: Connection = DriverManager.getConnection(jdbcUrl, username, password)

    // Prepare SQL statement for inserting regex patterns into information_type table
    val insertPatternSQL = "INSERT INTO information_type (name, pattern) VALUES (?, ?)"
    val patternPreparedStatement: PreparedStatement = connection.prepareStatement(insertPatternSQL, Statement.RETURN_GENERATED_KEYS)

    // Insert regex patterns into the information_type table and create corresponding tables for matched values
    val categoryIds = mutableMapOf<String, Int>()
    regexPatterns.regex_patterns.forEach { (category, pattern) ->
        // Insert category and pattern into information_type table
        patternPreparedStatement.setString(1, category)
        patternPreparedStatement.setString(2, pattern)
        patternPreparedStatement.executeUpdate()

        // Get the generated ID for the inserted category
        val generatedKeys = patternPreparedStatement.generatedKeys
        if (generatedKeys.next()) {
            val categoryId = generatedKeys.getInt(1)
            categoryIds[category] = categoryId
            println("Inserted pattern for category: $category with ID: $categoryId")

            // Sanitize the table name
            val tableName = category.replace(" ", "_").replace("'", "").replace("-", "_").replace(",", "").replace("&", "and")
            // Create a new table for the matched values of this category
            val createTableSQL = """
                CREATE TABLE IF NOT EXISTS `$tableName` (
                    ID INT AUTO_INCREMENT PRIMARY KEY,
                    data TEXT
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
            """.trimIndent()
            val statement: Statement = connection.createStatement()
            statement.executeUpdate(createTableSQL)
            println("Created table: $tableName")
        }
    }

    // Prepare SQL statements for inserting matched and non-matched data
    val insertNonMatchedSQL = "INSERT INTO `non-matched` (data) VALUES (?)"
    val nonMatchedPreparedStatement: PreparedStatement = connection.prepareStatement(insertNonMatchedSQL)

    val insertMatchedSQLs = categoryIds.mapValues { (category, _) ->
        val tableName = category.replace(" ", "_").replace("'", "").replace("-", "_").replace(",", "").replace("&", "and")
        connection.prepareStatement("INSERT INTO `$tableName` (data) VALUES (?)")
    }

    // Iterate over each data entry and match with the appropriate pattern
    inputData.data.forEach { entry ->
        var matched = false
        regexPatterns.regex_patterns.forEach { (category, pattern) ->
            val regex = Regex(pattern)
            if (regex.matches(entry)) {
                println("\"$category\": \"$entry\"")
                // Insert the matching result into the corresponding table
                insertMatchedSQLs[category]?.setString(1, entry)
                insertMatchedSQLs[category]?.executeUpdate()
                matched = true
                return@forEach
            }
        }
        if (!matched) {
            println("Non-matched data: \"$entry\"")
            nonMatchedPreparedStatement.setString(1, entry)
            nonMatchedPreparedStatement.executeUpdate()
        }
    }

    // Close the connection
    connection.close()
    println("Data processing complete. Connection closed.")
}

解答

VALUES(?)写法是否正确？

VALUES(?)的写法是完全正确的，这是JDBC预编译语句的标准语法，用于参数化查询，既能防止SQL注入，也能提升重复执行的效率。问题不在这个语法本身，而是代码的其他环节。

核心问题与修复步骤

1. 事务未提交

JDBC连接默认可能关闭了自动提交（取决于驱动配置），导致所有插入操作都在未提交的事务中，关闭连接时自动回滚，数据无法持久化。

修复：

方式一：开启自动提交
在获取连接后添加：

connection.autoCommit = true

方式二：手动提交事务
在所有数据插入完成后调用提交，同时添加异常回滚：

try {
    // 所有数据处理逻辑
    connection.commit()
} catch (e: SQLException) {
    connection.rollback()
    throw e
} finally {
    connection.close()
}

2. PreparedStatement重复使用未清理参数

同一个PreparedStatement多次使用时，前一次设置的参数可能残留，导致后续插入数据异常。

修复：
在每次设置参数前调用clearParameters()：

// 匹配数据插入部分
insertMatchedSQLs[category]?.clearParameters()
insertMatchedSQLs[category]?.setString(1, entry)
insertMatchedSQLs[category]?.executeUpdate()

// 非匹配数据插入部分
nonMatchedPreparedStatement.clearParameters()
nonMatchedPreparedStatement.setString(1, entry)
nonMatchedPreparedStatement.executeUpdate()

3. 表名处理逻辑不一致

创建表和插入数据时的表名清理逻辑是重复写的，可能出现不一致（比如后续修改一处另一处没同步），导致插入时找不到对应表。

修复：
抽离表名清理逻辑为单独函数：

fun sanitizeTableName(category: String): String {
    return category.replace(" ", "_")
        .replace("'", "")
        .replace("-", "_")
        .replace(",", "")
        .replace("&", "and")
}

然后在创建表和插入数据时统一调用：

// 创建表时
val tableName = sanitizeTableName(category)

// 插入数据时
val tableName = sanitizeTableName(category)
connection.prepareStatement("INSERT INTO `$tableName` (data) VALUES (?)")

4. `non-matched`表未提前创建

代码中只创建了分类对应的表，但没有处理non-matched表的创建，如果该表不存在，插入会失败。

修复：
在连接数据库后添加创建non-matched表的逻辑：

val createNonMatchedTableSQL = """
    CREATE TABLE IF NOT EXISTS `non-matched` (
        ID INT AUTO_INCREMENT PRIMARY KEY,
        data TEXT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
""".trimIndent()
connection.createStatement().executeUpdate(createNonMatchedTableSQL)

5. 缺少异常捕获与日志

代码中没有任何异常处理，插入时的SQL异常会直接终止程序，但你可能没看到错误信息，误以为是逻辑问题。

修复：
用try-catch包裹核心逻辑，打印异常信息：

fun main() {
    var connection: Connection? = null
    try {
        // 原有的所有代码（连接、数据处理等）
        connection = DriverManager.getConnection(jdbcUrl, username, password)
        connection.autoCommit = true
        // ... 其他逻辑
    } catch (e: Exception) {
        e.printStackTrace()
        connection?.rollback()
    } finally {
        connection?.close()
        println("Data processing complete. Connection closed.")
    }
}

修复后的关键代码片段

整合上述修复后的核心部分：

fun sanitizeTableName(category: String): String {
    return category.replace(" ", "_")
        .replace("'", "")
        .replace("-", "_")
        .replace(",", "")
        .replace("&", "and")
}

fun main() {
    var connection: Connection? = null
    try {
        // 加载数据、正则等逻辑...

        // 连接数据库
        val jdbcUrl = "jdbc:mariadb://localhost:3306/sensitive_data"
        val username = "root"
        val password = "your_password"
        connection = DriverManager.getConnection(jdbcUrl, username, password)
        connection.autoCommit = true

        // 创建non-matched表
        val createNonMatchedTableSQL = """
            CREATE TABLE IF NOT EXISTS `non-matched` (
                ID INT AUTO_INCREMENT PRIMARY KEY,
                data TEXT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
        """.trimIndent()
        connection.createStatement().executeUpdate(createNonMatchedTableSQL)

        // 插入正则模式、创建分类表逻辑...（使用sanitizeTableName函数）

        // 准备插入语句
        val insertNonMatchedSQL = "INSERT INTO `non-matched` (data) VALUES (?)"
        val nonMatchedPreparedStatement: PreparedStatement = connection.prepareStatement(insertNonMatchedSQL)

        val insertMatchedSQLs = categoryIds.mapValues { (category, _) ->
            val tableName = sanitizeTableName(category)
            connection.prepareStatement("INSERT INTO `$tableName` (data) VALUES (?)")
        }

        // 数据匹配与插入
        inputData.data.forEach { entry ->
            var matched = false
            regexPatterns.regex_patterns.forEach { (category, pattern) ->
                val regex = Regex(pattern)
                if (regex.matches(entry)) {
                    println("\"$category\": \"$entry\"")
                    insertMatchedSQLs[category]?.let { stmt ->
                        stmt.clearParameters()
                        stmt.setString(1, entry)
                        stmt.executeUpdate()
                    }
                    matched = true
                    return@forEach
                }
            }
            if (!matched) {
                println("Non-matched data: \"$entry\"")
                nonMatchedPreparedStatement.clearParameters()
                nonMatchedPreparedStatement.setString(1, entry)
                nonMatchedPreparedStatement.executeUpdate()
            }
        }
    } catch (e: Exception) {
        e.printStackTrace()
        connection?.rollback()
    } finally {
        connection?.close()
        println("Data processing complete. Connection closed.")
    }
}

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

Kotlin操作MariaDB匹配数据后无法写入表的问题修复求助

解答 #

VALUES(?)写法是否正确？ #

核心问题与修复步骤 #