Add Custom Implementation to generate query with Exposed to use DB built-in function

Question

I’m using Kotlin Exposed to interact MySQL in my service. Now I’m planning to create a new table, whose primary key is uuid converted to binary as binary(16) . In that case, when I execute each query, I need to trigger MySQL built-in function to convert the primary key value, like SELECT * FROM table where BIN_TO_UUID(id, 1) = <uuid> .

I think I should customize QueryBuilder, but is it possible to add implementation for generating query on Exposed?

Answer 1

There is no implementation of Comparable, it means you can't use it for entity classes, but you can use ByteArray as primary key, and make some queries.

It is going to look like FROM table where id = <ByteArray>

1. First of all you need some transforamtion methods.

---

 fun UUID.convertToByte(): ByteArray {
     val bytes = ByteArray(16)
     val buffer = ByteBuffer.wrap(bytes)
     buffer.putLong(mostSignificantBits)
     buffer.putLong(leastSignificantBits)
     return buffer.array()
 }

 fun ByteArray.convertToUUID(): UUID {
     val buffer = ByteBuffer.wrap(this)
     val firstLong = buffer.long
     val secondLong = buffer.long
     return UUID(firstLong, secondLong)
 }

---

2. You need some method for autoincrement.

---

fun Column<ByteArray>.autoGenerate(): Column<ByteArray> 
    = clientDefault { UUID.randomUUID().convertToByte() }

---

3. Then we create our table.

---

object ByteTable : Table(name = "test_table") {
    val id = binary("id", 16).autoGenerate()
    val someData = text("text")
    override val primaryKey = PrimaryKey(id)
}

---

4. Use it.

---

 transaction {
     SchemaUtils.create(ByteTable)

     val uuid = UUID.randomUUID()
     println(uuid)
     ByteTable.insert {
         it[id] = uuid.convertToByte()
         it[someData] = "my"
     }

     for (i in 0..<10) {
         ByteTable.insert {
             it[someData] = i.toString()
         }
     }

     val res = ByteTable.select(ByteTable.id eq uuid.convertToByte()).firstOrNull()
     if (res != null){
         println(res[ByteTable.someData])
     }
}