database H2数据库中的自增ID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9353167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
auto increment ID in H2 database
提问by eriq
Is there a way to have an auto_incrementing BIGINT ID for a table. It can be defined like so
有没有办法为表设置 auto_incrementing BIGINT ID。可以这样定义
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?
但这没有效果（它不会自动增加）。我想插入除 ID 字段之外的所有字段 - ID 字段应由 DBMS 提供。还是我需要调用一些东西来增加 ID 计数器？
回答by Thomas Mueller
It works for me. JDBC URL: jdbc:h2:~/temp/test2
这个对我有用。JDBC 网址：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
回答by Basil Bourque
IDENTITY
IDENTITY
The modern approach uses the IDENTITYtype, for automatically generating an incrementing 64-bit long integer.
现代方法使用IDENTITY类型来自动生成递增的 64 位长整数。
This single-word syntax used in H2 is an abbreviated variation of GENERATED … AS IDENTITYdefined in the SQL:2003standard. See summary in PDF document SQL:2003 Has Been Published. Other databases are implementing this, such as Postgres.
H2 中使用的这种单字语法是SQL:2003标准中GENERATED … AS IDENTITY定义的缩写变体。请参阅 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
)
;
Example usage. No need to pass a value for our pkeycolumn value as it is being automatically generated by H2.
示例用法。无需为我们的pkey列值传递值，因为它是由 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
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_KEYSwhen getting your prepared statement.
您可以捕获在执行插入命令期间生成的值。需要两个步骤。首先，Statement.RETURN_GENERATED_KEYS在获取准备好的语句时传递标志。
PreparedStatement pstmt = conn.prepareStatement( sql , Statement.RETURN_GENERATED_KEYS ) ;
Statement::getGeneratedKeys
Statement::getGeneratedKeys
Second step is to call Statement::getGeneratedKeysafter executing your prepared statement. You get a ResultSetwhose rows are the identifiers generated for the created row(s).
第二步是Statement::getGeneratedKeys在执行准备好的语句后调用。您会得到一个，ResultSet其行是为创建的行生成的标识符。
Example app
示例应用
Here is an entire example app. Running on Java 14 with Text Blockspreview featureenabled for fun. Using H2 version 1.4.200.
这是一个完整的示例应用程序。在 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();
}
}
}
Next, see results when run.
接下来，运行时查看结果。
Instant, OffsetDateTime, & ZonedDateTime
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_Angeleshad an offset-from-UTC of eight hours behind. So the OffsetDateTimeobject 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 OffsetDateTimeto either an Instantfor UTC or ZonedDateTimefor a specific time zone I had in mind. Be clear in understanding that the Instant, OffsetDateTime, and ZonedDateTimeobjects 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.
在执行此操作时，我的 JVM 当前默认时区是America/Los_Angeles. 在存储时刻的时间点（魁北克省 2021 年 1 月 23 日晚上 7 点），该区域America/Los_Angeles与 UTC 的偏移量比 UTC 晚了 8 小时。因此，OffsetDateTimeH2 JDBC 驱动程序返回的对象设置为偏移量-08:00。这是一个真正的分心，所以在实际工作中，我会立即转换是OffsetDateTime到任何一个Instant为UTC或ZonedDateTime因为我心中有一个特定的时区。清楚地了解Instant, OffsetDateTime, 和ZonedDateTime对象都将代表同一时刻，时间线上的同一点。每个人都通过不同的挂钟时间观看同一时刻。想象一下，加利福尼亚、魁北克和冰岛（其时区是 UTC，偏移量为零）的 3 个人在电话会议结束时都在交谈，他们每个人都在同一巧合时刻抬头看着各自墙上的时钟。
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]
生成的密钥：1
id_1 | 开始_：2021-01-23T16:00-08:00 | 持续时间：PT2H？运行时间：2021-01-23T19:00-05:00[America/Montreal] 至：2021-01-23T21:00-05:00[America/Montreal]
By the way, in real work on an app booking futureappointments, we would use a different data type in Java and in the database.
顺便说一下，在应用程序预订未来约会的实际工作中，我们将在 Java 和数据库中使用不同的数据类型。
We would have used LocalDateTimeand ZoneIdin Java. In the database, we would have used a data type akin to the SQL standard type TIMESTAMP WITHOUT TIME ZONEwith 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 ZonedDateTimeobject. 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.
我们会在 Java 中使用LocalDateTime和ZoneId。在数据库中，我们会使用类似于 SQL 标准类型的数据类型TIMESTAMP WITHOUT TIME ZONE，第二列是预期时区的名称。当从数据库中检索值以构建日程安排日历时，我们会将时区应用于存储的日期时间以获取ZonedDateTime对象。这将允许我们在一天中的某个时间预订约会，而不管该司法管辖区的家对 UTC 的偏移量进行了何种更改。
回答by beloblotskiy
Very simple:
很简单：
id int auto_increment primary key
H2 will create Sequence object automatically
H2 会自动创建 Sequence 对象
回答by ericj
You can also use default:
您还可以使用default：
create table if not exists my(id int auto_increment primary key,s text);
insert into my values(default,'foo');
回答by eveo
id bigint(size) zerofill not null auto_increment,
