分页的方式：
页面分页
数据库分页（现在通用的分页方式）：一次取数据的一部分，尽量采取数据库
机制来实现！
除了SQL 和 Access外 其他的数据库都提供了数据库分页机制，现在来用通用的分页方法来实现：游标分页！
本代码是基于java的反射机制实现！
首先：在数据库连接时给定参数，代码：
|
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:test");
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
|
建立main方法进行测试：
|
public static void main(String[] args) throws Exception {
BaseDao db = new BaseDao();
int pageSize = 3;//每页三条
int pageNow = 1;//当前第一页
ResultSet rs = db.executeQuery("select * from message");
rs.absolute((pageNow-1)*pageSize+1);
for (int i = 0; i < pageSize; i++) {
System.out.println(rs.getString(2));
if(!rs.next()) break;
}
}
|
测试成功 OK！ 继续
接着：在数据库操作方法中，写入一下代码：
|
//分页中获取总列数专用
public int getCount(String sql)
{
int count = 0;
BaseDao db = new BaseDao();
ResultSet rs;
try {
rs = db.executeQuery(sql);
if(rs.next())
count = rs.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally
{
db.close();
}
return count;
}
//分页专用
public List executeQuery(String sql,int pageNow,int pageSize ) {
// TODO Auto-generated method stub
List list = new ArrayList();
BaseDao dao = new BaseDao();
ResultSet rs;
try {
rs=dao.executeQuery(sql);
rs.absolute((pageNow-1)*pageSize+1);
String[] fields=new String[rs.getMetaData().getColumnCount()];
for (int i = 0; i < fields.length; i++) {
fields[i]=rs.getMetaData().getColumnName(i+1);
}
for(int p=0;p<pageSize;p++)
{
HashMap mp = new HashMap();
for (int i = 0; i < fields.length; i++) {
String value = rs.getString(fields[i]);
mp.put(fields[i], value);
}
list.add(mp);
if(!rs.next()) break;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally
{
dao.close();
}
return list;
}
|
其次：封装PageServlet类，用来具体实现分页的代码：
|
public class PageServlet extends HttpServlet {
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
int pageNow = 1;
int pageSize = 5;
String temp = request.getParameter("pageNow");
if (temp != null)
pageNow = Integer.parseInt(temp);
Method method;
Object[] objs = null;
try {
method = this.getClass().getDeclaredMethod("page",
new Class[] { int.class, int.class });
objs = (Object[]) method.invoke(this, new Object[] { pageNow,
pageSize });
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
request.setAttribute("message", objs[0]);
request.setAttribute("pageCount", objs[2] + "");
request.setAttribute("count", objs[1] + "");
request.setAttribute("pageNow", pageNow + "");
request.getRequestDispatcher(objs[3].toString()).forward(request,
response);
}
}
|
接着：在具体操作方法BoardDao中的代码：
|
//分页中查询共有的列数
public int getCount() {
// TODO Auto-generated method stub
return db.getCount("select count(*) from message");
}
//分页中获取所有页数
public int getPageCount(int pageSize) {
// TODO Auto-generated method stub
return (getCount()+pageSize-1)/pageSize;
}
//执行查询方法
public List findAll(int pageNow,int pageSize)
{
String sql = "SELECT dbo.message.messageid, dbo.message.title, dbo.message.content, dbo.message.marktime, dbo.userInfo.username FROM dbo.message INNER JOIN dbo.userInfo ON dbo.message.userid = dbo.userInfo.id";
return db.executeQuery(sql,pageNow,pageSize);
}
|
最后：在控制器ShowServlet（继承PageServlet ）中进行引用，查询结果集显示在MessageBoard.jsp
|
public class ShowServlet extends PageServlet {
public Object[] page(int pageNow, int pageSize) {
BoardDao dao = new BoardDao();
List list = dao.findAll(pageNow, pageSize);
int count = dao.getCount();
int pageCount = dao.getPageCount(pageSize);
return new Object[] { list, count, pageCount, "MessageBoard.jsp" };
}
}
|
在MessageBoard.jsp代码：
|
<TABLE align="center" border="0" width="30%" >
<TR>
<TD><a href="./ShowServlet?pageNow=1">首页</a></TD>
<c:if test="${pageNow>
<TD><a href="./ShowServlet?pageNow=${pageNow+1}">下一页</a></TD>
</c:if>
<c:if test="${pageNow==pageCount}">
<TD>下一页</TD>
</c:if>
<c:if test="${pageNow>1}">
<TD><a href="./ShowServlet?pageNow=${pageNow-1}">上一页</a></TD>
</c:if>
<c:if test="${pageNow==1}">
<TD>上一页</TD>
</c:if>
<TD><a href="./ShowServlet?pageNow=${pageCount}">末页</a></TD>
<TD>${pageNow}/${pageCount} 总共:${count }</TD>
</TR>
</TABLE>
|
到此，分页已经完成
阅读(1629) | 评论(0) | 转发(0) |
