我写个分页的方法 你看看 public static Vector doPagination(String columns,String tables,String primaryKey,String whereOrder,int page_record,String pages,String dbType){ int currentPage = 1; int allPage = 0; int allRecord = 0; Vector vector = new Vector(); OpenDB open = new OpenDB(dbType); String sql = "select count(*) from "+tables+" where "+whereOrder; ResultSet rs = open.doQuery(sql); try{ if (rs.next()) { allRecord = rs.getInt(1); } allPage = (allRecord+page_record-1)/page_record; if(pages!=null){ int orderPage = Integer.parseInt(pages); if(orderPage<1){ currentPage = 1; }else if(orderPage>allPage){ currentPage = allPage; }else{ currentPage = orderPage; } } }catch(Exception e){ e.printStackTrace(); } if(dbType.equalsIgnoreCase("sqlserver")){ sql = "select top "+page_record+" "+columns+" from "+tables+" where "+primaryKey+" not in (select top "+(currentPage-1)*page_record+" "+primaryKey+" from "+tables+" where "+whereOrder+" order by "+primaryKey+") and "+whereOrder+" order by "+primaryKey; } if(dbType.equalsIgnoreCase("mysql")){ sql = "select "+columns+" from "+tables+" where "+whereOrder+" order by "+primaryKey+" limit "+(currentPage-1)*page_record+","+page_record; } rs = open.doQuery(sql); List list = new ArrayList(); try{ int columnCount = rs.getMetaData().getColumnCount(); while (rs.next()) { for(int i=0;i<columnCount;i++){ list.add(rs.getString(i)); } } vector.add(0,new Integer(columnCount)); vector.add(1,new Integer(allRecord)); vector.add(2,new Integer(allPage)); vector.add(3,new Integer(currentPage)); vector.add(4,list); }catch(Exception e){ e.printStackTrace(); } return vector; } 在页面 根据封装的信息 进行显示就可以了
方法的参数我给你解释一边 String columns,需要查询的字段 String tables,需要查询的表 String primaryKey,排序的主键 String whereOrder,查询条件 int page_record,分页单位 String pages,你要求的页面(第几页) String dbType数据库类型 我写了两个 sqlserver 和 mysql
我的分页: /* * Pagination.java * * Created on 2006年5月18日, 上午9:37 * * To change this template, choose Tools | Template Manager * and open the template in the editor. */package org.me.hello; import java.sql.*; import java.util.*; import org.me.hello.DataConnection; /* * Pagination.java * * Created on 2006年3月23日, 上午9:39 * * To change this template, choose Tools | Options and locate the template under * the Source Creation and Management node. Right-click the template and choose * Open. You can then make changes to the template in the Source Editor. *//** * * @author Administrator */ public class Pagination { private DataConnection conn; private int pageCount; private int tot = -1; private String sql; private Vector v1,v2,v3; /** * * @param pageCount */ public Pagination(int pageCount) throws Exception { this.pageCount = pageCount; this.conn = new DataConnection(); v1 = new Vector(); v2 = new Vector(); v3 = new Vector(); }
public List executePageQuery(String sql, String bean, int start, int step) { rs = null; List list = new ArrayList(); int starts = 0; try { if (conn == null) { this.getConnection(); } if (conn != null) { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); }
你看看 public static Vector doPagination(String columns,String tables,String primaryKey,String whereOrder,int page_record,String pages,String dbType){
int currentPage = 1;
int allPage = 0;
int allRecord = 0;
Vector vector = new Vector();
OpenDB open = new OpenDB(dbType);
String sql = "select count(*) from "+tables+" where "+whereOrder;
ResultSet rs = open.doQuery(sql);
try{
if (rs.next()) {
allRecord = rs.getInt(1);
}
allPage = (allRecord+page_record-1)/page_record;
if(pages!=null){
int orderPage = Integer.parseInt(pages);
if(orderPage<1){
currentPage = 1;
}else if(orderPage>allPage){
currentPage = allPage;
}else{
currentPage = orderPage;
}
}
}catch(Exception e){
e.printStackTrace();
} if(dbType.equalsIgnoreCase("sqlserver")){
sql = "select top "+page_record+" "+columns+" from "+tables+" where "+primaryKey+" not in (select top "+(currentPage-1)*page_record+" "+primaryKey+" from "+tables+" where "+whereOrder+" order by "+primaryKey+") and "+whereOrder+" order by "+primaryKey;
}
if(dbType.equalsIgnoreCase("mysql")){
sql = "select "+columns+" from "+tables+" where "+whereOrder+" order by "+primaryKey+" limit "+(currentPage-1)*page_record+","+page_record;
}
rs = open.doQuery(sql);
List list = new ArrayList();
try{
int columnCount = rs.getMetaData().getColumnCount();
while (rs.next()) {
for(int i=0;i<columnCount;i++){
list.add(rs.getString(i));
}
}
vector.add(0,new Integer(columnCount));
vector.add(1,new Integer(allRecord));
vector.add(2,new Integer(allPage));
vector.add(3,new Integer(currentPage));
vector.add(4,list); }catch(Exception e){
e.printStackTrace();
}
return vector;
}
在页面
根据封装的信息 进行显示就可以了
String columns,需要查询的字段
String tables,需要查询的表
String primaryKey,排序的主键
String whereOrder,查询条件
int page_record,分页单位
String pages,你要求的页面(第几页)
String dbType数据库类型 我写了两个 sqlserver 和 mysql
/*
* Pagination.java
*
* Created on 2006年5月18日, 上午9:37
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/package org.me.hello;
import java.sql.*;
import java.util.*;
import org.me.hello.DataConnection;
/*
* Pagination.java
*
* Created on 2006年3月23日, 上午9:39
*
* To change this template, choose Tools | Options and locate the template under
* the Source Creation and Management node. Right-click the template and choose
* Open. You can then make changes to the template in the Source Editor.
*//**
*
* @author Administrator
*/
public class Pagination
{ private DataConnection conn;
private int pageCount;
private int tot = -1;
private String sql;
private Vector v1,v2,v3; /**
*
* @param pageCount
*/
public Pagination(int pageCount) throws Exception
{
this.pageCount = pageCount;
this.conn = new DataConnection();
v1 = new Vector();
v2 = new Vector();
v3 = new Vector();
}
public void prepareStatement(String sql) throws Exception
{
this.sql = sql;
conn.init();
}
public void setString(int n,String s)
{
this.v1.add(new Integer(n));
this.v2.add(new Integer(1));
this.v3.add(s);
}
public void setInt(int n,int t)
{
this.v1.add(new Integer(n));
this.v2.add(new Integer(2));
this.v3.add(new Integer(t));
}
public void setByte(int n,byte b)
{
this.v1.add(new Integer(n));
this.v2.add(new Integer(3));
this.v3.add(new Byte(b));
}
public void setLong(int n,long l)
{
this.v1.add(new Integer(n));
this.v2.add(new Integer(4));
this.v3.add(new Long(l));
}
public void setFloat(int n,float f)
{
this.v1.add(new Integer(n));
this.v2.add(new Integer(5));
this.v3.add(new Float(f));
}
public void setDate(int n,java.util.Calendar d)
{
this.v1.add(new Integer(n));
this.v2.add(new Integer(6));
this.v3.add(d);
} public int getTot() throws SQLException
{
if(tot < 0)
{
tot();
}
return tot;
}
private void tot() throws SQLException
{
String from = sql.substring(sql.indexOf("from"));
from = from.indexOf("order by") > 0 ? from.substring(0,from.indexOf("order by")) : from;
StringBuffer sbf = new StringBuffer("select count(*) as tot ");
sbf.append(from);
conn.prepareStatement(sbf.toString());
for(int i = 0;i < v1.size(); i++)
{
switch(((Integer)v2.get(i)).intValue())
{
case 1 : conn.setString(((Integer)v1.get(i)).intValue(),(String)v3.get(i)); break;
case 2 : conn.setInt(((Integer)v1.get(i)).intValue(),((Integer)v3.get(i)).intValue()); break;
case 3 : conn.setByte(((Integer)v1.get(i)).intValue(),((Byte)v3.get(i)).byteValue()); break;
case 4 : conn.setLong(((Integer)v1.get(i)).intValue(),((Long)v3.get(i)).longValue()); break;
case 5 : conn.setFloat(((Integer)v1.get(i)).intValue(),((Float)v3.get(i)).floatValue()); break;
case 6 : conn.setDate(((Integer)v1.get(i)).intValue(),(java.util.Calendar)v3.get(i)); break;
}
}
conn.executeQuery();
conn.next();
tot = conn.getInt("tot");
conn.closeRS();
conn.closePS();
} public int getTotPage() throws SQLException
{
if(tot < 0)
{
tot();
}
return tot % pageCount > 0 ? tot / pageCount + 1 : tot / pageCount;
} public void executeQuery(int page) throws SQLException
{
StringBuffer sbf = new StringBuffer("select * from(select tmp_table.*, rownum as tmp_rownum from(");
sbf.append(sql).append(") tmp_table where rownum <=").append(page * pageCount).append(") where tmp_rownum >=").append((page - 1) * pageCount + 1);
conn.prepareStatement(sbf.toString());
for(int i = 0;i < v1.size(); i++)
{
switch(((Integer)v2.get(i)).intValue())
{
case 1 : conn.setString(((Integer)v1.get(i)).intValue(),(String)v3.get(i)); break;
case 2 : conn.setInt(((Integer)v1.get(i)).intValue(),((Integer)v3.get(i)).intValue()); break;
case 3 : conn.setByte(((Integer)v1.get(i)).intValue(),((Byte)v3.get(i)).byteValue()); break;
case 4 : conn.setLong(((Integer)v1.get(i)).intValue(),((Long)v3.get(i)).longValue()); break;
case 5 : conn.setFloat(((Integer)v1.get(i)).intValue(),((Float)v3.get(i)).floatValue()); break;
case 6 : conn.setDate(((Integer)v1.get(i)).intValue(),(java.util.Calendar)v3.get(i)); break;
}
}
conn.executeQuery();
}
public String getString(String s) throws SQLException
{
return conn.getString(s);
}
public int getInt(String s) throws SQLException
{
return conn.getInt(s);
}
public byte getByte(String s) throws SQLException
{
return conn.getByte(s);
}
public long getLong(String s) throws SQLException
{
return conn.getLong(s);
}
public float getFloat(String s) throws SQLException
{
return conn.getFloat(s);
}
public java.util.Date getDate(String s) throws SQLException
{
return conn.getDate(s);
}
public boolean next() throws SQLException
{
return conn.next();
}
public void close() throws SQLException
{
conn.close();
}
public void closeRS() throws SQLException
{
conn.closeRS();
conn.closePS();
}
}
rs = null;
List list = new ArrayList();
int starts = 0;
try {
if (conn == null) {
this.getConnection();
}
if (conn != null) {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
}
Object myobj = null;
Class[] myclass = { "".getClass() };
while (rs.next()) {
Class mybean = Class.forName(bean);
myobj = mybean.newInstance();
if (starts >= (start - 1) * step && starts < step * start) {
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
String colname = rsmd.getColumnName(i);
StringBuffer col = new StringBuffer("set");
col.append(colname.replace(colname.charAt(0), Character
.toUpperCase(colname.charAt(0))));
col.replace(4, col.length(), col.substring(4)
.toLowerCase());
Method m = mybean.getMethod(col.toString(), myclass);
m.invoke(myobj, new Object[] { rs.getString(colname) });
}
list.add(myobj);
}
starts++;
}
} catch (SQLException e) {
this.closeConnection();
System.err.println("db.executeQuery:" + e.getMessage());
} catch (ClassNotFoundException ec) {
System.err.println("mydb():" + ec.getMessage());
} catch (Exception ee) {
System.err.println("myerr==" + ee.getMessage());
System.out.println("==" + ee.toString());
} finally {
this.closeConnection();
}
return list;
}}
sql:sql语句
bean:要付值的javabean
start:当前页
step:步长
session.setFirstResult((int page-1)*每页多少条)//从第几条开始
session.setMaxResult(int num);//一页多少条
int dipage=1;//当前页码数默认为1
String pages=request.getParameter("dipage");
if(pages==null)
{
pages="1";
}
try
{
dipage=Integer.parseInt(pages);}
catch(Exception e)
{
dipage=1;
}
%>
<html>
<body>
<center>
<%
//连接数据库语句
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from ???? ";
ResultSet rs=stmt.executeQuery(sql);
int countRecord=0;//记录条数
int countPageRecord=0;//每页记录条数
int countPage=0;//总页数
countPageRecord=5;//每页5条记录
rs.last();
countRecord=rs.getRow();
//得到总页数
if(countRecord/countPageRecord==0)
countPage=countRecord/countPageRecord;
else
countPage=countRecord/countPageRecord+1;
//把记录指针移至当前第一条记录之前
if((dipage-1)*countPageRecord==0)
rs.beforeFirst();
else
rs.absolute((dipage-1)*countPageRecord);
out.print("<Table Border style='font-size: 10pt'>");
out.print("<tr>");
out.print("<td width=60>"+"你的字段");
out.print("<td width=60>"+"你的字段");
out.print("<td width=60>"+"你的字段");
out.print("<td width=60>"+"你的字段");
out.print("<td width=60>"+"你的字段");
out.print("<td width=60>"+"你的字段");
out.print("</tr>");
int i=0;
while(rs.next())
{
out.print("<tr>");
out.print("<td>"+rs.getString("你的字段")+"</td>");
out.print("<td>"+rs.getString("你的字段")+"</td>");
out.print("<td>"+rs.getString("你的字段")+"</td>");
out.print("<td>"+rs.getString("你的字段")+"</td>");
out.print("<td>"+rs.getString("你的字段")+"</td>");
out.print("<td>"+rs.getString("你的字段")+"</td>");
out.print("</tr>");
i++;//该结果显示了一个六列的表
if(i>=countPageRecord) break;//当前页显示完,则退出循环
}
out.print("<tr><td colspan=8 align=center>");
out.print("共"+countRecord+"条记录,共"+countPage+"页,当前第"+dipage+"页,每页"+countPageRecord+"条记录,");
if(dipage==1)//当前是首页
;
else
{
out.print("<a href=userPage.jsp?dipage=1>首页</a>,");
out.print("<a href=userPage.jsp?dipage="+(dipage-1)+">上一页</a>,"); }
if(dipage==countPage)//当前是末页
;
else
{
out.print("<a href=userPage.jsp?dipage="+(dipage+1)+">下一页</a>,");
out.print("<a href=userPage.jsp?dipage="+countPage+">末页</a>");
}
out.print("</td></tr>");
out.print("</Table>");
conn.close();
%>
</center>
</body>
</html>
String pages=request.getParameter("dipage");
if(pages==null)
{
pages="1";
}
还有 rs.absolute((dipage-1)*countPageRecord);
这两快具体什么意思,能详细解释一下吗?我对dipage还是不太理解