Using JdbcTemplate to Insert a UUID into a MySQL 8 Table

Question

I'm trying to insert a row in a MySQL 8 table that has a column of type BINARY(16). In detail, I'm using Spring JdbcTemplate, and the information is stored in a Java UUID object. I can't find how to do it properly.

internal const val INSERT_QUOTATION = """
    INSERT INTO QUOTATION (PRODUCT_CODE, QUOTED_PRODUCT)
    VALUES (?, ?)
"""

jdbcTemplate.update(INSERT_QUOTATION) { ps ->
    ps.setObject(1, UUID.fromString("9efbfa7b-1573-44ea-99f4-9607b8e45e27"))
    ps.setString(2, "{}");
}

The above code generates the following DB error:

PreparedStatementCallback; SQL [
    INSERT INTO QUOTATION (PRODUCT_CODE, QUOTED_PRODUCT)
    VALUES (?, ?)
]; Data truncation: Data too long for column 'PRODUCT_CODE' at row 1

How can I do it?

Answer 1

The error "Data truncation: Data too long for column 'PRODUCT_CODE' at row 1," is most likely due to the way the UUID is being set in the PreparedStatement.

The BINARY(16) type expects exactly 16 bytes of data. When you are using UUID.fromString("9efbfa7b-1573-44ea-99f4-9607b8e45e27") you are creating a UUID object. Therefore you need to ensure that it is properly converted to a 16-byte array before inserting it into the database, since a UUID object is not guaranteed to be exactly 16 bytes long.

Here's what you could do instead :

internal const val INSERT_QUOTATION = """
    INSERT INTO QUOTATION (PRODUCT_CODE, QUOTED_PRODUCT)
    VALUES (?, ?)
"""

jdbcTemplate.update(INSERT_QUOTATION) { ps ->
    val uuid = UUID.fromString("9efbfa7b-1573-44ea-99f4-9607b8e45e27")
    val uuidBytes = ByteBuffer.allocate(16)
        .putLong(uuid.mostSignificantBits)
        .putLong(uuid.leastSignificantBits)
        .array()
    ps.setBytes(1, uuidBytes)
    ps.setString(2, "{}")
}

This approach will generally ensures that the UUID is correctly converted into the expected 16-byte format that the BINARY(16) column type requires in MySQL.