很少写文章开始之前,先大致说下文章的内容。。
本文采用了Dbutils 和C3P0 连接池实现数据库表的简单分页。
Dbutils 是Apache 对JDBC的封装,支持对数据库表的CRUD操作,C3P0 是一种通用的连接池,管理数据库连接资源。
具体的请谷歌或百度。。
OK,进入正题。。
1、开发依赖的jar包:c3p0-0.9.1.2、commons-dbutils-1.6.jar、ojdbc6.jar
2、实现数据库分页,还要准备一张数据库表,这里找个数据量只有7条数据的小表
3、打开eclipse创建个java工程,如下。。导入c3p0-0.9.1.2、commons-dbutils-1.6.jar、ojdbc6.jar
4、代码:
OfferType.java 实体bean 定义 和数据库表字段保持一致,不区分大小写。
package com.test;
/**
* <Description> <br>
*
* @author <br>
* @version 1.0<br>
* @taskId <br>
* @CreateDate 2016-10-12 <br>
* @since<br>
* @see com.test <br>
*/
public class OfferType {
private int offer_Type;
private String offer_Type_Name;
private String comments;
public String getComments() {
return comments;
}
public void setComments(String comments) {
this.comments = comments;
}
public int getOffer_Type() {
return offer_Type;
}
public void setOffer_Type(int offer_Type) {
this.offer_Type = offer_Type;
}
public String getOffer_Type_Name() {
return offer_Type_Name;
}
public void setOffer_Type_Name(String offer_Type_Name) {
this.offer_Type_Name = offer_Type_Name;
}
@Override
public String toString() {
return "OfferType [offer_Type=" + offer_Type + ", offer_Type_Name=" + offer_Type_Name + ", comments="
+ comments + "]";
}
}
Page.java
package com.test;
public class Page {
// 总条数
private int totalSize;
// 每页大小
private int pageSize;
// 总页数
private int totalPage;
// 第几页
private int pageNum = 1;
// 每页开始条数
private int pageBegin;
// 每页结束条数
private int pageEnd;
public Page(int pageSize) {
this.pageSize = pageSize;
}
public Page() {
}
public void init() {
// pageSize 默认为5
if (pageSize <= 0) {
pageSize = 5;
}
totalPage = totalSize / pageSize;
if (0 != totalSize % pageSize) {
totalPage += 1;
}
if (pageNum > totalPage) {
pageNum = totalPage;
}
if (pageNum < 1) {
pageNum = 1;
}
pageBegin = (pageNum - 1) * pageSize + 1;
pageEnd = (pageNum) * pageSize;
}
public int getTotalSize() {
return totalSize;
}
public void setTotalSize(int totalSize) {
this.totalSize = totalSize;
}
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 getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageBegin() {
return pageBegin;
}
public void setPageBegin(int pageBegin) {
this.pageBegin = pageBegin;
}
public int getPageEnd() {
return pageEnd;
}
public void setPageEnd(int pageEnd) {
this.pageEnd = pageEnd;
}
@Override
public String toString() {
return "Page [totalSize=" + totalSize + ", pageSize=" + pageSize + ", totalPage=" + totalPage + ", pageNum="
+ pageNum + ", pageBegin=" + pageBegin + ", pageEnd=" + pageEnd + "]";
}
}
QueryRemote.java 定义分页查询方法
package com.test;
import java.beans.PropertyVetoException;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class QueryRemote {
// 获得c3p0连接池对象
private static ComboPooledDataSource ds;
@SuppressWarnings({
"unchecked", "rawtypes"
})
public static List<Object> query(String sql, Page page, Object[] params, Class<?> clss) {
ds = getResource();
QueryRunner runner = new QueryRunner(ds);
List<Object> list = null;
try {
sql = sql.toUpperCase();
if (null == page) {
list = runner.query(sql, new BeanListHandler(clss.newInstance().getClass()), params);
}
else {
if (null == params) {
params = new Object[]{};
}
String pageSql = "SELECT * FROM (SELECT A.*,ROWNUM RN " + "FROM (" + sql + ") A WHERE ROWNUM <=?"
+ ") " + "WHERE RN >=?";
// 查询总数
String totalSql = "SELECT COUNT(*) FROM (" + sql + ")";
BigDecimal count = runner.query(totalSql,new ScalarHandler<BigDecimal>(),params);
page.setTotalSize(count.intValue());
page.init();
Object[] pageParams = new Object[params.length + 2];
System.arraycopy(params, 0, pageParams, 0, params.length);
pageParams[params.length] = page.getPageEnd();
pageParams[params.length + 1] = page.getPageBegin();
list = runner.query(pageSql, new BeanListHandler(clss.newInstance().getClass()), pageParams);
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
private static ComboPooledDataSource getResource() {
if (null == ds) {
try {
ds = new ComboPooledDataSource();
ds.setUser("xxxx");
ds.setPassword("xxxx");
ds.setJdbcUrl("jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:xx");
ds.setDriverClass("oracle.jdbc.driver.OracleDriver");
}
catch (PropertyVetoException e) {
// 获取数据库连接是失败
e.printStackTrace();
}
}
return ds;
}
}
Test.java 测试类
</pre><pre name="code" class="java">package com.test;
import java.util.ArrayList;
import java.util.List;
public class Test {
public static void main(String[] args) {
Page page = new Page();
page.setPageNum(1);
page.setPageSize(4);
Object[] params = new Object[]{1} ;
List<OfferType> offerTypes = new ArrayList<OfferType>();
String sql = "select * from offer_type where offer_type > ?";
List<Object> list = QueryRemote.query(sql, page, params, OfferType.class);
OfferType offerType = null;
for (int i = 0; i < list.size(); i++) {
offerType = new OfferType();
offerType = (OfferType) list.get(i);
System.out.println(offerType.toString());
offerTypes.add(offerType);
}
System.out.println();
System.out.println("共"+page.getTotalSize()+"条记录,每页大小"+page.getPageSize()+",当前第"+page.getPageNum()+"页,查询到 "+list.size()+" 条记录。。。");
}
}
运行结果!!! 这里只有6条记录 是因为 sql 有查询条件offer_type > ?,Object[] params = new Object[]{1} ;传了参1,offer_type > 1的只有六条。。
结束。。。。