You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Ruzal Yumaev <ru...@gmail.com> on 2015/03/25 20:55:23 UTC
(Unknown)
Hello!
I'm creating table USERS where primary key ID has autogenerating column:
ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
BY 1) PRIMARY KEY
After inserting second or more row ID column increment to 100 and
"INCREMENT BY 1" - it's multipling for one hundred.
Example from table:
ID FIO BIRTHDAY GROUPNUM
1 Name 2012-12-21 21312
101 Name 2001-12-21 23412
201 Name 2001-12-02 21323
I attach log file and maven dependency is
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.11.1.1</version>
</dependency>
Code:
String dbURL1 = "jdbc:derby:bd;create=true";
Connection conn1 = null;
try {
conn1 = DriverManager.getConnection(dbURL1);
if (conn1 != null) {
System.out.println("Connected to database");
}
Statement statement = conn1.createStatement();
try {
System.out.println("Create table USERS");
statement.executeUpdate("CREATE TABLE USERS (ID INT NOT NULL
GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY
KEY, FIO VARCHAR(255) NOT NULL,BIRTHDAY DATE, GROUPNUM VARCHAR(15))");
System.out.println("Table USERS created");
} catch (SQLException e) {
System.out.println("Table USERS already exists");
}
preallocated values for generated columns
Posted by Rick Hillegas <ri...@gmail.com>.
It sounds as though you are not shutting down your Derby engine
gracefully before your application exits. For instructions on how to
shutdown Derby gracefully, please see
http://db.apache.org/derby/docs/10.11/devguide/tdevdvlp40464.html
By default, Derby sequence generators preallocate blocks of 100 unused
values. This behavior improves the concurrency of applications which use
identity columns. The unused, preallocated values are reclaimed when you
shutdown Derby gracefully. However, they leak during an ungraceful
shutdown. For more information on the preallocation of sequence values,
please see
http://db.apache.org/derby/docs/10.11/ref/rrefproperpreallocator.html
Hope this helps,
-Rick
On 3/25/15 12:55 PM, Ruzal Yumaev wrote:
> Hello!
> I'm creating table USERS where primary key ID has autogenerating column:
> ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
> BY 1) PRIMARY KEY
> After inserting second or more row ID column increment to 100 and
> "INCREMENT BY 1" - it's multipling for one hundred.
> Example from table:
>
> ID FIO BIRTHDAY GROUPNUM
> 1 Name 2012-12-21 21312
> 101 Name 2001-12-21 23412
> 201 Name 2001-12-02 21323
> I attach log file and maven dependency is
> <dependency>
> <groupId>org.apache.derby</groupId>
> <artifactId>derby</artifactId>
> <version>10.11.1.1</version>
> </dependency>
>
>
>
> Code:
>
> String dbURL1 = "jdbc:derby:bd;create=true";
> Connection conn1 = null;
> try {
> conn1 = DriverManager.getConnection(dbURL1);
> if (conn1 != null) {
> System.out.println("Connected to database");
> }
> Statement statement = conn1.createStatement();
> try {
> System.out.println("Create table USERS");
> statement.executeUpdate("CREATE TABLE USERS (ID INT NOT NULL
> GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY
> KEY, FIO VARCHAR(255) NOT NULL,BIRTHDAY DATE, GROUPNUM VARCHAR(15))");
> System.out.println("Table USERS created");
> } catch (SQLException e) {
> System.out.println("Table USERS already exists");
> }
Re: Identity Increment
Posted by Zorro <hz...@gmail.com>.
Ruzal Yumaev schreef op 25-3-2015 om 20:55:
> Hello!
> I'm creating table USERS where primary key ID has autogenerating column:
> ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT
> BY 1) PRIMARY KEY
> After inserting second or more row ID column increment to 100 and
> "INCREMENT BY 1" - it's multipling for one hundred.
> Example from table:
>
> ID FIO BIRTHDAY GROUPNUM
> 1 Name 2012-12-21 21312
> 101 Name 2001-12-21 23412
> 201 Name 2001-12-02 21323
> I attach log file and maven dependency is
> <dependency>
> <groupId>org.apache.derby</groupId>
> <artifactId>derby</artifactId>
> <version>10.11.1.1</version>
> </dependency>
Out of curiosity I tried it, also with derby 10.11.1.1, java 1.8.
My code below is your code extended with 3 inserts .
{
String dbURL1 = "jdbc:derby:bd;create=true";
Connection conn1 = null;
try {
conn1 = DriverManager.getConnection(dbURL1);
if (conn1 != null) {
System.out.println("Connected to database");
}
Statement statement = conn1.createStatement();
try {
System.out.println("Create Table USERS");
statement.executeUpdate("CREATE TABLE USERS" +
"(ID INT NOT NULL GENERATED ALWAYS AS
IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY, FIO VARCHAR(255) NOT
NULL," +
"BIRTHDAY DATE, GROUPNUM VARCHAR(15))");
System.out.println("Table USERS created");
} catch (SQLException e) {
System.out.println("Table USERS already exists");
}
statement.execute("Insert Into USERS (FIO, BIRTHDAY,
GROUPNUM) VALUES('Name', '2012-12-21', '21312')");
statement.execute("Insert Into USERS (FIO, BIRTHDAY,
GROUPNUM) VALUES('Name', '2012-12-21', '21412')");
statement.execute("Insert Into USERS (FIO, BIRTHDAY,
GROUPNUM) VALUES('Name', '2012-12-02', '21323')");
}
catch (Exception e) {
System.out.println("Exception" + e.getMessage());
}
}
}
The outcome of a Select * From App.USERS in ij was:
1 |Name |2012-12-21|21312
2 |Name |2012-12-21|21412
3 |Name |2012-12-02|21323
3 rows selected
So seems to work.
Maybe you can try this code also.
Regards,
Harm-Jan Zwinderman
Cebuned.svipr.nl/Cebuned.html