Incorrect auto-add to Derby / Java DB embedded database

Question

I am developing an accounting program that uses the Apache Derby database in native mode. I have a Branch table 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 ); 

When I insert a new record into the Branch table, auto-incrementing by 1 does not work properly. I get the following result:

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

But the result should be as follows:

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

This 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; } 

And here is the way to insert a new record into 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 am I getting this result?

Answer 1

The sequence you are observing is the result of this error: https://issues.apache.org/jira/browse/DERBY-5151

The following documentation describes why this happens: https://db.apache.org/derby/docs/10.9/ref/rrefproperpreallocator.html

To summarize ... The generated values ​​are pre-selected (the default is 100 values). When the database is disconnected incorrectly, those previously allocated values ​​leak out - they are simply discarded, and when the database is loaded again, the allocator starts counting where it stopped (by entering a space in the sequence).

In other words, this is the expected behavior - to avoid this, make sure you close your database in an orderly manner:

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