我用的是apache的dbcp数据库连接池。 当数据库重启,或者网络中断一段时间后恢复,此时数据库连接不可用
,再怎么连接也连接不上了,只有重启才行,怎么解决啊?这个程序是不断的从数据库取东西的
下面是大概的代码import java.sql.*;
import java.util.*;
import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DataSourceConnectionFactory;public class CommDAO {
private boolean isSus=false;
DataSource dataSource;
private static ConnectionFactory fac=null; public CommDAO(){
dataSource = setupDataSource();
}
/**
* 连接配置信息
*/ public Connection getConnection() throws SQLException
{
Connection c = fac.createConnection();
return c;
}
/**
*查询记录
*@param String sql 要查询的SQL语句
*@return String 返回一个字符串, 取sql的第一行、第一列
*/
public String getOneString(String sql){ Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
String str_return="";
try{
conn=getConnection();
pstm=conn.prepareStatement(sql);
rs=pstm.executeQuery();
if(rs.next()){
if(rs.getString(1)!=null)
str_return=rs.getString(1);
}
}
catch(Exception e){
System.out.println(sql);
e.printStackTrace(System.out);
}finally{
try{
if(rs!=null)rs.close();
if(pstm!=null)pstm.close();
if(conn!=null)conn.close();
}catch(Exception nex){System.err.print(nex);};
}
return str_return;
}
public static DataSource setupDataSource() {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
ds.setUsername("sa");
ds.setPassword("*****");
ds.setUrl("jdbc:microsoft:sqlserver://10.1.1.2:1433;DatabaseName=pubs");
ds.setInitialSize(5); //初始化连接数量
ds.setMaxIdle(10); //最大idle数
ds.setMaxWait(1000*6); //超时回收时间
fac=new DataSourceConnectionFactory(ds); //得到连接工厂
return ds;
} public static void printDataSourceStats(DataSource ds) throws SQLException {
BasicDataSource bds = (BasicDataSource) ds;
System.out.println("NumActive: " + bds.getNumActive());
System.out.println("NumIdle: " + bds.getNumIdle());
} public static void shutdownDataSource(DataSource ds) throws SQLException {
BasicDataSource bds = (BasicDataSource) ds;
bds.close();
}
public static void main(String[] args){
CommDAO c = new CommDAO();
for(int i=0 ;i<100;i++){
String a = c.getOneString("select * from employee");
System.out.println(a);
a = c.getOneString("select * from sales");
System.out.println(a);
a = c.getOneString("select * from authors");
System.out.println(a);
try{Thread.sleep(1000*3);}catch(Exception ex){}
}
}
,再怎么连接也连接不上了,只有重启才行,怎么解决啊?这个程序是不断的从数据库取东西的
下面是大概的代码import java.sql.*;
import java.util.*;
import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DataSourceConnectionFactory;public class CommDAO {
private boolean isSus=false;
DataSource dataSource;
private static ConnectionFactory fac=null; public CommDAO(){
dataSource = setupDataSource();
}
/**
* 连接配置信息
*/ public Connection getConnection() throws SQLException
{
Connection c = fac.createConnection();
return c;
}
/**
*查询记录
*@param String sql 要查询的SQL语句
*@return String 返回一个字符串, 取sql的第一行、第一列
*/
public String getOneString(String sql){ Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
String str_return="";
try{
conn=getConnection();
pstm=conn.prepareStatement(sql);
rs=pstm.executeQuery();
if(rs.next()){
if(rs.getString(1)!=null)
str_return=rs.getString(1);
}
}
catch(Exception e){
System.out.println(sql);
e.printStackTrace(System.out);
}finally{
try{
if(rs!=null)rs.close();
if(pstm!=null)pstm.close();
if(conn!=null)conn.close();
}catch(Exception nex){System.err.print(nex);};
}
return str_return;
}
public static DataSource setupDataSource() {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
ds.setUsername("sa");
ds.setPassword("*****");
ds.setUrl("jdbc:microsoft:sqlserver://10.1.1.2:1433;DatabaseName=pubs");
ds.setInitialSize(5); //初始化连接数量
ds.setMaxIdle(10); //最大idle数
ds.setMaxWait(1000*6); //超时回收时间
fac=new DataSourceConnectionFactory(ds); //得到连接工厂
return ds;
} public static void printDataSourceStats(DataSource ds) throws SQLException {
BasicDataSource bds = (BasicDataSource) ds;
System.out.println("NumActive: " + bds.getNumActive());
System.out.println("NumIdle: " + bds.getNumIdle());
} public static void shutdownDataSource(DataSource ds) throws SQLException {
BasicDataSource bds = (BasicDataSource) ds;
bds.close();
}
public static void main(String[] args){
CommDAO c = new CommDAO();
for(int i=0 ;i<100;i++){
String a = c.getOneString("select * from employee");
System.out.println(a);
a = c.getOneString("select * from sales");
System.out.println(a);
a = c.getOneString("select * from authors");
System.out.println(a);
try{Thread.sleep(1000*3);}catch(Exception ex){}
}
}
<local-tx-datasource>
……
<!--sql to call when connection is created-->
<check-valid-connection-sql>select 1 from sysobjects</check-valid-connection-sql>
</local-tx-datasource>
</datasources>