Value of autoincremented column on insert using getGeneratedKeys() - wrong result

Question

I'm trying to get the value of an autoincremented column when I perform an insert in a table. I'm using sqlite. Here is the table:

CREATE TABLE `tasks` (
`task_id`   INTEGER,
`task_list_id`  INTEGER,
`task_name` VARCHAR(30),
`task_due_date` TIMESTAMP,
`task_created_date` TIMESTAMP,
`done`  BOOLEAN,
PRIMARY KEY(task_id),
FOREIGN KEY(`task_list_id`) REFERENCES `lists`(`list_id`)
);

And here is my insert method:

public int add(Task task) throws Exception {
        PreparedStatement statement = null;
        try {
            String SQL = "INSERT INTO TASKS (TASK_LIST_ID, TASK_NAME, DONE, TASK_CREATED_DATE) VALUES (?, ?, ?, CURRENT_TIMESTAMP)";
            statement = jdbcTemplate.getDataSource().getConnection()
                    .prepareStatement(SQL, new String[]{ "task_id" });
            statement.setInt(1, task.getListId());
            statement.setString(2, task.getTaskName());
            statement.setBoolean(3, task.isDone());
            statement.executeUpdate();

            ResultSet generatedKeys = statement.getGeneratedKeys();
            if (generatedKeys.next()) {
                return (int)generatedKeys.getLong(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("Failed to add Task to DB: " + task.toString());
        } finally {
            if (generatedKeys != null) {
                generatedKeys.close();
            }
            if (statement != null) {
                statement.close();
            }
        }
        return -1;
    }

The problem is that it doesn't return the value of my task_id column, but a value that increments for each insert that I do. If I have done 9 inserts, then deleted them all, the next time I call add() it will still return 10. This happens even though I restart my application. Any idea on what I'm doing wrong? I also have another method for another table (lists) where I have almost exactly the same code. I just saw that an entry is created in the lists table when I call the add(Task task) method after add(List list).

Answer 1

SQLite keeps track of the largest ROWID that a table has ever held using an internal table named "sqlite_sequence". The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

Taken from SQLite docs