SirGoose SirGoose - 3 months ago 9
Java Question

How to find what id to use next in SQLite3 and Java?

I'm currently using Java and JDBC to work with SQLite. When inserting a new object into a database, how am I supposed to know which id to use or how to generate it?

Here's my code I'm using:

private void insertIntoTable() {
Connection c = null;
Statement stmt = null;

try {
Class.forName("org.sqlite.JDBC");
c = DriverManager.getConnection("jdbc:sqlite:src/test.db");
c.setAutoCommit(false);
System.out.println("Opened database successfully");

stmt = c.createStatement();
String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" +
"VALUES (2 , 'Brian Brianson', 32, 'California', 60000.00);";
stmt.executeUpdate(sql);

stmt.close();
c.commit();
c.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(0);
}
}


When I'm inserting new items, how should I find out which id I should be using to insert into the database?

Answer

INTEGER PRIMARY KEY columns automatically behave as auto increment columns, q.v. the documentation. You need only insert NULL values for the ID column and SQLite will populate the value for you. That is, use this code:

String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" +
             "VALUES (null, 'Brian Brianson', 32, 'California', 60000.00);";
stmt.executeUpdate(sql);