?
Solved

Java JDBC ResultSet.insertRow, getting autoincrement field values

Posted on 2009-07-16
17
Medium Priority
?
1,594 Views
Last Modified: 2013-11-23
Using JDBC's  java.sql.ResultSet, I write a new record and want to get the automatically-generated key for the record that was just written.  Is there a way to do that?


// my current codebase uses ResultSet to write to a table
stmt = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("select * from " + strTableName + " where 0=1");
rs.moveToInsertRow();
// update some fields
rs.insertRow();
// now I want to fetch the value of the autoincrement field
 
 
 
// If I switch the code to the sample below, there is a way to get the keys
 
query = "INSERT INTO collection ( name , memo ) VALUES ( " + Criteria.escape(args0.getName()) + " , " + Criteria.escape(args0.getMemo()) + " )";         
statement.execute(query,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = statement.getGeneratedKeys();
if (rs.next()) {
  collectionId=rs.getInt(1);
}

Open in new window

0
Comment
Question by:amp834
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
17 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 24872425
You're already doing it - with getGeneratedKeys..?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24872530
Oh i see what you mean - you want to use the first technique. Have you tried just getting the auto-inc value from the ResultSet directly with rs.getInt("autoincfieldname")?
0
 

Author Comment

by:amp834
ID: 24872901
I tried rs.getInt("autoincfieldname"), there is no "current" rowset after the insert operation.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 86

Expert Comment

by:CEHJ
ID: 24873341
Try calling rs.last() before attempting to getInt
0
 

Author Comment

by:amp834
ID: 24873708
I already tried that.  Supposedly it works if the database is MySql, but not necessarily for other databases.  And if other processes inserted records, then it may return their stuff.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 24873981
Yes, the db/driver would have to support it.
0
 

Author Comment

by:amp834
ID: 24874018
The db/driver "supporting it" is just a special way MySql works.  it's not a standard to "goto last record" to get the just inserted record.

So that leaves the original question again!  

Either I have to switch to the prepared-statement "insert", or find some link between the resultset and the corresponding update statement, if there is such a thing.  Perhaps JDBC definition/standard doesn't support getting the inserted keys from resultset.insertRow.

Let's see if other people have any experience with this!
0
 
LVL 92

Expert Comment

by:objects
ID: 24875222
what database are you using?
0
 
LVL 92

Expert Comment

by:objects
ID: 24875484
there isn't a standard jdbc way to do it, so without knowing the database there is no way to determine what would work.
0
 

Author Comment

by:amp834
ID: 24875714
I'm using Derby right now, but want to be able to support other databases as well, maybe MySql, SqlServer, Postgres, etc.

Perhaps a better solution would be to take the ResultSet and write it out using a prepared statement (then discard the 'insertRow' ResultSet).  I could get the assigned key reliably.  (I read a benchmark for Derby that writing using prepared statements is slower than using ResultSet).

I assume the code for ResultSet.insertRow would be dependent on each JDBC driver.  (It seems it could be built on top of JDBC's prepared statements; if so, I could look at the code to see if I could make something similar).

I read something vaguely about a RowSet, maybe a temporary buffer for storing field values.  If there is such a thing, maybe I could use that instead of inventing another wheel.



0
 
LVL 92

Expert Comment

by:objects
ID: 24875740
yes writing with a resultset is typically going to be slower, whats the motivation for using insertRow() in the first place?
0
 

Author Comment

by:amp834
ID: 24875800
Actually, I read that writing with ResultSet is slower.

I wanted to use ResultSet because a lot of the work was already done, with the field names, field list, setting and getting, etc.,  but I suppose I can reinvent another wheel.  Many other people should have come across this issue, I wonder if there is a simple solution.

0
 
LVL 92

Expert Comment

by:objects
ID: 24876030
> Actually, I read that writing with ResultSet is slower.

thats what I said :)

Why aren't you using something like hibernate and save yourself all the overhead of implement the jdbc yourself.
0
 

Author Comment

by:amp834
ID: 24880360
I'm sorry, I meant writing with prepared statements was slower than with resultset (by about 20 to 30 %), but it seems you read the opposite!

I can look into using hibernate.  The reasons I stayed away:  
1. another layer to learn,
2. not sure if it can do things like scroll through a result set
3. not sure about its overhead/speed.  

For most simple gui operations, the speed doesn't matter.  But for batch processing of 500k+ records, it may.

4. I assume that hibernate would take care of getting the autoincrement value?

0
 
LVL 92

Accepted Solution

by:
objects earned 800 total points
ID: 24883493
Well it depends on how you use it, but using a PreparedStatement as it is intended should be quicker

> 4. I assume that hibernate would take care of getting the autoincrement value?

yes
0
 

Author Comment

by:amp834
ID: 24883701
I will wait to see if there are any other comments and will close this in a week.  (My Hibernate books should be in by then!)

By the way, objects, I have a question posted about refreshing tree nodes, if you get a chance, can you look at it?
0
 

Author Closing Comment

by:amp834
ID: 31604369
Thanks for the help.  I will use Prepared Statements, and maybe hibernate in the future.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses
Course of the Month10 days, 5 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question