Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

auto increment ID in H2 database

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?

like image 367
eriq Avatar asked Feb 19 '12 20:02

eriq


People also ask

How do you auto increment an ID?

The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.

How do I create a database auto increment?

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .

What is auto increment in database?

The auto increment in SQL is a feature that is applied to a field so that it can automatically generate and provide a unique value to every record that you enter into an SQL table. This field is often used as the PRIMARY KEY column, where you need to provide a unique value for every record you add.

What will be the auto increment ID of delete?

It is often the case that a column such as the ID column on a table will auto increment. This simply means that the next insert into the table will have an ID that is one more then the previous one and therefore all ID's will be unique.


4 Answers

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
like image 82
Thomas Mueller Avatar answered Oct 07 '22 23:10

Thomas Mueller


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.

like image 38
Basil Bourque Avatar answered Oct 07 '22 23:10

Basil Bourque


Very simple:

id int auto_increment primary key

H2 will create Sequence object automatically

like image 8
beloblotskiy Avatar answered Oct 07 '22 21:10

beloblotskiy


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');
like image 5
ericj Avatar answered Oct 07 '22 21:10

ericj