Postgres UUID JDBC not working

by

tl;dr

myPreparedStatement.setObject( 
    … , 
    java.util.UUID.randomUUID()
)

Details

(a) Show us your code.

PreparedStatement::setObject does work when passing a java.util.UUID. You likely have some other issue in your code.

(b) See my blog post UUID Values From JDBC to Postgres for a bit of discussion and example 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." );
}

(c) I’m not sure what you mean by

The latest Java JDBC drivers for postgres claim to support UUIDs natively

Which driver? There are at least two open-source JDBC drivers for Postgres, the current/legacy one and a new rewrite “next generation” one. And there are other commercial drivers as well.

“natively”? Can you link to the documentation you read? The SQL spec has no data type for UUID (unfortunately ☹), therefore the JDBC spec has no data type for UUID. As a workaround, the JDBC driver for Postgres uses the setObject and getObject methods on PreparedStatement move the UUID across the chasm between Java ↔ SQL ↔ Postgres. See the example code above.

As the PreparedStatement JDBC doc says:

If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

Perhaps by “natively”, you confused Postgres’ native support for UUID as a data type with JDBC having a UUID data type. Postgres does indeed support UUID as a data type, which means the value is stored as 128-bits rather than multiple times that if it were stored as as ASCII or Unicode hex string. And being native also means Postgres knows how to build an index on a column of that type.

The point of my blog post mentioned above was that I was pleasantly surprised by how simple it is to bridge that chasm between Java ↔ SQL ↔ Postgres. In my first uneducated attempts, I was working too hard.

Another note about Postgres supporting UUID… Postgres knows how to store, index, and retrieve existing UUID values. To generate UUID values, you must enable the Postgres extension (plugin) uuid-ossp. This extension wraps a library provided by The OSSP Project for generating a variety of kinds of UUID values. See my blog for instructions.

By the way…

If I knew how to petition the JDBC expert group or JSR team to make JDBC aware of UUID, I certainly would. They are doing just that for the new date-time types being defined in JSR 310: Date and Time API.

Similarly, if I knew how to petition the SQL standards committee to add a data type of UUID, I would. But apparently that committee is more secretive than the Soviet Politburo and slower than a glacier.

More Related Contents:

  1. Is it possible to specify the schema when connecting to postgres with JDBC?
  2. How to get a value from the last inserted row? [duplicate]
  3. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  4. What is the most recommended way to store time in PostgreSQL using Java?
  5. How to read all rows from huge table?
  6. Driver JDBC PostgreSQL with Android [duplicate]
  7. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  8. Java JDBC ignores setFetchSize?
  9. how to copy data from file to PostgreSQL using JDBC?
  10. Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
  11. Convert a Result Set from SQL Array to Array of Strings
  12. Simulate Result set closing failed [closed]
  13. Multiple queries executed in java in single statement
  14. Difference between Statement and PreparedStatement
  15. ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long on connect to MySQL
  16. Closing JDBC Connections in Pool
  17. No suitable driver found for ‘jdbc:mysql://localhost:3306/mysql [duplicate]
  18. How to enable logging for SQL statements when using JDBC
  19. What are the pros and cons of performing calculations in sql vs. in your application
  20. SQL Server JDBC Error on Java 8: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption
  21. Can I execute multiple queries separated by semicolon with MySQL Connector/J? [duplicate]
  22. mysql prepared statement error: MySQLSyntaxErrorException
  23. Call an Oracle function from Java
  24. SQL Error: 0, SQLState: 08S01 Communications link failure [duplicate]
  25. Java Enums, JPA and Postgres enums – How do I make them work together?
  26. How to correctly write UTF-8 strings into MySQL through JDBC interface
  27. How to get the number of columns from a JDBC ResultSet?
  28. Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values
  29. JDBC MySQL connection using Unix Socket
  30. JDBC connection error : unrecognized timezone [duplicate]

Leave a Comment