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](https://mvnrepository.com/artifact/org.xerial/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:

```java
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:

```sh
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:

```java
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](https://www.sqlite.org/faq.html#q1)), so I tried this:

```java
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)

```