Autoincrement issue - best way to add entries to the database and display them at the same time

Question

I use a SQLite Database and a Java GUI. The information entered on the GUI will be added to a table in the database. This table contains an autoincrement. On the same time I want to display the information on the GUI and change it later.

creating the database:

stat.executeUpdate("create table t1(ROWID INTEGER PRIMARY KEY AUTOINCREMENT, Value);";

adding values to the database:

Statement stat = con.createStatement();
String sql = "insert into t1 values ($next_id,'"+value+');";
stat.executeUpdate(sql);

How can I save the ID in my program so that it will be the same as in the database and I have easy access to my database?

Edit: I tried the solution mentioned in the comments and run into a NYI exception ...

String sql = "insert into t1 values($next_id,'"+value+"');";
PreparedStatement stmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet res = stmt.getGeneratedKeys();
while (res.next()){
     int id = res.getInt(1);
     System.out.println(id);
}
con.commit();

tried also the solution of Russel and got another exception ("not implemented by SQLite JDBC driver"):

String sql = "insert into t1 values($next_id,'"+value+"');";
Statement stat = con.createStatement();
stat.executeUpdate(sql);
stat.executeUpdate("SELECT LAST_INSERT_ROWID() from t1;", Statement.RETURN_GENERATED_KEYS);
ResultSet res = stat.getGeneratedKeys();
while (res.next()){
    int id = res.getInt(1);
    System.out.println(id);
}

What did I wrong?

Take a look at [this][1] stackoverflow question. [1]: http://stackoverflow.com/questions/76254/access-to-auto-increment-identity-field-after-sql-insert-in-java — Aleksandar Vucetic, Jan 09 '12 at 23:07
possible duplicate of [How to return the value of AUTO INCREMENT column in SQLite with VB6](http://stackoverflow.com/questions/531109/how-to-return-the-value-of-auto-increment-column-in-sqlite-with-vb6) — Miserable Variable, Jan 09 '12 at 23:08
@vucetica thank you I didn't saw the other threads before. Unfortunately I now run into a NYI exception ... — Anthea, Jan 09 '12 at 23:34

Russell Zahniser · Accepted Answer · 2012-01-10T02:27:36.113

0

What about just calling SELECT LAST_INSERT_ID()? This returns the last auto-increment value generated for your connection (not affected by other clients' actions).

Alternately, looking at the Javadoc, it seems you should be able to do this with an ordinary Statement:

stat.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet keys = stat.getGeneratedKeys();

edited Jan 10 '12 at 02:27

answered Jan 10 '12 at 02:21

Russell Zahniser

16,188
39
30

tried it and ran into a "not implemented by SQLite JDBC driver" exception ... I updated my question. – Anthea Jan 11 '12 at 18:36
If you do this with the "SELECT" call, the row IDs come back as the result of the query, not as the generated keys. That approach should be guaranteed to work because as far as JDBC is concerned it just made an ordinary query and got back an ordinary result. – Russell Zahniser Jan 12 '12 at 02:38

score 0 · Answer 2 · answered Jan 10 '12 at 21:15

0

If you don't mind using an ORM library, try sormula. It will do all of the work for identity column for you. All that is required is @Column(identity=true) annotation on the POJO field that is to be auto incremented.

The test cases in org.sormula.tests.identity package shows you how. Sqlite test set up and sqlitejdbc-v056.jar jar is included. Change build.properties to run db.dir=sqlitejdbc.

answered Jan 10 '12 at 21:15

Jeff Miller

1,424
1
10
19

thanks for that proposal, but this seems to be a little bit much overhead for one function. – Anthea Jan 11 '12 at 18:35
If you used sormula for all your database i/o, I would like to think everything gets easier including autoincrement. I agree that is not wise to use sormula for one update while rest of application uses JDBC. – Jeff Miller Jan 12 '12 at 18:28

Autoincrement issue - best way to add entries to the database and display them at the same time

2 Answers2