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.")
}

This is my code to match the input with regex pattern. If an input is matched with a pattern, then store that input into the corresponding table with that pattern. If an input is NOT matched with any pattern, then store that input in the non_matched table.
After running this code, it can match correctly but the inputs are NOT stored in the tables. How can I fixed this?
I think there are mistake in these line:
“val insertNonMatchedSQL = “INSERT INTO non-matched (data) VALUES (?)”
“connection.prepareStatement(“INSERT INTO $tableName (data) VALUES (?)”)”
is the VALUES (?) correct?

I would like to store the input data with the corresponding table in the DB