When using a UUID data type as the primary key of a table, and asking H2 to generate each UUID value by default upon INSERT of a new record, how does one access the value of the newly generated UUID value?
I am using plain JDBC 4.x in a Java app, if that helps with a solution.
I know SCOPE_IDENTITY function returns a long for a key generated on a column marked as IDENTITY for an auto-incrementing sequence number. But I am using UUID rather than an incrementing number as my primary key column types.
Advertisement
Answer
Statement::getGeneratedKeys
As seen in comments and the correct Answer by YCF_L, the solution lies in standard JDBC: Call Statement::getGeneratedKeys. This yields a ResultSet of the key values generated by default in the previous use of that statement. This works with PreparedStatement, and works with auto-generating UUID values as the primary key.
Statement.RETURN_GENERATED_KEYS
The catch is that by default you do not get back generated keys. You must activate this feature by passing an extra argument to your Connection::prepareStatement call. The extra argument is an int, using a constant defined on the Statement interface, Statement.RETURN_GENERATED_KEYS. In modern Java that would have likely have been defined as an Enum, but JDBC dates back to the earliest days of Java, so the argument is a simple int.
Example app
Here is a complete example app, in a single file.
package work.basil.example.h2.auto_uuid;
import java.sql.*;
import java.util.UUID;
public class App {
public static void main ( String[] args ) {
App app = new App();
app.doIt();
}
private void doIt ( ) {
try {
Class.forName( "org.h2.Driver" );
} catch ( ClassNotFoundException e ) {
e.printStackTrace();
}
try (
Connection conn = DriverManager.getConnection( "jdbc:h2:mem:auto_uuid_example_db;DB_CLOSE_DELAY=-1" ) ; // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
Statement stmt = conn.createStatement() ;
) {
String sql = "CREATE TABLE person_ ( n" +
" pkey_ UUID NOT NULL DEFAULT RANDOM_UUID() PRIMARY KEY , n" +
" name_ VARCHAR NOT NULL n" +
");";
stmt.execute( sql );
// Insert row.
sql = "INSERT INTO person_ ( name_ ) n";
sql += "VALUES ( ? ) n";
sql += ";";
try (
PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
) {
pstmt.setString( 1 , "Jesse Johnson" );
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
System.out.println( "INFO - Reporting generated keys." );
while ( rs.next() ) {
UUID uuid = rs.getObject( 1 , UUID.class );
System.out.println( "generated keys: " + uuid );
}
}
// Dump all rows.
System.out.println( "INFO - Reporting all rows in table `person_`." );
sql = "SELECT * FROM person_";
try ( ResultSet rs = stmt.executeQuery( sql ) ; ) {
while ( rs.next() ) {
UUID pkey = rs.getObject( "pkey_" , UUID.class );
String name = rs.getString( "name_" );
System.out.println( "Person: " + pkey + " | " + name );
}
}
} catch ( SQLException e ) {
e.printStackTrace();
}
}
}
When run.
INFO – Reporting generated keys.
generated keys: 9c6ce984-151b-4e64-8334-d96e17be9525
INFO – Reporting all rows in table
person_.Person: 9c6ce984-151b-4e64-8334-d96e17be9525 | Jesse Johnson
If you want to insert multiple rows at a time, rather than one, use batching. See: Java: Insert multiple rows into MySQL with PreparedStatement.
If you have multiple columns being auto-generated, rather than just the one single UUID column seen here, see the other Answer by YCF_L.