JDBI select on varbinary and uuid

Question

A legacy mysql db table has an id column that is non-human readable raw varbinary (don't ask me why :P)

CREATE TABLE IF NOT EXISTS `tbl_portfolio` (
    `id` varbinary(16) NOT NULL,
    `name` varchar(128) NOT NULL,
    ...
    PRIMARY KEY (`id`)
);

and I need to select on it based on a java.util.UUID

jdbiReader
    .withHandle<PortfolioData, JdbiException> { handle ->
        handle
            .createQuery(
                """
                    SELECT *
                    FROM tbl_portfolio
                    WHERE id = :id
                    """
            )
            .bind("id", uuid) //mapping this uuid into the varbinary
                              //id db column is the problem
            .mapTo(PortfolioData::class.java) //the mapper out does work
            .firstOrNull()
    }

just in case anyone wants to see it, here's the mapper out (but again, the mapper out is not the problem - binding the uuid to the varbinary id db column is)

class PortfolioDataMapper : RowMapper<PortfolioData> {

    override fun map(
        rs: ResultSet,
        ctx: StatementContext
    ): PortfolioData = PortfolioData(
        fromBytes(rs.getBytes("id")),
        rs.getString("name"),
        rs.getString("portfolio_idempotent_key")
    )

    private fun fromBytes(bytes: ByteArray): UUID {
        val byteBuff = ByteBuffer.wrap(bytes)
        val first = byteBuff.long
        val second = byteBuff.long
        return UUID(first, second)
    }

}

I've tried all kinds of things to get the binding to work but no success - any advice much appreciated!

Answer 1

Finally got it to work, partly thanks to https://jdbi.org/#_argumentfactory which actually deals with UUID specifically but I somehow missed despite looking at JDBI docs for hours, oh well

The query can remain like this

jdbiReader
    .withHandle<PortfolioData, JdbiException> { handle ->
        handle
            .createQuery(
                """
                    SELECT *
                    FROM tbl_portfolio
                    WHERE id = :id
                    """
            )
            .bind("id", uuid)
            .mapTo(PortfolioData::class.java)
            .firstOrNull()
    }

But jdbi needs a UUIDArgumentFactory registered

jdbi.registerArgument(UUIDArgumentFactory(VARBINARY))

where

class UUIDArgumentFactory(sqlType: Int) : AbstractArgumentFactory<UUID>(sqlType) {

    override fun build(
        value: UUID,
        config: ConfigRegistry?
    ): Argument {
        return UUIDArgument(value)
    }

}

where

class UUIDArgument(private val value: UUID) : Argument {

    companion object {
        private const val UUID_SIZE = 16
    }

    @Throws(SQLException::class)
    override fun apply(
        position: Int,
        statement: PreparedStatement,
        ctx: StatementContext
    ) {
        val bb = ByteBuffer.wrap(ByteArray(UUID_SIZE))
        bb.putLong(value.mostSignificantBits)
        bb.putLong(value.leastSignificantBits)
        statement.setBytes(position, bb.array())
    }
    
}

NOTE that registering an ArgumentFactory on the entire jdbi instance like this will make ALL UUID type arguments sent to .bind map to bytes which MAY not be what you want in case you elsewhere in your code base have other UUID arguments that are stored on the mysql end with something other than VARBINARY - eg, you may have another table with a column where your JVM UUID are actually stores as VARCHAR or whatever, in which case you'd have to, rather than registering the UUID ArgumentFactory on the entire jdbi instance, only use it ad hoc on individual queries where appropriate.