Mysql returns Long from PreparedStatement.getGeneratedKeys();

Question

Using JDBC 3 driver, one can insert a record into a table and immediately get autogenerated value for a column. This technique is used in ActiveJDBC.

Here is the table definition:

CREATE TABLE users (id  int(11) NOT NULL  auto_increment PRIMARY KEY, first_name VARCHAR(56), last_name VARCHAR(56), email VARCHAR(56)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is working fine on H2 and PostgreSQL, and the type of the returned value is Integer. However, in MySQL the type is Long, while I believe it should be Integer. When querying this same row in Mysql, the "id" comes back as Integer.

Anyone knows why the "getGeneratedKeys()" returns java.lang.Long in Mysql and how to fix it?

Answer 1

The why: The generator that MySQL uses for keeping track of the value is BIGINT, so the driver describes it as BIGINT, and that is equivalent to Long. See LAST_INSERT_ID in the MySQL manual.

Drivers like PostgreSQL return the actual column of the table (actually PostgreSQL returns all columns when using getGeneratedKeys(); I assume that MySQL simply calls LAST_INSERT_ID().

How to solve it: As indicated by Jim Garrison in the comments: Always use getInt(), or getLong(), and not getObject().