前面我已经提到了现在的项目无非就是列表里的分页加多条件查询,在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()){...}
Ok,88.