How to get the last inserted id in jdbc with SQLite3?

Question

So im working in a proyect from school, and now im completely stuck. I just need this one thing to work and Im done.

Problem description

My problem is to get the id of the last thing I inserted in the database.

Minimal Example

Download sqlite-jdbc-3.36.0.3, and have it in your current working directory. Make sure that you dont have a temp.sql file in it (Delete it after the program runs).

Create a file named Main.java with the following contents:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
    public static final String createDbQuery = 
        "CREATE TABLE temp (\n"
        + "id integer PRIMARY KEY,\n"
        + "data integer\n"
        +")";

    // In the actual code im using a proper PreparedStatement, but I will keep
    // is simple here
    public static final String insertSomethingQuery =
        "INSERT INTO temp (data) VALUES (340975)";

    public static void main(String[] args) {
        Connection conn = null;
        try {
            conn = connect();
            
            exec(createDbQuery, conn);
            exec(insertSomethingQuery, conn);

            /*
             * Try here the code that will be mentioned in the rest of the
             * question
             */

        } catch (final Exception e) {
            e.printStackTrace();
        }  finally {
            if (conn != null) {
                disconect(conn);
            }
        }
    }

    public static Connection connect() throws SQLException {
        return DriverManager.getConnection("jdbc:sqlite:temp.sql");
    }

    public static void disconect(Connection conn) {
        try {
            conn.close();
        } catch (final SQLException e) {
            e.printStackTrace();
        }
    }

    public static Statement exec(String query, Connection conn)
            throws SQLException {

        Statement s = conn.createStatement();
        s.execute(query);
        return s;
    }
}

Then to try the code:

1. Compile it. (javac Main.java)
2. Remove temp.sql if it exists
3. Run it. (java -cp "./sqlite-jdbc-3.36.0.3.jar;." Main)

If someone is using powershell in windows, im using this:

javac .\Main.java; rm .\temp.sql; java -cp ".\sqlite-jdbc-3.36.0.3.jar;." Main

What have I tried?

1) last_insert_rowid()

I saw on the internet that last_insert_rowid() will return the id of the last thing you inserted in SQLite, so I did this:

final Integer id =
    exec("SELECT last_insert_rowid()", conn).getUpdateCount();

System.out.println("The last inserted id is " + id);

Then I get the result:

The last inserted id is -1

When I expected:

The last inserted id is 1

And I know that the expected result is correct because if I run:

sqlite3.exe .\temp.sql "SELECT * FROM temp"

The ouput is:

1|340975

2) SELECT MAX(id) FROM ...

I know that the last inserted id will always be the biggest id in the table (except if the table has and id that is the maximum values that it can hold, but that will never be the case in my app, source), so I tried this:

final Integer id =
    exec("SELECT MAX(id) FROM temp", conn).getUpdateCount();

System.out.println("The last inserted id is " + id);

And I got the same output as with last_insert_rowid().

And trying with:

ResultSet rs =
    exec("SELECT MAX(id) FROM temp", conn).getResultSet();

if (!rs.next()) {
    System.out.println("Something is wrong...");
    return;
}

final Integer id = rs.getInt("id");
System.out.println("The last inserted id is " + id);

I get:

java.sql.SQLException: no such column: 'id'
        at org.sqlite.jdbc3.JDBC3ResultSet.findColumn(JDBC3ResultSet.java:49)
        at org.sqlite.jdbc3.JDBC3ResultSet.getInt(JDBC3ResultSet.java:402)
        at Main.main(Main.java:37)

Answer 1

The function last_insert_rowid() returns:

the ROWID of the last row insert from the database connection which invoked the function

So, if you closed the connection with which you did the last insertion and run the query with another connection you will not get the rowid of the last row inserted.
Also if there are multiple tables in your database and you do insertions in more than 1 of them with the same connection, you will have to call last_insert_rowid() after the last insertion in each table to get the rowid of the last row inserted in each table.

When you use MAX(id) in a query and id is defined as INTEGER PRIMARY KEY you get the max id of the table and not necessarily the rowid of the last row inserted, because:

If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.

The only way to be sure that MAX(id) will return the rowid of the last row inserted is if you have defined id with the keyword AUTOINCREMENT also:

id INTEGER PRIMARY KEY AUTOINCREMENT

because in this case:

The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used.

Finally, it is common practice to refer to columns by their names or aliases, so instead of:

SELECT MAX(id) FROM temp

you should use:

SELECT MAX(id) AS max_id FROM temp

so that you can access the result of the query by the alias max_id:

final Integer id = rs.getInt("max_id");

Answer 2

It's been a while since I used sqlite but this looks wrong to me: id = exec("SELECT last_insert_rowid()", conn).getUpdateCount(); You are not updating anything so there will be no update count.

Shouldn't it be something like this instead?

ResultSet rs = conn.prepareStatement("select last_insert_rowid();").executeQuery();

Executing a query is for selects and can return a result set. It is different from updating the database with data.

Answer 3

So while writing the question I found the answer. But now that I have gone through the effort of writing it, I'll guess I will share the answer, and hope it is helpfull for someone in the future.

final Integer id =
    exec("SELECT last_insert_rowid()", conn).getUpdateCount();

System.out.println("The last inserted id is " + id);

Is wrong, the correct way of doing it is:

ResultSet rs =
    exec("SELECT last_insert_rowid()", conn).getResultSet();

if (!rs.next()) {
    System.out.println("Something is wrong...");
    return;
}

final Integer id = rs.getInt("last_insert_rowid()");
System.out.println("The last inserted id is " + id);

And for the SELECT MAX ... way of doing it, the correct way of taking the value is:

ResultSet rs =
    exec("SELECT MAX(id) FROM temp", conn).getResultSet();

if (!rs.next()) {
    System.out.println("Something is wrong...");
    return;
}

final Integer id = rs.getInt("MAX(id)");
System.out.println("The last inserted id is " + id);