本文结合一个小demo主要介绍如何利用原生的jdbc实现分页技术。在我的上一篇博客中，我已经介绍了如何利用struts2搭建一个简单的图书检索系统，并实现检索功能，本文主要介绍如何对查询到的结果进行分页显示。
利用jdbc进行分页显示的核心要点：
①.sql语句要进行特殊处理使其能查询出按一定顺序排列的第n行到第m行，形如：
select *from (select t.*,rownum rn from (select *from 表 where 条件 order by 字段)t)a where a.rn>=搜索开始行 and a.rn<= 搜索结束行
②.建好分页对应的实体类，实现相关方法。
具体实现代码如下：
1.新建个page类,实现分页的关键实体类
package com.books.database.page;
public class Page {
public final static int PAGESIZE = 10;
public final static int PAGENUM = 1;
private int total; //记录总数
private int pageSize=PAGESIZE; //每页的最大记录数
private int totalPage; //总页数
private int startItems; //开始的记录数
private int endItems; //结束的记录数
private int pageNum=PAGENUM; //当前页码
public int getEndItems() {
return endItems;
}
public void setEndItems(int endItems) {
this.endItems = endItems;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getStartItems() {
return startItems;
}
public void setStartItems(int startItems) {
this.startItems = startItems;
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
/*
* 根据pageSize、pageNum、total计算开始行、结束行和总页数
*/
public void calculate(Page page){
int firstItem=page.getPageSize()*(page.getPageNum()-1)+1; //当前查询的开始行
int lastItem=page.getPageSize()*page.getPageNum(); //当前查询的结束行
page.setStartItems(firstItem);
page.setEndItems(lastItem);
//获取总页数
if(page.getTotal()/page.getPageSize()<=0){
page.setTotalPage(1);
}else{
if(page.getTotal()%page.getPageSize()==0){
page.setTotalPage(page.getTotal()/page.getPageSize());
}else{
page.setTotalPage((page.getTotal()/page.getPageSize())+1);
}
}
}
}2.Book类继承该Page类
package com.books.vo;
import com.books.database.page.Page;
/*
* 实体类Book
*/
public class Book extends Page{
private String bookName; //书名
private String author; //作者
private String bookId; //ISBN
private String seriesBook; //丛书
private String publishTime; //出版时间
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public String getBookId() {
return bookId;
}
public void setBookId(String bookId) {
this.bookId = bookId;
}
public String getSeriesBook() {
return seriesBook;
}
public void setSeriesBook(String seriesBook) {
this.seriesBook = seriesBook;
}
public String getPublishTime() {
return publishTime;
}
public void setPublishTime(String publishTime) {
this.publishTime = publishTime;
}
}3.action类的代码：
package com.books.search;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import com.books.common.BaseAction;
import com.books.database.page.Page;
import com.books.vo.Book;
public class BooksSearchAction extends BaseAction{
private Book book =new Book();
private Page page=new Page();
private BookSearchFacade bookSearchFacade=new BookSearchFacade();
public String booksSearch(){
try {
paging();
request.setAttribute("page", book);
} catch (Exception e) {
System.out.println(e.toString());
}
return "searchPage";
}
/*
* 查询
*/
@SuppressWarnings("null")
public void search(){
try {
JSONArray json1 =null;
JSONObject json2 =null;
JSONObject allJson =new JSONObject();
initBook();
List<Map<String, Object>> result= bookSearchFacade.search(book);
json1 = JSONArray.fromObject(result);
json2 = JSONObject.fromObject(book);
allJson.put("data",json1);
allJson.put("book",json2);
String str=allJson.toString();
this.ajaxResponse(str);
} catch (Exception e) {
System.out.println(e.toString());
}
}
/*
* 处理出版时间
*/
public String getPublishTime(String time){
String pbltime=null;
if(time.equals("1")){//三个月内
pbltime= getBeforeDate(3);
}else if(time.equals("2")){//一年内
pbltime= getBeforeDate(12);
}else if(time.equals("3")){//三年内
pbltime= getBeforeDate(36);
}
return pbltime;
}
/*
* 获得*个月前的日期
*/
public String getBeforeDate(int month){
Date now=new Date();
Date dBefore = new Date();
Calendar calendar = Calendar.getInstance(); //得到日历
calendar.setTime(now);//把当前时间赋给日历
calendar.add(Calendar.MONTH, -month); //设置为前*月
dBefore = calendar.getTime(); //得到前*月的时间
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); //设置时间格式
String defaultStartDate = sdf.format(dBefore); //格式化前*个月前的日期
return defaultStartDate;
}
/*
* 前台过来时初始化book实体
*/
public void initBook() throws SQLException, ClassNotFoundException{
String bookName=request.getParameter("bookName");
String author=request.getParameter("author");
String bookId=request.getParameter("bookId");
String seriesBook=request.getParameter("seriesBook");
String publish=request.getParameter("publishTime");
String publishTime=getPublishTime(publish);
int pageSize=Integer.parseInt(request.getParameter("pageSize")); //每页行数
int pageNum=Integer.parseInt(request.getParameter("pageNum")); //页号
book.setBookName(bookName);
book.setAuthor(author);
book.setBookId(bookId);
book.setSeriesBook(seriesBook);
book.setPublishTime(publishTime);
//分页
book.setPageNum(pageNum); //当前页
book.setPageSize(pageSize); //每页显示的行数
paging();
}
/*
* 分页
*/
public void paging()throws SQLException, ClassNotFoundException{
book.setTotal(bookSearchFacade.getTotalData(book)); //总行数
book.calculate(book); //计算总页数、开始行数和结束行数
}
}
4.server类的代码如下，由于没有使用框架，因此在sql语句写起来要麻烦一些：
package com.books.search;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import com.books.database.dao.CommonDao;
import com.books.vo.Book;
public class BookSearchServer {
private CommonDao dao=new CommonDao();
/*
* 图书检索
*/
public List<Map<String, Object>> search(Book book) throws SQLException, ClassNotFoundException{
String sql ="select *from (select t.*,rownum rn from ( select * from books where 1=1";
if(book.getBookId()!=null&&book.getBookId().length()>0){
sql+=" and bookid = '"+book.getBookId()+"'";
}if(book.getBookName()!=null&&book.getBookName().length()>0){
sql+=" and bookname like '%"+book.getBookName()+"%'";
}if(book.getAuthor()!=null&&book.getAuthor().length()>0){
sql+=" and author like '%"+book.getAuthor()+"%'";
}if(book.getSeriesBook()!=null&&book.getSeriesBook().length()>0){
sql+=" and seriesbook like '%"+book.getSeriesBook()+"%'";
}if(book.getPublishTime()!=null&&book.getPublishTime().length()>0){
sql+=" and publishtime > to_date('"+book.getPublishTime()+"',"+"'yyyy-mm-dd')";
}
sql+=" order by publishtime,bookid) t )a";
sql+=" where a.rn>='"+book.getStartItems()+"' and a.rn<='"+book.getEndItems()+"'";
List<Map<String, Object>> result =dao.excuteSQL(sql);
return result;
}
/*
* 获取总行数
*/
public int getTotalData(Book book) throws SQLException, ClassNotFoundException{
String sql ="select * from books where 1=1";
if(book.getBookId()!=null&&book.getBookId().length()>0){
sql+=" and bookid = '"+book.getBookId()+"'";
}if(book.getBookName()!=null&&book.getBookName().length()>0){
sql+=" and bookname like '%"+book.getBookName()+"%'";
}if(book.getAuthor()!=null&&book.getAuthor().length()>0){
sql+=" and author like '%"+book.getAuthor()+"%'";
}if(book.getSeriesBook()!=null&&book.getSeriesBook().length()>0){
sql+=" and seriesbook like '%"+book.getSeriesBook()+"%'";
}if(book.getPublishTime()!=null&&book.getPublishTime().length()>0){
sql+=" and publishtime > to_date('"+book.getPublishTime()+"',"+"'yyyy-mm-dd')";
}
sql+=" order by publishtime,bookid ";
int toatl =dao.getTotalData(sql);
return toatl;
}
}
5.dao层新增获取查询结果总数的方法：
package com.books.database.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class CommonDao {
/*
* 连接数据库
* 注意：这个需要自己配置！！！
*/
public Connection connection() throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver"); // 加载Oracle驱动程序
String url = "jdbc:oracle:thin:@10.157.244.72:1521:hrdbtest"; // 连接使用的url
String user = "recruit"; // 数据库用户名
String password = "1qaz@WSX"; // 密码
Connection con = DriverManager.getConnection(url, user, password);// 获取连接
return con;
}
/*
* 原生jdbc执行查询
*/
public List<Map<String, Object>> excuteSQL(String sql) throws SQLException, ClassNotFoundException {
List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
Connection con =connection();
Statement sta = con.createStatement();
ResultSet rs = sta.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据
int columnCount = md.getColumnCount(); //获得列数
while(rs.next()){
Map<String,Object> rowData=new HashMap<String,Object>();
for(int i=1;i<=columnCount;i++){
//String key=md.getColumnName(i);//获得表头
String orderKey =String.valueOf(i);//为了使hashmap中的数据存储顺序和数据库一致
Object value=null;
if(rs.getObject(i)!=null){
value=rs.getObject(i).toString();//为了解决从数据库中取出的Date格式的数据不能转化为json格式的问题
}
rowData.put(orderKey, value);
}
list.add(rowData);
}
con.close();
return list;
}
/*
* 计算数据总量
*/
public int getTotalData(String sql) throws ClassNotFoundException, SQLException{
Connection con =connection();
Statement sta = con.createStatement();
ResultSet rs = sta.executeQuery(sql);
int total=0;
while(rs.next()){
total++;
}
return total;
}
}
6.jsp代码
<%@ page language="java" pageEncoding="UTF-8"%>
<%
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>图书检索系统</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<script type="text/javascript" src="${path}/scripts/jquery-1.12.0.js"></script>
<script type="text/javascript" language="javascript">
function init(){
search();
}
//按下回车键执行查询功能
document.onkeyup = function(event) {
if(event.keyCode === 13) {
search();
}
}
//换页
function changePage(key) {
if (key == 1) {
var num = document.getElementById("tablePageNum").value;
if (num == 1)
alert("已为首页！");
else {
var maxNum = document.getElementById("totalPage").value;
var pageNum = document.getElementById("tablePageNum").value - 1;
if (pageNum > maxNum)
pageNum = maxNum;
document.getElementById("tablePageNum").value = pageNum;
search();
}
} else if (key == 2) {
var maxNum = parseInt(document.getElementById("totalPage").value);
var num = parseInt(document.getElementById("tablePageNum").value);
if (num < maxNum) {
var pageNum = parseInt(document.getElementById("tablePageNum").value) + 1;
document.getElementById("tablePageNum").value = pageNum;
search();
} else {
alert("超过最大页！");
}
}
}
//用jQuery中的ajax查询后台符合条件的数据
function search(){
var bookName=document.getElementById("bookName").value;
var author=document.getElementById("author").value;
var bookId=document.getElementById("bookId").value;
var seriesBook=document.getElementById("seriesBook").value;
var publishTime=document.getElementById("publishTime").value;
var pageNum=document.getElementById("tablePageNum").value;
var pageSize=document.getElementById("tablePageSize").value;
$.ajax({
url:"books_search.action",
type:"post",
data:{bookName:bookName,author:author,
bookId:bookId,seriesBook:seriesBook,publishTime:publishTime,
pageNum:pageNum,pageSize:pageSize},
success:function(jvData){
var allData = eval('(' + jvData + ')');
var data=allData["data"];
var book=allData["book"];
var txt="";
var intxt="";
var innerContent="";
for(var i=0,len=data.length;i<len;i++){
var x=0;
for(var key in data[i]){
var describe=key;
var content=data[i][key];
++x;
txt+="<td align='center' valign='middle'>"+content+"</td>";
if(x==5){//每行的数据个数
intxt="<tr>"+txt+"</tr>";
innerContent+=intxt;
txt="";
x=0;
break;
}
}
if(txt!=""){
innerContent+="<tr>"+txt+"</tr>";
txt="";
}
}
document.getElementById("resultTable").innerHTML=innerContent;
document.getElementById("totalPage").value=book["totalPage"];
document.getElementById("totalNum").value=book["total"];
}
});
}
</script>
</head>
<body οnlοad="init();">
<div style="position:absolute;width:100%;height:100%;">
<div style="top:0px;width:100%;height:100px;">
<h1 style="position:absolute;left:35%;" >图书检索系统</h1>
</div >
<div>
<table style="margin-left:80px;">
<tr ><td style="padding-left:120px;">书名：</td><td><input name="bookname" id="bookName" type="text"></td>
<td style="padding-left:120px;">作者：</td><td ><input name="author" id="author" type="text" /></td>
</tr>
<tr><td style="padding-left:120px;">ISBN：</td><td ><input name="bookId" id="bookId" type="text" /></td>
<td style="padding-left:120px;">丛书：</td><td ><input name="seriesBook" id="seriesBook" type="text" /></td>
</tr>
<tr><td style="padding-left:120px;">出版时间：</td><td><select style="width:173px;" id="publishTime" name="book.publishTime">
<option value="">-全部- </option>
<option value="1">三个月内 </option>
<option value="2">一年内 </option>
<option value="3">三年内</option>
</select></td></tr>
</table>
<br>
<br>
<input style="margin-left:750px;" type="button" id="search" οnclick="search();" value="检 索" />
</div>
<br>
<br>
<!-- 分页-->
<div>
<table style="margin-left:320px;" ><tr>
<td><input style="width:58px;" type="button" value="上一页" οnclick="changePage(1)"/></td>
<td>共<input type="button" style="background-color:white;width:36px;" disabled="disabled" id="totalNum" value="${page.total}"/>条</td>
<td >当前页码：<input style="width:30px;" id="tablePageNum" value="${page.pageNum}"/>/<input type="button" style="background-color:white;width:30px;" disabled="disabled" id="totalPage" value="${page.totalPage}"/></td>
<td>每页行数：<input style="width:30px" id="tablePageSize" value="${page.pageSize}"/></td>
<td><input style="width:58px;" type="button" value="下一页" οnclick="changePage(2)"/></td>
</tr>
</table>
</div>
<!-- 分页-->
<div>
<table style="margin-left:170px;" border="1" cellspacing="0">
<tr><th width="100px;">ISBN</th><th width="200px;">书名</th><th width="100px;">作者</th><th width="150px;">丛书</th><th width="150px;">出版时间</th></tr>
<tbody id="resultTable">
</tbody>
</table>
</div>
</div>
</body>
</html>7.运行结果：
1622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
