Search code examples
jdbctypesuuidderbyjavadb

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

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?

Solution

  • 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.