第一版：将数据库连接池的创建一并放入到了分页类了。这样的话如果我的应用中还有其他的地方servlets也要用到这个连接池的话就不行了。所以我将数据库连接池部分抽出来当作父类处理了。
代码如下：
PoolParent.java 父类
package cn.ty.bean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import cn.ty.tools.*;
public class PoolParent {
protected static ConnectionPool connPool; //如果不是静态的话就每一个对象过来都得再重新创建
protected Statement statmeStatement;
protected Connection connection;
public PoolParent() {
init();
}
private void init() {
if (connPool == null) {
connPool = new ConnectionPool("com.mysql.jdbc.Driver","jdbc:mysql://localhost:3306/cublog","root","123");
try {
connPool.setInitialConnections(10);
connPool.setMaxConnections(50);
connPool.createPool();
this.connection = connPool.getConnection();
this.statmeStatement = this.connection.createStatement();
} catch (Exception e) {
System.out.println(e.toString());
}
} else {
try {
this.connection = connPool.getConnection();
this.statmeStatement = this.connection.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 将ResultSet转换成List封装
* */
protected List convertList(ResultSet rs) throws SQLException {
List list = new ArrayList();
ResultSetMetaData md = rs.getMetaData();
int columnCount = md.getColumnCount();
while (rs.next()) {
Map rowData = new HashMap();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i), rs.getObject(i));
}
list.add(rowData);
}
return list;
}
/**
* 显示将数据库连接对象回收
* */
public void resetDB() {
connPool.returnConnection(this.connection);
this.connection = null;
this.statmeStatement = null;
}
}
|
将子类可能会用到的方法一并抽出来放入到父类中处理了！
子类：PageBean.java
package cn.ty.bean;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import cn.ty.tools.*;
/**
* @author hkebao@126.com
* @version 1.0
* 通用的MYSQL分页BEAN
* */
public class PageBean extends PoolParent {
private int currentpage = 1;
private int totalPages = 0;
private String sqlConditions = ""; //查询的SQL条件
private int percentRecords = 1; //每页的记录数
private boolean hasNextPage = false; //是否有下一页
private boolean hasPreviousPage = false; //是否有前一页
//private Statement statmeStatement;
//private Connection connection;
private String tableName;
private String fieldName;
private String fileName;
private int recordCount;
//private static ConnectionPool connPool; //如果不是静态的话就每一个对象过来都得再重新创建
public int getCurrentpage() {
return currentpage;
}
public void setCurrentpage(int currentpage) {
this.currentpage = currentpage;
}
public int getTotalPages() {
return totalPages;
}
public void setTotalPages(int totalPages) {
this.totalPages = totalPages;
}
public String getSqlConditions() {
return sqlConditions;
}
public void setSqlConditions(String sqlConditions) {
this.sqlConditions = sqlConditions;
}
public int getPercentRecords() {
return percentRecords;
}
public void setPercentRecords(int percentRecords) {
this.percentRecords = percentRecords;
}
public boolean isHasNextPage() {
return hasNextPage;
}
public void setHasNextPage(boolean hasNextPage) {
this.hasNextPage = hasNextPage;
}
public boolean isHasPreviousPage() {
return hasPreviousPage;
}
public void setHasPreviousPage(boolean hasPreviousPage) {
this.hasPreviousPage = hasPreviousPage;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public int getRecordCount() {
return recordCount;
}
public void setRecordCount(int recordCount) {
this.recordCount = recordCount;
}
public PageBean(){
}
/**
* @see: 计算出来总的页面
* */
private void getTotalPage() {
ResultSet rSet = null;
String sqlString = "select count(*) as total from " + tableName + " where " + sqlConditions;
try {
rSet = this.statmeStatement.executeQuery(sqlString);
while(rSet.next()) recordCount = Integer.parseInt(rSet.getString("total"));
if ((recordCount % this.percentRecords) == 0) {
totalPages = recordCount / this.percentRecords;
} else {
totalPages = recordCount / this.percentRecords + 1;
}
//判断是否有下一页
if (currentpage >= totalPages) {
hasNextPage = false;
} else {
hasNextPage = true;
}
//判断是否有前一页
if ((currentpage - 1) > 0) {
hasPreviousPage = true;
} else {
hasPreviousPage = false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
rSet = null;
}
}
/**
* 计算当前所传入的字段、条件、表名、页数返回当前页的记录集.记录集合
* */
public List getCurrPageRecords() {
ResultSet rs = null;
List list = new ArrayList();
StringBuffer tmp = new StringBuffer();
if(currentpage < 1) {
return list;
}
int limit = (currentpage - 1) * percentRecords;
tmp.append("select ");
tmp.append(fieldName);
tmp.append(" from ");
tmp.append(tableName);
tmp.append(" where ");
tmp.append(sqlConditions);
tmp.append(" limit " + String.valueOf(limit) + "," + percentRecords);
String sqlString = tmp.toString();
try {
rs = this.statmeStatement.executeQuery(sqlString);
list = convertList(rs);
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 显示分页的条目 第一页..
* */
public String showPages() {
getTotalPage();
StringBuffer tmp = new StringBuffer();
tmp.append("共有文章"+String.valueOf(recordCount) + "篇");
tmp.append("第("+currentpage+"/"+totalPages+") ");
tmp.append("首页 ");
if (hasPreviousPage){
int prepage = currentpage - 1;
tmp.append("上一页 ");
}
else
tmp.append("上一页 ");
tmp.append("1 ");
tmp.append("2 ");
tmp.append("3 ");
tmp.append("4 ");
tmp.append("5 ..");
if (hasNextPage){
int prepage = currentpage + 1;
tmp.append("下一页 ");
}
else
tmp.append("下一页 ");
tmp.append("未页 ");
return tmp.toString();
}
}
|
另外一个Servlets也用到了连接池了。看代码
ContentBeanAction.java
package cn.ty.bean;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.ty.bean.ContentBean;
public class ContentBeanAction extends PoolParent{
private ContentBean contentBean = null;
public ContentBeanAction() {
contentBean = new ContentBean();
}
/**
* 依据ID值提取内容
* */
public String fetchContent(int id) {
String tmp = "";
ResultSet rs = null;
try {
rs = this.statmeStatement.executeQuery("select content from cu_blog_page where id = " + String.valueOf(id));
while(rs.next()) {
tmp = rs.getString("content");
}
} catch (SQLException e) {
e.printStackTrace();
}
return tmp;
}
}
|
JSP页面调用示例
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<jsp:useBean id="pagebean" scope="request" class="cn.util.PageBean" beanName="cn.util.PageBean"/>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<LINK href="css/adminstyle.css" type=text/css rel=stylesheet>
<script type="text/JavaScript" src="js/jquery-1.2.6.js"></script>
</head>
<body>
<div align="center">查看指定用户的缴费历史清单<a href="javascript:history.go(-1);"><b>返回前一页</b></a></div>
<TABLE class=tableborder cellSpacing=1 cellPadding=3 width="100%" align=center border=0>
<TBODY>
<TR class=header align=center>
<td width="12%" >账单编号</td>
<td width="12%" >用户名称</td>
<td width="12%" >缴费时间</td>
<td width="12%" >缴费金额($单位元)</td>
</TR>
<%
int currpage = 1;
try {
currpage = Integer.parseInt(request.getParameter("page"));
} catch(Exception e) {
currpage = 1;
}
String uuid = request.getParameter("uuid").trim();
pagebean.setSqlConditions("a.uuid = b.uuid and a.uuid='"+uuid+"'");
pagebean.setPercentRecords(1);
pagebean.setFieldName("a.names as names,a.uuid as uuid,b.moneys as moneys,b.serial as serial,b.createtime as times");
pagebean.setFileName("showaccounthistory.jsp");
pagebean.setTableName("users a ,userscz b");
pagebean.setCurrentpage(currpage);
List resultSet = pagebean.getCurrPageRecords();
Map rowData = new HashMap();
for (Iterator i = resultSet.iterator(); i.hasNext();) {
rowData = (Map)i.next();
String names = String.valueOf(rowData.get("names"));
String moneys = String.valueOf(rowData.get("moneys"));
String serial = String.valueOf(rowData.get("serial"));
String times = String.valueOf(rowData.get("times"));
%>
<tr bgcolor="#EBF2F9" align=center>
<td align="center">
<label><%=serial%></label>
</td>
<td align="center">
<label><%=names%></label>
</td>
<td align="center">
<label><%=times%></label>
</td>
<td align="center">
<label><%=moneys%></label>
</td>
</tr>
<%} %>
</tbody>
</table>
<TABLE cellSpacing=1 cellPadding=3 width="100%" bgColor=#6298e1 border=0>
<TBODY>
<TR>
<TD noWrap align=middle bgColor=#ebf2f9 height=30>
<TABLE cellSpacing=0 width="100%" border=0>
<TBODY>
<TR>
<TD><%out.print(pagebean.showPages("uuid="+uuid));%>
</TR>
</TBODY>
</TABLE>
</TD>
</TR>
</TBODY>
</TABLE>
</body>
</html>
<%pagebean.resetDB(); %>
|
阅读(860) | 评论(2) | 转发(0) |
