前端使用分頁組件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>
數(shù)據(jù)庫(kù)
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`)
) ;
#數(shù)據(jù)初始化
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' );
后端代碼
實(shí)體類
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;
}
}
