说明
- Vue数据绑定
- axios请求服务器端数据
- layui提供表格样式
- laypage提供分页
- 数据库脚本
CREATE TABLE `tb_dept` (
`deptno` tinyint(2) UNSIGNED NOT NULL COMMENT '部门编号',
`dname` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
`loc` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门地址',
PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
- Maven依赖
junit
junit
4.12
mysql
mysql-connector-java
8.0.18
javax
javaee-api
8.0.1
com.alibaba
fastjson
1.2.62
- Dept.java
public class Dept {
private Integer deptno;
private String dname;
private String loc;
//……getter/setter、默认构造方法、全参构造方法
}
- PageBean.java
public class PageBean {
/**
* 每页显示的条数
*/
private long pageSize = 10;
/**
* 当前的页码
*/
private long pageNum;
/**
* 一共有多少条记录
*/
private long total;
/**
* 一共有多少页
*/
private long pages;
/**
* 每一页所显示的数据
*/
private List records;
//……getter/setter、默认构造方法、全参构造方法
}
- DBUtil.java
public final class DBUtil {
private static final String url = "jdbc:mysql://localhost:3306/db_test?useSSL=false&serverTimezone=GMT%2B8";//连接字符串
private static final String name = "root"; //用户名
private static final String pass = "root"; //密码
static {// 1、加载驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, name, pass);
}
public static void free(ResultSet rs, Statement stmt, Connection conn) {
try { // 建议采用这种形式来释放资源，因为finally里面的一定会被释放
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
}
- DeptService.java
public class DeptService {
public List getAll() throws SQLException {
List depts = new ArrayList();
Connection conn = DBUtil.getConnection();
String sql = "select * from tb_dept";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
depts.add(new Dept(rs.getInt("deptno"), rs.getString("dname"), rs.getString("loc")));
}
return depts;
}
public List getWithPage(Integer pageNum, Integer pageSize) throws SQLException {
List depts = new ArrayList();
Connection conn = DBUtil.getConnection();
String sql = "select * from tb_dept limit ?,?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, (pageNum - 1) * pageSize);
ps.setInt(2, pageSize);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
depts.add(new Dept(rs.getInt("deptno"), rs.getString("dname"), rs.getString("loc")));
}
DBUtil.free(rs,ps,conn);
return depts;
}
public int count() throws SQLException {
Connection conn = DBUtil.getConnection();
String sql = "select count(*) from tb_dept";
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
rs.next();
int res = rs.getInt(1);
DBUtil.free(rs,ps,conn);
return res;
}
}
- DeptServlet
@WebServlet(urlPatterns = "/dept")
public class DeptServlet extends HttpServlet {
private DeptService deptService = new DeptService();
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String op = req.getParameter("op");
PrintWriter out = resp.getWriter();
switch (op) {
case "getAll":
try {
getAll(out);
} catch (SQLException e) {
e.printStackTrace();
}
break;
case "getWithPage":
try {
getWithPage(req, out);
} catch (SQLException e) {
e.printStackTrace();
}
break;
default:
break;
}
out.flush();
}
private void getWithPage(HttpServletRequest req, PrintWriter out) throws SQLException {
int pageNum = Integer.parseInt(req.getParameter("pageNum"));
int pageSize = Integer.parseInt(req.getParameter("pageSize"));
List depts = deptService.getWithPage(pageNum, pageSize);
PageBean pageBean = new PageBean();
pageBean.setPageNum(pageNum);
pageBean.setPageSize(pageSize);
pageBean.setRecords(depts);
int count = deptService.count();
pageBean.setTotal(count);
pageBean.setPageSize(count % pageSize == 0 ? count / pageSize : count / pageSize + 1);
out.write(JSON.toJSONString(pageBean));
}
private void getAll(PrintWriter out) throws SQLException {
List depts = deptService.getAll();
PageBean pageBean = new PageBean();
pageBean.setRecords(depts);
out.write(JSON.toJSONString(pageBean));
}
}
DOCTYPE html>
Title
部门编号
部门名称
部门地址
{{dept.deptno}}
{{dept.dname}}
{{dept.loc}}
let vm = new Vue({
el: "#app",
data: { //函数对象：用来接收数据(数据承载模型)
deptList: [],
pageNum: 1, //设置首页页码
pageSize: 10, //设置一页显示的条数
total: 0, //总条数
pages: 1 //一共多少页
},
methods: {
getData: function () {
axios.get('dept', {
params: {
op: 'getWithPage',
pageNum: this.pageNum,
pageSize: this.pageSize
}
}).then(res => {
this.deptList = res.data.records;
this.total = res.data.total; //设置总条数
this.pages = res.data.pages;
console.info(res);
if (this.pageNum == 1) {
this.showPage();
}
}).catch(error => {
console.log(error);
});
},
showPage: function () {
layui.use('laypage', function () {
let laypage = layui.laypage;
//执行一个laypage实例
laypage.render({
elem: 'laypage', //注意laypage是 ID，不用加 # 号
count: vm.total, //数据总数，从服务端得到
limit: vm.pageSize, //每页条数设置
limits: [10, 20, 30], //可选每页显示条数
curr: 1, //起始页
groups: 3, //连续页码个数
prev: '上一页', //上一页文本
next: '下一页', //下一页文本
first: 1, //首页文本
last: vm.pages, //尾页文本
layout: ['prev', 'page', 'next', 'limit', 'refresh', 'skip'],
jump: function (obj, first) { //触发分页后的回调
//obj包含了当前分页的所有参数，第一次加载first为true
console.log(obj.curr); //得到当前页，以便向服务端请求对应页的数据。
console.log(obj.limit); //得到每页显示的条数
vm.pageNum = obj.curr; //改变当前页码
vm.pageSize = obj.limit;
//首次不执行，一定要加此判断，否则初始时会无限刷新
if (!first) {
vm.getData(); //加载数据
}
}
});
});
}
},
created: function () {
this.getData();
}
});
