通过以下DAO操作proxool连接池访问量大的时候,老是有问题?DAO如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.CallableStatement;public final class DAO implements DBsource,DAO
{
private static Connection conn=null;
private Statement st=null;
ResultSet rs=null;
PreparedStatement ps=null;
CallableStatement cs=null;
public DAO()
{
}
static{
try{
Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
}catch(Exception ex){
ex.printStackTrace();
}
}
private void getConn()
{
try{
conn = DriverManager.getConnection("proxool.xml-test");
//conn = DriverManager.getConnection(Url, username, password);
//conn.setAutoCommit(false);//支持事务
}catch (Exception ex){
ex.printStackTrace();
}
}
//负责建立状态通道
private Statement getStatement(){
try {
if(conn==null||conn.isClosed())
{
getConn();
}
st = conn.createStatement();
if(st==null)
{
st=getStatement();
}
}catch (SQLException ex) {
ex.printStackTrace();
}
return st;
}
//
public boolean doBatch(List SQLs){
boolean is=false;
getStatement();
try{
for(int i=0;i<SQLs.size();i++){
st.addBatch(SQLs.get(i).toString());
}
int[] a=st.executeBatch();
// for(int i=0;i<a.length;i++){
// //System.out.println(a[i]+"记录");
// }
is=true;
//conn.commit();
}catch(SQLException ex) {
ex.printStackTrace();
//conn.rollback();
}finally{
Close();
return is;
}
}
//下面是使用预通道的通用方法
public ResultSet execQuery(String sql,String[] args){
try {
if(conn == null || conn.isClosed()) {
getConn();
ps=conn.prepareStatement(sql);
for(int i=1;i<=args.length;i++){
ps.setString(i,args[i-1]);
}
rs=ps.executeQuery();
}
}catch (SQLException ex) {
ex.printStackTrace();
}
return rs;
}
public ResultSet execQuery(String sql,List args)
{
try {
if(conn == null || conn.isClosed()) {
getConn();
ps=conn.prepareStatement(sql);
if(ps==null)
{
getConn();
ps=conn.prepareStatement(sql);
}
if(args!=null)
{
for(int i=1;i<=args.size();i++)
{
ps.setString(i,args.get(i-1).toString());
}
}
rs=ps.executeQuery();
}
}catch (SQLException ex) {
ex.printStackTrace();
}
return rs;
}
public boolean execOperate(String sql,String[] args)
{
boolean isCorrect=false;
try {
if(conn==null||conn.isClosed())getConn();
ps=conn.prepareStatement(sql);
for(int i=1;i<=args.length;i++){
Object Objvalue=args[i-1];
if(Objvalue==null)
ps.setString(i,null);
else
ps.setString(i,Objvalue.toString());
}
int a=ps.executeUpdate();
////System.out.println(a+"记录");
isCorrect=true;
//conn.commit();
}catch (SQLException ex) {
ex.printStackTrace();
}finally{
Close();
}
return isCorrect;
}
//查询
public ResultSet doQuery(String sql)
{
try{
rs=getStatement().executeQuery(sql);
}catch (SQLException ex) {
ex.printStackTrace();
}
return rs;
}
//新增insert into 、、、、、、
public boolean doInsert(String sql)
{
boolean isCorrect=false;
try{
getStatement().execute(sql);//true false(执行增删改查sql)
isCorrect=true;
//conn.commit();
}catch(SQLException ex){
ex.printStackTrace();
//conn.rollback();
}finally{
Close();
return isCorrect;
}
} public ResultSet execMethod(String sql)
{
try{
boolean is=getStatement().execute(sql);//true false(执行增删改查sql)
if(is==true)rs=st.getResultSet();
}catch(SQLException ex){
ex.printStackTrace();
}finally{
return rs;
}
} //修改和删除
public boolean doUpdate(String sql)
{
boolean isCorrect=false;
try{
int a=getStatement().executeUpdate(sql);//int
////System.out.println(a+"记录!");
isCorrect=true;
//conn.commit();
}catch(SQLException e){
e.printStackTrace();
//conn.rollback();
}finally{
Close();
return isCorrect;
}
}
//得到总记录数select count(*) from dept
public int getCount(String sql)
{
int count=0;
try{
rs=getStatement().executeQuery(sql);
if(rs.next())
{
count=rs.getInt(1);
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return count;
}
}
public String getOneValue(String sql)
{
String count="";
try{
rs=getStatement().executeQuery(sql);
if(rs.next())
{
count=rs.getString(1);
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return count;
}
}
public int getPK(String tablename,String pkname)
{
int pk=0;
String sql="select max("+pkname+") from "+tablename;
try{
rs=this.doQuery(sql);
rs.next();
pk=rs.getInt(1)+1;
}catch(Exception e){
e.printStackTrace();
}finally{
this.Close();
return pk;
}
}
public List getCols(String sql)
{
List list=new ArrayList();
try{
rs=this.doQuery(sql);
int colcount=rs.getMetaData().getColumnCount();
while(rs.next())
{
for(int i=1;i<=colcount;i++)
{
list.add(rs.getString(i));
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return list;
}
}
public List getCols(String sql,List args)
{
List list=new ArrayList();
try{
rs=this.execQuery(sql, args);
int colcount=rs.getMetaData().getColumnCount();
while(rs.next())
{
for(int i=1;i<=colcount;i++)
{
list.add(rs.getString(i));
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return list;
}
}
/************************************************************************************/
public void Close()
{
try{
if(rs!=null)rs.close();
//if(st!=null)st.close();
if(conn!=null)conn.close();
//if(ps!=null)ps.close();
if(cs!=null)cs.close();
}catch (SQLException ex) {
ex.printStackTrace();
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.CallableStatement;public final class DAO implements DBsource,DAO
{
private static Connection conn=null;
private Statement st=null;
ResultSet rs=null;
PreparedStatement ps=null;
CallableStatement cs=null;
public DAO()
{
}
static{
try{
Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
}catch(Exception ex){
ex.printStackTrace();
}
}
private void getConn()
{
try{
conn = DriverManager.getConnection("proxool.xml-test");
//conn = DriverManager.getConnection(Url, username, password);
//conn.setAutoCommit(false);//支持事务
}catch (Exception ex){
ex.printStackTrace();
}
}
//负责建立状态通道
private Statement getStatement(){
try {
if(conn==null||conn.isClosed())
{
getConn();
}
st = conn.createStatement();
if(st==null)
{
st=getStatement();
}
}catch (SQLException ex) {
ex.printStackTrace();
}
return st;
}
//
public boolean doBatch(List SQLs){
boolean is=false;
getStatement();
try{
for(int i=0;i<SQLs.size();i++){
st.addBatch(SQLs.get(i).toString());
}
int[] a=st.executeBatch();
// for(int i=0;i<a.length;i++){
// //System.out.println(a[i]+"记录");
// }
is=true;
//conn.commit();
}catch(SQLException ex) {
ex.printStackTrace();
//conn.rollback();
}finally{
Close();
return is;
}
}
//下面是使用预通道的通用方法
public ResultSet execQuery(String sql,String[] args){
try {
if(conn == null || conn.isClosed()) {
getConn();
ps=conn.prepareStatement(sql);
for(int i=1;i<=args.length;i++){
ps.setString(i,args[i-1]);
}
rs=ps.executeQuery();
}
}catch (SQLException ex) {
ex.printStackTrace();
}
return rs;
}
public ResultSet execQuery(String sql,List args)
{
try {
if(conn == null || conn.isClosed()) {
getConn();
ps=conn.prepareStatement(sql);
if(ps==null)
{
getConn();
ps=conn.prepareStatement(sql);
}
if(args!=null)
{
for(int i=1;i<=args.size();i++)
{
ps.setString(i,args.get(i-1).toString());
}
}
rs=ps.executeQuery();
}
}catch (SQLException ex) {
ex.printStackTrace();
}
return rs;
}
public boolean execOperate(String sql,String[] args)
{
boolean isCorrect=false;
try {
if(conn==null||conn.isClosed())getConn();
ps=conn.prepareStatement(sql);
for(int i=1;i<=args.length;i++){
Object Objvalue=args[i-1];
if(Objvalue==null)
ps.setString(i,null);
else
ps.setString(i,Objvalue.toString());
}
int a=ps.executeUpdate();
////System.out.println(a+"记录");
isCorrect=true;
//conn.commit();
}catch (SQLException ex) {
ex.printStackTrace();
}finally{
Close();
}
return isCorrect;
}
//查询
public ResultSet doQuery(String sql)
{
try{
rs=getStatement().executeQuery(sql);
}catch (SQLException ex) {
ex.printStackTrace();
}
return rs;
}
//新增insert into 、、、、、、
public boolean doInsert(String sql)
{
boolean isCorrect=false;
try{
getStatement().execute(sql);//true false(执行增删改查sql)
isCorrect=true;
//conn.commit();
}catch(SQLException ex){
ex.printStackTrace();
//conn.rollback();
}finally{
Close();
return isCorrect;
}
} public ResultSet execMethod(String sql)
{
try{
boolean is=getStatement().execute(sql);//true false(执行增删改查sql)
if(is==true)rs=st.getResultSet();
}catch(SQLException ex){
ex.printStackTrace();
}finally{
return rs;
}
} //修改和删除
public boolean doUpdate(String sql)
{
boolean isCorrect=false;
try{
int a=getStatement().executeUpdate(sql);//int
////System.out.println(a+"记录!");
isCorrect=true;
//conn.commit();
}catch(SQLException e){
e.printStackTrace();
//conn.rollback();
}finally{
Close();
return isCorrect;
}
}
//得到总记录数select count(*) from dept
public int getCount(String sql)
{
int count=0;
try{
rs=getStatement().executeQuery(sql);
if(rs.next())
{
count=rs.getInt(1);
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return count;
}
}
public String getOneValue(String sql)
{
String count="";
try{
rs=getStatement().executeQuery(sql);
if(rs.next())
{
count=rs.getString(1);
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return count;
}
}
public int getPK(String tablename,String pkname)
{
int pk=0;
String sql="select max("+pkname+") from "+tablename;
try{
rs=this.doQuery(sql);
rs.next();
pk=rs.getInt(1)+1;
}catch(Exception e){
e.printStackTrace();
}finally{
this.Close();
return pk;
}
}
public List getCols(String sql)
{
List list=new ArrayList();
try{
rs=this.doQuery(sql);
int colcount=rs.getMetaData().getColumnCount();
while(rs.next())
{
for(int i=1;i<=colcount;i++)
{
list.add(rs.getString(i));
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return list;
}
}
public List getCols(String sql,List args)
{
List list=new ArrayList();
try{
rs=this.execQuery(sql, args);
int colcount=rs.getMetaData().getColumnCount();
while(rs.next())
{
for(int i=1;i<=colcount;i++)
{
list.add(rs.getString(i));
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
Close();
return list;
}
}
/************************************************************************************/
public void Close()
{
try{
if(rs!=null)rs.close();
//if(st!=null)st.close();
if(conn!=null)conn.close();
//if(ps!=null)ps.close();
if(cs!=null)cs.close();
}catch (SQLException ex) {
ex.printStackTrace();
}
}
}
解决方案 »
- JavaMail 发邮件
- 大学毕业了 马上要做毕业设计 找工作 迷茫啊 大家帮我参考参考
- struts迭代终止
- strtus中的资源文件突然识别不到了
- mysql使用PreparedStatement的一个奇怪问题???
- 上传文件时的问题,谢谢!
- 实时显示当前时间的javascript脚本的firefox兼容问题
- ckeditor怎么获取纯文本内容
- 各位前辈来指点小弟一次吧,小弟明天面试jsp程序员
- JNI的问题:jstring => AnsiString?
- TOMCAT问题;严重: Servlet.service() for servlet jsp threw exception
- 企业portal网站
<?xml version="1.0" encoding="ISO-8859-1"?>
<!-- the proxool configuration can be embedded within your own application's.
Anything outside the "proxool" tag is ignored. -->
<something-else-entirely>
<proxool>
<alias>xml-test</alias>
<driver-url>jdbc:mysql://127.0.0.1:3306/itfuture?autoReconnect=true</driver-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<driver-properties>
<property name="user" value="root"/>
<property name="password" value="123456"/>
</driver-properties>
<maximum-connection-count>500</maximum-connection-count>
<house-keeping-test-sql>select getdate()</house-keeping-test-sql>
</proxool>
</something-else-entirely>
在jsp中没调用一次要关闭资源rs,但dao中的其它资源并不关闭,在本页面中的其它地方使用完之后再关闭,使用如下:
<%
String sql="select A.* from webcontent";
DAO dao=new DAO();
ResultSet rs=dao.doQuery(sql);
while(rs.next()){
int cid=rs.getInt("cid");
String title=rs.getString("ctitle");
%>
<tr>
<td width="20" height="22"><div align="center"><img src="images/index_19.jpg" width="6" height="6"></div></td>
<td width="175" height="22"><a href="show.jsp?cid=<%=cid%>" target="_blank" class="b12">
<%=title%></a>
</td>
</tr>
<%
}
rs.close();
sql="select A.* from webclass";
rs=dao.doQuery(sql);
while(rs.next()){
int cid=rs.getInt("cid");
String title=rs.getString("ctitle");
String infotime=rs.getString("cinfotime");
%>
<table width="100%" height="25" border="0" align="center" cellpadding="0" cellspacing="0" class="unnamed1">
<tr>
<td width="23" height="22"><div align="center"><img src="images/tubaio.jpg" width="9" height="7"></div></td>
<td width="431" height="22"><a href="show.jsp?cid=<%=cid%>" target=_blank> <%=title %> </a> </td>
<td width="107"><%=(infotime.substring(0,10))%></td>
</tr>
</table>
<%
}
rs.close();
dao.Close();
%>
但每次使用之后老是出现 在关闭Statement之后有执行操作的提示,有时得到statement的是null,系统运行两天mysql数据库就不能用了。必须重起整个系统,mysql数据库才能使用,重起tomcat和mysql,mysql还是不能用。
而且rs在JSP中已经关闭了,在dao中却又关闭一次?mysql数据库就不能用应该是proxool的连接用完了,很多有效连接被废置,这个还需要检查proxool的配置。
比如有个配置项是house-keeping-test-sql,如果这条语句配置不正确也会引发该问题,我们是用:select CURRENT_DATE
你出的错误应该是连接太多!
这样保证解决问题!
http://blog.csdn.net/rainv/archive/2007/03/14/1529270.aspx