带where子句的SQL select语句 [英] SQL select statement with where clause

问题描述

我如何在没有硬编码值的情况下编写此sql语句?

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

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

先谢谢了.

推荐答案

以目前的方式构造SQL查询是一个糟糕的主意，因为它为各种SQL注入攻击打开了大门.要正确执行此操作，您将必须使用准备的语句代替.这也将解决您目前显然遇到的各种逃避问题.

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

请注意，prepareStatement()是一个昂贵的调用(除非您的应用程序服务器使用语句缓存和其他类似的功能).从理论上讲，最好先准备一次语句，然后多次重用(尽管同时 not ):

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

这篇关于带where子句的SQL select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！