利用servlet+jsp+javabean进行分页查询

分页项目

文件结构

文件说明

Companyinfo：是一个JavaBean类，需要显示的内容
pageBean：描述分页信息的内容
CompanyinfoDao：Companyinfo的Dao层用来数据库访问的一个接口
DB：连接数据库，获取数据库连接对象
page：servlet与前台进行交换，在运行该项目的时候在浏览器的的地址栏访问
http://localhost:8080/page/page
show.jsp：用来显示从数据库获取的记录，并可以进行上下页进行查看
lib：导入该项目所需要的jar

mysql的limit查询

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset  

说明：
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个
或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指
定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记
录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句
法： LIMIT # OFFSET #。

源代码下载地址

利用servlet+jsp+javabean进行分页查询

JDBC

package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DB {
	private final static String driver="com.mysql.jdbc.Driver";
	private final static String url = "jdbc:mysql://
    127.0.0.1:3306/job?useSSL=false";
	private final static String user = "root";
	private final static String password  ="";
	public static Connection getConn() {
		Connection connection = null;
		try {
			Class.forName(driver);
			connection=DriverManager.getConnection(url, user, 
            password);
		} catch (ClassNotFoundException e) {
			System.out.println("数据库驱动加载失败");
			e.printStackTrace();
		}catch (SQLException e) {
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		return connection;
	}
	
	public static void close (Connection connection ,Statement 
    statement , ResultSet resultSet) {
		try {
			if(connection!=null)connection.close();
			if(statement!=null)statement.close();
			if(resultSet!=null)resultSet.close();
		} catch (SQLException e) {
			System.out.println("数据库关闭异常");
			e.printStackTrace();
		}
	}
	
	//测试数据库连接是否成功
	public static void main(String []args) {
		Connection connection=null;
		connection = getConn();
		System.out.println(connection);
		close(connection, null, null);
		
	}
}

javaBean的Companyinfo

package Bean;

public class Companyinfo {
	private String c_name;
	private String c_email;
	private String c_serve;

	@Override
	public String toString() {
		return "Companyinfo [c_name=" + c_name + ", c_email=" + c_email + ", c_serve=" + c_serve + "]";
	}

	public Companyinfo(String c_name, String c_email, String c_serve) {
		super();
		this.c_name = c_name;
		this.c_email = c_email;
		this.c_serve = c_serve;
	}

	public Companyinfo() {
		super();
	}

	public String getC_name() {
		return c_name;
	}

	public void setC_name(String c_name) {
		this.c_name = c_name;
	}

	public String getC_email() {
		return c_email;
	}

	public void setC_email(String c_email) {
		this.c_email = c_email;
	}

	public String getC_serve() {
		return c_serve;
	}

	public void setC_serve(String c_serve) {
		this.c_serve = c_serve;
	}

}

Dao层

package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import Bean.Companyinfo;
import jdbc.DB;

public class CompanyinfoDao {
	
	public int getCount(String sql) {
		int count=0;
		Connection connection = DB.getConn();
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement=connection.createStatement();
			resultSet=statement.executeQuery(sql);
			while(resultSet.next()) {
				count=resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DB.close(connection, statement, resultSet);
		}
		return count;
	}
	
	public List<Companyinfo> getList(String sql){
		List<Companyinfo> list = new ArrayList<>();
		Connection connection = DB.getConn();
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement=connection.createStatement();
			resultSet=statement.executeQuery(sql);
			while(resultSet.next()) {
				Companyinfo companyinfo = new Companyinfo(resultSet.getString(1),resultSet.getString(2),resultSet.getString(3));
				list.add(companyinfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DB.close(connection, statement, resultSet);
		}
		return list;
	}
	
}

pageBean

package Bean;
public class pageBean {
	private int everyPage; // 每页显示记录数
	private int totalCount; // 总记录数
	private int totalPage; // 总页数
    private int currentPage; //当前页
    private int beginIndex;			//查询起始点
	public pageBean(int everyPage, int totalCount ,int currentPage) {
		super();
		this.everyPage = everyPage;
		this.totalCount = totalCount;
		this.currentPage = currentPage;
		if(totalCount != 0 &&totalCount % everyPage == 0) {
			totalPage = totalCount / everyPage;
		} else {
			totalPage = totalCount / everyPage + 1;
		}
	}
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		if(currentPage<1)currentPage=1;
		this.currentPage = currentPage;
		
	}
	public int getEveryPage() {
		return everyPage;
	}
	public void setEveryPage(int everyPage) {
		this.everyPage = everyPage;
	}
	public int getTotalCount() {
		return totalCount;
	}
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
	}
	
	public int getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}
	public int getBeginIndex() {
		setBeginIndex();
		return beginIndex;
	}
	public void setBeginIndex() {
		this.beginIndex =(currentPage - 1) * everyPage;
	}

}

