MySQL-JDBC Question: Can I use the column name as parameter?

Question

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?

Answer 1

i think it shouldnt be a problem as it works with the position of the placeholder , so this should work if u have the i variable defined above it.

pre.setString(1, "c1");
pre.setString(2, i);

Answer 2

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();

Answer 3

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).

Answer 4

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());

Answer 5

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.