I have a JavaFX application which uses an embedded database to store all data. Establishing a connection and inserting data works just fine besides the column 'id' auto incrementing by 100 instead of 1. I've read(Wrong auto increment in embedded Derby/ Java DB) that I have to shut down the database properly by using following code in my main class:
@Override
public void stop() {
try {
DriverManager.getConnection("jdbc:derby:"+"db"+";shutdown=true");
}
catch (SQLException sqlException){
sqlException.printStackTrace();
System.out.println("Database shutted down...");
}
}
However it's still incrementing by 100 and I'm not sure why.
My code for establishing a connection and doing inserts:
try {
Connection connection = DriverManager.getConnection("jdbc:derby:db");
connection.setAutoCommit(true);
System.out.println("Connection established");
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO orders(order_name, order_date, price) VALUES (?, ?, ?)");
preparedStatement.setString(1, "TestOrder");
preparedStatement.setDate(2, Date.valueOf(LocalDate.now()));
preparedStatement.setDouble(3, 1.1);
preparedStatement.execute();
preparedStatement.close();
connection.close();
} catch (SQLException throwables) {
System.out.println("Unable to establish connection");
throwables.printStackTrace();
}
And my SQL statement to create the table
DROP TABLE orders;
CREATE TABLE orders(
order_number INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1),
order_name VARCHAR(100),
order_date DATE,
price DECIMAL(4, 2));