前段时间做了一个分页的程序,当时是用的oracle数据库运行的很好,前几天我换用了mysql的时候竟然查出来的数据不对,后来苦苦找了半天才发现原来oracle和mysql的分页sql语句不一样,现在知道了给大家说一下。
使用了JDBC、struts,用JDBC来访问数据库,如果用hibernate会更方便,把分页的请求提交到一个Action来处理然后再转到显示数据的页面
完整的分页代码如下:
首先是一个为Page的POJO类:
public
class
Page
...
{
int ipage=10; //分页单位
int allpage; //总页数
int pages; //接受的页码变量
int cpage=1; //当前页
int spage; //开始页
public int getAllpage() ...{
return allpage;
}
public void setAllpage(int allpage) ...{
this.allpage = allpage;
}
省略getter和setter……
int ipage=10; //分页单位
int allpage; //总页数
int pages; //接受的页码变量
int cpage=1; //当前页
int spage; //开始页
public int getAllpage() ...{
return allpage;
}
public void setAllpage(int allpage) ...{
this.allpage = allpage;
}
省略getter和setter……
然后是访问数据库的DAO
//这里使用的是mysql,使用mysql 自己提供的limit语句
public
Collection loadByPage(Integer start, Integer end)
throws
Exception
...
{
Connection conn = JDBCFactory.getConnection();
/**//*
* mysql中limit start,end
* start表示从第几条记录开始显示
* end表示从start位置开始显示多少条记录
*/
String sql = "select * from bbs limit " + start + "," + end;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Collection<Bbs> coll = new ArrayList<Bbs>();
while (rs.next()) ...{
Bbs bbs = new Bbs();
bbs.setBbsId(rs.getInt("bbs_id"));
bbs.setUserId(rs.getString("user_id"));
bbs.setTitle(rs.getString("title"));
bbs.setContent(rs.getString("context"));
bbs.setAddTime(rs.getTimestamp("addTime"));
coll.add(bbs);
}
return coll;
}
Connection conn = JDBCFactory.getConnection();
/**//*
* mysql中limit start,end
* start表示从第几条记录开始显示
* end表示从start位置开始显示多少条记录
*/
String sql = "select * from bbs limit " + start + "," + end;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Collection<Bbs> coll = new ArrayList<Bbs>();
while (rs.next()) ...{
Bbs bbs = new Bbs();
bbs.setBbsId(rs.getInt("bbs_id"));
bbs.setUserId(rs.getString("user_id"));
bbs.setTitle(rs.getString("title"));
bbs.setContent(rs.getString("context"));
bbs.setAddTime(rs.getTimestamp("addTime"));
coll.add(bbs);
}
return coll;
}
下面使用的是oracle
public
Collection load(
int
begin,
int
end)
throws
SQLException
...
{
String sql = "select * from(select a.*,rownum rn from (select * from yhlt_student) a where rownum <=" + end + ") where rn >=" + begin;
Connection conn = Utils.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Collection<Student> stuColl = new ArrayList<Student>();
while (rs.next()) ...{
Student stu = new Student();
stu.setStudentId(rs.getInt(1));
stu.setStuName(rs.getString(2));
stu.setStuSex(rs.getString(3));
stuColl.add(stu);
}
return stuColl;
}
String sql = "select * from(select a.*,rownum rn from (select * from yhlt_student) a where rownum <=" + end + ") where rn >=" + begin;
Connection conn = Utils.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Collection<Student> stuColl = new ArrayList<Student>();
while (rs.next()) ...{
Student stu = new Student();
stu.setStudentId(rs.getInt(1));
stu.setStuName(rs.getString(2));
stu.setStuSex(rs.getString(3));
stuColl.add(stu);
}
return stuColl;
}
*******************************
关键还是下面这个Action是怎么控制分页的流程的
@Override
public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception ... {
HttpSession session = request.getSession();
Page page = (Page) session.getAttribute("curPage");
BbsDAO bbsDAO = DAOFactory.getBbsDAO();
int columns = bbsDAO.getMaxColumn();
//第一次访问
if (page == null) ...{
page = new Page();
page.setAllpage((int) Math.ceil(columns) / page.getIpage() + 1);
}
// ����pages
if (request.getParameter("pages") == null) ...{
page.setPages(1);
} else ...{
page.setPages(Integer.parseInt(request.getParameter("pages")));
}
if (page.getPages() > page.getAllpage() || page.getPages() <= 0) ...{
return mapping.findForward("bbsIndex");
}
// current page
if (page.getPages() > page.getAllpage()) ...{
page.setCpage(1);
} else ...{
page.setCpage(page.getPages());
}
// 开始查找的记录位置
page.setSpage(((page.getPages() - 1) * page.getIpage()));
int start = page.getSpage();
Collection coll = bbsDAO.loadByPage(start, page.getIpage());
//把取到的对象集合放到session中
session.setAttribute("bbses", coll);
//保存当前的页数
session.setAttribute("curPage", page);
//一共多少条记录,通过这个来计算一共多少页
session.setAttribute("countRows", columns);
return mapping.findForward("bbsIndex");
}
public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception ... {
HttpSession session = request.getSession();
Page page = (Page) session.getAttribute("curPage");
BbsDAO bbsDAO = DAOFactory.getBbsDAO();
int columns = bbsDAO.getMaxColumn();
//第一次访问
if (page == null) ...{
page = new Page();
page.setAllpage((int) Math.ceil(columns) / page.getIpage() + 1);
}
// ����pages
if (request.getParameter("pages") == null) ...{
page.setPages(1);
} else ...{
page.setPages(Integer.parseInt(request.getParameter("pages")));
}
if (page.getPages() > page.getAllpage() || page.getPages() <= 0) ...{
return mapping.findForward("bbsIndex");
}
// current page
if (page.getPages() > page.getAllpage()) ...{
page.setCpage(1);
} else ...{
page.setCpage(page.getPages());
}
// 开始查找的记录位置
page.setSpage(((page.getPages() - 1) * page.getIpage()));
int start = page.getSpage();
Collection coll = bbsDAO.loadByPage(start, page.getIpage());
//把取到的对象集合放到session中
session.setAttribute("bbses", coll);
//保存当前的页数
session.setAttribute("curPage", page);
//一共多少条记录,通过这个来计算一共多少页
session.setAttribute("countRows", columns);
return mapping.findForward("bbsIndex");
}
*****************************
好了,现在DAO、Action、POJO都准备好了,我们就来测试一下吧:
<%
@ page language
=
"
java
"
import
=
"
java.util.*,com.nee.page.*
"
pageEncoding
=
"
UTF-8
"
%>
<%
Integer countRows = (Integer)request.getSession().getAttribute( " countRows " );
Page curPage = (Page)request.getSession().getAttribute( " curPage " );
Integer allPages = curPage.getAllpage();
Integer currentPage = curPage.getCpage();
String path = request.getContextPath();
%>
<% if (currentPage == 1 ) ... {
%>
首页||
<% } else ... {%>
<a href="<%=path %>/controlPage.do?pages=1">首页||</a>
<%} %>
共 <%= countRows %> 条记录 & nbsp; ||& nbsp;
第 <%= currentPage %> 页 / 共 <%= allPages %> 页 ||
<% if (currentPage - 1 != 0 ) ... { %>
<a href="<%=path %>/controlPage.do?pages=<%=currentPage-1 %>">上一页||</a>
<%} else ... { %>
上一页||
<%} %>
<% if (currentPage < allPages) ... { %>
<a href="<%=path %>/controlPage.do?pages=<%=currentPage+1 %>">下一页||</a>
<%} else ... { %>
下一页||
<%} %>
<% if (currentPage == allPages) ... {%>
尾页
<%} else ... {%>
<a href="<%=path %>/controlPage.do?pages=<%=allPages%>">尾页</a>
<%} %>
< form action = " <%=path %>/controlPage.do " name = " go " method = " post " >
我要去第
< input type = " text " size = " 5 " name = " pages " /> 页
< input type = " submit " value = " GO " />
</ form >
<%
Integer countRows = (Integer)request.getSession().getAttribute( " countRows " );
Page curPage = (Page)request.getSession().getAttribute( " curPage " );
Integer allPages = curPage.getAllpage();
Integer currentPage = curPage.getCpage();
String path = request.getContextPath();
%>
<% if (currentPage == 1 ) ... {
%>
首页||
<% } else ... {%>
<a href="<%=path %>/controlPage.do?pages=1">首页||</a>
<%} %>
共 <%= countRows %> 条记录 & nbsp; ||& nbsp;
第 <%= currentPage %> 页 / 共 <%= allPages %> 页 ||
<% if (currentPage - 1 != 0 ) ... { %>
<a href="<%=path %>/controlPage.do?pages=<%=currentPage-1 %>">上一页||</a>
<%} else ... { %>
上一页||
<%} %>
<% if (currentPage < allPages) ... { %>
<a href="<%=path %>/controlPage.do?pages=<%=currentPage+1 %>">下一页||</a>
<%} else ... { %>
下一页||
<%} %>
<% if (currentPage == allPages) ... {%>
尾页
<%} else ... {%>
<a href="<%=path %>/controlPage.do?pages=<%=allPages%>">尾页</a>
<%} %>
< form action = " <%=path %>/controlPage.do " name = " go " method = " post " >
我要去第
< input type = " text " size = " 5 " name = " pages " /> 页
< input type = " submit " value = " GO " />
</ form >
这里就是要判断在什么时候显示带链接的文字,什么时候不显示带链接的文字
一切顺利,你还等什么,现在就来试试吧
oracle和mysql的分页实现主要就是在写sql语句的时候应该怎么写:
oracle可以使用它自己的rownum来实现从第几行到第几行
String sql
=
"
select * from(select a.*,rownum rn from (select * from yhlt_student) a where rownum <=
"
+
end
+
"
) where rn >=
"
+
begin;
mysql是从第几行开始取多少行
String sql
=
"
select * from bbs limit
"
+
start
+
"
,
"
+
end;