JSP 分页查询关键代码
- 实体类代码:
package com.book.entity;
public class Book {
private int book_id;
private String book_name;
private String book_num;
private String book_author;
private double book_price;
private String book_synopsis;
private String book_publishTime;
—-这里省略get和set方法
}
2.分页查询代码:
package com.book.pagin;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.book.commons.GetConnection;
import com.book.entity.Book;
public class BookPaginQuery extends GetConnection {
private final int PAGEROW = 8;//每页显示的行数
private int countRow;//总行数
private int countPage;//总页数
private int currentlyPage;//当前第几页
/**
* 得到总页数
* @return
*/
public int getCountPage() {
return countPage;
}
/**
* 设置总页数
* @param countPage
*/
public void setCountPage() {
//通过总行数设置总页数
if (this.countRow % this.PAGEROW == 0) {//如果总行数除以每页显示的行数余数为零时,总页数等于它们的商
this.countPage = this.countRow / this.PAGEROW;
} else {//否则,总页数等于它们的商加1
this.countPage = this.countRow / this.PAGEROW + 1;
}
}
/**
* 得到总行数
* @return
*/
public int getCountRow() {
return countRow;
}
/**
* 设置总行数
* @param countRow
*/
public void setCountRow() {
//通过聚合函数查询 TBL_BOOK 表中一共有多少条数据,并把值存储到 countRow 中
String sql = "SELECTCOUNT(*) FROM TBL_BOOK";
try {
PreparedStatement ps = super.getConn().prepareStatement(sql);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
this.countRow = rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generatedcatch block
e.printStackTrace();
}
}
/**
* 得到当前页数
* @return
*/
public int getCurrentlyPage() {
return currentlyPage;
}
/**
* 设置当前页数
* @param currentlyPage
*/
public void setCurrentlyPage(int currentlyPage) {
this.currentlyPage = currentlyPage;
}
/**
* 分页查询
* @param page 当前页数
* @return
*/
public List<Book> myBookPaginQuery(int page){
List<Book> bookList = new ArrayList<Book>();
int num = (page-1) * this.PAGEROW; //要被排除的行数
String sql = "SELECTtop("+this.PAGEROW+") * FROMTBL_BOOK WHERE book_id NOT IN (SELECT TOP("+num+") book_id FROM TBL_BOOK)";
try {
PreparedStatement ps = super.getConn().prepareStatement(sql);//预编译 SQL 指令并把预编译好的 SQL 存储在 PreparedStatement 对象中
ResultSet rs = ps.executeQuery(); //执行预编译好的 SQL 指令,并把获得的查询结果集存储在 ResultSet 对象中
while (rs.next()) {//通过 while 循环迭代出结果集中的所有数据,并把它们存储在 List<Book> 集合中
Book book = new Book();
book.setBook_id(rs.getInt("book_id"));
book.setBook_name(rs.getString("book_name"));
book.setBook_num(rs.getString("book_num"));
book.setBook_author(rs.getString("book_author"));
book.setBook_price(rs.getDouble("book_price"));
book.setBook_synopsis(rs.getString("book_synopsis"));
book.setBook_publishTime(rs.getString("book_publishTime"));
bookList.add(book);
}
} catch (SQLException e) {
// TODO Auto-generatedcatch block
e.printStackTrace();
}
return bookList;
}
}
- JSP 页面代码:
<%@ page language=”java” import=”java.util.*” pageEncoding=”UTF-8”%>