Kotlin处理SQL插入语句后IntelliJ存文件为空及索引越界求助

我有1万行SQL插入语句文本，需要转换为自定义的NamesData数据类格式。编写了Kotlin处理代码后，在IntelliJ中运行生成的formatted_names.txt文件为空，且出现IndexOutOfBoundsException异常。

原代码 #

data class NamesData(
val id: Int,
val name: String,
val gender: String,
val meaning: String,
val origin: String
)
fun main() {
val sqlInsertStatements = """
insert into all_names (Id, Word, Meaning, is_user_added) values (54143, 'Abadjon', '<p style="text-align: end;">\<span style="color: #0000ff;">O''g''il bolalar ismi\</span></p><p>Ism qismi: Jon</p><p>KELIB CHIQISHI:\<br<br</br> Arabcha</p><br><p><strong>MA’NOSI:</strong></p><p>Mangu, cheksiz, abadiy, umri uzoq</p>', null);
insert into all_names (Id, Word, Meaning, is_user_added) values (54144, 'Abay', '<p style="text-align: end;">\<span style="color: #0000ff;">O''g''il bolalar ismi\</span></p><p></p><p>KELIB CHIQISHI:\<br<br</br> O''zbekcha</p><br><p><strong>MA’NOSI:</strong></p>
<p>Hushyor, ehtiyotkor yoki himoya, muhofaza qilingan</p>', null);
""".trimIndent()

    val formattedNamesList = formatNames(sqlInsertStatements)
    val insertStatements = createInsertStatements(formattedNamesList)
    
    val filePath = "${getFilePath()}/formatted_names.txt"
    saveToFile(insertStatements, filePath)
    println("File saved successfully at: $filePath")

}

fun formatNames(sql: String): List<NamesData> {
val idRegex = "Id, (\\d+),".toRegex()
val nameRegex = "Word, '([^']+)'" .toRegex()
val meaningRegex = "Meaning, '([^']+?)'.*?span[^&gt;]+&gt;([^&lt;]+)&lt;/span&gt;.*?KELIB CHIQISHI:\&lt;br&lt;br&lt;/br&gt;([^&lt;]+)&lt;/p&gt;".toRegex()

    val idMatches = idRegex.findAll(sql).map { it.groupValues[1].toInt() }
    val nameMatches = nameRegex.findAll(sql).map { it.groupValues[1] }
    val meaningMatches = meaningRegex.findAll(sql).map {
        val gender = it.groupValues[1]
        val meaning = it.groupValues[2]
        val origin = it.groupValues[3]
        Triple(gender, meaning, origin)
    }
    val formattedNamesList = mutableListOf<NamesData>()
    
    for ((id, name) in idMatches.zip(nameMatches)) {
        val (gender, meaning, origin) = meaningMatches.elementAtOrElse(id - 54143) { Triple("", "", "") }
        formattedNamesList.add(NamesData(id, name, gender, meaning, origin))
    }
    return formattedNamesList

}
fun createInsertStatements(namesList: List<NamesData>): String {
val insertStatements = StringBuilder()

    for (namesData in namesList) {
        insertStatements.appendLine("insert into all_names (Id, name, gender, meaning, origin) values (${namesData.id}, \"${namesData.name}\", \"${namesData.gender}\", \"${namesData.meaning}\", \"${namesData.origin}\");")
    }
    return insertStatements.toString()

}
fun saveToFile(text: String, filePath: String) {
val file = File(filePath)
try {
BufferedWriter(FileWriter(file)).use { writer ->
writer.write(text)
}
} catch (e: IOException) {
println("Error while writing to the file: ${e.message}")
}
}
fun getFilePath(): String {
return System.getProperty("user.dir")
}

预期输出 #

insert into all_names (Id, name, gender, meaning, origin) values (54143, "Abadjon", "O'gil bolalar ismi", "Ism qismi: Jon", "Arabcha");
insert into all_names (Id, name, gender, meaning, origin) values (54144, "Abay", "O'gil bolalar ismi", "", "O'zbekcha");

核心问题与修复方案 #

1. 原代码的关键问题 #

• 正则匹配逻辑完全错误：原正则试图匹配字段名后的内容，但实际数据在values括号内，导致全局匹配无结果，生成空列表
• 索引依赖不合理：用id - 54143作为匹配结果的索引，假设id连续，但匹配失败时会直接导致索引越界
• 未处理SQL转义字符：SQL中的''是单引号的转义，未还原会导致内容错误

2. 修复后的代码 #

import java.io.File
import java.io.FileWriter
import java.io.BufferedWriter

data class NamesData(
    val id: Int,
    val name: String,
    val gender: String,
    val meaning: String,
    val origin: String
)

