有没有一种方法可以为表格创建一个自增的BIGINT ID。可以这样定义:
id bigint auto_increment
但这没有任何效果(它不会自动增加)。 我想插入所有字段,但是ID字段应该由DBMS提供。 还是说我需要调用什么东西来递增ID计数器吗?
有没有一种方法可以为表格创建一个自增的BIGINT ID。可以这样定义:
id bigint auto_increment
但这没有任何效果(它不会自动增加)。 我想插入所有字段,但是ID字段应该由DBMS提供。 还是说我需要调用什么东西来递增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
default
的语法:insert into test values(default,'hello');
,适用于有很多字段的表格。 - Aubin更新
H2最近将非标准的IDENTITY
替换为标准的GENERATED BY DEFAULT AS IDENTITY
语法。
IDENTITY
现代方法使用IDENTITY
类型,自动生成递增的64位长整数。
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
id int auto_increment primary key
default
:create table if not exists my(id int auto_increment primary key,s text);
insert into my values(default,'foo');
对于
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)
)
id bigint(size) zerofill not null auto_increment,
zerofill
。 - Thomas Mueller