0

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?

ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • How do you know it's `Long`? Show how you retrieved the generated key and its datatype. – Jim Garrison Oct 09 '12 at 19:09
  • the following code: https://gist.github.com/3861026 generates this output: ` Generated Id value: 1 Generated Id class: class java.lang.Long realId Id value: 1 realId Id class: class java.lang.Integer ` Sorry, this editor is really annoying. The bottom line, this is the difference. For a generated key the type is Long, but for the same key read from table, the type is Integer. I hope this helps – ipolevoy Oct 09 '12 at 19:53
  • 2
    Why don't you use `getInt()` instead of `getObject()` to get your generated key, since you want it as an int? – JB Nizet Oct 09 '12 at 20:04
  • @ipolevoy Please don't post code in comments, update your original post. – Jim Garrison Oct 09 '12 at 20:05
  • This has nothing to do with MySQL or Java... it's the JDBC driver that defines the returned resultset's data types in this case. As JBNizet says, use `getInt()`. – Jim Garrison Oct 09 '12 at 20:15
  • I'm not sure I can use getInt, because I'm supporting multiple databases, and it might not be Integer in others. @Jim Garrinson, here is a snippet I was trying to type:https://gist.github.com/3861900 – ipolevoy Oct 09 '12 at 22:37
  • It will always be a number, no? – Jim Garrison Oct 10 '12 at 01:53

1 Answers1

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().

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • right, the original code used getLong(), but that caused problems in other parts of the framework, because one model would have a Long, while the other Integer. I think the ultimate solution is to read the metadata, determine the actual type if this column, and cast to that type when a new ID is generated. Thanks all for help! – ipolevoy Oct 12 '12 at 17:52
  • 1
    Actually, if you do that, depending on the driver you might get `java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer`. – Cristian Vrabie Apr 30 '13 at 07:13
  • @ChristianVrabie What are you commenting on? – Mark Rotteveel Apr 30 '13 at 11:12
  • @CristianVrabie I did exactly that and got the same error. I used metadata to determine the original database column type and cast it to that (in this case integer) and got a `java.lang.ClassCastException`. But I believe what @ipolevoy said is to look at the metadata of the value returned by getObject() and then do a cast depending on that as opposed to the column itself. – Karthic Raghupathi Sep 17 '13 at 14:44