How should I use UUID with JavaDB/Derby and JDBC?

Question

I currently use INT as type for primary key in JavaDB (Apache Derby), but since I'm implementing an distributed system I would like to change the type to java.util.UUID. A few questions about this:

• What datatype in JavaDB/Derby should I use for UUID? I have seen CHAR(16) FOR BIT DATA been mentioned but I don't know much about it. Is VARCHAR(16) an alternative?

• How should I use it with JDBC? E.g. in an PreparedStatement, how should I set and get an UUID?

• If I later would likte to change database to SQL Server, is there a compatible datatype to java.util.UUID?

Simply, How should I use UUID with JavaDB/Derby and JDBC?

Answer 1

UUID is a 128 bit value. The CHAR(16) FOR BIT DATA type reflects that it is bit data stored in character form for conciseness. I don't think VARCHAR(16) would work because it doesn't have the bit flag. The database would have to be able to convert the binary data to character data which deals with encoding and is risky. More importantly, it wouldn't buy you anything. Since a UUID is always 128 bits, you don't get the space savings from using VARCHAR over CHAR. So you might as well use the intended CHAR(16) FOR BIT DATA.

With JDBC, I think you use the get/setBytes() method since it is dealing with small amounts of binary data. (Not positive, would have to try this)

And no idea about the SQL Server part.

Answer 2

You could convert the UUID to a string and store it as VARCHAR. Most UUID string formats are similar to this one: 32 digits separated by hyphens: 00000000-0000-0000-0000-000000000000, so then you'd want a VARCHAR(36), or make it something like VARCHAR(64) if you please, since it doesn't hurt to have extra 'space' available in your VARCHAR -- only the actual digits are stored.

Once you've converted it to a string, just call Statement.SetString to include it in your INSERT statement.