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

Are you sure it's worth it to store these as binary? The length savings is usually minimal, and it makes it super annoying to query manually. — tadman
– tadman, Commented Jul 18, 2013 at 14:47
Yup, we're going to have a brazillion records, and we need it to be a smaller PK. And yes, UUID isn't the best PK out there. I know. But it's what I've got. Thanks. — MonkeyWrench
– MonkeyWrench, Commented Jul 18, 2013 at 14:49

BenMorel · Accepted Answer · 2014-01-06 17:57:11Z

5

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);

edited Jan 6, 2014 at 17:57

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Jul 18, 2013 at 15:12

Joop Eggen

110k8 gold badges89 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MonkeyWrench Over a year ago

Java 7 jdk doesn't make the UUID( byte[] ) constructor visible.. "The constructor UUID(byte[]) is not visible"

Joop Eggen Over a year ago

@MonkeyWrench I did oversee that; made a function for it, and corrected answer.

MonkeyWrench Over a year ago

lol. Maybe I should just store the dashes in the DB, use "hex()" in the select statement, and read them as a string in Java. But I'm surprised there isn't a better way to deal with this. "everyone" storing UUIDs in MySQL state using binary(16) is the way to go. What am I missing here?

MonkeyWrench · Accepted Answer · 2013-07-20 15:52:42Z

5

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)

answered Jul 20, 2013 at 15:52

MonkeyWrench

1,8292 gold badges24 silver badges49 bronze badges

1 Comment

scottb Over a year ago

It's a nice solution and fine if you are not constrained by performance concerns. Parsing a 16 byte number into a String and then parsing the resulting String back into a 16 byte number (which is what a UUID is internally) is a solution which leaves the door open for performance improvements.

Collectives™ on Stack Overflow

Read MySQL binary(16) UUID with Java

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related