servlet的page

package servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import Bean.Companyinfo;
import Bean.pageBean;
import dao.CompanyinfoDao;
@WebServlet("/page")
public class page extends HttpServlet {
	private static final long serialVersionUID = 1L;
	public static CompanyinfoDao companyinfoDao =  new CompanyinfoDao();
	String sql;
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		HttpSession session = request.getSession();
		pageBean pBean = (pageBean)session.getAttribute("page");
		if(pBean==null) {
			sql = "select count(*) from companyinfo ";
			int count =companyinfoDao.getCount(sql);
			pBean  = new pageBean(3, count, 1);
		}else {
			String cString = request.getParameter("currentPage");
			if(cString==null)cString="1";
			int currentPage = Integer.parseInt(cString);
			pBean.setCurrentPage(currentPage);
		}
		String sql = "select c_name,c_email, c_serve from companyinfo limit "+pBean.getBeginIndex()+","+pBean.getEveryPage();
		List<Companyinfo> list = companyinfoDao.getList(sql);
		session.setAttribute("page", pBean);
		request.setAttribute("list", list);
		request.getRequestDispatcher("show.jsp").forward(request, response);
	}
	
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

#前台 show.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="Bean.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Document</title>
</head>
<style type="text/css">
table {
	border-collapse: collapse;
	border:2px solid black;
}

th,td {
    padding: .5em .75em;
}

th {
	border:1px solid grey;
}

td {
   	border:1px dotted grey;
}
</style>
 <body>
        <table>
            <caption>彩排安排</caption>
            <thead>
                <!-- 表格头部 -->
                <tr>
                    <th scope="rowgroup">公司名称</th>
                    <th scope="col">公司email</th>
                    <th scope="col">公司服务</th>
                </tr>
            </thead>
            <tbody>
                <!-- 表格主体 -->
               <c:forEach items="${requestScope.list}" var="companyinfo">
               	<tr>
               		<td>${companyinfo.c_name}</td>
               		<td>${companyinfo.c_email}</td>
               		<td>${companyinfo.c_serve}</td>
               </tr>
               </c:forEach>
            </tbody>
        </table>
        <a href="page?currentPage=1">首页</a>&nbsp;&nbsp;
         <c:if test="${sessionScope.page.currentPage !=1}">
        	 <a href="page?currentPage=${sessionScope.page.currentPage-1}">上一页</a>&nbsp;&nbsp;
         </c:if>
         <c:if test="${sessionScope.page.currentPage !=sessionScope.page.totalPage}">
        	 <a href="page?currentPage=${sessionScope.page.currentPage+1}">下一页</a>&nbsp;&nbsp;
         </c:if>       
          <a href="page?currentPage=${sessionScope.page.totalPage}">尾页</a>
    </body>
</html>

运行截图

eclipse常用的快捷键

描述	快捷键
生成set和get方法	Alt+shift+s r
生成toString方法	Alt+shift+s s
生成object方法	Alt+shift+s o
格式化代码	Alt+shift+s f
保存所有代码	ctrl+shift+s
自动补全代码或者提示代码	alt+?或alt+/

注意：Alt+shift+s r 是指先按下組合按下Alt，shift，s 后按下r即可