H2数据库中的自增ID

Question

有没有一种方法可以为表格创建一个自增的BIGINT ID。可以这样定义：

id bigint auto_increment

但这没有任何效果（它不会自动增加）。 我想插入所有字段，但是ID字段应该由DBMS提供。 还是说我需要调用什么东西来递增ID计数器吗？

- eriq

---

https://dev59.com/_2Ys5IYBdhLWcg3wBvRp#13206514 - tostao

Answer 1

对我而言它可行。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

- Thomas Mueller

---

谢谢！我发现我的错误不在于创建，而是在插入语句中。我使用了"insert into test values('hello');"。 - eriq

如果您能给我的回答点个赞，那就太好了 :-) - Thomas Mueller

谢谢！你不需要把它抬得更高，那样就可以了。我只是不明白为什么有人会对一个正确的答案投-1票……可能是某个不理解问题的人。 - Thomas Mueller

还有一种使用 default 的语法：insert into test values(default,'hello');，适用于有很多字段的表格。 - Aubin

无懈可击的答案，Thomas！ - Gaurav

Answer 2

更新

H2最近将非标准的IDENTITY替换为标准的GENERATED BY DEFAULT AS IDENTITY语法。

IDENTITY

现代方法使用IDENTITY类型，自动生成递增的64位长整数。

这个在H2中使用的单词语法是SQL:2003标准中定义的GENERATED … AS IDENTITY的缩写变体。在PDF文档SQL:2003 Has Been Published中可以找到摘要。其他数据库也在实现这个功能，比如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

你可以在执行插入命令期间捕获生成的值。需要两个步骤。首先，在获取预处理语句时，传递标志Statement.RETURN_GENERATED_KEYS。

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

Statement::getGeneratedKeys

第二步是在执行预编译语句后调用Statement::getGeneratedKeys。您将获得一个ResultSet，其中的行是为创建的行生成的标识符。

示例应用程序

这是一个完整的示例应用程序。在Java 14上运行，启用了文本块预览功能以增加乐趣。使用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时区与协调世界时（UTC）相差8小时。因此，H2 JDBC驱动返回的OffsetDateTime对象的偏移量设置为-08:00。这实际上是一个干扰，所以在实际工作中，我会立即将该OffsetDateTime转换为UTC的Instant对象或特定时区的ZonedDateTime对象。请明确理解，Instant、OffsetDateTime和ZonedDateTime对象都代表同一时刻，同一时间线上的点。每个对象都通过不同的挂钟时间来观察同一时刻。想象一下，加利福尼亚、魁北克和冰岛（其时区为UTC，偏移量为零）的3个人在同一时间点进行电话会议，他们同时看着各自墙上的时钟。

生成的密钥：1 id_1 | 开始时间：2021-01-23T16:00-08:00 | 持续时间：PT2H ➙ 运行时间：2021-01-23T19:00-05:00[美国/蒙特利尔] 至 2021-01-23T21:00-05:00[美国/蒙特利尔]

---

顺便说一下，在一个预约应用程序的实际工作中，我们会在Java和数据库中使用不同的数据类型。

在Java中，我们会使用LocalDateTime和ZoneId。在数据库中，我们会使用类似于SQL标准类型"TIMESTAMP WITHOUT TIME ZONE"的数据类型，并在第二列中存储所需的时区名称。在从数据库中检索值以构建日程安排时，我们会将时区应用于存储的日期时间，以获得一个ZonedDateTime对象。这将使我们能够预约某个特定时间，而不受该司法管辖区的政治家对UTC偏移量所做的更改的影响。

- Basil Bourque

---

如果您展示一个插入并创建表，那么这个答案会更有用。 - JesseBoyd

@JesseBoyd 感谢您的建议。我添加了一个完整的示例应用程序，展示了表的创建，插入行，检索生成的键以及将表转储到控制台。 - Basil Bourque

对我来说，似乎身份类型不再可用。请参阅https://github.com/h2database/h2database/issues/3326。这个方法有效：BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY。 - undefined

@Nils 谢谢你的提醒。我在答案的顶部添加了一个提及。这个新的语法是标准的SQL。这是一个很好的改变。 - undefined

Answer 3

非常简单：

id int auto_increment primary key

会自动创建 Sequence 对象

- beloblotskiy

Answer 4

您也可以使用default:

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

- ericj

Answer 5

对于

Oracle模式（jdbc:h2:mem:testdb;Mode=Oracle）

你有一个不同的语法，即

GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY 示例：

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

- Alaa jawhar

Answer 6

id bigint(size) zerofill not null auto_increment,

- eveo

---

H2 数据库不支持 zerofill。 - Thomas Mueller