/**
* 这个分页比较高较，项目中的数据是上千万级的数量级
* 当然了，我的数据库用了索引，where 条件中根据索引来查询是很快的哦，索引怎么用就不在本章的讨论范围之内了
* 核心SQL语句是：SELECT * FROM (SELECT SUB_TAB.*, ROWNUM RN FROM (your_sql) SUB_TAB WHERE ROWNUM <= 20) WHERE RN >= 1
* DataSource.java：获取数据库的链接
* GlobalConst.java：定义全局变量
* Page.java：分页的核心java bean
* PageTest.java：一个测试类
* jdbc.properties：jdbc的配置文件
*/
/*
* @author Sugar.Tan<br>
*/
package common;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
/**
* pageSize, pageCount, hasNextPage, hasPreviousPage<br>
* rule: the value of -1 is last page; 1 is the first page.
*/
public class Page {
private int pageSize; //一页显示的记录数
private int pageCount; //总页数
private boolean hasNextPage; //是否有下一页
private boolean hasPrePage; //是否有上一页
private int curPage = 1; //待显示的页面
private int tolCount; //总记录数量
@SuppressWarnings("unchecked")
private List dataList;
private int startRow = 0;
private int endRow = 10;
private void calPreNext() {
//this.hasNextPage = curPage == pageCount ? false : true;
this.hasNextPage = (pageCount == 0 ||curPage == pageCount) ? false : true;
this.hasPrePage = (curPage > 1) ? true : false;
}
private void calPage() {
pageCount = (tolCount + pageSize - 1) / pageSize;
}
private void calStartEnd () {
startRow = pageSize * (curPage - 1) + 1; //1
endRow = pageSize * curPage; //10
}
private void volidate() {
if (curPage == -1 || curPage > pageCount) curPage = pageCount;
if (curPage == 0 || curPage < -1) curPage = 1;
}
public void cal() {
calPage();
volidate();
calStartEnd();
calPreNext();
}
public int getTolCount() {
return tolCount;
}
public int getStartRow() {
return startRow;
}
public int getEndRow() {
return endRow;
}
public void setEndRow(int endRow) {
this.endRow = endRow;
}
public void setTolCount(int tolCount) {
this.tolCount = tolCount;
this.cal();
}
public boolean isHasPrePage() {
return hasPrePage;
}
@SuppressWarnings("unchecked")
public List getDataList() {
return dataList;
}
@SuppressWarnings("unchecked")
public void setDataList(List dataList) {
this.dataList = dataList;
}
/**
* 构造方法
*/
public Page(int curPage) {
this.pageSize = 10; //初始设置一页的记录数
this.hasNextPage = false; //初始
this.hasPrePage = false; //初始
this.curPage = curPage;
}
public Page(){}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public boolean isHasNextPage() {
return hasNextPage;
}
public void setHasNextPage(boolean hasNextPage) {
this.hasNextPage = hasNextPage;
}
public void setHasPrePage(boolean hasPrePage) {
this.hasPrePage = hasPrePage;
}
public int getCurPage() {
return curPage;
}
public void setCurPage(int curPage) {
this.curPage = curPage;
}
public static Page newInstance(String curPage, String pageSize) {
Page page = null;
if (curPage == null || curPage.equals("")) {
page = new Page(1);
} else {
page = new Page(Integer.parseInt(curPage));
page.setPageSize(Integer.parseInt(pageSize));
}
return page;
}
/**
* set the totalCount of page, then calculate
* @param page
* @param hphm
*/
public static void setTolCount(Connection conn, Page page, String sql) {
Statement st = null;
ResultSet rs = null;
try {
st = conn.createStatement();
rs = st.executeQuery(new StringBuffer()
.append(GlobalConst.STR_COUNT)
.append(sql).append(")").toString());
if (rs.next()) {
page.setTolCount(rs.getInt(1));
page.cal();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) try {rs.close();} catch (SQLException e) {}
if (st != null) try {st.close();} catch (SQLException e) {}
}
}
public static int getTolCount(Connection conn, String sql) {
Statement st = null;
ResultSet rs = null;
try {
st = conn.createStatement();
rs = st.executeQuery(new StringBuffer()
.append(GlobalConst.STR_COUNT)
.append(sql).append(")").toString());
if (rs.next()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
return -1;
} finally {
if (rs != null) try {rs.close();} catch (SQLException e) {}
if (st != null) try {st.close();} catch (SQLException e) {}
}
return 0;
}
/*
public static void main(String args[]){
Page page = new Page(0);
page.setTolCount(101);
page.cal();
System.out.println("有" + page.getPageCount() + "页");
System.out.println("StartRow(): " + page.getStartRow());
System.out.println("EndRow(): " + page.getEndRow());
System.out.println("有下一页: " + page.isHasNextPage());
System.out.println("有上一页: " + page.isHasPrePage());
}
*/
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//GlobalConst.java
package common;
/**
* 定义一些通用常量
* @author Sugar.Tan
*
*/
public interface GlobalConst {
public static final String STR_COUNT = "SELECT COUNT(*) FROM (";
//for page
public static final String STR_PAGE_START = "SELECT * FROM (SELECT SUB_TAB.*, ROWNUM RN FROM (";
/**
* param1: EndRow number, param2: startRow number.
*/
public static final String STR_PAGE_END = ") SUB_TAB WHERE ROWNUM <= ?) WHERE RN >= ?";
}
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//DataSource.java
package common;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import common.DataSource;
public class DataSource
{
public Connection getconnection() {
Connection conn = null;
try {
InputStream in = DataSource.class.getResourceAsStream("/jdbc.properties");
Properties pro = new Properties();
pro.load(in);
String classname = pro.getProperty("classname");
String url = pro.getProperty("url");
String userid = pro.getProperty("userid");
String password = pro.getProperty("password");
Class.forName(classname);
conn = DriverManager.getConnection(url,userid,password);
} catch(Exception e) {
e.printStackTrace();
}
return conn;
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
#jdbc.properties, 放在src类路径下
classname=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@192.168.1.125:1521:MYORCL
userid=USERONE
password=123456
////////////////////////////////////////////// 测试类 /////////////////////////////////////////////////////////////////////////////////////
//PageTest .java
package common;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import config.DataSource;
public class PageTest {
public static void main(String[] args) {
StringBuffer sbSql = new StringBuffer();
//在这里拼SQL
sbSql.append("SELECT * FROM ITS_TABLE1 W");
sbSql.append(" ORDER BY W.C1 DESC ");
//获取数据库链接
Connection conn = new DataSource().getconnection();
String curPage = "1";
String pageSize = "10";
Page page = Page.newInstance(curPage, pageSize);
common.Page.setTolCount(conn, page, sbSql.toString());
// do the lst result
if (page.getTolCount() > 0) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(new StringBuffer().append(GlobalConst.STR_PAGE_START)
.append(sbSql)
.append(GlobalConst.STR_PAGE_END).toString());
ps.setInt(1, page.getEndRow());
ps.setInt(2, page.getStartRow());
rs = ps.executeQuery();
while (rs.next()) {
System.out.println("读一条记录");
// ItsObject itsWf = new ItsWf();
// itsWf.setHphm(rs.getString(1));
// //.........
// itsWf.setClbj(rs.getString(12));
// lstItsWf.add(itsWf);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) try {rs.close();} catch (SQLException e) {}
if (ps != null) try {ps.close();} catch (SQLException e) {}
if (conn != null) try {conn.close();} catch(SQLException e) {}
}
}
}
}