前端使用分页组件mricode+artTemplate
<!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title></title> <meta charset="utf-8" /> <link href="mricode.pagination.css" rel="stylesheet" /> <script src="jquery-2.1.4.min.js"></script> <script src="mricode.pagination.js"></script> <script src = "template.js"></script> <script> window.onload = function () { $("#page").pagination({ pageIndex: 0, pageSize: 5, showInfo: true, showJump: true, showPageSizes: true, remote: { url: '/page.action', success: function (data) { var html = template('test', data); document.getElementById('content').innerHTML = html; } } }); } </script> </head> <body> <div id="content"></div> <div id="page" class="m-pagination"></div> <script id="test" type="text/html"> <table> <thead> <td>ID</td> <td>姓名</td> <td>密码</td> </thead> {{each list as value i}} <tr> <td>{{value.id}}</td> <td>{{value.name}}</td> <td>{{value.pwd}}</td> </tr> {{/each}} </table> </script> </body> </html>
数据库
drop database if exists jdbc; CREATE DATABASE IF NOT EXISTS jdbc; USE jdbc; drop table if exists user ; CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL , `pwd` varchar(50) DEFAULT NULL , PRIMARY KEY (`id`) ) ; #数据初始化 insert into user(name,pwd) values ('a1' ,'123456' ); insert into user(name,pwd) values ('a2' ,'123456' ); insert into user(name,pwd) values ('a3' ,'123456' ); insert into user(name,pwd) values ('a21' ,'123456' ); insert into user(name,pwd) values ('a22' ,'123456' ); insert into user(name,pwd) values ('a23' ,'123456' ); insert into user(name,pwd) values ('a31' ,'123456' ); insert into user(name,pwd) values ('a32' ,'123456' ); insert into user(name,pwd) values ('a33' ,'123456' );
后端代码
实体类
User.java
public class User { private int id; private String name; private String pwd; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPwd() { return pwd; } public void setPwd(String pwd) { this.pwd = pwd; } }
PageDataModel.java
public class PageDataModel<T> { private int total; private List<T> list; public int getTotal() { return total; } public void setTotal(int total) { this.total = total; } public List<T> getList() { return list; } public void setList(List<T> list) { this.list = list; } }
DBUtils.java
package util; import java.sql.*; public class DBUtils { static{ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException{ String url = "jdbc:mysql://localhost/jdbc?useUnicode=true&characterEncoding=UTF-8"; Connection conn = DriverManager.getConnection(url,"root","root"); return conn; } public static void close(Statement st,Connection conn){ try{ if(st != null){ try { st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }finally{ if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } public static void close(ResultSet rs,Statement st,Connection conn){ try{ if(rs != null){ try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }finally{ try{ if(st != null){ try { st.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }finally{ if(conn != null){ try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } } }
pageServlet2.java
@WebServlet(name = "pageServlet2",urlPatterns = "/page.action") public class pageServlet2 extends HttpServlet { protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { } protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String strPageIndex = request.getParameter("pageIndex"); System.out.println(strPageIndex); int nPageIndex = Integer.parseInt(strPageIndex); String strPageSize = request.getParameter("pageSize"); System.out.println(strPageSize); int nPageSize = Integer.parseInt(strPageSize); IUserInfoService iuis = UserInfoServiceImpl.getInstance(); List<User> lstUser = iuis.GetAllUsersPaged(nPageIndex,nPageSize); int nCount = iuis.GetPagedDataCount(); PageDataModel<User> p = new PageDataModel<User>(); p.setTotal(nCount); p.setList(lstUser); String strJson = JSON.toJSONString(p); response.getWriter().println(strJson); } }
IUserInfoService.java
public interface IUserInfoService { List<User> GetAllUsersPaged(int nPageIndex, int nPageSize); int GetPagedDataCount(); }
UserInfoServiceImpl.java
package Service.ServiceImpl; import Dao.DaoImpl.UserInfoDaoImpl; import Dao.IUserInfoDao; import Service.IUserInfoService; import util.User; import java.util.List; public class UserInfoServiceImpl implements IUserInfoService { private static UserInfoServiceImpl userInfoServiceImpl = new UserInfoServiceImpl(); private UserInfoServiceImpl() { ; } public static UserInfoServiceImpl getInstance() { return userInfoServiceImpl; } @Override public List<User> GetAllUsersPaged(int nPageIndex, int nPageSize) { IUserInfoDao iuid = new UserInfoDaoImpl(); List<User> lst = iuid.GetAllUsersPaged(nPageIndex,nPageSize); return lst; } @Override public int GetPagedDataCount() { IUserInfoDao iuid = new UserInfoDaoImpl(); int nCount = iuid.GetPagedDataCount(); return nCount; } }
IUserInfoDao.java
public interface IUserInfoDao { List<User> GetAllUsersPaged(int nPageIndex, int nPageSize); int GetPagedDataCount(); }
UserInfoDaoImpl.java
package Dao.DaoImpl; import Dao.IUserInfoDao; import util.DBUtils; import util.User; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class UserInfoDaoImpl implements IUserInfoDao { @Override public List<User> GetAllUsersPaged(int nPageIndex, int nPageSize) { Connection con = null; PreparedStatement ps = null; List<User> lst = new ArrayList<User>(); StringBuilder sb = new StringBuilder(); try { con = DBUtils.getConnection(); sb.append("select * FROM USER where 1 = 1 "); sb.append(" limit ?, ?"); ps = con.prepareStatement(sb.toString()); ps.setInt(1,nPageIndex*nPageSize); ps.setInt(2,nPageSize); ResultSet rs = ps.executeQuery(); while(rs.next()) { User ui = new User(); ui.setId(rs.getInt(1)); ui.setName(rs.getString(2)); ui.setPwd(rs.getString(3)); lst.add(ui); } } catch (SQLException e) { e.printStackTrace(); }finally { DBUtils.close(ps,con); } return lst; } @Override public int GetPagedDataCount() { Connection con = null; PreparedStatement ps = null; int nCount = 0; StringBuilder sb = new StringBuilder(); try { con = DBUtils.getConnection(); sb.append("select count(*) FROM user where 1 = 1 "); ps = con.prepareStatement(sb.toString()); ResultSet rs = ps.executeQuery(); if(rs.next()) nCount = rs.getInt(1); } catch (SQLException e) { e.printStackTrace(); }finally { DBUtils.close(ps,con); } return nCount; } }
