pom.xml添加依赖jar
添加spring-mvc、mybatis、mysql、pagehelper等依赖jar包。
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>sdm.cn</groupId>
<artifactId>sdm-web</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<dependencies>
<!-- spring-webmvc依赖jar包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<!-- spring-jdbc依赖jar包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<!-- mybatis依赖jar包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<!-- mybatis-spring依赖jar包 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<!-- mysql依赖jar包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!-- commons-dbcp依赖jar包 -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<!-- junit依赖jar包 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<!-- jstl依赖jar包 -->
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.2.1</version>
</dependency>
</dependencies>
</project>
Dao层
package cn.sdm.mapper;
import java.util.List;
import cn.sdm.bean.Person;
/**
* 用户
*
* @author liuyuwen
*/
public interface UserMapper {
/**
* 账号管理
* 查询所有账号信息(账号、姓名、学院、年级、班级、联系方式)
* @return
*/
List<Person> selectPersonAll();
/**
* 账号管理
* 根据条件查询人员信息
* @return
*/
List<Person> selectPersonByCondition(Person person);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
"http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="cn.sdm.mapper.UserMapper">
<!-- List<Person> selectPersonAll(); 账号管理: 查询所有账号信息(账号、姓名、学院、年级、班级、联系方式) -->
<select id="selectPersonAll" resultType="cn.sdm.bean.Person">
select
u.id AS userId,p.id AS id,u.username,p.name,c.code college,c.name AS collegeName,p.grade,p.classes,p.phone,
(CASE WHEN p.gender="boy" THEN "男" WHEN p.gender="girl" THEN "女" ELSE "" END) AS gender
from
t_user u
LEFT JOIN t_person p ON u.id=p.user_id
LEFT JOIN t_college c ON c.id=p.college_id
</select>
<!-- List<Person> selectPersonByCondition(Person person); 账号管理: 根据条件查询人员信息 -->
<select id="selectPersonByCondition" resultType="cn.sdm.bean.Person" parameterType="cn.sdm.bean.Person">
select
u.id AS userId,p.id AS id,u.username,p.name,c.code college,c.name AS collegeName,p.grade,p.classes,p.phone,
(CASE WHEN p.gender="boy" THEN "男" WHEN p.gender="girl" THEN "女" ELSE "" END) AS gender
from
t_user u
LEFT JOIN t_person p ON u.id=p.user_id
LEFT JOIN t_college c ON c.id=p.college_id
<where>
<if test="collegeId!=null">
college_id=#{collegeId}
</if>
<if test="grade!=0">
and grade=#{grade}
</if>
<if test="classes!=0">
and classes=#{classes}
</if>
</where>
</select>
</mapper>
Service层
package cn.sdm.service;
import com.github.pagehelper.PageInfo;
import cn.sdm.bean.Person;
/**
* 用户管理
* @author awen
*
*/
public interface IUserService {
/**
*
* 账号管理
* 查询所有人员信息
* @return
*/
PageInfo<Person> selectPersonAll(int pageNum, int pageSize);
/**
* 账号管理
* 根据条件查询人员信息
* @return
*/
PageInfo<Person> selectPersonByCondition(int pageNum, int pageSize,Person person);
}
package cn.sdm.service;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import cn.sdm.bean.Person;
import cn.sdm.mapper.UserMapper;
/**
* 用户
* @author awen
*
*/
@Service
public class UserService implements IUserService {
@Resource
private UserMapper userMapper;
/**
* 账号管理
* 查询所有人员信息
* @return
*/
public PageInfo<Person> selectPersonAll(int pageNum, int pageSize) {
//设置当前页数与每页大小
PageHelper.startPage(pageNum, pageSize);
//从数据库获得要实现分页的数据
List<Person> list = userMapper.selectPersonAll();
//插入分页数据
PageInfo<Person> pageInfo = new PageInfo<Person>(list);
return pageInfo;
}
/**
* 账号管理
* 根据条件查询人员信息
* @return
*/
public PageInfo<Person> selectPersonByCondition(int pageNum, int pageSize,Person person){
//设置当前页数与每页大小
PageHelper.startPage(pageNum, pageSize);
//从数据库获得要实现分页的数据
List<Person> list = userMapper.selectPersonByCondition(person);
System.out.println("条件person: "+person.toString());
System.out.println("查询数据list: "+list.toString());
//插入分页数据
PageInfo<Person> pageInfo = new PageInfo<Person>(list);
return pageInfo;
}
}
Controller层
package cn.sdm.controller;
import java.util.HashMap;
import javax.annotation.Resource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.github.pagehelper.PageInfo;
import cn.sdm.bean.Person;
import cn.sdm.bean.ResponseResult;
import cn.sdm.service.IUserService;
/**
* 用户管理
* @author awen
*/
@Controller
@RequestMapping("/user")
public class UserController extends BaseController{
@Resource
private IUserService userService;
// 分页尺寸
@Value("#{config.pageSize}")
private int pageSize;
// 显示账号管理页面
@RequestMapping("/accountManage.do")
public String showAccountManage(ModelMap map){
PageInfo<Person> pagePerson = userService.selectPersonAll(1, pageSize); // 参数1:第几页; 参数2:每页几条记录
map.put("persons", pagePerson.getList());
map.put("currPage", 1);
map.put("totalPage", pagePerson.getPages());
map.put("totalRecord", pagePerson.getTotal());
return "account_manage";
}
// 异步请求根据条件查询账号信息+异步请求分页查询
// 这里我需要根据学院、年纪、班级等条件来查询信息
@RequestMapping("/accountByCondition.do")
@ResponseBody
public ResponseResult<HashMap<String,Object>> accountByCondition(Integer currPage,String college,Integer grade,Integer classes,ModelMap map){
System.out.println("currPage: "+currPage+",college: "+college+",grade: "+grade+",classes: "+classes);
Person person = new Person();
person.setCollege(college);
person.setGrade(grade);
person.setClasses(classes);
PageInfo<Person> pagePerson = userService.selectPersonByCondition(currPage,pageSize,person);
//返回指定页数的数据
map.put("persons", pagePerson.getList());
//返回当前页
map.put("currPage", currPage);
//返回总页数
map.put("totalPage", pagePerson.getPages());
// 返回总记录数
map.put("totalRecord", pagePerson.getTotal());
ResponseResult<HashMap<String,Object>> rr = new ResponseResult<HashMap<String,Object>>(1,"根据条件查询成功",map);
return rr;
}
}
jsp
<%@ page contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<%@ page import="java.util.*,java.text.*" %>
<!DOCTYPE html>
<html>
<head lang="en">
<meta charset="utf-8" />
<title>账号管理</title>
<script src="../js/jquery-3.1.1.min.js"></script>
<script src="../js/jquery.cookie.js"></script>
<script src="../js/account_manage.js"></script>
<style type="text/css">
</style>
<script type="text/javascript">
</script>
</head>
<body>
<div class="container">
<table class="data_table">
<tr>
<th class="s_check"><input type="checkbox" name="check" id="checkAll" value="" class="checkAll"/></th><!-- 是否全选 -->
<th class="s_seq">序号</th>
<th class="s_username">账号</th>
<th class="s_name">姓名</th>
<th class="s_grade">性别</th>
<th class="s_college">学院</th>
<th class="s_grade">年级</th>
<th class="s_classes">班级</th>
<th class="s_phone">联系方式</th>
<th class="s_operation">操作</th>
</tr>
<c:forEach items="${persons }" var="person" varStatus="s">
<tr class="row${s.index%2+1 }">
<td class="s_check"><input type="checkbox" name="check" value="${person.id }"/></td>
<td class="s_seq">${s.index+1 }</td>
<td class="s_username">${person.username }</td>
<td class="s_name">${person.name }</td>
<td class="s_grade">${person.gender }</td>
<td class="s_college">${person.collegeName=null?"":person.collegeName}</td>
<td class="s_grade"><c:if test="${person.grade!=null }">${person.grade }级</c:if></td>
<td class="s_classes"><c:if test="${person.classes!=null }">${person.classes }班</c:if></td>
<td class="s_phone">${person.phone }</td>
<td class="s_operation">
<input type="button" id="edit_button" onclick="edit_button(${person.userId },${person.id })" value="编辑">
<input type="button" id="delete_button" onclick="delete_button(${person.userId },${person.id })" value="删除">
</td>
</tr>
</c:forEach>
</table>
</div>
<div class="page-button">
<span>
第<input type="text" value="${currPage }" id="currPage">页 | 共<input type="text" value="${totalPage }" id="totalPage" disabled="disabled">页 | 共<input type="text" value="${totalRecord }" id="totalRecord" disabled="disabled">条记录
</span>
<input type="button" id="qty_page_button" onclick="qty_page_button()" value="查询">
<input type="button" id="prev_page_button" onclick="prev_page_button()" value="上一页">
<input type="button" id="next_page_button" onclick="next_page_button()" value="下一页">
</div>
</div>
</body>
</html>
/** 分页查询 **/
// 上一页
function prev_page_button(){
var currPage = Number($("#currPage").val());
if(currPage<=1){
return;
}
var url = "../user/accountByCondition.do";
var params= $("#condition").serializeArray();
params.push({"name":"currPage", "value":currPage-1});
showTable(url,params,(currPage-2)*3);
}
// 下一页
function next_page_button(){
var currPage = Number($("#currPage").val());
var totalPage = Number($("#totalPage").val());
if(currPage>=totalPage){
return;
}
var url = "../user/accountByCondition.do";
var params= $("#condition").serializeArray();
params.push({"name":"currPage", "value":currPage+1});
showTable(url,params,currPage*3);
}
// 按指定页查询
function qty_page_button(){
var currPage = Number($("#currPage").val());
var totalPage = Number($("#totalPage").val());
if(currPage<1 || currPage>totalPage){
return;
}
var url = "../user/accountByCondition.do";
var params= $("#condition").serializeArray();
params.push({"name":"currPage", "value":currPage});
showTable(url,params,(currPage-1)*3);
}
总结分页PageHelper
public PageInfo<Person> selectPersonByCondition(int pageNum, int pageSize,Person person){
// 设置当前页数与每页大小
PageHelper.startPage(pageNum, pageSize); // pageNum表示第几页,pageSize表示每页几条记录
// 从数据库获得要实现分页的数据
List<Person> list = userMapper.selectPersonByCondition(person);
// 插入分页数据
PageInfo<Person> pageInfo = new PageInfo<Person>(list);
return pageInfo;
}