Let's say I have a table with 3 columns: C1, C2, C3
I make a search based on the C1 column. Could I make something similar like this (this is not working - because this is not the way prepareStatement it's used:) )
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(); The main idea, I don't want to have 3 ifs for every column. An elegant solution?
This won't work. The prepare statement parses the SQL, sends to the database for validation and compilation. If question marks could substitute parts of the SQL, you would loose the whole point of bound variables - speed and security. You would reintroduce SQL injection back and statements will have to be recompiled for all parameters.
Wouldn't something like SELECT * FROM table WHERE c1 = ? OR c2 = ? OR c3 = ? be better (of course depending on indexes and table sizes).
you could code up a a set of sql queries and store them in a map, then grab one based on the column in question.
enum column { a, b, c} Map<column, string> str; static { str.put(a, "select * from tbl where a = ? "); ... } then just grab one out of the map later based on the enum. String appends in sql statements have a way of becoming security problems in the future.
Use a dynamic query and a 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()); 
can't you 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 then this might be a 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(); 
