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

Best of this Week Summary 26 April - 02 May 2010

• Google's Chief Java Architect Josh Bloch "discusses many of the problems facing the Java community, including the ineffectiveness of J2ME, licensing problems, Java 7's late ship date, and even the JCP issues that James Gosling often opined about. He points out that these problems predate the Oracle acquisition of Sun, so everyone should just stop pointing their fingers at Larry Ellison."
  


• Another Getting Started blogpost for a Mahout-Taste based movie recommendation engine, which uses Wicket as presentation layer.
  


• Martin Fowler explaining Inversion of Control and suggesting to call it the Dependency Injection pattern. Notes that the Service Locator pattern can also remove the dependency, just as the DI pattern tries to. Describes which of these options to use. Finally he also gives pros/cons for constructor vs setter injection. Also pros/cons for configuration in the code or via configuration files.
  


• Several Javascript performance-optimization tips taken at JSConf 2010.
  


• An article "which looks at various storage mechanisms - JDBC, JPA, JavaSpaces, Java Content Repository, MongoDB, and DB4O, primarily - from the perspective of how good they are at CRUD operations and queries". The conclusion is that there is no one "best" mechanism, each has its own best application.
  

Best of this Week Summary 17 February - 01 March 2009

• A relatively new type of mashups are so called clipping mashups. Instead of building a mashup against some official API, a clipping mashup just parses the final content in the presentation, thus including CSS, Javascript etc. Links can be rewritten, new elements injected. The presentation doesn't have to follow for example portal JSR-168 standards.
  An example use case would be that you could use it to (temporarily) fix a bug in a system (not necessarily owned by you) until the official next release comes out.
  


• Eight architectural styles described. Handy overview including which one could be when appropriate.
  


• Summary of migration project from JDBC to Hibernate. Valid comment at the bottom is that iBatis might be a better solution when you have to work from an existing schema.
  


• Additional tips and articles for the Wicket in Action book. For example this article on how to do a partial Ajax repaint of newly created repeater items (e.g adding 1 row dynamically to a table or list with a "+" button). This in contrast to rendering all rows in a table when repainting the WebMarkupContainer as in the example described here.
  


• Nice step-by-step introduction to asynchronous processing in Java 5 using Futures, ExecutorServices, CompletionService, Callback interfaces and ThreadPools. Here's part 2 where it's being used in combination with Javascript to improve the user experience.