使用 H2 資料庫中的序列

1.概述

為了提高效率和可擴展性，我們的生產環境應用程式通常支援基於序列的主鍵生成。然而，在開發或測試過程中，我們經常會切換到 H2 之類的記憶體資料庫。

在本教程中，我們將探討如何在 H2 中使用序號。

2. 基於序列的 ID 生成

資料庫序列提供資料庫端對 ID 產生的控制，在高並發下提供比IDENTITY更好的效能。

許多流行的資料庫都支援序列，包括 PostgreSQL、Oracle、SQL Server 等。

在本教程中，我們將重點放在如何在 H2 資料庫中使用序列。首先，我們將學習如何透過原生 SQL 語句建立序列、取得下一個值以及刪除序列。然後，我們將展示一個在 Spring Boot 和 JPA 中使用 H2 序列作為自動產生的@Id的範例。

由於我們在測試中經常需要使用H2來模擬生產資料庫，因此我們以Oracle為例，介紹如何讓H2相容於Oracle的序列查詢語句。

為簡單起見，我們將利用 Spring Boot 測試來驗證結果。

接下來，讓我們深入探討。

3. 使用 H2 的序列

在本節中，我們將示範如何透過 Spring Boot 測試處理 H2 序列。

3.1. 配置

我們首先在 Spring Boot 設定檔application-h2-seq.yml中建立一個嵌入式記憶體 H2 資料庫：

spring:

 datasource:

 driverClassName: org.h2.Driver

 url: jdbc:h2:mem:seqdb

 username: sa

 password:

 jpa:

 hibernate:

 ddl-auto: none

要載入檔案application-seq-h2.yml,我們的測試應該啟動h2-seq設定檔：

@ExtendWith(SpringExtension.class)

 @ActiveProfiles("h2-seq")

 @Transactional

 public class H2SeqDemoIntegrationTest {

 }

接下來，讓我們建立並使用一些序列。

3.2. 建立、查詢和刪除 H2 序列

我們首先來看一個測試方法，它涵蓋了使用原生 SQL 語句建立序列、取得其「下一個值」以及刪除序列的操作。然後，我們將總結這些用法：

private final String sqlNextValueFor = "SELECT NEXT VALUE FOR my_seq";

 private final String sqlNextValueFunction = "SELECT nextval('my_seq')";



 @Test

 void whenCreateH2SequenceWithDefaultOptions_thenGetExpectedNextValueFromSequence() {

 entityManager.createNativeQuery("CREATE SEQUENCE my_seq").executeUpdate();



 Long nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();

 assertEquals(1, nextValue);



 nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFor).getSingleResult();

 assertEquals(2, nextValue);



 nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();

 assertEquals(3, nextValue);



 entityManager.createNativeQuery("DROP SEQUENCE my_seq").executeUpdate();

 }

如範例所示，我們可以使用下列 SQL 語句在 H2 資料庫中建立、取得值和刪除序列：

建立 – CREATE SEQUENCE <name>
取得下一個值 — “ SELECT NEXT VALUE FOR <name> ” 或使用nextval()函數：“ SELECT nextval(<name>) ”
刪除 – DROP SEQUENCE <name>

值得注意的是，上面的CREATE語句將建立一個初始值為 1 且增量為 1 的序列，這是預設選項。但是，有時我們希望建立一個具有不同起始值和自訂增量的序列。

接下來，我們來看看如何實現這一點。

3.3. 自訂起始值和增量值

我們可以在建立語句中使用START WITH和INCREMENT BY來設定所需的初始值和增量值：

@Test

 void whenCustomizeH2Sequence_thenGetExpectedNextValueFromSequence() {

 entityManager.createNativeQuery("CREATE SEQUENCE my_seq START WITH 1000 INCREMENT BY 10")

 .executeUpdate();



 Long nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFor).getSingleResult();

 assertEquals(1000, nextValue);



 nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();

 assertEquals(1010, nextValue);



 nextValue = (Long) entityManager.createNativeQuery(sqlNextValueFor).getSingleResult();

 assertEquals(1020, nextValue);



 entityManager.createNativeQuery("DROP SEQUENCE my_seq").executeUpdate();

 }

在這個例子中，我們建立了my_seq序列，它從 1000 開始並以 10 為增量。

3.4. 在 JPA 實體中使用序列

接下來，讓我們使用 H2 序列來產生 JPA 實體的主鍵：

