PreparedStatement.setString() method without quotes [duplicate] – This article will take you through the common SQL errors that you might encounter while working with java, sql, jdbc. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I’m trying to use a PreparedStatement with code similar to this:
SELECT * FROM ? WHERE name = ?
Obviously, what happens when I use setString() to set the table and name field is this:
SELECT * FROM 'my_table' WHERE name = 'whatever'
and the query doesn’t work. Is there a way to set the String without quotes so the line looks like this:
SELECT * FROM my_table WHERE name = 'whatever'
or should I just give it up and use the regular Statement instead (the arguments come from another part of the system, neither of those is entered by a user)?
Solution :
Parameters cannot be used to parameterize the table, or parameterize any database objects. They’re mostly used for parameterizing WHERE/HAVING clauses.
To do what you want, you’ll need to do the substitution yourself and create a regular statement as needed.
When you use a prepared statement, this is a hint to the database to do up front processing on the statement – e.g. parse the string and possibly determine an execution plan. If the objects used in the query can change dynamically, then the database could not do much up front preparation.
Unfortunately you cannot parameterize table names for prepared statements. If desired, you could construct a String and execute it as dynamic SQL.
I doubt that your SQL is really infinitely flexible that way. You only have a finite number of tables, so the number of static final Strings to express the SQL you need is finite as well.
Continue to use PreparedStatement and bind your variables. It’s totally worth it, especially helpful when avoiding SQL injection problems.
The mistake you did is that you cannot pass the table name as a parameter. You should only pass the values to a SQL Statement.
Ex:
If you’re wantto :
Select * from LoggedUsers where username='whatever' and privilege='whatever';
then you’ve to build the PreparedStatement as :
Select * from LoggedUsers where username=? and privilege=?
setString(1, usernameObject);
setString(2, privilegeObject);
The purpose of PreparedStatement is to reduce the difficulty and readability of the database connection code. when the developer has to use so many column values with Statement’s instance it’s so difficult to put semicolons, commas and plus (concat operator).
I think you’re mistakenly wanted to take advantage of it, which is not designed to be….
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.