海安市建设局网站,表白网页制作模板,wordpress制作友情链接页面,建自己的个人网站一 概述
1 原理 根据用户传入的条件进行数据的筛选查询#xff0c;最后将结果放回给用户#xff0c;例如对员工的姓名与工资范围进行筛选查询
// 查询所有员工
SELECT * FROM employee
// 模糊查询员工名称
SELECT * FROM employee WHERE name LIKE %黄%
// 查询工资为8000…一 概述
1 原理 根据用户传入的条件进行数据的筛选查询最后将结果放回给用户例如对员工的姓名与工资范围进行筛选查询
// 查询所有员工
SELECT * FROM employee
// 模糊查询员工名称
SELECT * FROM employee WHERE name LIKE %黄%
// 查询工资为8000的员工
SELECT * FROM employee WHERE salary8000
// 查询工资在8000-9000范围内的员工包含
SELECT * FROM employee WHERE salary 8000 AND salary 9000
// 模糊查询员工名称的同时对工资范围进行限定
SELECT * FROM employee WHERE name LIKE %黄% AND salary 8000 AND salary 9000SELECT 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 testboolean 表达式/if// 例子 分页查询薪资于8000-9000范围内的姓名含employeeName的员工
!--分页查询:mybatis--
select idqueryForList resultTypeemployeeSELECT * from employeeif testemployeeName ! nulland employeeName LIKE CONCAT(%,#{employeeName},%)/ifif testminSalary ! null and salary gt;#{minSalary}/ifif testmaxSalary ! nulland salary lt;#{maxSalary}/ifLIMIT #{start},#{pageSize}
/select当用户只模糊查询时 minSalary 与 maxSalary 没有传递此时需要使用where标签解决SQL语法问题
2 where 标签 – 去除多余and或or关键字
// 语法
where条件语句/where// 例子
select idqueryForList resultTypeEmployeeSELECT * from employeewhereif testemployeeName ! nulland employeeName LIKE CONCAT(%,#{employeeName},%)/ifif testminSalary ! null and salary gt;#{minSalary}/ifif testmaxSalary ! nulland salary lt;#{maxSalary}/if/whereLIMIT #{start},#{pageSize}
/selectsql查询语句无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 idqueryForCount resultTypeintSELECT count(*) from employeeinclude refidwhere_sql/include/select!--分页查询:mybatis--select idqueryForList resultTypeEmployeeSELECT * from employeeinclude refidwhere_sql/includeLIMIT #{start},#{pageSize}/select!--封装sql片段--sql idwhere_sqlwhereif testemployeeName ! nulland employeeName LIKE CONCAT(%,#{employeeName},%)/ifif testminSalary ! null and salary gt;#{minSalary}/ifif testmaxSalary ! nulland salary lt;#{maxSalary}/if/where/sql③ list,jsp
form action/employee methodpost姓名input typetext nameemployeeName value${qo.employeeName}工资input typetext nameminSalary value${qo.minSalary}-input typetext namemaxSalary value${qo.maxSalary}input typesubmit 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);// 继承了qoqo有的他也有EmployeeQueryObject qonew 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分页查询的方法PageResultEmployee 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();}
}
