Return primary key value generated by default in H2 database upon INSERT of new row, for UUID type column

Question

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.

Answer 1

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.