auto increment ID in H2 database

Question

Is there a way to have an auto_incrementing BIGINT ID for a table. It can be defined like so

id bigint auto_increment

but that has no effect (it does not increment automatically). I would like to insert all fields but the ID field - the ID field should be provided by the DBMS. Or do I need to call something to increment the ID counter?

Splash

https://mcmap.net/q/260282/-create-table-syntax-not-working-in-hsql – Whosoever

Answer 1

It works for me. JDBC URL: jdbc:h2:~/temp/test2

drop table test;
create table test(id bigint auto_increment, name varchar(255));
insert into test(name) values('hello');
insert into test(name) values('world');
select * from test; 

result:

ID  NAME  
1   hello
2   world

Bent

Thank you! I learned, that my mistake was not in the create, but in the insert statement. I used insert into test values('hello'); – Splash

Another syntax using default exists: insert into test values(default,'hello'); helpful for tables with a lot of fields. – Sauternes

For me this didn't work with a NUMBER and version 1.4.199 of h2. The other solution involving IDENTITY worked. – Priscella

Answer 2

GENERATED … AS IDENTITY

Update

H2 has recently swapped the non-standard IDENTITY for the standard GENERATED BY DEFAULT AS IDENTITY & GENERATED ALWAYS AS IDENTITY syntax.

CREATE TABLE event_ 
( 
    pkey_ INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY , -- Implicitly NOT NULL.
    …
)
;

See documentation on Column Definition.

IDENTITY

The modern approach uses the IDENTITY type, for automatically generating an incrementing 64-bit long integer.

This single-word syntax used in H2 is an abbreviated variation of GENERATED … AS IDENTITY defined in the SQL:2003 standard. See summary in PDF document SQL:2003 Has Been Published. Other databases are implementing this, such as Postgres.

CREATE TABLE event_ 
( 
    pkey_ IDENTITY NOT NULL PRIMARY KEY ,  -- ⬅ `identity` = auto-incrementing long integer.
    name_ VARCHAR NOT NULL ,
    start_ TIMESTAMP WITH TIME ZONE NOT NULL , 
    duration_ VARCHAR NOT NULL
) 
;

Example usage. No need to pass a value for our pkey column value as it is being automatically generated by H2.

INSERT INTO event_ ( name_ , start_ , stop_ )
VALUES ( ? , ? , ? ) 
;

And Java.

ZoneId z = ZoneId.of( "America/Montreal" ) ;
OffsetDateTime start = ZonedDateTime.of( 2021 , Month.JANUARY , 23 , 19 , 0 , 0 , 0 , z ).toOffsetDateTime() ; 
Duration duration = Duration.ofHours( 2 ) ;

myPreparedStatement.setString( 1 , "Java User Group" ) ;
myPreparedStatement.setObject( 2 , start ) ;
myPreparedStatement.setString( 3 , duration.toString() ) ; 

Returning generated keys

Statement.RETURN_GENERATED_KEYS

You can capture the value generated during that insert command execution. Two steps are needed. First, pass the flag Statement.RETURN_GENERATED_KEYS when getting your prepared statement.

PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;

Statement::getGeneratedKeys

Second step is to call Statement::getGeneratedKeys after executing your prepared statement. You get a ResultSet whose rows are the identifiers generated for the created row(s).

Example app

Here is an entire example app. Running on Java 14 with Text Blocks preview feature enabled for fun. Using H2 version 1.4.200.

package work.basil.example;

import org.h2.jdbcx.JdbcDataSource;

import java.sql.*;
import java.time.*;
import java.util.Objects;

public class H2ExampleIdentity
{
    public static void main ( String[] args )
    {
        H2ExampleIdentity app = new H2ExampleIdentity();
        app.doIt();
    }

