??根据用户传入的条件进行数据的筛选查询,最后将结果放回给用户,例如对员工的姓名与工资范围进行筛选查询
// 查询所有员工
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
??过滤查询时,请求可能会传递多个过滤条件的参数来查询对应的数据,这些参数需在后台多个层次之间进行传递(servlet – service – dao),故将参数封装到指定的对象中便于参数传递
??编写 EmployeeQueryObject 来封装过滤查询的参数。产品查询又想支持分页查询,又想支持过滤查询,所以使用 EmployeeQueryObject 来继承 QueryObject,既可以封装分页查询的参数,又可以封装过滤查询的参数。
// EmployeeQueryObject
@Setter
@Getter
public class EmployeeQueryObject extends QueryObject{
private String employeeName;
private Double minSalary;//小的区间值
private Double maxSalary;//大的区间值
}
// 语法
<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 >=#{minSalary}
</if>
<if test="maxSalary != null">
and salary <=#{maxSalary}
</if>
LIMIT #{start},#{pageSize}
</select>
??当用户只模糊查询时 minSalary 与 maxSalary 没有传递,此时需要使用where标签解决SQL语法问题
// 语法
<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 >=#{minSalary}
</if>
<if test="maxSalary != null">
and salary <=#{maxSalary}
</if>
</where>
LIMIT #{start},#{pageSize}
</select>
??sql查询语句无where关键字,则在语句前插入where,若sql查询语句以and和or开头,则用where替换掉
< < 小于
> > 大于
& & 与
' ' 单引号
" " 双引号
① 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 >=#{minSalary}
</if>
<if test="maxSalary != null">
and salary <=#{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();
}
}