Java JDBC ResultSet.insertRow, getting autoincrement field values

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

amp834Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
You're already doing it - with getGeneratedKeys..?
0
CEHJCommented:
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
amp834Author Commented:
I tried rs.getInt("autoincfieldname"), there is no "current" rowset after the insert operation.
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

CEHJCommented:
Try calling rs.last() before attempting to getInt
0
amp834Author Commented:
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
CEHJCommented:
Yes, the db/driver would have to support it.
0
amp834Author Commented:
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
Mick BarryJava DeveloperCommented:
what database are you using?
0
Mick BarryJava DeveloperCommented:
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
amp834Author Commented:
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
Mick BarryJava DeveloperCommented:
yes writing with a resultset is typically going to be slower, whats the motivation for using insertRow() in the first place?
0
amp834Author Commented:
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
Mick BarryJava DeveloperCommented:
> 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
amp834Author Commented:
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
Mick BarryJava DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amp834Author Commented:
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
amp834Author Commented:
Thanks for the help.  I will use Prepared Statements, and maybe hibernate in the future.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.