Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

java.sql.Statement.getGeneratedKeys()

843854May 29 2002 — edited Mar 21 2007

Hi!

I'm using the latest MySQL driver (org.gjt.mm.mysql) and JDK1.4. After an INSERT I want the inserted ID. I found the method Statement.getGeneratedKeys() but if I try the following code I'll get an exception. Is this function not implemented in the MySQL drivers? Or what is wrong?

query = "INSERT ........";
PreparedStatement ps = connection.prepareStatement(query,Statement.RETURN_GENERATED_KEYS); //
try {
result = ps.execute();
ResultSet rs = ps.getGeneratedKeys();

int columns = rs.getMetaData().getColumnCount();

while (rs.next()) {
for (int i=1;i<=columns;i++) {
System.out.println(rs.getMetaData().getColumnName(i)+":"+rs.getString(i));
}
}
rs.close();
rs = null;
} catch (NullPointerException npe) {
error("NullPointerException durch die Query '"+query+"'",npe);
}
ps.close();
ps = null;

Exception in thread "main" java.lang.AbstractMethodError:
org.gjt.mm.mysql.jdbc2 Connection.prepareStatement(Ljava/lang/String;I)Ljava/sql/PreparedStatement;
at de.gateway4m.mp3db.common.database.DataBaseConnection.execute(DataBaseConnection.java:201)
at de.gateway4m.mp3db.importer.MP3File.addToDB(MP3File.java:156)
at de.gateway4m.mp3db.importer.Importer.load(Importer.java:79)
at de.gateway4m.mp3db.importer.Importer.begin(Importer.java:52)
at de.gateway4m.mp3db.importer.Start.main(Start.java:113)

If I use connection.prepareStatement(query) without the auto_incremented_id constant and ask for getGeneratedKeys() I'll get the same exception (but with undefined getGeneratedKeys() method). If I don't call it, anything is OK, but a following SELECT id FROM... is very slow (XX minutes - because the database is very big) and I want avoid this...

843854

I'm using version 2.0.13 of mmmysql.The change log says Rudimentary version of Statement.getGeneratedKeys() from JDBC-3.0 now implemented (you need to be using JDK-1.4 for this to work, I believe)

I've previously found it works with java.sql.Statement but not with java.sql.PreparedStatement.

From the change log it looks like he hasn't yet implemented getGeneratedKeys() for PerparedStatements.

Col

843854

What does that mean? Should I use something like that:

Statement ps = (Statement)connection.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);

Isn't PreparedStatement an extended Statement? OK, it' an interface, but Java should use Statement.getGeneratedKeys, if PreparedStatement.getGeneratedKeys doesn't exists, or what?

???? So, how do you test, that the method getGeneratedKeys works ??? Could I use the code to get the ID or is it only a dummy?

843854

What does that mean? Should I use something like
that:

Statement ps =
(Statement)connection.prepareStatement(query,Statement.
ETURN_GENERATED_KEYS);

Isn't PreparedStatement an extended Statement? OK, it'
an interface, but Java should use
Statement.getGeneratedKeys, if
PreparedStatement.getGeneratedKeys doesn't exists, or
what?

Don't get confused between interface inheritance and implementation inheritance. There is no method Statement.getGeneratedKeys(), only a declaration of a method. The actual method is defined in some class (lets call it MyStatement) in the MMMySQL JDBC driver. There is also a MMMySQL class (lets call it MyPreparedStatement) that implements PreparedStatement. Just because PreparedStatement extends Statement it doesn't follow that MyPreparedStatement extends MyStatement. Thats an implementation issue.

>

???? So, how do you test, that the method
getGeneratedKeys works ??? Could I use the code to get
the ID or is it only a dummy?

You test it by calling it. You tried this and it doesn't work for PreparedStatements and no amount of casting is going to make it work. You'll have to wait for the driver to implement it or switch to another driver.

I'd suggest putting up a feature request at the MMMySql site so that the developer knows that somebody needs this functionality. The driver is under active development at the moment.

Col

843854

Ha, thats the code I should use:

