带动态where子句的预备语句 [英] Prepared statement with dynamic where clause

问题描述

我有一个包含多个搜索条件的搜索页面

1. 员工姓名
2. 员工编号
3. 加入日期
4. 部门

等

用户可以提供一个或多个搜索条件.我需要查询数据库以获取搜索结果.

使用普通的JDBC，有两种方法可以实现.

1. 通过附加用户提供的搜索条件来准备SQL查询.

例如:

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

2. 使用preparestatement

例如:

String query = "SELECT * FROM EMPLOYEES WHERE EMP_NAME = ? AND EMP_ID = ? DATE_OF_JOINING = ? AND DEPARTMENT = ?";

此答案解释说，像上面的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 ...

然后仔细地(牢记参数索引)将输入设置为准备好的语句.听起来这不是一个非常理想的解决方案.

是否有一种优雅的方式(没有ORM框架)来做到这一点?

解决方案

我不想每次都使用StringBuilder动态创建查询，尤其是在有意义的组合的数量是可数且有限的情况下./p>

我总是喜欢静态字符串.是的，您必须输入它们，但只需输入一次.我宁愿这样做，也不愿在复杂性和运行时付出代价.

I have a search page with multiple search criteria

1. Employee Name
2. Employee Id
3. Date of joining
4. Department

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.

1. Prepare SQL query by appending search criteria provided by user.

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

2. Using preparestatement

ex:

String query = "SELECT * FROM EMPLOYEES WHERE EMP_NAME = ? AND EMP_ID = ? DATE_OF_JOINING = ? AND DEPARTMENT = ?";

This answer explains that like ex 1 above, ex2 can be modified, something like below

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) ?

解决方案

I wouldn't like using a StringBuilder to dynamically create a query each and every time, especially when the number of meaningful combinations is countable and finite.

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.

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