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:[e-mail]
 */
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:[e-mail]
 */
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", "=", "[e-mail]"));
		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.

 

 

评论

2 楼 drinkjava2 2016-11-23  

太复杂了而且不通用，利用ThreadLocal可完美解决这一问题，参见 http://drinkjava2.iteye.com/blog/2340020
以下为代码示例，参数利用q()方法暂存到Threadlocal中去，在SQL执行中取出来并装入preparedStatement:
public int conditionQuery(int condition, Object parameter) {//动态拼接SQL查询   
            User u = new User();   
            String sql = "Select count(*) from " + u.Table() + " where ";   
            if (condition == 1 || condition == 3)   
                sql = sql + u.UserName() + "=" + q(parameter) + " and " + u.Address() + "=" + q("Address1");   
       
            if (condition == 2)   
                sql = sql + u.UserName() + "=" + q(parameter);   
       
            if (condition == 3)   
                sql = sql + " or " + u.Age() + "=" + q(parameter);   
       
            return Dao.dao.queryForInteger(sql);   
        }   
       
        @Test   
        public void doJdbcConditionQuery() {   
            Assert.assertEquals(1, conditionQuery(1, "User1"));   
            Assert.assertEquals(0, conditionQuery(2, "User does not exist"));   
            Assert.assertEquals(1, conditionQuery(3, 10));   
            Assert.assertEquals(0, conditionQuery(3, 20));   
        }   

1 楼 jianxia801 2012-07-10  

哥们你的文章不错，值得一读！