fun main() {
    val sqlInsertStatements = """
insert into all_names (Id, Word, Meaning, is_user_added) values (54143, 'Abadjon', '<p style="text-align: end;">\<span style="color: #0000ff;">O''g''il bolalar ismi\</span></p><p>Ism qismi: Jon</p><p>KELIB CHIQISHI:\<br<br</br> Arabcha</p><br><p><strong>MA’NOSI:</strong></p><p>Mangu, cheksiz, abadiy, umri uzoq</p>', null);
insert into all_names (Id, Word, Meaning, is_user_added) values (54144, 'Abay', '<p style="text-align: end;">\<span style="color: #0000ff;">O''g''il bolalar ismi\</span></p><p></p><p>KELIB CHIQISHI:\<br<br</br> O''zbekcha</p><br><p><strong>MA’NOSI:</strong></p>
<p>Hushyor, ehtiyotkor yoki himoya, muhofaza qilingan</p>', null);
    """.trimIndent()

    val formattedNamesList = parseSqlToNamesData(sqlInsertStatements)
    val insertStatements = createInsertStatements(formattedNamesList)
    
    val filePath = "${getFilePath()}/formatted_names.txt"
    saveToFile(insertStatements, filePath)
    println("File saved successfully at: $filePath")
}

/**
 * 拆分每个SQL语句并逐个解析为NamesData
 */
fun parseSqlToNamesData(sql: String): List<NamesData> {
    // 拆分每个insert语句（以;分割，过滤空行）
    val insertStatements = sql.split(";").filter { it.trim().isNotEmpty() }
    val namesList = mutableListOf<NamesData>()

    for (statement in insertStatements) {
        // 匹配values后的括号内容：(id, 'name', 'meaning_content', null)
        val valuesRegex = Regex("values\\s*\\((\\d+),\\s*'([^']+)',\\s*'([^']+)',\\s*null\\)")
        val valuesMatch = valuesRegex.find(statement) ?: continue

        val id = valuesMatch.groupValues[1].toInt()
        val name = valuesMatch.groupValues[2].replace("''", "'") // 替换SQL转义单引号
        val meaningHtml = valuesMatch.groupValues[3]

        // 解析gender：从<span>标签中提取
        val genderRegex = Regex("\\&lt;span[^&]+\\&gt;([^&]+)\\&lt;/span\\&gt;")
        val gender = genderRegex.find(meaningHtml)?.groupValues[1]?.replace("''", "'") ?: ""

        // 解析meaning：提取<span>之后、KELIB CHIQISHI之前的<p>内容
        val meaningRegex = Regex("\\&lt;/span\\&gt;.*?\\&lt;p\\&gt;([^&]+)\\&lt;/p\\&gt;.*?KELIB CHIQISHI:")
        val meaning = meaningRegex.find(meaningHtml)?.groupValues[1] ?: ""

        // 解析origin：提取KELIB CHIQISHI之后的内容
        val originRegex = Regex("KELIB CHIQISHI:\\&lt;br&lt;br&lt;/br\\&gt;\\s*([^&]+)\\&lt;/p\\&gt;")
        val origin = originRegex.find(meaningHtml)?.groupValues[1]?.replace("''", "'") ?: ""

        namesList.add(NamesData(id, name, gender, meaning, origin))
    }

    return namesList
}

fun createInsertStatements(namesList: List<NamesData>): String {
    val insertStatements = StringBuilder()
    for (namesData in namesList) {
        // 对内容中的双引号进行转义，避免SQL语法错误
        val escapedName = namesData.name.replace("\"", "\\\"")
        val escapedGender = namesData.gender.replace("\"", "\\\"")
        val escapedMeaning = namesData.meaning.replace("\"", "\\\"")
        val escapedOrigin = namesData.origin.replace("\"", "\\\"")
        
        insertStatements.appendLine("insert into all_names (Id, name, gender, meaning, origin) values (${namesData.id}, \"$escapedName\", \"$escapedGender\", \"$escapedMeaning\", \"$escapedOrigin\");")
    }
    return insertStatements.toString()
}

fun saveToFile(text: String, filePath: String) {
    val file = File(filePath)
    try {
        BufferedWriter(FileWriter(file)).use { writer ->
            writer.write(text)
        }
    } catch (e: Exception) {
        println("Error while writing to the file: ${e.message}")
    }
}

fun getFilePath(): String {
    return System.getProperty("user.dir")
}

修复要点 #

• 拆分SQL语句：先将整个文本按;拆分为单个insert语句，逐个解析，避免全局匹配混乱
• 修正正则逻辑：直接匹配values括号内的原始数据，再分别提取gender、meaning、origin字段
• 处理转义字符：还原SQL中的''为'，生成新SQL时转义双引号避免语法错误
• 消除索引依赖：每个语句的解析结果直接对应一个NamesData对象，彻底解决索引越界问题

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