SSM框架–实现模糊分页查询
一.准备工作
-
使用MyBatis逆向生成员工表和部门表的实体类,接口以及映射文件
-
pom.xml中添加依赖
<!--1.MyBatis--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.4.6</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.0</version> </dependency> <dependency> <groupId>org.mybatis.generator</groupId> <artifactId>mybatis-generator-core</artifactId> <version>1.3.5</version> </dependency> <!--逆向生成带分页的插件--> <dependency> <groupId>com.itfsw</groupId> <artifactId>mybatis-generator-plugin</artifactId> <version>1.0.5</version> </dependency> <!--2.Spring--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>4.2.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.2.5.RELEASE</version> </dependency> <!--3.SpringMvc--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.2.5.RELEASE</version> </dependency> <!--4.c3p0--> <dependency> <groupId>com.mchange</groupId> <artifactId>c3p0</artifactId> <version>0.9.5.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <dependency> <groupId>jstl</groupId> <artifactId>jstl</artifactId> <version>1.2</version> </dependency> <dependency> <groupId>taglibs</groupId> <artifactId>standard</artifactId> <version>1.1.2</version> </dependency> <!--pojo转换成 json --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> <version>2.7.3</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>2.7.3</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> <version>2.7.3</version> </dependency> <!--servlet 2.5 --> <dependency> <groupId>javax.servlet</groupId> <artifactId>servlet-api</artifactId> <version>2.5</version> <!--此处的servlet仅在编译和测试的时候使用,运行的时候不使用,使用的是tomcat中的servlet--> <scope>provided</scope> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.4</version> </dependency>
-
mbg.xmll中添加分页插件配置
<!--分页插件--> <plugin type="com.itfsw.mybatis.generator.plugins.LimitPlugin"/>
二.展示页面
1.form表单包含查询条件和回显
- 需要查出所有的图书类型
<form id="myForm" action="/emp/search" method="post" style="text-align: center">
姓名:<input type="text" name="name" value="${page.name}">
生日:<input type="date" name="startBirth" value="<fmt:formatDate value='${page.startBirth}' pattern='yyyy-MM-dd'/>" >-
<input type="date" name="endBirth" value="<fmt:formatDate value='${page.endBirth}' pattern='yyyy-MM-dd'/>" >
部门:<select name="deptId" >
<option value="0">--请选择--</option>
<c:forEach var="dept" items="${depts}">
<option value="${dept.did}"
<c:if test="${dept.did==page.deptId}">selected=true</c:if>
>${dept.dname}</option>
</c:forEach>
</select>
<input type="hidden" id="pageNum" name="currPage" value="1">
<input type="submit" value="搜索">
</form>
2.table做主体数据展示
- Book.bookType.name 需要在后台把booktype存到book中
<table align="center" cellpadding="15" cellspacing="0" border="1">
<tr>
<td>编号</td>
<td>姓名</td>
<td>年龄</td>
<td>性别</td>
<td>生日</td>
<td>津贴</td>
<td>工资</td>
<td>部门</td>
</tr>
<c:if test="${not empty list}">
<c:forEach var="emp" items="${list}">
<tr>
<td>${emp.id}</td>
<td>${emp.name}</td>
<td>${emp.age}</td>
<td>${emp.sex}</td>
<td><fmt:formatDate value="${emp.birth}" pattern="yyyy-MM-dd"/></td>
<td>${emp.bonus}</td>
<td>${emp.salary}</td>
<td>${emp.deptid}</td>
</tr>
</c:forEach>
</c:if>
</table>
3.分页
- 使用超链接标签调用js函数to_Page,实现form表单的提交
<table align="center" cellpadding="10" cellspacing="10" border="1">
<tr>
<td><a href="javascript:goPage(1)">首页</a></td>
<td><a href="javascript:goPage(${page.currPage-1})">上一页</a></td>
<c:forEach var="i" begin="1" end="${page.totalPage}">
<td><a href="javascript:goPage(${i})">${i}</a></td>
</c:forEach>
<td><a href="javascript:goPage(${page.currPage+1})">下一页</a></td>
<td><a href="javascript:toPage()">跳转</a>到第<input size="2" type="text" value="${page.currPage}" id="go">页</td>
<td><a href="javascript:goPage(${page.totalPage})">尾页</a></td>
</tr>
</table>
<!--js部分-->
<script>
function goPage(page) {
var total=${page.totalPage};
if(page>total){
page=total;
}
if(page<1){
page=1;
}
$("#pageNum").val(page);
$("#myForm").submit()
}
function toPage() {
var page=$("#go").val();
goPage(page)
}
</script>
-
修改页面update.jsp
-
<form action="/emp/update2" method="post"> <input type="hidden" name="id" value="${emp.id}"> 姓名:<input type="text" name="name" value="${emp.name}"/> <br> 年龄:<input type="text" name="age" value="${emp.age}"/><br> 性别:<input type="text" name="sex" value="${emp.sex}"/><br> 生日:<input type="date" name="birth" value="<fmt:formatDate value='${emp.birth}' pattern='yyyy-MM-dd'/>" /><br> 津贴:<input type="text" name="bonus" value="${emp.bonus}"/><br> 工资:<input type="text" name="salary" value="${emp.salary}"/><br> 部门:<select name="deptid"> <c:forEach var="dept" items="${depts}"> <option value="${dept.did}" <c:if test="${dept.did==emp.deptid}" >selected='true'</c:if> > ${dept.dname} </option> </c:forEach> </select><br> <input type="submit" value="提交"> </form>
-
新增页面add.jsp
-
<form action="/emp/add2" method="post"> 姓名:<input type="text" name="name"/> <br> 年龄:<input type="text" name="age"/><br> 性别:<input type="text" name="sex"/><br> 生日:<input type="date" name="birth"/><br> 津贴:<input type="text" name="bonus"/><br> 工资:<input type="text" name="salary"/><br> 部门:<select name="deptid"> <c:forEach var="dept" items="${depts}"> <option value="${dept.did}">${dept.dname}</option> </c:forEach> </select><br> <input type="submit" value="提交"> </form>
三.Controller层
1.接收jsp的查询条件参数,和当前页参数pageNum
2.查询所有的部门信息
3.进行模糊条件 查询
4.将pageinfo,所有部门 和查询条件存进model
5.跳转到展示页面
@Controller
@RequestMapping("/emp")
public class EmpController {
@Autowired
private IEmpService service;
//新增页面跳转
@RequestMapping("/add1")
public String add1(Model m){
//查询所有的部门--中下拉框展示所有的部门
List<Dept> depts=service.selectDepts();
m.addAttribute("depts",depts);
return "add";
}
//实现新增
@RequestMapping("/add2")
public String add2(Emp emp){
int i=service.addEmp(emp);
return "redirect:/emp/show";//去show方法 重查
}
//1.查询单条 2.页面跳转
@RequestMapping("/update1/{id}")
public String update1(@PathVariable String id, Model m){
Emp e=service.selectById(Integer.parseInt(id));
m.addAttribute("emp",e);
//查询所有的部门--中下拉框展示所有的部门
List<Dept> depts=service.selectDepts();
m.addAttribute("depts",depts);
return "update";//转发到update.jsp
}
//执行修改
@RequestMapping("/update2")
public String update2(Emp emp){
int i=service.updateEmp(emp);
return "redirect:/emp/show";//去show方法 重查
}
@RequestMapping("/deleteById/{id}")
public String deleteById(@PathVariable String id){
int i=service.deleteById(Integer.parseInt(id));
//删除后重查数据库
return "redirect:/emp/show";//去show方法 重查
}
@RequestMapping("/show")
public ModelAndView show(PageBean page,ModelAndView mav){
//取 调 存 转
//查询所有的部门--show.jsp中下拉框展示所有的部门
List<Dept> depts=service.selectDepts();
//模糊分页条件查询员工
List<Emp> list=service.show(page);
//存 depts list page
mav.addObject("depts",depts);
mav.addObject("list",list);
mav.addObject("page",page);
//转
mav.setViewName("show");
return mav;
}
}
四.Service层
1.开始执行分页(必须放在最前面)
2.模糊条件查询 得到结果集 emps
3.emps集合进行遍历 并查询每一名员工的部门 存进员工对象中
4.将结果集 以有参构造的方式存到pageInfo(分页的参数 查询结果集)中
@Service
@Transactional
public class EmpServiceImpl implements IEmpService {
@Autowired
private DeptMapper deptMapper;
@Autowired
private EmpMapper empMapper;
@Override//查询所有的部门
public List<Dept> selectDepts() {
return deptMapper.selectByExample(null);
}
@Override//条件分页查询员工
public List<Emp> show(PageBean page) {
//1.条件 name startBirth endBirth deptId
EmpExample e = new EmpExample();
EmpExample.Criteria c = e.createCriteria();
if(page.getName()!=null){
c.andNameLike("%"+page.getName()+"%");
}
if(page.getStartBirth()!=null){
c.andBirthGreaterThanOrEqualTo(page.getStartBirth());
}
if(page.getEndBirth()!=null){
c.andBirthLessThanOrEqualTo(page.getEndBirth());
}
if(page.getDeptId()!=null&&page.getDeptId()!=0){
c.andDeptidEqualTo(page.getDeptId());
}
//查询总记录数
int count = (int) empMapper.countByExample(e);
int size = page.getSize();
int totalPage=(count%size==0)?(count/size):(count/size+1);
page.setCount(count);
page.setTotalPage(totalPage);
//分页
int startRow=(page.getCurrPage()-1)*size;
e.limit(startRow,size);
List<Emp> list = empMapper.selectByExample(e);
for(Emp emp:list){
Dept dept = deptMapper.selectByPrimaryKey(emp.getDeptid());
emp.setDept(dept);
}
return list;
}
@Override
public int deleteById(int id) {
return empMapper.deleteByPrimaryKey(id);
}
@Override
public int addEmp(Emp emp) {
return empMapper.insertSelective(emp);
}
@Override
public Emp selectById(int id) {
return empMapper.selectByPrimaryKey(id);
}
@Override
public int updateEmp(Emp emp) {
return empMapper.updateByPrimaryKeySelective(emp);
}
}
- PageBean 封装条件类
public class PageBean {
//1.分页四大参数 2.搜索条件参数
private int currPage=1;
private int size=3;
private int count;//总条数
private int totalPage;//总页数
// 2.搜索条件参数
private String name;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE)
private Date startBirth;
@DateTimeFormat(iso = DateTimeFormat.ISO.DATE)
private Date endBirth;
private Integer deptId;
//省略 get/set set中String类型的带 trim()方法
}