SQL select statement with where clause

Question

how would i write this sql statement without a hard coded value?

resultSet = statement
    .executeQuery("select * from myDatabase.myTable where name = 'john'");
// this works

rather have something like:

String name = "john"; 
resultSet = statement
    .executeQuery("select * from myDatabase.myTable where name =" + name);
// Unknown column 'john' in 'where clause' at
// sun.reflect.NativeConstructorAccessorImpl.newInstance0...etc...

thanks in advance..

Answer 1

It is a terrible idea to construct SQL queries the way you currently do, as it opens the door to all sorts of SQL injection attacks. To do this properly, you'll have to use Prepared Statements instead. This will also resolve all sorts of escaping issues that you're evidently having at the moment.

PreparedStatement statement = connection.prepareStatement("select * from myDatabase.myTable where name = ?");    
statement.setString(1, name);    
ResultSet resultSet = statement.executeQuery();

Note that prepareStatement() is an expensive call (unless your application server uses statement caching and other similar facilities). Theoretically, it'd be best if you prepare the statement once, and then reuse it multiple times (though not concurrently):

String[] names = new String[] {"Isaac", "Hello"};
PreparedStatement statement = connection.prepareStatement("select * from myDatabase.myTable where name = ?");

for (String name: names) {
    statement.setString(1, name);    
    ResultSet resultSet = statement.executeQuery();
    ...
    ...
    statement.clearParameters();
}