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
);
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;
}
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?
