File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Variable column name in PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Variable column name in PreparedStatement" Watch "Variable column name in PreparedStatement" New topic
Author

Variable column name in PreparedStatement

Branko Paskutini
Greenhorn

Joined: Apr 18, 2002
Posts: 25
Hi all,
I would like to set column name in my prepared statement with parameter. For example, user might search by column FirstName (SELECT * FROM TableName WHERE FirstName = 'Homer'), or by column LastName (SELECT * FROM TableName WHERE LastName = 'Simpson'). To do that, the PreparedStatement should be "SELECT * FROM TableName WHERE ? = ?". If I try to set the column name on that PreparedStatement with the setString method, JDBC driver doesn't like it, because the 'setString' method puts either single or double quotes around the column name. Could someone please suggest how can I set the column name with parameter without having it enclosed in quotes?
Thanks, Branko
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
Can you do something like this?


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

you'll need to use a Statement object:
"SELECT * FROM TableName WHERE " + column_name + " = '" + value + "' "
or
create a new PreparedStatement each time the column changes.
Jamie
Branko Paskutini
Greenhorn

Joined: Apr 18, 2002
Posts: 25
Thank you both! Looks like creating new PS each time is the go.
Regards, Branko.
Tina Coleman
Ranch Hand

Joined: Dec 12, 2001
Posts: 150
Out of curiousity, why use the PreparedStatement instead of the Statement if you're going to have to generate it each time? (As an aside, you could theoretically hold onto the set of PreparedStatements in a Collection somewhere that mapped the column name to the appropriate PreparedStatement, so long as the Connection was still open. As I recall, though, if the Connection on which the PreparedStatement is 'prepared' closes, then the PreparedStatement'll throw an exception if you attempt to execute against it.)
Michael Zalewski
Ranch Hand

Joined: Apr 23, 2002
Posts: 168
Originally posted by Tina Coleman:
Out of curiousity, why use the PreparedStatement instead of the Statement if you're going to have to generate it each time? (As an aside, you could theoretically hold onto the set of PreparedStatements in a Collection somewhere that mapped the column name to the appropriate PreparedStatement, so long as the Connection was still open. As I recall, though, if the Connection on which the PreparedStatement is 'prepared' closes, then the PreparedStatement'll throw an exception if you attempt to execute against it.)

There is still an advantage to using a PreparedStatement, even if you only use it one time. Many database servers such as Oracle will cache the PreparedStatement on the server side. If another request comes in to prepare the same statement, the pre-compiled version (and it's execution plan) do not have to be recomputed.
Also, when you have a statement such as
SELECT * FROM MyTable WHERE mycolumn='myvalue'
it is probably better to make a PreparedStatement that looks like this:
psMyStatement = con.prepareStatement(
"SELECT * FROM MyTable WHERE mycolumn=?"
);
Then put the value in with
psMyStatement.setString( 1, sSearchTerm);
You won't have to worry about the search term containing embedded quotes or other characters that may be significant to the particular database.
You would definately *not* want to keep a reference to a PreparedStatement in a Collection after you have closed the database connection. Doing so may hold resources on the server end (though not if you close the PreparedStatement first... but as you point out, after you close the PreparedStatement, it is pretty much useless).
And... There is at least one database which allows this:
PreparedStatement psMyDynamicStatement = con.prepareStatement(
"SELECT * FROM MyTable WHERE ?"
);
psMyDynamicStatement.setString( 1,
"column1='value1'"
);
It is DB2.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Variable column name in PreparedStatement
 
Similar Threads
Simple SQL question
JDBC
Search on name using LIKE statement
How to create dynamic query string
MySQL Prepared Statement & NULL