PreparedStatement: can I specify the column name as a parameter?
Let's say I have a table with three columns: C1, C2, C3
I am doing a search based on column C1. Can I do something like this ( it doesn't work ) because this is not the method it is used in :)
String c;// the name of the column
...
String sql = "select * from table where ? = ?";
pre = con.prepareStatement(sql);
pre.setString(1, c);
pre.setString(1, i);
rs = pre.executeQuery();
Basic idea, I don't want to have 3 ifs for each column. An elegant solution?
source to share
you can program a bunch of sql queries and store them in a map and then grab one based on the corresponding column.
enum column { a, b, c}
Map<column, string> str;
static {
str.put(a, "select * from tbl where a = ? ");
...
}
then just take one from the card later based on the enumeration. A string added in SQL statements could become a security issue in the future.
source to share
It won't work. The preparation statement parses the SQL, sends it to the database for verification and compilation. If question marks can replace parts of SQL, you lose the whole point of bound variables - speed and safety. You must re-inject SQL injection and the statements must be recompiled for all parameters.
Wouldn't it be better SELECT * FROM table WHERE c1 = ? OR c2 = ? OR c3 = ?
(depending on indexes and table sizes, of course).
source to share
You cannot do this:
String c;// the name of the column
...
String sql = "select * from table where " + c + " = ?";
pre = con.prepareStatement(sql);
pre.setString(1, i);
rs = pre.executeQuery();
?
If not, this might be the solution:
String c;// the name of the column
...
String sql = "select * from table where ('C1' = ? AND C1 = ?)
OR ('C2' = ? AND C2 = ?)
OR ('C3' = ? AND C3 = ?)"
pre = con.prepareStatement(sql);
pre.setString(1, c);
pre.setString(2, i);
pre.setString(3, c);
pre.setString(4, i);
pre.setString(5, c);
pre.setString(6, i);
rs = pre.executeQuery();
source to share
Use a dynamic query and java.sql.Statement
:
String whereClause = c + " = " + i;
// Form the dynamic Query
StringBuffer query = new StringBuffer( "SELECT * FROM TABLE" );
// Add WHERE clause if any
query.append(" WHERE " + whereClause);
// Create a SQL statement context to execute the Query
Statement stmt = con.createStatement();
// Execute the formed query and obtain the ResultSet
ResultSet resultSet = stmt.executeQuery(query.toString());
source to share