Gain auto business by 100 at a given value of 1
In the used query to create the database tables, derby are composed of an auto-incrementing primary column.
CREATE TABLE \"table\" (\n"
+ " \"id\" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,\n"
+ " \"path\" VARCHAR(2000) DEFAULT NULL,\n"
+ " \"downloaded\" BOOLEAN DEFAULT false NOT NULL,\n"
+ " \"retried_times\" SMALLINT DEFAULT 0 NOT NULL,\n"
+ " \"name\" VARCHAR(40),\n"
+ " \"downloaded_date\" TIMESTAMP DEFAULT NULL,\n"
+ " PRIMARY KEY (\"id\")\n"
When I insert a line through spring jdbc
, it increments by 100. Is there an error in the request?
source to share
This is due to the pre-distribution of values for auto-increment columns . Derby, which is an in-memory database, caches auto-increment values when the database is first loaded into memory. Then the future auto-increment column values are generated using the cache instead of querying the database over and over again. If the database is not closed properly, unused values from the cache are lost forever.
You have two options for solving this problem:
- Add
;shutdown=true
to JDBC url. This will stop the database down when the application ends. - Set the property to a
derby.language.sequence.preallocator
value1
(its default is100
). This ensures that the column value is never cached.
Note that most databases behave the same for sequences. For example H2 has the same behavior, but uses the cache size 32
instead of the 100
way Derby does.
source to share