@Entity

 @Table(name = "book")

 class Book {

 @Id

 @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "book_seq_gen")

 @SequenceGenerator(name = "book_seq_gen", sequenceName = "book_seq", allocationSize = 1)

 private Long id;

 private String title;



 public Book() {

 }



 public Book(String title) {

 this.title = title;

 }



 // ... getters and setters are omitted

 }

在Book實體中：

@GeneratedValue與GenerationType.SEQUENCE告訴 JPA 使用序列
@SequenceGenerator將我們的實體對應到命名的資料庫序列
allocationSize = 1確保 ID 是連續的，而不是批量的

接下來，讓我們建立一個測試來驗證序列行為：

@Test

 void whenSaveEntityUsingSequence_thenCorrect() {

 entityManager.createNativeQuery("CREATE SEQUENCE book_seq").executeUpdate();

 Book book1 = new Book("book1");

 assertNull(book1.getId());

 entityManager.persist(book1);

 assertEquals(1, book1.getId());



 Book book2 = new Book("book2");

 entityManager.persist(book2);

 assertEquals(2, book2.getId());

 }

測試表明，序列 ID 產生器按預期工作。

在測試中，我們手動建立了book_seq序列，因為我們有「 jpa.hibernate.ddl-auto=none 」。如果我們有ddl-auto=create-drop或create ，JPA 會自動為我們建立序列。

4. 使 H2 序列與 Oracle 特定的 SQL 語句協同運作

在實際測試中，我們經常使用記憶體型 H2 資料庫來模擬生產環境。然而，H2 預設使用其自身的方言和 SQL 行為，這可能與我們的目標生產資料庫有所不同。

例如，我們可以使用SELECT <seq_name>.nextval FROM dual從 Oracle 序列中取得下一個值。但是 H2 的方言不支援dual表，因此預設情況下，帶有dual的語句在 H2 中不起作用。

為了解決這個問題，我們可以在 JDBC URL 中指定MODE=…選項來設定 SQL 相容模式。此選項告訴 H2 像其他資料庫一樣運作。

例如，我們可以在 H2 資料庫的 JDBC URL（ application-h2-seq-oracle.yml ）中新增MODE=Oracle ：

spring:

 datasource:

 driverClassName: org.h2.Driver

 url: jdbc:h2:mem:seqdb;MODE=Oracle

 username: sa

 password:

然後，Oracle序列查詢將按預期工作：

@ExtendWith(SpringExtension.class)

 @ActiveProfiles("h2-seq-oracle")

 @Transactional

 public class H2SeqAsOracleDemoIntegrationTest {

 @Autowired

 private EntityManager entityManager;



 @Test

 void whenCreateH2SequenceWithDefaultOptions_thenGetExpectedNextValueFromSequence() {

 entityManager.createNativeQuery("CREATE SEQUENCE my_seq").executeUpdate();



 String sqlNextValueFor = "SELECT NEXT VALUE FOR my_seq";

 BigDecimal nextValueH2 = (BigDecimal) entityManager

 .createNativeQuery(sqlNextValueFor).getSingleResult();

 assertEquals(0, BigDecimal.ONE.compareTo(nextValueH2));



 String sqlNextValueOralceStyle = "SELECT my_seq.nextval FROM dual";

 BigDecimal nextValueOracle = (BigDecimal) entityManager.createNativeQuery(sqlNextValueOralceStyle)

 .getSingleResult();

 assertEquals(0, BigDecimal.TWO.compareTo(nextValueOracle));



 String sqlNextValueFunction = "SELECT nextval('my_seq')";

 nextValueOracle = (BigDecimal) entityManager.createNativeQuery(sqlNextValueFunction).getSingleResult();

 assertEquals(0, BigDecimal.valueOf(3).compareTo(nextValueOracle));



 entityManager.createNativeQuery("DROP SEQUENCE my_seq").executeUpdate();

 }

 }

我們可以看到，使用MODE=Oracle, Oracle 查詢和標準 H2 查詢都可以運作並給出正確的結果。

還需要注意的是，當MODE=Oracle,序列值將會對應到BigDecimal而不是Long 。

5. 結論

在本文中，我們探討如何在 H2 資料庫中使用序號，並了解到設定 H2 的相容模式可以使測試更加真實，並且不太可能在生產中中斷。

與往常一樣，範例的完整原始程式碼可在 GitHub 上找到。

本作品係原創或者翻譯，採用《署名-非商業性使用-禁止演繹4.0國際》許可協議