    private void doIt ( )
    {
        JdbcDataSource dataSource = Objects.requireNonNull( new JdbcDataSource() );  // Implementation of `DataSource` bundled with H2.
        dataSource.setURL( "jdbc:h2:mem:h2_identity_example_db;DB_CLOSE_DELAY=-1" ); // Set `DB_CLOSE_DELAY` to `-1` to keep in-memory database in existence after connection closes.
        dataSource.setUser( "scott" );
        dataSource.setPassword( "tiger" );

        String sql = null;

        try (
                Connection conn = dataSource.getConnection() ;
        )
        {
            sql = """
                  CREATE TABLE event_
                     ( 
                        id_ IDENTITY NOT NULL PRIMARY KEY,  -- ⬅ `identity` = auto-incrementing integer number.
                        title_ VARCHAR NOT NULL ,
                        start_ TIMESTAMP WITHOUT TIME ZONE NOT NULL ,
                        duration_ VARCHAR NOT NULL
                      )
                  ;
                  """;
            System.out.println( "sql:  \n" + sql );
            try ( Statement stmt = conn.createStatement() ; )
            {
                stmt.execute( sql );
            }

            // Insert row.
            sql = """
                  INSERT INTO event_ ( title_ , start_ , duration_ )
                  VALUES ( ? , ? , ? )
                  ;
                  """;
            try (
                    PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
            )
            {
                ZoneId z = ZoneId.of( "America/Montreal" );
                ZonedDateTime start = ZonedDateTime.of( 2021 , 1 , 23 , 19 , 0 , 0 , 0 , z );
                Duration duration = Duration.ofHours( 2 );

                pstmt.setString( 1 , "Java User Group" );
                pstmt.setObject( 2 , start.toOffsetDateTime() );
                pstmt.setString( 3 , duration.toString() );

                pstmt.executeUpdate();
                try (
                        ResultSet rs = pstmt.getGeneratedKeys() ;
                )
                {
                    while ( rs.next() )
                    {
                        int id = rs.getInt( 1 );
                        System.out.println( "generated key: " + id );
                    }
                }
            }

            // Query all.
            sql = "SELECT * FROM event_ ;";
            try (
                    Statement stmt = conn.createStatement() ;
                    ResultSet rs = stmt.executeQuery( sql ) ;
            )
            {
                while ( rs.next() )
                {
                    //Retrieve by column name
                    int id = rs.getInt( "id_" );
                    String title = rs.getString( "title_" );
                    OffsetDateTime odt = rs.getObject( "start_" , OffsetDateTime.class );  // Ditto, pass class for type-safety.
                    Instant instant = odt.toInstant();  // If you want to see the moment in UTC.
                    Duration duration = Duration.parse( rs.getString( "duration_" ) );

                    //Display values
                    ZoneId z = ZoneId.of( "America/Montreal" );
                    System.out.println( "id_" + id + " | start_: " + odt + " | duration: " + duration + " ➙ running from: " + odt.atZoneSameInstant( z ) + " to: " + odt.plus( duration ).atZoneSameInstant( z ) );
                }
            }
        }
        catch ( SQLException e )
        {
            e.printStackTrace();
        }
    }
}

Next, see results when run.

Instant, OffsetDateTime, & ZonedDateTime

At the time of this execution, my JVM’s current default time zone is America/Los_Angeles. At the point in time of the stored moment (January 23, 2021 at 7 PM in Québec), the zone America/Los_Angeles had an offset-from-UTC of eight hours behind. So the OffsetDateTime object returned by the H2 JDBC driver is set to an offset of -08:00. This is a distraction really, so in real work I would immediately convert that OffsetDateTime to either an Instant for UTC or ZonedDateTime for a specific time zone I had in mind. Be clear in understanding that the Instant, OffsetDateTime, and ZonedDateTime objects would all represent the same simultaneous moment, the same point on the timeline. Each views that same moment through a different wall-clock time. Imagine 3 people in California, Québec, and Iceland (whose zone is UTC, an offset of zero) all talking in a conference call end they each looked up at the clock on their respective wall at the same coincidental moment.

generated key: 1

id_1 | start_: 2021-01-23T16:00-08:00 | duration: PT2H ➙ running from: 2021-01-23T19:00-05:00[America/Montreal] to: 2021-01-23T21:00-05:00[America/Montreal]

---

By the way, in real work on an app booking future appointments, we would use a different data type in Java and in the database.

We would have used LocalDateTime and ZoneId in Java. In the database, we would have used a data type akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONE with a second column for the name of the intended time zone. When retrieving values from the database to build an scheduling calendar, we would apply the time zone to the stored date-time to get a ZonedDateTime object. This would allow us to book appointments for a certain time-of-day regardless of changes to the offset-from-UTC made by the politicians in that jurisdiction.

Angeles

this answer would be more useful if you show an insert as well as creating the table – Gordan

@Gordan Thanks for the suggestion. I added an entire example app showing the creation of the table, inserting a row, retrieving the generated key, and dumping the table to console. – Angeles

It looks for me that the identity type is no longer available. see github.com/h2database/h2database/issues/3326 This worked: BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY – Economical

@Economical Thanks for the notice. I added a mention at top of Answer. That new syntax is standard SQL. This is a good change. – Angeles

Answer 3

Very simple:

id int auto_increment primary key

H2 will create Sequence object automatically

Curnin

Answer 4

You can also use default:

create table if not exists my(id int auto_increment primary key,s text);
insert into my values(default,'foo');

Kono

Answer 5

for

oracle Mode (jdbc:h2:mem:testdb;Mode=Oracle)

you have a different syntax which is

GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY Example:

   CREATE TABLE "ONLINE_ARCHIVE"
   (
       "TABLE_KY"            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       "OTHER_COLUMN"        VARCHAR2(4000)
   )

Boneblack

Answer 6

id bigint(size) zerofill not null auto_increment,

Juno

zerofill is not supported by the H2 database. – Bent