Further to this issue..
Running the following code...
conn = ds.getConnection();
Statement statement = conn.createStatement();
for ( int i = 0 ; i < 10 ; i++ )
{
ResultSet rs = statement.executeQuery("SELECT NEXTVAL('CategoryIdSeq')");
while (rs.next())
{
System.out.println(rs.getLong(1));
}
}
conn.commit();
conn.close();
Give the following output on run 1..
1
2
3
4
5
6
7
8
9
10
and this on run 2
51
52
53
54
55
56
57
58
59
60
Now with the following code...
for ( int i = 0 ; i < 10 ; i++ )
{
conn = ds.getConnection();
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("SELECT NEXTVAL('CategoryIdSeq')");
while (rs.next())
{
System.out.println(rs.getLong(1));
}
conn.commit();
conn.close();
}
Run 1 outputs..
101
151
201
251
301
351
401
451
501
551
and run 2 outputs..
601
651
701
751
801
851
901
951
1001
1051
So it seems that the db will assign and cache the configured amount of
ids and they will be returened by the select nextval sql. But if the
connection is closed the db will assign another range of ips for that
connection.
Looking back at the logging output I posted it shows that a conneciton
is opened, commited then closed for every sql statement executed. I
think this explains what im seeing.
Looking at my test app and its spring configuration, the tx manager
will use a new connection everytime I call the storeCategory() method
of my CategoryDAO which explains why the ids are being assign as they
are.
Regards
Ben
