Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

Column name for getGeneratedKeys()?

 
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?)
 
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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.
 
Ranch Hand
Posts: 694
Mac OS X Eclipse IDE Firefox Browser
  • 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
Posts: 4173
29
jQuery Eclipse IDE Java
  • 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
 
What's brown and sticky? ... a stick. Or a tiny ad.
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic