UUID Values From JDBC to Postgres

I'm a big fan of UUIDs in general and especially for database record identifiers. If using a modern database such as Postgres that natively understands and stores the 128-bit value of a UUID, then you gain many benefits with the main cost being the double or quadruple storage space of the otherwise common 32-bit or 64-bit serial integer number.

Unfortunately, neither the SQL standard nor JDBC has seen fit to recognize UUIDs as a data type. So that means a second cost to using UUIDs in a database: Dragging those UUID values across the chasm of JDBC + SQL. One one end, we have Java which understands UUID as a built-in class (java.util.UUID). That's good. On the other end, we have Postgres which understands UUID as a native data type. That's good. The problem is in between.

The solution is that JDBC has a trap-door to handle data types unknown to SQL, called simply object. Let's look at an example, a snippet of Java code.

// Generate or obtain data to store in database.
java.util.UUID uuid = java.util.UUID.randomUUID(); // Generate a random UUID. 
String foodName = "Croissant";
// JDBC Prepared Statement.
PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO food_ (pkey_, food_name_  ) VALUES (?,?)" );
int nthPlaceholder = 1; // 1-based counting (not an index).
preparedStatement.setObject( nthPlaceholder++, uuid ); 
preparedStatement.setString( nthPlaceholder++, foodName ); 
// Execute SQL.
if ( !( preparedStatement.executeUpdate() == 1 ) ) { 
  // If the SQL reports other than one row inserted…
  this.logger.error( "Failed to insert row into database." );
}

This code is simpler than you might expect. No need to transform the UUID as textual hex representation on the Java side. No need to cast the value on the Postgres side. By calling the ".setObject" method, and passing an actual UUID value, it all works.

Going the other direction, retrieving a UUID value from the database, call the getObject method and cast the result to a java.util.UUID object. Like so, where `rs` is a ResultSet:

java.util.UUID uuid = ( java.util.UUID ) rs.getObject( "uuid_" );

By the way, if you need to pass an empty or 'nil' value for a UUID, generate a UUID with all bits of all zero values. This is commonly understood to mean "no value" in UUID-speak. One way to generate such a UUID is:

java.util.UUID emptyUuid = new java.util.UUID( 0L, 0L );