Read MySQL binary(16) UUID with Java

Question

This should be a very simple question, I'm just missing something basic here and I'm having 'one of those days...' Cannot use Hibernate or other ORM. Using Java PreparedStatement.

MySQL stuff:

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, reading the UUIDs from the database...

    select hex(articleID) from article;

1C711C50E4773873AB1533401E2F420C
A1FCD341EE9311E297B700FFB00BB509
A95E06B6EEE611E297B700FFB00BB509

But dumping out what I read in the java code:

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

Now, is this because I'm removing the dashes from the UUID before storing them as binary, and the rehydration is assuming they're there?

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

Edit: if I change the the preparedStatment query to "select hex(articleID) as articleID..." and read it as a string, it's of course what the DB contains, but UUID throws an exception because the string is missing the dashes...

Solution 1

UUID artID = UUID.nameUUIDFromBytes(artIDArr);

Uses MD5 and patches bytes. 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);

Solution 2

So I solved this simply by wrapping the binary(16) with a hex() function call within the query. Not sure which is more efficient, having the DB handle the conversion or flipping the bits in Java. Either way, 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'll mark Eggen's answer as correct since he put in the effort and it probably works. 8)