Statement ps = connection.createStatement();
try {
result = ps.execute(query);
ResultSet rs = ps.getGeneratedKeys();

int columns = rs.getMetaData().getColumnCount();

while (rs.next()) {
for (int i=1;i<=columns;i++) {
System.out.println(rs.getMetaData().getColumnName(i)+":"+rs.getString(i));
}
}
rs.close();
rs = null;
} catch (NullPointerException npe) {
error("NullPointerException durch die Query '"+query+"'",npe);
}
result = true;

ps.close();
ps = null;

843854

It's specific to mySql but there's a function for the last insert ID, execute

SELECT LAST_INSERT_ID()

as a query.

Unlike trying to look for the max value this is reasonably thread-safe as the value is recorded per connection (so if some other task inserts another record between the insert and the select you should still get the your own value).

843854

The value of getGeneratedKeys() over using malcolm's suggestion... which is still a very valid one... is that any changes of database do not require any changes to your code. As was mentioned, getting the last ID is usually DB dependant... SQLServer uses SELECT @@IDENTITY, etc. As long as you continue to use drivers that support getGeneratedKeys(), then switching databases is less of an issue.

843854

Say we have an online Form with

first name
last name
address line1
address line2
state
zip

And there are two tables in the DB

PERSON (ID,FNAME,LNAME,ADDRESSID)
ADDRESS(ADDRESSID,ADDRLINE1,ADDRLINE2,STATE,ZIP)

The data from needs to be entered into the above 2
tables and RI needs to maintained with the correct
key.

Here are the logical steps:
1. Get the address data from the form
2. Insert the address data into the ADDRESS table
3. Get the ID from the ADDRESS table, and store it in
memory
4. Get the info of the person from the HTML form
5. Insert the person info into the PERSON table, along
with the ADDRESS ID

Is this is the best approach ?

In JDBC 3.0 (beta) we can call getGeneratedKeys method
on the ResultSet returned from the executeUpdate()
method on the Statement object. And use it for the
next entry.

With JDBC 2.0, has anyone implemented something like this.

-Rohit

843854

But this is another transaction - and a simple getGeneratedKeys() should be much faster...

843859

Statement stmt = conn.createStatement();
PreparedStatement ps = conn.prepareStatement("INSERT into SCHEDULE( SHIFT_DATE,SHIFT_NUM,USERS,CREATE_TIME) VALUES (?,?,?,getDate())", PreparedStatement.RETURN_GENERATED_KEYS );

ps.setDate(1,shiftDate);
ps.setString(2, shift);
ps.setString(3, userName);
ps.executeUpdate();
ResultSet resultset = ps.getGeneratedKeys();

if (resultset.next())
{
// retrieve the new ID
sch.setScheduleID(resultset.getInt(1));
}
ps.close();
stmt.close();

843859

no matter how I tried to use java.sql.Statement.getGeneratedKeys() I could not get the information I needed (last inserted id).
Maybe it is a problem of versions (mysql-4.1 / mysql connector 3.1.10) but getGeneratedKeys() always returns an empty ResultSet.

So if I have to go to a platform dependant solution, here's a way to do it:

			Statement s = connection.createStatement();
			s.execute(sql);
			if (s instanceof com.mysql.jdbc.Statement) {
				com.mysql.jdbc.Statement mysqls = (com.mysql.jdbc.Statement)s;
				return mysqls.getLastInsertID();
			} else {
				return -1l;
			}

Hoping it helps some lost souls.

843859

Hi I was getting this same error and searched on net but was not able to get satisfactory solution. Therefore I am giving u the solution which worked fine for me :)
If u are using MS SQL Server 2005 just make sure u register u r driver as
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
where as if u r using MS SQL Server 2000 use
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
and use the latest jdbc driver and now u will be able to use
Statement.RETURN_GENERATED_KEYS and getGeneratedKeys().

Hope this will be usefull for all :)
Cheers

1 - 11

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 18 2007

Added on May 29 2002

#java-database-connectivity-jdbc

11 comments

3,542 views

Java Database Connectivity (JDBC)

java.sql.Statement.getGeneratedKeys()

Comments

Post Details