JDBC的多条件动态查询
fmms
13年前
<p class="MsoNormal"><span>前面我已经提到了现在的项目无非就是列表里的分页加多条件查询，在<span lang="EN-US"><a href="/misc/goto?guid=4959517454226837774">http://xdwangiflytek.iteye.com/blog/1358080</a></span>里我已经简单的归纳了一下分页的实现手段和<span lang="EN-US">JDBC</span>里的分页，这里我们在来说说<span lang="EN-US">JDBC</span>里的多条件动态查询，为什么说多条件，因为在项目列表中，不可能只有只有一个字段，所以说条件也不可能只有一个，所以这里我们还是说一下多条件下的查询，多条件<span lang="EN-US">ok</span>了那么单个条件不更<span lang="EN-US">ok</span>了么（<span lang="EN-US">Hibernate</span>里有<span lang="EN-US">Criteria Queries</span>哦）。</span></p> <p class="MsoNormal"><span>首先我们和分页一样先分析一下多条件综合查询的实现方式有哪些？</span></p> <p class="MsoListParagraph"><span lang="EN-US">1、</span><span>直接将参数值拼接到<span lang="EN-US">SQL</span>语句中，然后进行<span lang="EN-US">Select</span>，但是说这种方式存在安全性问题，比如说<span lang="EN-US">SQL</span>的注入，尽管我们可以通过正则等手段来过滤掉这些特殊字符，但是这样看上去不是很爽。</span></p> <p class="MsoNormal"><span lang="EN-US">2</span><span>、先使用占位符“？”来拼接<span lang="EN-US">SQL</span>，然后再通过条件判断去填充<span lang="EN-US">PreparedStatement</span>，但是这种方式显然很麻烦，因为我们在拼<span lang="EN-US">SQL</span>的时候需要判断一次，在填充<span lang="EN-US">pst</span>的时候还需要再进行判断，所以不是好的解决方案。</span></p> <p class="MsoNormal"><span lang="EN-US">3</span><span>、存储过程，这个因为本人对存储过程认识不是很深，所以这里不加描述；</span></p> <p class="MsoNormal"><span>其实方法很多，只是找一个相对好一点的，工作的这段时间做<span lang="EN-US">.NET</span>项目时使用里面的<span lang="EN-US">SqlHelper</span>感觉很犀利，记得以前学习的时候老师也给我们做了一个类似的，所以想写一个工具类。</span></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><span lang="EN-US">Parameter.java:</span></p> <pre class="brush:java; toolbar: true; auto-links: false;">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; } }</pre> <p> </p> <p class="MsoNormal"> <span>DynamicQuery.java:</span></p> <pre class="brush:java; toolbar: true; auto-links: false;">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++; } } }</pre> <p> </p> <p> <span>SelectServlet.java:</span></p> <pre class="brush:java; toolbar: true; auto-links: false;"> 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);</pre> <p> </p> <p><span>StduentDao.java:</span></p> <pre class="brush:java; toolbar: true; auto-links: false;">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; }</pre> <p> </p> <p class="MsoNormal"><span>下面来总结一下上面的整个流程：</span></p> <p class="MsoListParagraph"><span lang="EN-US"> 1、<span style="line-height:normal;font-variant:normal;font-style:normal;font-size:7pt;font-weight:normal;"> </span></span><span>首先我们在<span lang="EN-US">Servlet</span>、<span lang="EN-US">Action</span>等控制器里将需要查询的表单参数值添加到查询条件列表中；</span></p> <p class="MsoListParagraph"><span lang="EN-US"> 2、<span style="line-height:normal;font-variant:normal;font-style:normal;font-size:7pt;font-weight:normal;"> </span></span><span>在数据访问层<span lang="EN-US">DAO</span>中设置基本的<span lang="EN-US">SQL</span>；</span></p> <p class="MsoListParagraph"><span lang="EN-US"> 3、<span style="line-height:normal;font-variant:normal;font-style:normal;font-size:7pt;font-weight:normal;"> </span></span><span>下面我们来迭代查询条件列表，使用占位符拼接<span lang="EN-US">SQL</span>；</span></p> <p class="MsoListParagraph"><span lang="EN-US"> 4、<span style="line-height:normal;font-variant:normal;font-style:normal;font-size:7pt;font-weight:normal;"> </span></span><span>在<span lang="EN-US">DAO</span>中拿<span lang="EN-US">SQL</span>创建<span lang="EN-US">PreparedStatement</span>；</span></p> <p class="MsoListParagraph"><span lang="EN-US"> 5、<span style="line-height:normal;font-variant:normal;font-style:normal;font-size:7pt;font-weight:normal;"> </span></span><span>迭代查询条件列表，为<span lang="EN-US">pst</span>填充值；</span></p> <p class="MsoListParagraph"><span lang="EN-US"> 6、<span style="line-height:normal;font-variant:normal;font-style:normal;font-size:7pt;font-weight:normal;"> </span></span><span>接下来，没有特殊（是否添加排序）的地方了，就是正常的<span lang="EN-US">executeQuery</span>，<span lang="EN-US">while(rs.next()){...}</span></span></p>