SQL select statement with where clause

问题:

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

回答1:

It is generally 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();
}

回答2:

You are missing the single quotes around your string, your code corrected:

String name = "john";
String sql = "select * from myDatabase.myTable where name = '" + name + "'";
// Examine the text of the query in the debugger, log it or print it out using System.out.println
resultSet = statement.executeQuery(sql);

Print out / log text of the query before executing the query to see if it looks OK.

If you are going to do a lot of similar queries where only the constant changes, consider using prepared statements

回答3:

this should work:

String name = "john"; 
resultSet = statement
    .executeQuery("select * from myDatabase.myTable where name =" + "'" + name + "'");

回答4:

you need to put quotes around the value ('john' instead of john)...

回答5:

Try the following :

String name = "john"; 

resultSet = statement
      .executeQuery("select * from myDatabase.myTable where myTable.name = '" + name + "'");

回答6:

Put quotes around your name value since it's a string.

"select * from myDatabase.myTable where name ='" + name + "'"