1

UUID's with Hibernate/JPA

I have long been accustoming to storing UUID values in binary database columns (e.g. BYTEA for PostgreSQL, BINARY(16) for MySQL). When using Hibernate and JPA, I can annotate my entity fields like this...

@Id
@Column(name = "id", columnDefinition = "BINARY(16)")
private UUID id;

... and it just works.

UUID's with JDBC

On the occasions in which I've needed to write use JDBC, I've written UUID values like this...

PreparedStatement ps = new PreparedStatement("INSERT INTO foo (id) VALUES (?)");
ps.setObject(1, uuid, Types.BINARY)

... and read UUID values like this...

while (rs.net() {
    UUID id = UUID.nameUUIDFromBytes(rs.getBytes("id"));
}

Mix and match?

Recently I took a MySQL database used by a Hibernate/JPA application, exported and imported it with the mysqldump tool, and tried using the data with some raw JDBC code. The Hibernate/JPA code and read and write UUID's, and the raw JDBC code can read and write UUID's. However, if I:

  1. Use JDBC to read in a UUID that was originally written by Hibernate/JPA, and
  2. Have my JDBC code write that same UUID elsewhere, then
  3. The value stored in MySQL by the JDBC code does not match the original value!

Has anyone ever seen this before, or have any theories? I don't fully understand how Hibernate/JPA is serializing the UUID value under the covers, so I'm not sure what I might need to do differently with my raw JDBC code to produce the same result.

Thanks!

Steve Perkins
  • 11,520
  • 19
  • 63
  • 95
  • 1
    BTW, Postgres supports [UUID as a data type](https://www.postgresql.org/docs/current/static/datatype-uuid.html) including indexing and primary key. Use `getObject`/`setObject` in the JDBC driver. – Basil Bourque Jul 01 '16 at 01:45

1 Answers1

1

The answer is available in Mysql Documentation for Binary and Varbinary. 

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.

Binary in mysql stores binary strings which means byte strings not characters.

If you want to use UUID as it is you would been better of using VARCHAR type both in mysql and postgresql.

Declare

@Id
@Column(name = "id")
private String id;

Write to db

PreparedStatement ps = new PreparedStatement("INSERT INTO foo (id) VALUES (?)");
ps.setObject(1, uuid.toString());

Read from db.

while (rs.net() {
    UUID id = UUID.fromString(rs.getString("id"));
}
shazin
  • 21,379
  • 3
  • 54
  • 71
  • Hmm... the mysqldump export file from contains a hex UUID value: `X'581fc98ce7c447898de130d0b01759e5'`. However, when I try to read it from a `ResultSet` as a string as you suggest, I get the error: `java.lang.IllegalArgumentException: Invalid UUID string: 581fc98ce7c447898de130d0b01759e5`. – Steve Perkins Jul 01 '16 at 01:33
  • Ahh! The issue is simply that MySQL is storing a UUID string *without hyphens*, and Java's `UUID.fromString()` method cannot handle the lack of hyphens. I got it working in conjunction with the regex discussed in this other StackOverflow question: http://stackoverflow.com/questions/18986712/creating-a-uuid-from-a-string-with-no-dashes – Steve Perkins Jul 01 '16 at 02:12
  • Thanks for the quick and detailed help! – Steve Perkins Jul 01 '16 at 02:13