嵌入式Derby/Java DB中错误的自动增量 [英] Wrong auto increment in embedded Derby/ Java DB

问题描述

我正在开发一种会计程序，该程序在嵌入式模式下使用Apache Derby数据库.我有一个带有两列的表Branch:

I'm developing an accounting program that uses an Apache Derby database in embedded mode. I have a table Branch with two columns:

CREATE TABLE Branch(
     idBranch INT NOT NULL PRIMARY KEY 
     GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
     place VARCHAR(255) NOT NULL
);

当我在分支"表中插入新记录时，自动递增1不能正常工作. 我得到以下结果:

When I insert a new record in the Branch table, the auto increment by 1 doesn't work properly. I'm getting the following result:

+----------+
| idBranch |
+----------+
| 1        |
| 101      |
| 201      |
| 301      |
+----------+

但是结果应该是以下内容:

But the result should be the following:

+----------+
| idBranch |
+----------+
| 1        |
| 2        |
| 3        |
| 4        |
+----------+

这是我连接数据库的方式:

Here is how I connect to the database:

private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

public static Connection createConnection() {
    Connection connection = null;
    try {
        Class.forName(DRIVER);
        connection = DriverManager.getConnection("jdbc:derby:" + DATABASE);
    } catch (ClassNotFoundException ex) {
        logger.log(Level.SEVERE, "JDBC Driver not loaded!", ex);
        System.exit(1);
    } catch (SQLException ex) {
        // create the database
        DerbyDB derbyDB = new DerbyDB();
        connection = derbyDB.create();
    }
    return connection;
}

这是在Branch表中插入新记录的方法:

And here is the method to insert the new record in the Branch table:

private static final String CREATE_QUERY = "INSERT INTO Branch(place) VALUES(?)";

public int createBranch(Branch branch) {
    Connection connection = DerbyDAOFactory.createConnection();
    try {
        PreparedStatement statement = connection.prepareStatement(CREATE_QUERY, Statement.RETURN_GENERATED_KEYS);
        statement.setString(1, branch.getPlace());
        statement.execute();
        ResultSet result = statement.getGeneratedKeys();
        if(result.next()) {
            return result.getInt(1);
        }
    } catch (SQLException ex) {
        logger.log(Level.SEVERE, null, ex);
    }
    return -1;
}

为什么会得到这个结果?

Why do I get this result?

推荐答案

您正在观察的序列是此错误的结果:

The sequence you are observing is a consequence of this bug: https://issues.apache.org/jira/browse/DERBY-5151

以下文档描述了发生这种情况的原因: https://db .apache.org/derby/docs/10.9/ref/rrefproperpreallocator.html

The following documentation describes why it is happening: https://db.apache.org/derby/docs/10.9/ref/rrefproperpreallocator.html

总结... 生成的值是预先分配的(默认一次为100个值).当数据库错误地关闭时，那些预先分配的值将泄漏-它们将被丢弃，并且当数据库再次启动时，分配器将开始计算它从何处中断(在序列中引入间隔).

To summarize... The generated values are pre-allocated (100 values at a time by default). When the database is shutdown incorrectly those pre-allocated values are leaked - they are simply discarded and when the database is booted again the allocator starts counting where it left off (introducing a gap in the sequence).

换句话说，这是预期的行为-为避免这种情况，请确保按有序方式关闭数据库:

In other words this is expected behavior - to avoid it make sure you are closing your database in an orderly manner:

DriverManager.getConnection("jdbc:derby:;shutdown=true")

这篇关于嵌入式Derby/Java DB中错误的自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！