JDBC的多條件動態查詢
前面我已經提到了現在的項目無非就是列表里的分頁加多條件查詢，在http://xdwangiflytek.iteye.com/blog/1358080里我已經簡單的歸納了一下分頁的實現手段和JDBC里的分頁，這里我們在來說說JDBC里的多條件動態查詢，為什么說多條件，因為在項目列表中，不可能只有只有一個字段，所以說條件也不可能只有一個，所以這里我們還是說一下多條件下的查詢，多條件ok了那么單個條件不更ok了么（Hibernate里有Criteria Queries哦）。
首先我們和分頁一樣先分析一下多條件綜合查詢的實現方式有哪些？
1、直接將參數值拼接到SQL語句中，然后進行Select，但是說這種方式存在安全性問題，比如說SQL的注入，盡管我們可以通過正則等手段來過濾掉這些特殊字符，但是這樣看上去不是很爽。
2、先使用占位符“？”來拼接SQL，然后再通過條件判斷去填充PreparedStatement，但是這種方式顯然很麻煩，因為我們在拼SQL的時候需要判斷一次，在填充pst的時候還需要再進行判斷，所以不是好的解決方案。
3、存儲過程，這個因為本人對存儲過程認識不是很深，所以這里不加描述；
其實方法很多，只是找一個相對好一點的，工作的這段時間做.NET項目時使用里面的SqlHelper感覺很犀利，記得以前學習的時候老師也給我們做了一個類似的，所以想寫一個工具類。
Parameter.java:
package com.iflytek.page;
/**
* 查詢參數工具類，用于表示條件參數對象
*
* @author xudongwang 2012-1-19
*
* Email:xdwangiflytek@gmail.com
*/
public class Parameter {
/**
* 數據庫字段名
*/
private String field;
/**
* 參數值 Object
*/
private Object value;
/**
* 數據庫操作符 =、>=、<、like...
*/
private String operator;
/**
* 構造方法
*
* @param field
* 數據庫字段名
* @param operator
* 數據庫操作符 =、>=、<、like...
* @param value
* 參數值 Object
*/
public Parameter(String field, String operator, Object value) {
super();
this.field = field;
this.value = value;
this.operator = operator;
}
public String getField() {
return field;
}
public Object getValue() {
return value;
}
public String getOperator() {
return operator;
}
}
DynamicQuery.java:
package com.iflytek.page;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
/**
* 動態查詢工具類，用于拼接SQL、填充PreparedStatement
*
* @author xudongwang 2012-1-19
*
* Email:xdwangiflytek@gmail.com
*/
public class DynamicQuery {
private String templet = " AND %s %s ?";
private String baseSql;
private ArrayList<Parameter> parameters = new ArrayList<Parameter>();
public DynamicQuery() {
}
/**
* baseSql需要帶有where條件
*
* @param baseSql
*/
public void setBaseSql(String baseSql) {
this.baseSql = baseSql;
}
public void addParameter(Parameter parameter) {
parameters.add(parameter);
}
public String generateSql() {
StringBuffer sb = new StringBuffer(baseSql);
for (Parameter parameter : parameters) {
sb.append(String.format(templet, parameter.getField(),
parameter.getOperator()));
}
return sb.toString();
}
public void fillPreparedStatement(PreparedStatement pst)
throws SQLException {
int count = 1;
for (Parameter p : parameters) {
// 這里使用SetObjcet的缺點是失去了類型的驗證功能，如果大家不嫌麻煩，可以判斷，根據類型，set不同的類型
pst.setObject(count, p.getValue());
count++;
}
}
}
SelectServlet.java:
DynamicQuery query = new DynamicQuery();
query.addParameter(new Parameter("name", "like", "xudongwang"));
query.addParameter(new Parameter("email", "=", "xdwangiflytek@gmail.com"));
StudentDao studentDao = new StudentDao();
List<Stduent> students = studentDao.selectByQuery(query);
request.setAttribute("students", students);
StduentDao.java:
public List<Student> selectByQuery(DynamicQuery query) {
List<Student> students = new ArrayList<Student>();
try {
String sql = "SELECT id,name,email FROM tbl_stduent";
query.setBaseSql(sql);
//如果想排序，自行在sql后添加
sql = query.generateSql();
Connection conn = null;
try {
conn = DbUtil.getConnection();
PreparedStatement pst= conn.prepareStatement(sql);
//填充pst
query.fillPreparedStatement(pst);
ResultSet rs = pst.executeQuery();
while (rs.next()) {
Student student = new Student();
……
students.add(student);
}
rs.close();
pst.close();
} finally {
if (conn != null) {
conn.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
return students;
}
下面來總結一下上面的整個流程：
1、 首先我們在Servlet、Action等控制器里將需要查詢的表單參數值添加到查詢條件列表中；
2、 在數據訪問層DAO中設置基本的SQL；
3、 下面我們來迭代查詢條件列表，使用占位符拼接SQL；
4、 在DAO中拿SQL創建PreparedStatement；
5、 迭代查詢條件列表，為pst填充值；
6、 接下來，沒有特殊（是否添加排序）的地方了，就是正常的executeQuery，while(rs.next()){...}