This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Dynamic column selection in prepared statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Dynamic column selection in prepared statement?" Watch "Dynamic column selection in prepared statement?" New topic
Author

Dynamic column selection in prepared statement?

Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
Say I have two tables:
user_field_data, containing user_id, field_1, field_2, field_...
user_field_meta, containing field_id, field_title
So whatever has a field_id of 1 in the meta table corresponds to the field_1 column in the data table.
If I was building the query in java, it'd be something like...

My question is, is there a way to do this in one SQL SELECT statement? Ie, have sql dynamically build a column name?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30789
    
157

How do you determine the fid in java?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Phil Chuang
Ranch Hand

Joined: Feb 15, 2003
Posts: 251
let's assume that there is a fictional (but functional!) method/class called fieldIdLookupQuery.findIdForField (String) that gets the id from the database.
Wayne L Johnson
Ranch Hand

Joined: Sep 03, 2003
Posts: 399
I don't believe that you can do this with a prepared statement. The table names and column names can't be parameters. If you think about it, the purpose of using prepared statements is to create a pre-compiled SQL once that can be used many time. If you used a table name or column name in the SQL, the database engine would have to validate the SQL every time you use it to make sure the table and/or column name is valid. And that's essentially what happens when you use a statement.
So you have a couple of choices. Option 1: use a (regular) statement

Option 2: use a prepared statement

In the second example the query returns every column, but you pull out only the one you want.
[ March 29, 2004: Message edited by: Wayne L Johnson ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Dynamic column selection in prepared statement?