在H2数据库中自动递增ID[英] auto increment ID in H2 database

本文是小编为大家收集整理的关于在H2数据库中自动递增ID的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

是否有一种方法可以为桌子设置自动插入bigint ID. 可以像这样定义

id bigint auto_increment

,但这没有效果(不会自动增加). 我想插入所有字段,但是ID字段 - ID字段应由DBM提供. 还是我需要打电话给一些东西来增加ID计数器?

推荐答案

它对我有用. 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; 

结果:

ID  NAME  
1   hello
2   world

其他推荐答案

IDENTITY

现代方法使用 IDENTITY - 长整数.

h2中使用的这种单字语法是 sql "> sql "> sql "> sql "> sql sql sql, :2003 标准.请参阅pdf文档中的摘要 sql:2003已发布 .其他数据库正在实现此功能,例如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
) 
;

示例用法.无需传递我们的pkey列值的值,因为它是由H2自动生成的.

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

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() ) ; 

返回生成的密钥

Statement.RETURN_GENERATED_KEYS

您可以捕获该插入命令执行期间生成的值.需要两个步骤.首先,通过flag Statement.RETURN_GENERATED_KEYS 获得准备好的语句时.

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

Statement::getGeneratedKeys

第二步是调用 Statement::getGeneratedKeys 执行准备好的语句后.您会得到一个ResultSet,其行是为创建行生成的标识符.

示例应用程序

这是一个整个示例应用程序.在Java 14上使用 text Blocks 预览功能启用.使用H2版本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();
        }
    }
}

接下来,请参阅运行时的结果.

Instant,OffsetDateTime,&ZonedDateTime

在此执行时,我的JVM当前默认时区为America/Los_Angeles.在存储时刻的时间点(2021年1月23日,晚上7点在魁北克),该区域America/Los_Angeles落后了八个小时的偏移量.因此,由H2 JDBC驱动程序返回的OffsetDateTime对象设置为-08:00的偏移.这确实是一个分心,因此,在实际工作中,我会立即将其转换为UTC或ZonedDateTime的OffsetDateTime,对于我想到的特定时区.要明确了解OffsetDateTime,OffsetDateTime和ZonedDateTime对象都将代表同一时刻,在时间轴上的相同点.每一个都可以通过不同的墙壁时间观看同一时刻.想象一下加利福尼亚,魁北克和冰岛的3个人(其区域是UTC,零的抵消)在电话会议结束时都在交谈,他们各自在同一偶然的时刻抬头看着各自的墙上的时钟.

生成的密钥:1

id_1 | Start_:2021-01-23T16:00-08:00 |持续时间:PT2H➙从:2021-01-23T19:00-05:00 [America/Montreal]到:2021-01-23T21:00-05:00 [America/Montreal]


顺便说一句,在应用程序预订的实际工作未来约会中,我们将在Java和数据库中使用其他数据类型.

我们将在Java中使用LocalDateTime和ZoneId.在数据库中,我们将使用类似于SQL Standard类型TIMESTAMP WITHOUT TIME ZONE的数据类型,并使用第二列使用预期时区的名称.从数据库中检索值以构建调度日历时,我们将将时区应用于存储的日期时间,以获取ZonedDateTime对象.这将使我们能够预约一定时间的约会,而不管该司法管辖区的政客对偏移量的更改如何.

其他推荐答案

非常简单:

id int auto_increment primary key

H2将自动创建序列对象

本文地址:https://www.itbaoku.cn/post/597576.html

问题描述

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?

推荐答案

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

其他推荐答案

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.

其他推荐答案

Very simple:

id int auto_increment primary key

H2 will create Sequence object automatically