|
前几天Q上有几个朋友都在问我说，分页在页面中直接实现比较简单，但是如果写成BEAN就不知道怎么写了，昨天晚上没事的时候帮一朋友写了一个简单的ORACLE数据库的分页BEAN,在这里也给大家发一下，希望可以给刚刚接触的朋友一点思路。写的不好，老鸟不要扔鸡蛋！
注意：这个BEAN所用到的SQL查询语句请不要使用WHERE子句，只用一个最简单的查询。比如：select * from mytable
如果需要使用条件，请自行修改BEAN中的needPage()方法中的拆分组合SQL语句的过程。
1. 配置文件：hl-config.properties 将此文件保存至C盘根目录下
内容如下：
#Oracle
db.drivers=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@192.168.1.8:1521:INFODB
db.user=test
db.password=test
2.分页BEAN: MyOraclePageBean.java
代码如下：
package com.hltoolsinfo.page;
import java.io.FileNotFoundException;
import java.io.IOException;
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.HashMap;
import java.util.Vector;
public class MyOraclePageBean {
public static String propFilePath = "properties/hl-config.properties";
private static String driver = "";
private static String url = "";
private static String username = "";
private static String userpass = "";
private static java.util.Properties prop = null;
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet rs = null;
private static ResultSetMetaData rmd = null;
private int MaxPageSize;//总页数
private int MaxRes;//总记录数
private int PageSize;//每页记录数
private int Page;//当前页
private int RowNum;
private Vector ver = new Vector();
private String sql = "";
public MyOraclePageBean(int pagesize,int page,String sql)
{
prop = MyOraclePageBean.getPropertiesFile(MyOraclePageBean.propFilePath);
driver = prop.getProperty("db.drivers");
url = prop.getProperty("db.url");
username = prop.getProperty("db.user");
userpass = prop.getProperty("db.password");
setPageSize(pagesize);//设置每页的记录数
setPage(page);//设置当前页
this.sql = sql;
int iSize = getColumCount(this.sql);
setMaxRes(iSize);
}
/**
* 分页查询方法
* @return Vector 分页数据
*/
public Vector needPage()
{
sql = sql.toUpperCase();
int i = sql.indexOf("FROM");
int j = sql.length();
sql = sql.substring(i+5,j);
PageSize = getPageSize();
Page = getPage();
System.out.println(Page+" PageSize:"+PageSize);
String pageSql = "SELECT * FROM (SELECT ROWNUM R,t1.* From "+sql+" t1 WHERE ROWNUM < "+((PageSize*Page-PageSize+1)+PageSize)+
") t2 Where t2.R >= "+(PageSize*Page-PageSize+1);
ver = selectVector(pageSql);
return ver;
}
public int getHLID()
{
Page = getPage();
PageSize = getPageSize();
int hlid = Page*PageSize-PageSize;
if(hlid == 0) hlid = 1;
return hlid;
}
/**
* 判断是否还有下一页
* @return flag
*/
public boolean nextPage()
{
boolean flag = false;
if(Page return flag;
}
/**
* 判断是否还有上一页
* @return flag
*/
public boolean precPage()
{
boolean flag = false;
if(Page>1) flag = true;
return flag;
}
public int getRowNum() {
Page = getPage();
PageSize = getPageSize();
RowNum = Page + PageSize;
return RowNum;
}
public int getMaxPageSize() {
MaxRes = getMaxRes();
PageSize = getPageSize();
if(MaxRes%PageSize == 0) MaxPageSize = MaxRes / PageSize;
else MaxPageSize = MaxRes / PageSize + 1;
return MaxPageSize;
}
public int getPage() {
MaxPageSize = getMaxPageSize();
if(Page > MaxPageSize) Page = MaxPageSize;
if(Page < 1 ) Page = 1;
return Page;
}
public int getPageSize() {
if(PageSize <= 0) PageSize = 5;
return PageSize;
}
public void setPage(int page) {
Page = page;
}
public void setPageSize(int pageSize) {
PageSize = pageSize;
}
public int getMaxRes() {
return MaxRes;
}
public void setMaxRes(int maxRes) {
MaxRes = maxRes;
}
/**
* 分页显示样式 [首页 上一页 下一页 尾页]
* @param url
* @return 分页控制的HTML代码
*/
public String list(String url)
{
if(url.indexOf("?")>=0)
{
url = url+"&";
}
else
{
url = url+"?";
}
StringBuffer sHTML = new StringBuffer("");
if(!precPage() && !nextPage())
{
return sHTML+"";
}
sHTML.append("");
return sHTML+"";
}
/**
* 分页查询显示
* @param sql 查询语句
* @return Vector 将结果集封装到Vector对象中返回。键名对应表的列名，键值对应列中相应的数据
*/
public Vector selectVector(String sql)
{
Vector ver = new Vector();
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,username,userpass);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
rmd = rs.getMetaData();
int columCount = rmd.getColumnCount();
String[] columNames = new String[columCount];
while(rs.next())
{
HashMap hash = new HashMap();
String rss = "";
int idss = 0;
for(int j=0;j {
columNames[idss] = rmd.getColumnName(j+1);
rss = rs.getString(columNames[idss]);
hash.put(columNames[idss],rss);
}
ver.add(idss,hash);
idss++;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally
{
this.closeConnection(conn,stmt,rs);
}
return ver;
}
/**
* 关闭Connection连接
*/
public void closeConnection(Connection conn,Statement stmt,ResultSet rs)
{
if(rs != null)
{
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null)
{
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null)
{
try {
conn.close();
}catch (SQLException e) {
System.out.println("连接关闭失败。可能连接未创建成功！"+e.getMessage());
}
}else{
System.out.println("=> [Connection未创建，无法关闭]");
}
}
public int getColumCount(String tableName)
{
int columNo = 0;
try {
String sql = "SELECT COUNT(*) FROM "+tableName;
Class.forName(driver);
conn = DriverManager.getConnection(url,username,userpass);
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
rs.next();
columNo = rs.getInt(1);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
finally
{
closeConnection(conn,stmt,rs);
}
return columNo;
}
/**
* 分页显示样式 << 1 2 3 4 5 6 >>
* @param url
* @return
*/
public String getHTML(String url)
{
if(url.indexOf("?")>=0)
{
url = url+"&";
}
else
{
url = url+"?";
}
StringBuffer sHTML = new StringBuffer("");
if(!precPage() && !nextPage())//只有一页记录
{
return sHTML+"";
}
sHTML.append(" \n");
return sHTML+"";
}
private String theHtml(String url)
{
StringBuffer sHTML = new StringBuffer("");
Page = getPage();//当前页
MaxPageSize = getMaxPageSize();//总页数
int iTempPageSize = 5;//基数
if(iTempPageSize >= MaxPageSize) iTempPageSize = MaxPageSize;
if(Page < iTempPageSize)
{
for (int i = 0; i < iTempPageSize; i++) {
if((i+1) == Page)
{
sHTML.append(""+(i+1)+" ");
}
else
{
sHTML.append(""+(i+1)+" ");
}
}
}
else
{
if(Page - 3 > 0)
{
sHTML.append(""+(Page-3)+" ");
sHTML.append(""+(Page-2)+" ");
sHTML.append(""+(Page-1)+" ");
sHTML.append(""+Page+" ");
}
else if(Page - 2 > 0)
{
sHTML.append(""+(Page-2)+" ");
sHTML.append(""+(Page-1)+" ");
sHTML.append(""+Page+" ");
}
else if(Page - 1 > 0)
{
sHTML.append(""+(Page-1)+" ");
sHTML.append(""+Page+" ");
}
for (int i = 1; i < 6; i++)
{
if(Page + i > MaxPageSize) break;
sHTML.append(""+(Page+i)+" ");
}
}
return sHTML+"";
}
/**
* 读取properties文件
* 进行读取时使用 getProperty(键名) 进行读取
*
* @param filePath 文件路径
* @return java.util.Properties
*/
public static java.util.Properties getPropertiesFile(String filePath)
{
java.util.Properties prop = new java.util.Properties();
java.io.FileInputStream fis = null;
try{
fis = new java.io.FileInputStream(filePath);
prop.load(fis);
}catch(java.io.FileNotFoundException ffe){
System.out.println("自定义配置文件未找到! "+ffe.getMessage());
System.out.println("=> [自动更正为默认的配置文件路径，继续读取......]");
try {
System.out.println("=> [默认配置文件读取成功]");
fis = new java.io.FileInputStream("c:/hl-config.properties");
prop.load(fis);
} catch (FileNotFoundException e) {
System.out.println("未创建默认的配置文件! "+e.getMessage());
} catch (IOException e) {
System.out.println("读取默认的配置文件出错! "+e.getMessage());
}
}catch(java.io.IOException ex){
System.out.println("读取自定义配置文件出错! "+ex.getMessage());
}
return prop;
}
}
使用说明：
1.在ACTION中导入这个BEAN
import bean.MyOraclePageBean
2.创建实例并将结果传入前台页面
action中的调用过程：
public class ListAction extends Action{
public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response)
throws Exception
{
int page;
try{
page = Integer.parseInt(request.getParameter("page"));
}catch(Exception e)
{
page = 1;
}
String sql = "select * from my";
int PageSize = 8;
MyOraclePageBean my = new MyOraclePageBean(PageSize,page,sql);
Vector verResult = my.needPage();
int iIndex = my.getHLID();
String strPage = my.list("./list.do");
request.setAttribute("ver",verResult);
request.setAttribute("idx",iIndex+"");
request.setAttribute("str",strPage);
return mapping.findForward("SUCCESS");
}
页面中的调用过程：（以JSP页面为例）
如果要测试,自己添加页面中的其它信息记着在页面中import: java.util.Vector和java.util.HashMap
<%
//首先要将所需要的数据从request中取出
Vector ver = (Vector) request.getAttribute("ver");
String str = (String) request.getAttribute("str");
int idx = Integer.parseInt((String)request.getAttribute("idx"));
if(ver != null){
for(int i=0;i HashMap hash = (HashMap)ver.get(i);
String name = (String)hash.get("name");
String pwd = (String)hash.get("pwd");
int ids = idx++;
int s = 0;
if(ids%2 != 0) s =1;//记算每行的显示样式
%>
<%= isdf %> |
<%= name %> |
<%= pwd %> |
<%
}
%>
<%
out.println(str);
}else{
out.print("NULL!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
}
%> |
原文：http://boardlayout.blog.ccidnet.com/blog-htm-itemid-120997-do-showone-type-blog-uid-43437.html |
