Reading MySQL binary (16) UUID with Java

Question

This should be a very simple question, I just missed something basic here, and I have "one of those days ..." It is impossible to use Hibernate or another ORM. Using Java PreparedStatement.

MySQL Material:

CREATE TABLE `article` ( `articleID` binary(16) NOT NULL, `publisherID` bigint(20) DEFAULT NULL, PRIMARY KEY (`articleID`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8$$ insert into article ( articleID, publisherID ) values ( (UNHEX(REPLACE(UUID(),'-',''))), 1111 ); 

Java stuff

 PreparedStatement ps = connection.prepareStatement( "select articleID, publisherID from article" ); ResultSet rs = ps.executeQuery(); while( rs.next()) { byte[] artIDArr = rs.getBytes( "articleID" ); UUID artID = UUID.nameUUIDFromBytes( artIDArr ); } rs.close(); ps.close(); 

Now, after reading the UUID from the database ...

  select hex(articleID) from article; 1C711C50E4773873AB1533401E2F420C A1FCD341EE9311E297B700FFB00BB509 A95E06B6EEE611E297B700FFB00BB509 

But dumping what I read in java code:

 6c825dc9-c98f-37ab-b01b-416294811a84 de6337f9-f276-3e30-b9a3-8d9338a1977f 57ccb5af-1a66-329f-b069-69638e1af24f 

Now, is it because I remove the dashes from the UUIDs before storing them as binary, and does rehydration assume they are there?

What is the correct method for reading a UUID stored as binary (16) in MySql for a Uavid Jav object?

Edit: if I changed the preparedStatment's request to “select hex (articleID) as articleID ...” and read it as a string, this, of course, is what the database contains, but the UUID throws an exception because the string is missing a dash ...

Answer 1

 UUID artID = UUID.nameUUIDFromBytes(artIDArr); 

Uses MD5 and patches. Use something like

 static UUID toUUID(byte[] bytes) { if (bytes.length != 16) { throw new IllegalArgumentException(); } int i = 0; long msl = 0; for (; i < 8; i++) { msl = (msl << 8) | (bytes[i] & 0xFF); } long lsl = 0; for (; i < 16; i++) { lsl = (lsl << 8) | (bytes[i] & 0xFF); } return new UUID(msl, lsl); } UUID artID = toUUID(artIDArr); 

Answer 2

So I solved it simply by wrapping the binary code (16) by calling the hex () function in the request. Not sure if it’s more efficient, having a DB, handle bit conversion or flipping in Java. In any case, done.

  PreparedStatement ps = connection.prepareStatement( "select hex(articleID) as articleID, publisherID from article" ); ResultSet rs = ps.executeQuery(); while( rs.next()) { String artIDStr = rs.getString( "articleID" ); UUID artID = getUUIDFromString( artIDStr ); 

I will answer Eggen as it is correct, as he made efforts, and this probably works. 8)