java - How to use SQLite UNIQUEIDENTIFIER type fields in Java

Question

I have a Sqlite database with Id fields declared as type UNIQUEIDENTIFIER. The DB Browser for SQLite shows these fields as type BLOB and the type of data in cell as Binary 16 bytes. They looks like "a1 75 e0 47 e6 07 47 37 a7 ea 0a 8d 7f 22 f6 55". My question is how to store these type of field from a resultset to use them in another SQL statement. Example:

      sql = "select id from contract where customer= 'John'"
      ps = connection.prepareStatement(sql);
      rs = ps.executeQuery();
      while (rs.next() {
          id = rs.getString(1);
      }
      sql = "select * from invoices where customerid = " + id;

I had tried with id as type byte, array of bytes, inputstream, nothing works. Thanks for your answer.

标签： javasqliteuniqueidentifier

Answer 1

They probably implemented 16 unique bytes by a UUID. Unfortunately there is no appropriate SQL type in ResultSet access. The java class UUID is a fitting data holder:

public static UUID toUUID(byte[] bytes) {
    ByteBuffer idBuffer = ByteBuffer.wrap(bytes);
    return new UUID(idBuffer.getLong(), idBuffer.getLong());
}

    UUID uuid = toUUID(rs.getBytes(1));

public static byte[] fromUUID(UUID uuid) {
    byte[] bytes = new byte[16];
    ByteBuffer idBuffer = ByteBuffer.wrap(bytes);
    idBuffer.putLong(uuid.getMostSignificantBits());
    idBuffer.putLong(uuid.getLeastSignificantBits());
    return bytes;
}

    rs.setBytes(1, fromUUID(uuid));

The reasons for not using byte[] is: it is a too low level type, not saying anything and in need of some operations.

UUID on the other hand bears the meaning of a unique identifier. It als has equals, compareTo, hashCode and even a somewhat readable toString.