Get autoincremented generated key with apache dbutils without Statement

Question

I don't use Statement to execute queries. The methods look like

public static int insertIntoUserTable (String username, String password) {
        String query = "insert into user (username, password) values (?, ?)";
        QueryRunner run = new QueryRunner(FeedDbDataSource.getDataSource());
        ResultSetHandler<User> resultHandler = new BeanHandler<>(User.class);
        try {
            run.insert(query, resultHandler, username, password);
        } catch (SQLException e) {
            // handle
        }
        return ;
    }

How can I get the id of inserted row (without making additional select * from user where...)?

Drew · Answer 1 · 2016-08-05T19:43:28.857

Noting Ollie's deleted answer visible to only some people, I would create a stored proc. It performs the INSERT, then returns a 1 row 1 column result set of LAST_INSERT_ID() at the end of it just after the INSERT call.

So your call would be to the stored proc passing whatever parameters necessary to be picked up for the INSERT part, followed by the aforementioned.

INSERT student (lastname, ...) values (pLastName, ...); 
SELECT LAST_INSERT_ID() AS `ai_id`; -- ai_id becomes the column name

The ID returned is relative to this connection, shielded from any interference by other users, and prior to the return of any objects to a connection pool.

Fortunately these types of calls are infrequent for the most part.

If you need help with a stored proc wrapper, just ask.

Get autoincremented generated key with apache dbutils without Statement

1 Answers1