1

I'm using Hibernate v5.4.0.Final with SQLite with this dialect but when I create a table with a UUID column it gets stored as a BLOB rather than as text in the database.

For example:

enter image description here

I want to store the data as text so I can view it in the database and I found this solution which works great by using @Type(type = "uuid-char") on the column field.

However, in my application I don't have access to the table classes so I cant add this annotation to the field easily.

Some of the comments/answers in the question I linked, claimed you could subclass the dialect you are using and use registerColumnType() in the constructor but this doesn't seem to be working and the answer is 9 years old so I don't find that surprising.

How is it possible to override the default UUID type?

kmecpp
  • 2,371
  • 1
  • 23
  • 38
  • You need to specify an *exact* version of Hibernate. – chrylis -cautiouslyoptimistic- Jan 10 '19 at 02:23
  • Possible duplicate of [Using UUIDs in SQLite](https://stackoverflow.com/questions/17277735/using-uuids-in-sqlite) – Tim Biegeleisen Jan 10 '19 at 02:24
  • 5.4.0.Final @chrylis – kmecpp Jan 10 '19 at 02:26
  • @TimBiegeleisen I was not wondering how to convert the blob back to text, my question was about how to override the default serialization of the type in the Hibernate library so that it doesn't use BLOB in the first place. – kmecpp Jan 10 '19 at 02:31
  • And I'm saying don't store the UUIDs as text, if the only reason is to view them in the database. – Tim Biegeleisen Jan 10 '19 at 02:32
  • Note that using the `@Type` annotation might make your code dependent on SQLite, which partially defeats the purpose of using Hibernate. – Tim Biegeleisen Jan 10 '19 at 02:35
  • The question is still valid though and it definitely is not a duplicate of the one you marked it as. Regardless of whether the performance of what I'm doing is slightly worse than with using a BLOB I still would like to know if anyone has a solution to the problem so that if I am confronted with something similar in the future or someone else has a better reason for storing UUID's as text then they can also know the answer – kmecpp Jan 10 '19 at 02:38
  • I just realized. Thank you – kmecpp Jan 10 '19 at 02:43

1 Answers1

0

I actually recommend storing your UUIDs in the SQLite table as 16 byte binary BLOBs. This is the most compact and efficient way to store a UUID in a SQL database. If you want a way to view the binary UUIDs as text, then this SO question offers a couple of options:

SELECT hex(uuid) FROM yourTable
SELECT quote(uuid) FROM yourTable

If you really want to store your UUIDs as strings, then you should convert them to string in your Java/Hibernate layer, e.g.

String uuid = UUID.randomUUID().toString();

Then, just annotate the corresponding SQLite column as text and treat it as any other text column.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360