Java研学-过滤查询

一 概述

1 原理

  根据用户传入的条件进行数据的筛选查询，最后将结果放回给用户，例如对员工的姓名与工资范围进行筛选查询

// 查询所有员工
SELECT * FROM employee
// 模糊查询员工名称
SELECT * FROM employee WHERE name LIKE '%黄%'
// 查询工资为8000的员工
SELECT * FROM employee WHERE salary=8000
// 查询工资在8000-9000范围内的员工（包含）
SELECT * FROM employee WHERE salary >= 8000 AND salary <= 9000
// 模糊查询员工名称的同时对工资范围进行限定
SELECT * FROM employee WHERE name LIKE '%黄%' AND salary >= 8000 AND salary <= 9000

  SELECT ＊ FROM employee 这部分SQL是固定的，只是WHERE子句部分在变动，这取决于用户在页面传递的查询参数，根据用户查询传递的参数，拼接WHERE条件，这就是过滤查询的底层原理。
  想实现上述需求，应定义一个类封装用户传递到后台的请求参数，使用 MyBaits的动态SQL 根据用户传递的参数在Mapper XML中拼接对应的SQL

2 数据封装 – EmployeeQueryObject

  过滤查询时，请求可能会传递多个过滤条件的参数来查询对应的数据，这些参数需在后台多个层次之间进行传递（servlet – service – dao），故将参数封装到指定的对象中便于参数传递
  编写 EmployeeQueryObject 来封装过滤查询的参数。产品查询又想支持分页查询，又想支持过滤查询，所以使用 EmployeeQueryObject 来继承 QueryObject，既可以封装分页查询的参数，又可以封装过滤查询的参数。

// EmployeeQueryObject
@Setter
@Getter
public class EmployeeQueryObject extends QueryObject{
    private String employeeName;
    private Double minSalary;//小的区间值
    private Double maxSalary;//大的区间值
}

二 Mybatis动态Sql

1 if 标签 – 单条件判断

// 语法
<if test="boolean 表达式"></if>

// 例子 分页查询薪资于8000-9000范围内的，姓名含employeeName的员工
<!--分页查询:mybatis-->
<select id="queryForList" resultType="employee">
	SELECT * from employee
	<if test="employeeName != null">
		and employeeName LIKE CONCAT("%",#{employeeName},"%")
	</if>
	<if test="minSalary != null ">
		and salary &gt;=#{minSalary}
	</if>
	<if test="maxSalary != null">
		and salary &lt;=#{maxSalary}
	</if>
	LIMIT #{start},#{pageSize}
</select>

  当用户只模糊查询时 minSalary 与 maxSalary 没有传递，此时需要使用where标签解决SQL语法问题

2 where 标签 – 去除多余and或or关键字

// 语法
<where>条件语句</where>

// 例子
<select id="queryForList" resultType="Employee">
	SELECT * from employee
	<where>
	<if test="employeeName != null">
	and employeeName LIKE CONCAT("%",#{employeeName},"%")
	</if>
	<if test="minSalary != null ">
	and salary &gt;=#{minSalary}
	</if>
	<if test="maxSalary != null">
	and salary &lt;=#{maxSalary}
	</if>
	</where>
	LIMIT #{start},#{pageSize}
</select>

  sql查询语句无where关键字，则在语句前插入where，若sql查询语句以and和or开头，则用where替换掉

3 Mybatis转义符

&lt;       <        小于
&gt;       >        大于
&amp;      &        与
&apos;     '        单引号
&quot;     "        双引号

三 过滤实现

① EmployeeQueryObject

@Setter
@Getter
public class EmployeeQueryObject extends QueryObject{
    private String employeeName;
    private Double minSalary;//小的区间值
    private Double maxSalary;//大的区间值
}

② EmployeeMapper.xml

<!--查询总条数-->
    <select id="queryForCount" resultType="int">
        SELECT count(*) from employee
        <include refid="where_sql"></include>
    </select>
    <!--分页查询:mybatis-->
    <select id="queryForList" resultType="Employee">
        SELECT * from employee
        <include refid="where_sql"></include>
        LIMIT #{start},#{pageSize}
    </select>
    <!--封装sql片段-->
    <sql id="where_sql">
        <where>
            <if test="employeeName != null">
                and employeeName LIKE CONCAT("%",#{employeeName},"%")
            </if>
            <if test="minSalary != null ">
                and salary &gt;=#{minSalary}
            </if>
            <if test="maxSalary != null">
                and salary &lt;=#{maxSalary}
            </if>
        </where>
    </sql>

③ list,jsp

<form action="/employee" method="post">
        姓名：<input type="text" name="employeeName" value="${qo.employeeName}">
        工资：<input type="text" name="minSalary" value="${qo.minSalary}">-
        <input type="text" name="maxSalary" value="${qo.maxSalary}">
        <input type="submit" value="查询">

④ EmployeeServlet

// 获取过滤参数封装对应QO对象中
/*分页查询所有*/
private void list(HttpServletRequest req, HttpServletResponse resp) {
	try {
	/*客户端如果传入了当前页码和每页显示条数则设置给qo对象，如果没有传入则使用默认值*/
	String currentPage = req.getParameter("currentPage");
	String pageSize = req.getParameter("pageSize");
	// 获取过滤参数
	String employeeName = req.getParameter("employeeName");
	String minSalary = req.getParameter("minSalary");
	String maxSalary = req.getParameter("maxSalary");
	// 继承了qo，qo有的他也有
	EmployeeQueryObject qo=new EmployeeQueryObject();
	if (StringUtil.hasLength(currentPage)){
	qo.setCurrentPage(Integer.valueOf(currentPage));
	}
	if (StringUtil.hasLength(pageSize)){
	qo.setPageSize(Integer.valueOf(pageSize));
	}
	if (StringUtil.hasLength(employeeName)){
	qo.setEmployeeName(employeeName);
	}
	if (StringUtil.hasLength(minSalary)){
	qo.setMinSalary(new Double(minSalary));
	}
	if (StringUtil.hasLength(maxSalary)){
	qo.setMaxSalary(new Double(maxSalary));
	}
	//调用service分页查询的方法
	PageResult<Employee> pageResult = employeeService.queryByPage(qo);
	//将查询的结果存储到请求作用域
	req.setAttribute("pageResult",pageResult);
	// 查询条件回显
	req.setAttribute("qo",qo);
	//转发到列表页面
	req.getRequestDispatcher("/WEB-INF/views/employee/list.jsp").forward(req,resp);
	} catch (Exception e) {
		e.printStackTrace();
	}
}