aspose file tools*
The moose likes JDBC and the fly likes Column name for getGeneratedKeys()? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Column name for getGeneratedKeys()?" Watch "Column name for getGeneratedKeys()?" New topic
Author

Column name for getGeneratedKeys()?

Philippe Desrosiers
Ranch Hand

Joined: Mar 29, 2006
Posts: 138
I'm using Statement.getGenertedKeys() on MySQL. I get the generted values back fine, but I'd like to also be able to retrieve the name of the column that contains this generated key value (I'm doing this in a DAO base class, where I don't know the name of the table or anything, really.



In the rsultset returned by getGeneratedKeys(), there are never any column names, whcih is where I would expect to find the name of the generated key column.

Can I get this somehow from the table that I'm inserting into (ResultSetMetadata, perhaps?)
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I would think rs.getMetaData() would contain the key names, although keep in mind getGeneratedKeys() is not a well supported JDBC feature. Some drivers don't support it at all. In other words, be happy with what you have with getGeneratedKeys(), it doesn't always work right on all systems.

Not to get too off subject, but how many keys are you expecting to generate from a single sql statement? Perhaps you should rewrite your queries so only one is generated?


My Blog: Down Home Country Coding with Scott Selikoff
Philippe Desrosiers
Ranch Hand

Joined: Mar 29, 2006
Posts: 138
Scott Selikoff wrote:
Not to get too off subject, but how many keys are you expecting to generate from a single sql statement? Perhaps you should rewrite your queries so only one is generated?


Well, there are two things here. One is that I'm writing a reusable component, and have no control over how the end-user will design their database. The other is bulk inserts, which will generate as many keys as there are records inserted (in theory). In the first case, I would expect getGeneratedKeys to return multiple columns. In the second, multiple rows.

As you said, GetGeneratedKeys support varies wildly between vendors, so the behavior is pretty sketchy.

rs.getMetaData() doesn't contain the key names, unfortunately, although as you mentioned, this could vary from vendor to vendor as well, for all I know :-)

thanks for your help!
Philippe Desrosiers
Ranch Hand

Joined: Mar 29, 2006
Posts: 138
[deleted. too dumb :-0]
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Philippe Desrosiers wrote:One is that I'm writing a reusable component, and have no control over how the end-user will design their database.


Software developers ALWAYS NEED TO KNOW ABOUT THE DATABASE SCHEMA. The idea you can do software development without database information is a myth, even with ORM tools. But since you mentioned end-users, I imagine you are building an application that lets people create database schemas on the fly? If so, be weary of database anti-patterns such as building an application that is a glorified database GUI tool. Otherwise, you should create properties file or add a field to a table that allows you to lookup when the name of the table and auto-generated key is.
Kaydell Leavitt
Ranch Hand

Joined: Nov 18, 2006
Posts: 689

It's best to generate you own keys rather than having to depend upon the database engine to do it for you because the method getGeneratedKeys() can't always be depended upon.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Kaydell Leavitt wrote:It's best to generate you own keys rather than having to depend upon the database engine to do it for you because the method getGeneratedKeys() can't always be depended upon.


True, the method is risky to use on a lot of levels. I also wrote a post on this subject a few months back you guys might like: Database key generation in Java Applications you
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Column name for getGeneratedKeys()?