Demonstrate use of jsonb.kt

main.kt

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
import org.jetbrains.exposed.sql.vendors.DatabaseDialect
import kotlin.random.Random

// Unable to get the database dialect within Exposed...
lateinit var databaseDialect: DatabaseDialect

fun main() {
    val db = "pg"

    if (db == "pg") {
        Database.connect(
            "jdbc:postgresql://localhost:15432/boon", driver = "org.postgresql.Driver",
            user = "boon", password = "boon"
        ).apply { databaseDialect = dialect }
    } else {
        Database.connect(
            "jdbc:mysql://localhost:3306/boon", driver = "com.mysql.jdbc.Driver",
            user = "boon", password = "boon"
        ).apply { databaseDialect = dialect }
    }

    transaction {
        val tx = this
        SchemaUtils.create(Customers)

        val r = Random(System.currentTimeMillis())

        if (Customers.selectAll().count() == 0) {
            for (i in 1..12_000)
                Customers.insert {
                    it[name] = "Alice$i"
                    it[misc] = mapOf(
                        "age" to r.nextInt(99),
                        "salary" to r.nextInt(9999) + 2000
                    )
                    if ((i + 1) % 10_000 == 0) {
                        println("Added ${i + 1}.")
                        tx.flushCache()
                    }
                }
        }

        transaction {
            val start = System.currentTimeMillis()
            val customers = Customers.select { Customers.misc.json<Int>(JsonKey("salary")) eq 5555 }

            var count = 0
            for (customer in customers) {
                println(customer)
                count++
            }

            val took = System.currentTimeMillis() - start
            println("Total customer count: $count (took $took ms)")
        }
    }
}