• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Column name for getGeneratedKeys()?

 
Philippe Desrosiers
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Philippe Desrosiers
Ranch Hand
Posts: 138
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 138
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
[deleted. too dumb :-0]
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 690
Eclipse IDE Firefox Browser Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic