CODEWITHSUNDEEP
javamysqlhibernatejpajdbc
Steve Perkins
Steve Perkins

Reputation: 11860

Storing Java UUID's in binary database columns, Hibernate/JPA vs. raw JDBC

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!

Upvotes: 1

Views: 4094

Answers (1)

shazin
shazin

Reputation: 21883

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"));
}

Upvotes: 1

Related Questions