java - 带有 where 子句的 SQL select 语句

Question

如果没有硬编码值，我将如何编写此 sql 语句？

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

宁愿有这样的东西:

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

提前致谢..

Answer 1

以您当前的方式构建 SQL 查询是一个糟糕的想法，因为它为各种 SQL 注入(inject)攻击打开了大门。要正确执行此操作，您必须使用 Prepared Statements反而。这也将解决您目前显然遇到的各种转义问题。

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

请注意，prepareStatement() 是一个开销很大的调用(除非您的应用程序服务器使用语句缓存和其他类似设施)。从理论上讲，最好是准备语句一次，然后多次重复使用它(虽然不是并发):

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