Mysql Question posted in
The official MySQL documentation can be found here.

Using JdbcTemplate to Insert a UUID into a MySQL 8 Table

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You are passing the UUID to the setObject method this will not automatically convert it to a format suitable for BINARY(16). Which should also be clear from the answer you linked to yourself.

    You can do 1 of 2 things.

    1. Use the UUID_TO_BIN function from MySQL and pass the UUID as as string to the query, MySQL will then do the conversion.
    2. Convert the UUID to a byte[] by extracting the significant bits from it and set it as a byte[] with setBytes.
    internal const val INSERT_QUOTATION = """
    INSERT INTO QUOTATION (PRODUCT_CODE, QUOTED_PRODUCT)
    VALUES (UUID_TO_BIN(?, true), ?)
"""

jdbcTemplate.update(INSERT_QUOTATION, "9efbfa7b-1573-44ea-99f4-9607b8e45e27", "{}");

    This will insert it as a BINARY(16) for the SELECT you would need to use the BIN_TO_UUID function to convert it to a a UUID again.

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

val uuid = UUID.fromString("9efbfa7b-1573-44ea-99f4-9607b8e45e27");
val uuidInBytes = ByteBuffer.allocate(16)
        .putLong(uuid.mostSignificantBits)
        .putLong(uuid.leastSignificantBits)
        .array();
jdbcTemplate.update(INSERT_QUOTATION, uuidInBytes, "{}");

    Either should work.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search