JSP分页显示a

运用JDBC技术，实现jsp页面链接数据库，显示分页查询

准备数据库（这里使用Sql Server） 创建表 ，插入数据

搭建框架
下载 sql server 驱动包

编写model层代码

public class T_book {
   int Book_number;
   String Book_name;
   String Author;
   String Publisher; 
   double Price;
public int getBook_number() {
	return Book_number;
}
public void setBook_number(int book_number) {
	Book_number = book_number;
}
public String getBook_name() {
	return Book_name;
}
public void setBook_name(String book_name) {
	Book_name = book_name;
}
public String getAuthor() {
	return Author;
}
public void setAuthor(String author) {
	Author = author;
}
public String getPublisher() {
	return Publisher;
}
public void setPublisher(String publisher) {
	Publisher = publisher;
}
public double getPrice() {
	return Price;
}
public void setPrice(double price) {
	Price = price;
}
public T_book(int book_number, String book_name, String author,
		String publisher, double price) {
	super();
	Book_number = book_number;
	Book_name = book_name;
	Author = author;
	Publisher = publisher;
	Price = price;
}
public T_book() {
	super();
}
    
      
   
}

编写dao层代码

因为有大量公共的代码所以抽取出去做一个父类

public class BaseDao {
	String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
	String url="jdbc:sqlserver://DESKTOP-6PS0PMV\\SQL2012:1433;databaseName=BookStoreDB";
	String user="sa";
	String password="2206919994jp";
    
	public void executeNoQuery(String sql){
		try {
			Class.forName(driver);
			try {
				Connection conn = DriverManager.getConnection(url, user, password);
				Statement stmt = conn.createStatement();
				stmt.execute(sql);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	/*
	 * select
	 */
	public ResultSet executeQuery(String sql){
		ResultSet rs = null;
		try {
			Class.forName(driver);
			try {
				Connection conn = DriverManager.getConnection(url, user, password);
				Statement stmt = conn.createStatement();
				
				rs = stmt.executeQuery(sql);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rs;
	}
}

public class BookDao extends BaseDao{
    
	public List<T_book> select(int pagesize,int currentPage){
		List<T_book> bookList = new ArrayList<T_book>();
		String sql = "select top " + pagesize + " * from T_book where " +
				"Book_number not in(select top ("+ pagesize + " *("+ currentPage +"-1) )Book_number from T_book order by Book_number ) order by Book_number";
		
		ResultSet rs = super.executeQuery(sql);
		try {
			while(rs.next()){
				int book_number = rs.getInt("Book_number");
				String book_name = rs.getString("Book_name");
				String author = rs.getString("Author");
				String publisher = rs.getString("Publisher");
				double price = rs.getDouble("Price");
				
				T_book book = new T_book(book_number,book_name, author, publisher, price);
				bookList.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return bookList;
	}
	
	public int getTotalPage(int pagesize){
		int totalPage =1;
		String sql = "select count(*) as total from T_book";
		ResultSet rs =super.executeQuery(sql);
		try {
			if(rs.next()){
				//总条数
				int totalRows = rs.getInt("total");
				if(totalRows%pagesize ==0){
					totalPage = totalRows /pagesize;
				}else{
					totalPage = totalRows/pagesize+1;
					System.out.println(totalPage);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return totalPage;
		
		
	}
	
}

测试一下
public class Test {
public static void main(String[] args) {
BookDao bookDao = new BookDao();
int pagesize = 4;
int currentPage=2;
List<T_book> booklist = bookDao.select(pagesize,currentPage);
for(T_book book:booklist){
System.out.println(book.getBook_number()+","+book.getBook_name()+","+book.getAuthor()+","+book.getPublisher());
}
}
}

编写视图层

booklist.jsp

<%@page import="com.dao.BookDao"%>
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>My JSP 'booklist.jsp' starting page</title>
    
  </head>
  
  <body>
  <h2>图书信息列表</h2>
  <br>
  <br>
  <br>
  <%
  //实现分页导航
  int currentPage=1;
  if( request.getParameter("page")!=null)
    	{
    		currentPage = Integer.parseInt( request.getParameter("page") );
    	} 
  int totalPage = 1;	//总页数
  BookDao bookDao = new BookDao();
   totalPage = bookDao.getTotalPage(4);
  int nextPage =1; //下一页
  if( currentPage < totalPage)
    	{
    		nextPage = currentPage + 1;
    	} 
  
  int priorPage = 1;//上一页
  if( currentPage > 1)
    	{
    		priorPage = currentPage -1;
    	} 
   %>
  <a href="BookServlet?page=1">首页</a>
  <a href="BookServlet?page=<%=priorPage%>">上一页</a>
  <a href="BookServlet?page=<%=nextPage %>">下一页</a>
  <a href="BookServlet?page=<%=totalPage %>">末页</a>
  <br>
  <br>
  <table border="1" width="50%">
  <tr>
    		<td>书号</td>
    		<td>书名</td>
    		<td>作者</td>    
    		<td>出版社</td>
    		<td>定价</td>
    		<td>操作</td>		
    	</tr>
    	<c:forEach items="${booklist }" var="book">
    	<tr>
    	<td>${book.book_number }</td>
    	<td>${book.book_name }</td>
    	<td>${book.author }</td>
    	<td>${book.publisher }</td>
    	<td>${book.price }</td>
    	<td><a href="#">删除</a> &nbsp <a href="#">编辑</a></td>
    	
    	</tr>
    	</c:forEach>
  </table>
  </body>
</html>

编写controller层

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dao.BookDao;
import com.model.T_book;

public class BookServlet extends HttpServlet {

	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		doPost(request, response);
	}

	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

	
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		
		BookDao bookDao = new BookDao();
		int pagesize = 4;
		int totalPage = 1;
		totalPage =bookDao.getTotalPage(pagesize);
		
		//从页面传递
				int currentPage = 1;
		if(request.getParameter("page")!=null){
			currentPage=Integer.parseInt(request.getParameter("page"));
		}
		List<T_book> booklist = bookDao.select(pagesize, currentPage);
		request.setAttribute("booklist", booklist);
		request.getRequestDispatcher("booklist.jsp").forward(request, response);
		
	}

}

项目演示