数据分页显示

时间：2021-02-22 21:11:55

最近事情较多，很久没写博客了，这几天休息正好总结一些前段时间的知识，先说一下数据分页吧，第一种分页技术采用游标定位，返回本页数据，先查询到所有的数据记录，然后使用游标定位到结果集中本页对应的起始记录，读取并返回本页数据。第二种分页技术使用数据库专有的特殊sql语句，只返回特定记录的数据。第一种方式需要与数据库多次进行交互，第二种方式只能真对特定的数据库使用，效率高，但是不可移植。

使用：Eclipse、jdk8、 tomcat9.0、 MySQL 5.5

Employee类:

package com.wyl.JavaBean;

import java.sql.Date;

public class Employee {
private int empid;
private String name;
private String gender;
private Date birthday;

public Employee() {
super();
    }

public Employee(int empid,String name,String gender,Date birthday) {
super();
this.empid=empid;
this.name=name;
this.gender=gender;
this.birthday=birthday;
    }

public int getEmpid() {
return empid;
    }
public void setEmpid(int empid) {
this.empid = empid;
    }
public String getName() {
return name;
    }
public void setName(String name) {
this.name = name;
    }
public String getGender() {
return gender;
    }
public void setGender(String gender) {
this.gender = gender;
    }
public Date getBirthday() {
return birthday;
    }
public void setBirthday(Date birthday) {
this.birthday = birthday;
    }

}

数据分页类

package com.wyl.JavaBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class PagedEmployee {
public List<Employee> getEmployeeList(int start,int length){
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
// LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数
// SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
        String sql="select * from employee LIMIT ?, ?";
        List<Employee> result=new ArrayList<Employee>();
try {
            connection = DBUtil.getConnection();//获取数据库连接
            pstmt=connection.prepareStatement(sql);
// setInt指定字符串sql中的两个参数
            pstmt.setInt(1, start);
            pstmt.setInt(2, length);
            rs=pstmt.executeQuery();
while(rs.next()) {
                Employee employee=new Employee();
                employee.setEmpid(rs.getInt("empid"));
                employee.setName(rs.getString("name"));
                employee.setGender(rs.getString("gender"));
                employee.setBirthday(rs.getDate("birthday"));
                result.add(employee);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
try {
if(connection!=null) {
                    connection.close();
                }
            }catch(Exception e) {}
        }
return result;
    }

public List<Employee> getList(){
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="select * from employee";
        List<Employee> result=new ArrayList<Employee>();
try {
            connection = DBUtil.getConnection();//获取数据库连接
            pstmt=connection.prepareStatement(sql);
            rs=pstmt.executeQuery();
while(rs.next()) {
                Employee employee=new Employee();
                employee.setEmpid(rs.getInt("empid"));
                employee.setName(rs.getString("name"));
                employee.setGender(rs.getString("gender"));
                employee.setBirthday(rs.getDate("birthday"));
                result.add(employee);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
try {
if(connection!=null) {
                    connection.close();
                }
            }catch(Exception e) {}
        }
return result;
    }

public List<Employee> getEmployeeListGeneral(int start,int length){
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="select * from Employee";
        List<Employee> result=new ArrayList<Employee>();
try {
            connection=DBUtil.getConnection();
            pstmt=connection.prepareStatement(sql);
            rs=pstmt.executeQuery();
// 把游标移动到对应的数据下标
            rs.absolute(start+1);
for(int i=0;i<length;i++) {
                Employee employee=new Employee();
                employee.setEmpid(rs.getInt("empid"));
                employee.setName(rs.getString("name"));
                employee.setGender(rs.getString("gender"));
                employee.setBirthday(rs.getDate("birthday"));
                result.add(employee);
if(!rs.next()) {
break;
                }
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
if(connection != null) {
try {
                    connection.close();
                }catch(Exception e) {}
            }
        }

return result;
    }

public int totalCount() {
        Connection connection=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
int result=0;

try {
            connection=DBUtil.getConnection();
// count(*)得到表中所有记录数量
            String sql="select count(*) from employee";
            pstmt=connection.prepareStatement(sql);
            rs=pstmt.executeQuery();
if(rs.next()) {
                result=rs.getInt(1);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
try {
if(connection!=null) {
                    connection.close();
                }
            }catch(Exception e){}
        }
return result;
    }

}

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
 pageEncoding="UTF-8"%>
<%@ page import="com.wyl.JavaBean.*,java.util.List" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>分页示例</title>
</head>
<body>
<%
 //获取总页数 
 PagedEmployee dao=new PagedEmployee();
int pageRecords = 3;//每页显示的数据
int count=dao.totalCount();//总条数
int total=count/pageRecords;//页数
if(count%pageRecords!=0){
 total++;
 }

 //获取当前页码页面的数据
String pageNumber = request.getParameter("pageNumber");
if(pageNumber==null||pageNumber.equals("")){
 pageNumber="1";
 }
int start=0;//默认pageNumber=0+1开始检索
 //根据当前页码进行检索
int number = Integer.parseInt(pageNumber);
 start=(number-1)*pageRecords;
 List<Employee> employees=dao.getEmployeeList(start, pageRecords);

 %>
<table width="80%" border="1">
<tr bgcolor="blue">
<td width="10%">编号</td>
<td width="20%">姓名</td>
<td width="10%">性别</td>
<td width="40%">生日</td>
</tr>
<%
for(Employee emp : employees){
 %>
<tr>
<td width="10%"><%=emp.getEmpid()%></td>
<td width="20%"><%=emp.getName()%></td>
<td width="10%"><%=emp.getGender()%></td>
<td width="40%"><%=emp.getBirthday()%></td>
</tr>
<% 
 }
 %>
</table>
<br/>页码
<%
for(int i=1;i<=total;i++){
 %>
<a href="index.jsp?pageNumber=<%=i%>"><%=i%></a>&nbsp;&nbsp;
<% 
 }
 %>
</body>
</html>

html查看
数据分页显示
数据分页显示
注意代码中的DBUtil类使用了数据库连接池技术，如果有人使用了以上代码，可自定义一个链接数据库类来替换DBUtil类

QAQ
标签：

相关文章