Spring JDBC and MySql using UUIDs in Java and VARCHAR(36) in database incorrect string value solution

Introduction

Using H2 as initial embedded database for a Spring Boot application worked fine. H2 is very forgiving in many situations and of course only tries to emulate the real target database, in this case MySql 8.0.
So after connecting my Spring Boot application to MySql, this error started to appear when inserting a row in a table with a java.util.UUID property as 'id' field:  

    java.sql.SQLException: Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'id' at row 1

A quick internet search showed that potentially my character set and collate setting for the tables were using 3 bytes instead of 4 for UTF-8 storage.
But the database, tables and columns all had utf8mb4 specified as CHARACTER SET and COLLATE, since I'm using MySql 8.0. So that 3 vs 4 bytes for UTF-8 issue did not apply for me. 

Solution

Then I found this blog https://petrepopescu.tech/2021/01/how-to-use-string-uuid-in-hibernate-with-mysql/ that at least when using Hibernate, it doesn't know how to convert UUIDs to strings (varchars), and you need to specify a Hibernate-provided converter. But the Hibernate annotation of course did not work for Spring Data JDBC.

Luckily there is a way to write your own converters for Spring JDBC datatypes.

Implementing that fixed that initial error message. Note the example in the above post is missing the @Configuration annotation on the MyJdbcConfiguration class.

But then the error happened again during this custom JdbcTemplate select query:

    String query = "SELECT DISTINCT r.id, user_id FROM recipe r WHERE user_id = ?";
    List<Object> parameterValues = new ArrayList<>();
    parameterValues.add(userId);
    Object[] parameterValuesArray = parameterValues.toArray();
    jdbcTemplate.query(query, parameterValuesArray, new JdbcRecipeRowMapper());

This was the output of that query, including the parameters used in the query:

Executing prepared SQL statement [SELECT DISTINCT r.id, user_id, r.name, vegetarian, number_of_servings, instructions, r.created_at, r.updated_at FROM recipe r INNER JOIN ingredient i ON r.id = i.recipe_id WHERE user_id = ? AND vegetarian = ? AND number_of_servings = ? AND instructions LIKE ? AND  i.name IN (?) ]
2022-09-14 15:46:38.062 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [e26b2a0a-3d2c-442f-8fa1-f26336d5a9d3], value class [java.util.UUID], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [true], value class [java.lang.Boolean], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [3], value class [java.lang.String], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [%Step%], value class [java.lang.String], SQL type unknown
2022-09-14 15:46:38.068 TRACE 16148 --- [nio-7000-exec-2] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [spinach], value class [java.lang.String], SQL type unknown
2022-09-14 16:06:37.952 DEBUG 19748 --- [nio-7000-exec-2] o.s.jdbc.core.JdbcTemplate               : SQLWarning ignored: SQL state 'HY000', error code '1366', message [Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'user_id' at row 1]

So that only shows a warning message at DEBUG level, not even as an error, so at first I missed it completely! The query just returned 0 results.

    SQLWarning ignored: SQL state 'HY000', error code '1366', message [Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'user_id' at row 1]

Makes sense though that this custom query has the same issue, since my string-based query of course does not use the converters that I configured earlier.
So I had to change the third line to explicitly convert the value to a string, so Spring JDBC will pass it on as a string:

        parameterValues.add(userId.toString());

Note: maybe implementing a placeholder interface for each repository like this would also have fixed it for the Spring generated methods/queries like save(), remove() etc, e.g: interface RecipeRepository extends Repository<Recipe, UUID>. Did not try that out.

Other related links used to get to the above solution:

• https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/UUIDValues.htm
  
• https://www.anycodings.com/1questions/1947192/how-do-i-use-a-uuid-in-a-jdbc-template
  
• https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc