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?
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 ?"
It is DB2.