java 带有动态 where 子句的准备好的语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15405288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prepared statement with dynamic where clause
提问by Apurv
I have a search page with multiple search criteria
我有一个包含多个搜索条件的搜索页面
- Employee Name
- Employee Id
- Date of joining
- Department
- 员工姓名
- 员工ID
- 入职日期
- 部
etc
等等
User can provide one or more search criteria. I need to query database to get the search results.
用户可以提供一个或多个搜索条件。我需要查询数据库以获取搜索结果。
Using plain JDBC, there are two options to achieve this.
使用普通的 JDBC，有两个选项可以实现这一点。
- Prepare SQL query by appending search criteria provided by user.
- 通过附加用户提供的搜索条件来准备 SQL 查询。
ex:
前任：
String selectClause = "SELECT * FROM EMPLOYEES WHERE ";
String whereClause = "";
if(StringUtils.isNotBlank(empName)){
if(whereClause.length > 0){
whereClause += " AND ";
}
selectQuery += " EMP_NAME = " + empName;
}
if(StringUtils.isNotBlank(empID)){
if(whereClause.length > 0){
whereClause += " AND ";
}
selectQuery += " EMP_ID = " + empID;
}
//... and so on ...
- Using
preparestatement
- 使用
preparestatement
ex:
前任：
String query = "SELECT * FROM EMPLOYEES WHERE EMP_NAME = ? AND EMP_ID = ? DATE_OF_JOINING = ? AND DEPARTMENT = ?";
This answerexplains that like ex 1 above, ex2 can be modified, something like below
这个答案解释了像上面的ex 1一样，可以修改ex2，如下所示
String selectClause = "SELECT * FROM EMPLOYEES WHERE ";
String whereClause = "";
if(StringUtils.isNotBlank(empName)){
if(whereClause.length > 0){
whereClause += " AND ";
}
selectQuery += " EMP_NAME = ?";
}
if(StringUtils.isNotBlank(empID)){
if(whereClause.length > 0){
whereClause += " AND ";
}
selectQuery += " EMP_ID = ?";
}
//... and so on ...
Then carefully (keeping parameter index in mind) the input needs to set to the prepared statement. This doesn't sounds to be a very ideal solution.
然后仔细（记住参数索引）输入需要设置为准备好的语句。这听起来不是一个非常理想的解决方案。
Is there a way to do this in an elegant way (without ORM frameworks) ?
有没有办法以优雅的方式（没有 ORM 框架）做到这一点？
采纳答案by duffymo
I wouldn't like using a StringBuilderto dynamically create a query each and every time, especially when the number of meaningful combinations is countable and finite.
我不喜欢StringBuilder每次都使用 a动态创建查询，尤其是当有意义的组合数量是可数且有限的时。
I'd always prefer static Strings. Yes, you have to type them in, but you'll do that once. I'd rather do that than pay the price in complexity and at runtime.
我总是更喜欢静态字符串。是的，您必须输入它们，但您只需输入一次。我宁愿这样做也不愿在复杂性和运行时付出代价。
回答by xyz
In such conditions I prefer adding 1=1in where clause so that you dont have to keep track of where to insert AND.
在这种情况下，我更喜欢添加1=1where 子句，这样您就不必跟踪插入的位置AND。
String selectClause = "SELECT * FROM EMPLOYEES WHERE 1=1 ";
if(StringUtils.isNotBlank(empName)){
selectQuery += "AND EMP_NAME = " + empName;
}
if(StringUtils.isNotBlank(empID)){
selectQuery += "AND EMP_ID = " + empID;
}
//... and so on ...
Related question.
相关问题。
回答by John Joseph
This is easy to do without any complex or expensive logic, in a single line...
这很容易做到，没有任何复杂或昂贵的逻辑，在一行中......
Assuming that your three variables are @name, @surname, and @gender.
假设您的三个变量是@name、@surname 和@gender。
Also assuming that a zero-length string will be provided when a filter is not required.
还假设在不需要过滤器时将提供零长度字符串。
Then, your Select statement is simply:
然后，您的 Select 语句很简单：
select * from table_name where (name = @name or @name = '') and (surname = @surname or @surname = '') and (gender = @gender or @gender = '')
That's all there is to it! No complex or expensive logic.
这里的所有都是它的！没有复杂或昂贵的逻辑。
