在bbs、电子商务等众多系统中,分页是一个必不可少的功能。在hibernate中,setFirstResult以及setMaxResults方法给分页的实现带来了极大方便。参考了不少网上资源,结合我自己的系统需要,新建了一个pageDAO类,实现分页查询功能,有生成了接口以供调用。我并没有建pageBean,我下了不少网上的代码,估计对分页太不了解吧,看的头大,最后选择了比较简单的方法,后台实现分页查询,前台用el表达式慢慢判断、分页输出吧。 但是,当我认为成功的实现了分页功能时,忽然发现一个问题,就是每当程序运行,没多久程序就似乎死机一样,失去了响应。只能重启tomcat,然而重启后,我连续发帖7、8个,又变成无响应状态。为此,我在网上请教了不少朋友。
其中,我的分页函数是:
[java]view plaincopyprint?
- //注意:这个函数是有问题的,下文说明改进。
- public List findByPropertyPage(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
- StringBuffer strBuffer = new StringBuffer();
- strBuffer.append("from ");
- strBuffer.append(Whichclass);
- strBuffer.append(" as model where ");
- for (int i = 0; i < propertyNames.length; i++)
- {
- if (i != 0)
- strBuffer.append(" and");
- strBuffer.append(" model.");
- strBuffer.append(propertyNames[i]);
- strBuffer.append("=");
- strBuffer.append("? "); //Topic
- }
- if (Whichclass.equals("Topic")) {
- strBuffer.append("order by postTime desc ");
- }
-
- String queryString = strBuffer.toString();
- int firstResult = (page - 1) * pageSize;
- List list=null;
- try {
- Query query =<SPAN style="FONT-FAMILY: simsun">this.getSession()</SPAN><SPAN style="FONT-FAMILY: simsun">.createQuery(queryString);</SPAN>
- query.setFirstResult(firstResult);
- query.setMaxResults(pageSize);
- for (int i = 0; i < values.length; i++)
- {
- query.setParameter(i, values[i]);
- }
- list =query.list();
- } finally {
-
- }
- return list;
- }
//注意:这个函数是有问题的,下文说明改进。
public List findByPropertyPage(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("from ");
strBuffer.append(Whichclass);
strBuffer.append(" as model where ");
for (int i = 0; i < propertyNames.length; i++)
{
if (i != 0)
strBuffer.append(" and");
strBuffer.append(" model.");
strBuffer.append(propertyNames[i]);
strBuffer.append("=");
strBuffer.append("? "); //Topic
}
if (Whichclass.equals("Topic")) {
strBuffer.append("order by postTime desc ");
}
String queryString = strBuffer.toString();
int firstResult = (page - 1) * pageSize;
List list=null;
try {
Query query =this.getSession().createQuery(queryString);
query.setFirstResult(firstResult);
query.setMaxResults(pageSize);
for (int i = 0; i < values.length; i++)
{
query.setParameter(i, values[i]);
}
list =query.list();
} finally {
}
return list;
}
当我在网上请教时,一位朋友告知,大概是因为使用了setFirstResult以及setMaxResults方法,说这2个方法常常引发这样的问题,即程序长时间无响应。我大喜过望,询问后试着用sql的limit直接实现分页,不用hibernate那2个方法。代码如下:
[java]view plaincopyprint?
- //注意:这个函数是有问题的,下文说明改进。
- public List findByPropertyPageSQL(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
- StringBuffer strBuffer = new StringBuffer();
- strBuffer.append("select * from ");
- strBuffer.append(Whichclass);
- strBuffer.append(" as model where ");
- for (int i = 0; i < propertyNames.length; i++)
- {
- if (i != 0)
- strBuffer.append(" and");
- strBuffer.append(" model.");
- strBuffer.append(propertyNames[i]);
- strBuffer.append("=");
- strBuffer.append("? "); //Topic
- }
- if (Whichclass.equals("Topic")) {
- strBuffer.append("order by postTime desc ");
- }
- strBuffer.append("limit ");
- strBuffer.append((page - 1) * pageSize);//limit是从0开始的
- strBuffer.append(",");
- strBuffer.append(pageSize);
-
- String queryString = strBuffer.toString();
- List list = new ArrayList();
- try{
- Query query = this.getSession().createSQLQuery(queryString).addEntity(Class.forName("pengbbs.dao."+Whichclass));
- for (int i = 0; i < values.length; i++)
- {
- query.setParameter(i, values[i]);
- }
- list=query.list();
-
- }catch (Exception e) {
- // TODO: handle exception
- e.printStackTrace();
- }
- return list;
- }
//注意:这个函数是有问题的,下文说明改进。
public List findByPropertyPageSQL(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("select * from ");
strBuffer.append(Whichclass);
strBuffer.append(" as model where ");
for (int i = 0; i < propertyNames.length; i++)
{
if (i != 0)
strBuffer.append(" and");
strBuffer.append(" model.");
strBuffer.append(propertyNames[i]);
strBuffer.append("=");
strBuffer.append("? "); //Topic
}
if (Whichclass.equals("Topic")) {
strBuffer.append("order by postTime desc ");
}
strBuffer.append("limit ");
strBuffer.append((page - 1) * pageSize);//limit是从0开始的
strBuffer.append(",");
strBuffer.append(pageSize);
String queryString = strBuffer.toString();
List list = new ArrayList();
try{
Query query = this.getSession().createSQLQuery(queryString).addEntity(Class.forName("pengbbs.dao."+Whichclass));
for (int i = 0; i < values.length; i++)
{
query.setParameter(i, values[i]);
}
list=query.list();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return list;
}
可是我发现问题依旧!后来我查询了许多资料,也怪自己没深入学习,其实上面的代码和第一个setFirstResult方法是一样的,setFirstResult方法内部其实正是像上面那样执行limit,我只不过是无事找事,写了sql语句出来徒劳而已。如果真的不要setFirstResult,除非直接用jdbc吧。但如果这样,还用hibernate何用?所以下意识才知道,应该不是setFirstResult方法的问题。
又有朋友猜测,说肯定没有关闭数据库连接导致的。但是又说,我既然已经用了hibernate模版实现,则数据库的连接与关闭则并不需要我们来干涉啊。此问题我甚至以为是某些bug吧,后来终于在一篇博客上发现了问题,问题就在:
Query query =this.getSession().createQuery(queryString);
这句代码,发现创建了一个session,但是程序最末却并未关闭他它,果然是没有关闭数据库连接导致的。终于解决了。最终代码如下:
[java]view plaincopyprint?
- public List findByPropertyPageSQL(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
- StringBuffer strBuffer = new StringBuffer();
- strBuffer.append("select * from ");
- strBuffer.append(Whichclass);
- strBuffer.append(" as model where ");
- for (int i = 0; i < propertyNames.length; i++)
- {
- if (i != 0)
- strBuffer.append(" and");
- strBuffer.append(" model.");
- strBuffer.append(propertyNames[i]);
- strBuffer.append("=");
- strBuffer.append("? "); //Topic
- }
- if (Whichclass.equals("Topic")) {
- strBuffer.append("order by postTime desc ");
- }
- strBuffer.append("limit ");
- strBuffer.append((page - 1) * pageSize);//limit是从0开始的
- strBuffer.append(",");
- strBuffer.append(pageSize);
-
- String queryString = strBuffer.toString();
- List list = new ArrayList();
- Session s=this.getSession();
- try{
- Query query = s.createSQLQuery(queryString).addEntity(Class.forName("pengbbs.dao."+Whichclass));
- for (int i = 0; i < values.length; i++)
- {
- query.setParameter(i, values[i]);
- }
- list=query.list();
-
- }catch (Exception e) {
- // TODO: handle exception
- e.printStackTrace();
- }finally {
- s.close(); //此要记得关闭连接关闭session,不然问题大了,死机的。
- }
- return list;
- }
- @Override
- public List findByPropertyPage(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
- StringBuffer strBuffer = new StringBuffer();
- strBuffer.append("from ");
- strBuffer.append(Whichclass);
- strBuffer.append(" as model where ");
- for (int i = 0; i < propertyNames.length; i++)
- {
- if (i != 0)
- strBuffer.append(" and");
- strBuffer.append(" model.");
- strBuffer.append(propertyNames[i]);
- strBuffer.append("=");
- strBuffer.append("? "); //Topic
- }
- if (Whichclass.equals("Topic")) {
- strBuffer.append("order by postTime desc ");
- }
-
- String queryString = strBuffer.toString();
- int firstResult = (page - 1) * pageSize;
- Session s=this.getSession();
- List list=null;
- try {
- Query query = s.createQuery(queryString);
- query.setFirstResult(firstResult);
- query.setMaxResults(pageSize);
- for (int i = 0; i < values.length; i++)
- {
- query.setParameter(i, values[i]);
- }
- list =query.list();
- } finally {
- s.close(); //此要记得关闭连接关闭session,不然问题大了,死机的。
- }
- return list;
- }
public List findByPropertyPageSQL(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("select * from ");
strBuffer.append(Whichclass);
strBuffer.append(" as model where ");
for (int i = 0; i < propertyNames.length; i++)
{
if (i != 0)
strBuffer.append(" and");
strBuffer.append(" model.");
strBuffer.append(propertyNames[i]);
strBuffer.append("=");
strBuffer.append("? "); //Topic
}
if (Whichclass.equals("Topic")) {
strBuffer.append("order by postTime desc ");
}
strBuffer.append("limit ");
strBuffer.append((page - 1) * pageSize);//limit是从0开始的
strBuffer.append(",");
strBuffer.append(pageSize);
String queryString = strBuffer.toString();
List list = new ArrayList();
Session s=this.getSession();
try{
Query query = s.createSQLQuery(queryString).addEntity(Class.forName("pengbbs.dao."+Whichclass));
for (int i = 0; i < values.length; i++)
{
query.setParameter(i, values[i]);
}
list=query.list();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
s.close(); //此要记得关闭连接关闭session,不然问题大了,死机的。
}
return list;
}
@Override
public List findByPropertyPage(String Whichclass, String []propertyNames, Object []values,int page, int pageSize) {
StringBuffer strBuffer = new StringBuffer();
strBuffer.append("from ");
strBuffer.append(Whichclass);
strBuffer.append(" as model where ");
for (int i = 0; i < propertyNames.length; i++)
{
if (i != 0)
strBuffer.append(" and");
strBuffer.append(" model.");
strBuffer.append(propertyNames[i]);
strBuffer.append("=");
strBuffer.append("? "); //Topic
}
if (Whichclass.equals("Topic")) {
strBuffer.append("order by postTime desc ");
}
String queryString = strBuffer.toString();
int firstResult = (page - 1) * pageSize;
Session s=this.getSession();
List list=null;
try {
Query query = s.createQuery(queryString);
query.setFirstResult(firstResult);
query.setMaxResults(pageSize);
for (int i = 0; i < values.length; i++)
{
query.setParameter(i, values[i]);
}
list =query.list();
} finally {
s.close(); //此要记得关闭连接关闭session,不然问题大了,死机的。
}
return list;
}
其实在这个分页函数的解决过程中,有一插曲,差点我就没能真正解决。我曾发帖http://topic.csdn.net/u/20120808/11/8607d338-595e-40b5-85d2-5d7e55fa201b.html询问,有不少朋友均指出是没有关闭数据库连接,但由于我使用了hibernate,大概又没看到getSession()这行代码,所以暂时并无解决方案。我后来看到一篇文章,设计spring中的配置数据库连接,我加了这样一段:
[html]view plaincopyprint?
- <propertyname="maxIdle"value="50"></property>
- <propertyname="maxWait"value="5000"></property>
- <propertyname="maxActive"value="50"></property>
- <propertyname="defaultAutoCommit"value="true"></property>
- <propertyname="removeAbandonedTimeout"value="600"></property>
<property name="maxIdle" value="50"></property>
<property name="maxWait" value="5000"></property>
<property name="maxActive" value="50"></property>
<property name="defaultAutoCommit" value="true"></property>
<property name="removeAbandonedTimeout" value="600"></property>
发现死机的情况没了,我竟以为解决了,辛亏ldh911朋友指出,我这段代码其实就是增加了数据库连接数量而已,如改为
[html]view plaincopyprint?
- <propertyname="maxIdle"value="5"></property>
- <propertyname="maxWait"value="5000"></property>
- <propertyname="maxActive"value="5"></property>
- <propertyname="defaultAutoCommit"value="true"></property>
- <propertyname="removeAbandonedTimeout"value="600"></property>
<property name="maxIdle" value="5"></property>
<property name="maxWait" value="5000"></property>
<property name="maxActive" value="5"></property>
<property name="defaultAutoCommit" value="true"></property>
<property name="removeAbandonedTimeout" value="600"></property>
问题其实依旧。
所以我才有继续寻找问题关键,终于找到是上面session没有关闭,这个问题。关于session没关闭,网上有文章说也可以通过spring事务解决,即把这个连接让spring管理即可,并且响应速度似乎更快。这个我没有去试。在以后的学习中,再深入学习spring吧。
不过庆幸的是,分页函数问题终于解决了,如哪位大哥看到还有问题还望不吝指导。
后面action、jsp页面就简单了。希望大家都别再犯和我一样的错误了。
在action中,我定义了三个变量就好(我在网上看到许多文章中自己创建的pageBean那么多的变量,那么多的函数,我十分的头大....)
private Integer page;//当前页
private Integer allpage;//总页数
private Integer pageSize=5;//每页显示的数目
生成get,set方法。我习惯用session,在action返回值前,写上了
session.put("allPage", allPage);
session.put("page", page);
其中,当第一次打开主题页,肯定显示第一页,所以action里,如果page=null,就把page=1,其中计算总页数方法:
allPage= (allRows+pageSize-1) / pageSize; // 计算总页数,allRows是符合条件的记录总数
当打开第一页的主题页后,即这个jsp文件,使用el表达式做一些if,else判断,总的说,当存在上一页或下一页,则显示出超链接,不然则无链接,且颜色显示也不一样。
代码如下:
[html]view plaincopyprint?
- <center>
- 总共有${allPage}页, 当前是第${page}页
- <c:choose>
- <c:whentest="${allPage<=1}">
- <fontsize="2"color="blue">首页</font>
- <fontsize="2"color="blue">上一页</font>
- <fontsize="2"color="blue">下一页</font>
- <fontsize="2"color="blue">末页</font>
- </c:when>
- <c:otherwise>
- <c:choose>
- <c:whentest="${page==1}">
- <fontsize="2"color="blue">首页</font></a>
- <fontsize="2"color="blue">上一页</font></a>
- <ahref="post_viewTopics.action?page=${page+1}&boardName=<%=str%>"><fontsize="2"color="red">下一页</font></a>
- <ahref="post_viewTopics.action?page=${allPage}&boardName=<%=str%>"><fontsize="2"color="red">末页</font></a>
- </c:when>
- <c:whentest="${page>1}">
- <ahref="post_viewTopics.action?page=1&boardName=<%=str%>"><fontsize="2"color="red">首页</font></a>
- <ahref="post_viewTopics.action?page=${page-1}&boardName=<%=str%>"><fontsize="2"color="red">上一页</font></a>
- <c:choose>
- <c:whentest="${allPage>page}">
- <ahref="post_viewTopics.action?page=${page+1}&boardName=<%=str%>"><fontsize="2"color="red">下一页</font></a>
- <ahref="post_viewTopics.action?page=${allPage}&boardName=<%=str%>"><fontsize="2"color="red">末页</font></a>
- </c:when>
- <c:otherwise>
- <fontsize="2"color="blue">下一页</font>
- <fontsize="2"color="blue">末页</font>
- </c:otherwise>
- </c:choose>
- </c:when>
- </c:choose>
- </c:otherwise>
- </c:choose>
- </center>
<center>
总共有${allPage}页, 当前是第${page}页
<c:choose>
<c:when test="${allPage<=1}">
<font size="2" color="blue">首页</font>
<font size="2" color="blue">上一页</font>
<font size="2" color="blue">下一页</font>
<font size="2" color="blue">末页</font>
</c:when>
<c:otherwise>
<c:choose>
<c:when test="${page==1}">
<font size="2" color="blue">首页</font></a>
<font size="2" color="blue">上一页</font></a>
<a href="post_viewTopics.action?page=${page+1}&boardName=<%=str%>"><font size="2" color="red">下一页</font></a>
<a href="post_viewTopics.action?page=${allPage}&boardName=<%=str%>"><font size="2" color="red">末页</font></a>
</c:when>
<c:when test="${page>1}">
<a href="post_viewTopics.action?page=1&boardName=<%=str%>"><font size="2" color="red">首页</font></a>
<a href="post_viewTopics.action?page=${page-1}&boardName=<%=str%>"><font size="2" color="red">上一页</font></a>
<c:choose>
<c:when test="${allPage>page}">
<a href="post_viewTopics.action?page=${page+1}&boardName=<%=str%>"><font size="2" color="red">下一页</font></a>
<a href="post_viewTopics.action?page=${allPage}&boardName=<%=str%>"><font size="2" color="red">末页</font></a>
</c:when>
<c:otherwise>
<font size="2" color="blue">下一页</font>
<font size="2" color="blue">末页</font>
</c:otherwise>
</c:choose>
</c:when>
</c:choose>
</c:otherwise>
</c:choose>
</center>
这样就可以了。当然,这里似乎缺少一个gotoPage的实现,但这个只是调用action时page赋值不一样而已,就不想费时间做了。