我的程序是个socketserver,在程序启动时建立个oracle的数据库连接池,与oracle各持长连接,但有时因为网络原因,与oracle的连接会被阻断,在程序写了重新连接的代码,但在执行时却不能重新连接,报错:
java.sql.SQLException: Io 异常: Broken pipe
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
at oracle.jdbc.driver.OracleConnection.commit(OracleConnection.java:13811)
下面是我的连接池类
package jserver;import java.sql.*;
import java.util.*;
import jserver.tools.ErrorLog;public class ConnectionMgr {//连接池管理
public ConnectionMgr() {
try {
jbInit();
} catch (Exception ex) {
ex.printStackTrace();
}
} String driver;
String url;
String user;
String pwd;
int initCon; //连接池中初始创建连接数
int maxCon; //连接池中允许创建的最大的连接数
int incCon; //请求连接数大于活动连接数时，允许请求用户创建的连接数（必须大于0）
int currCon; //当前活动连接数，包括已经从连接池中分配出去的连接和连接池中的可分配连接
PreparedStatement pstmt;
Hashtable connectionPool;
Hashtable releaseconnectionPool;
int overtime = 60; public ConnectionMgr(String driver, String url, String user, String pwd,
int initCon, int maxCon, int incCon) {
connectionPool = new Hashtable();
releaseconnectionPool = new Hashtable();
this.driver = driver;
this.url = url;
this.user=user;
this.pwd = pwd;
this.initCon = initCon;
this.maxCon = maxCon;
this.incCon = incCon;
init();
} public void init() {
try {
System.out.println(this.url);
System.out.println(this.user);
System.out.println(this.pwd);
Class.forName(driver);
System.out.println(driver);
for (int i = 0; i < initCon; i++) {
try {
Connection con = DriverManager.getConnection(this.url, this.user,
this.pwd);
connectionPool.put(con, new Integer(0));
}
catch (Exception e) {
e.printStackTrace();
System.err.println("ConnectionMgr.init():faild to create connection " +
i);
}
if (i == 10) {
break;
}
}
}
catch (Exception e) {
ErrorLog.writeLog(e);
System.err.println("connectionMgr.init():faild to load jdbc driver:" +
driver);
}
currCon = connectionPool.size();
} public synchronized Connection requestCon() throws Exception {
Connection con = null;
Enumeration eConnections;
con = null;
eConnections = connectionPool.keys();
int member = 0;
if (eConnections.hasMoreElements()) {
con = (Connection) eConnections.nextElement();
if (con.isClosed()) {
connectionPool.remove(con);
currCon--;
//递归调用自己，尝试再次获取可用连接
con = requestCon();
}
else if (con == null) {
con = requestCon();
}
else {
//成功请求连接
connectionPool.remove(con);
releaseconnectionPool.put(con, new java.util.Date());
System.out.println("requestCon:" + con);
}
}
else {
if (currCon >= maxCon) {
boolean overtimecon = false;
eConnections = releaseconnectionPool.keys();
for (; eConnections.hasMoreElements(); ) {
con = (Connection) eConnections.nextElement();
java.util.Date date = (java.util.Date) releaseconnectionPool.get(con);
java.util.Date crrdate = new java.util.Date();
long time = (crrdate.getTime() - date.getTime()) / 1000;
if (time > overtime && con != null) {
releaseconnectionPool.remove(con);
connectionPool.put(con, new Integer(0));
overtimecon = true;
}
}
con = null;
if (overtimecon) {
con = requestCon();
}
else {
System.err.println("No connection available!");
Exception e = new Exception("No connection available!");
throw e;
}
}
else {
int iTotal = currCon + incCon;
iTotal = iTotal <= maxCon ? iTotal : maxCon;
try {
for (int i = currCon; i < iTotal; i++) {
con = DriverManager.getConnection(url, user, pwd);
connectionPool.put(con, new Integer(0));
currCon++;
System.out.println("Create connection " + i + " :" + con);
member++;
if (member == 10) {
break;
} }
//递归调用自己，尝试再次获取可用连接
}
catch (Exception e) {
ErrorLog.writeLog(e);
System.err.println(
"connectionMgr.requestCon():failed to create connection " +
(currCon + 1));
}
finally {
//con = requestCon(); }
}
}
return con;
}/////
public synchronized void releaseCon(Connection con) {
try {
if (con != null) {
if (con.isClosed()) {
currCon--;
releaseconnectionPool.remove(con);
}
else {
releaseconnectionPool.remove(con);
connectionPool.put(con, new Integer(0));
}
}
}
catch (Exception e) {ErrorLog.writeLog(e);}
finally {
System.out.println("releaseCon:" + con);
}
} public void closeConnections() throws Exception {
System.out.println("closing connection......");
Enumeration[] Connections = {
connectionPool.keys(), releaseconnectionPool.keys()};
for (int i = 0; i < Connections.length; i++) {
while (Connections[i].hasMoreElements()) {
Connection con = (Connection) Connections[i].nextElement();
con.close();
}
}
currCon = 0;
connectionPool.clear();
releaseconnectionPool.clear();
} private void jbInit() throws Exception {
}
}package jserver;import java.sql.*;
//import java.io.*;
//import oracle.sql.*;
//import oracle.jdbc.driver.*;
import jserver.tools.*;public class DBConnection {//连接调用
final String DbServer = "192.192.192.162"; //数据库服务器IP地址
final String SPort = "1521"; //数据库服务器端口
final String SID = "test"; //数据库服务器server ID
final String UserId = "shp"; //数据库用户名
final String Password = "shp"; //用户密码
final String Password =FileConf.DBPass;
final String sDBDriver = "oracle.jdbc.driver.OracleDriver";
String sConnStr = "jdbc:oracle:thin:@" + DbServer + ":"+SPort+":" + SID;
public static ConnectionMgr dbconnectionMgr = null;
Connection conn = null;
Statement stmt = null;
CallableStatement stmtproc=null;
ResultSet rs = null;
int initCon = 6; //the connection number ,init when application start
int maxCon = 40; //the max connection number
int incCon = 6; //the connection incteasing number
boolean autoCommit = true;
public DBConnection() {
if (dbconnectionMgr == null) {
dbconnectionMgr = new ConnectionMgr(sDBDriver, sConnStr, UserId, Password,
initCon, maxCon, incCon);
}
} public void setAutoCommit(boolean autoCommit) throws Exception {
if (conn == null) {
conn = dbconnectionMgr.requestCon();
}
conn.setAutoCommit(autoCommit);
this.autoCommit = autoCommit;
} public void commit() throws Exception {
if (conn != null) {
conn.commit();
}
} public void rollback() throws Exception {
if (conn != null) {
conn.rollback();
}
}
public CallableStatement excuteProcedures(String proc) throws Exception{
stmtproc=null;
int intP=0;
try{
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn==null){
if(!this.autoCommit){
Exception ep=new Exception("Connection Error!");
throw ep;
}else{
conn=dbconnectionMgr.requestCon();
}
}
stmtproc=conn.prepareCall(proc);
}catch(Exception e){ErrorLog.writeLog(e);
e.printStackTrace();
}
return stmtproc;
} public Connection getConnection() throws Exception {
if (conn == null) {
conn = dbconnectionMgr.requestCon();
}
if (!this.autoCommit) {
conn.setAutoCommit(autoCommit);
}
return conn;
} public void close() throws Exception {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
if (!this.autoCommit) {
conn.setAutoCommit(autoCommit);
this.autoCommit = true;
}
dbconnectionMgr.releaseCon(conn);
conn = null;
}
}
catch (SQLException e) {ErrorLog.writeLog(e);
//System.err.println("DBConnection.close():" + e.getMessage());
throw e;
}
}
}
java.sql.SQLException: Io 异常: Broken pipe
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
at oracle.jdbc.driver.OracleConnection.commit(OracleConnection.java:13811)
下面是我的连接池类
package jserver;import java.sql.*;
import java.util.*;
import jserver.tools.ErrorLog;public class ConnectionMgr {//连接池管理
public ConnectionMgr() {
try {
jbInit();
} catch (Exception ex) {
ex.printStackTrace();
}
} String driver;
String url;
String user;
String pwd;
int initCon; //连接池中初始创建连接数
int maxCon; //连接池中允许创建的最大的连接数
int incCon; //请求连接数大于活动连接数时，允许请求用户创建的连接数（必须大于0）
int currCon; //当前活动连接数，包括已经从连接池中分配出去的连接和连接池中的可分配连接
PreparedStatement pstmt;
Hashtable connectionPool;
Hashtable releaseconnectionPool;
int overtime = 60; public ConnectionMgr(String driver, String url, String user, String pwd,
int initCon, int maxCon, int incCon) {
connectionPool = new Hashtable();
releaseconnectionPool = new Hashtable();
this.driver = driver;
this.url = url;
this.user=user;
this.pwd = pwd;
this.initCon = initCon;
this.maxCon = maxCon;
this.incCon = incCon;
init();
} public void init() {
try {
System.out.println(this.url);
System.out.println(this.user);
System.out.println(this.pwd);
Class.forName(driver);
System.out.println(driver);
for (int i = 0; i < initCon; i++) {
try {
Connection con = DriverManager.getConnection(this.url, this.user,
this.pwd);
connectionPool.put(con, new Integer(0));
}
catch (Exception e) {
e.printStackTrace();
System.err.println("ConnectionMgr.init():faild to create connection " +
i);
}
if (i == 10) {
break;
}
}
}
catch (Exception e) {
ErrorLog.writeLog(e);
System.err.println("connectionMgr.init():faild to load jdbc driver:" +
driver);
}
currCon = connectionPool.size();
} public synchronized Connection requestCon() throws Exception {
Connection con = null;
Enumeration eConnections;
con = null;
eConnections = connectionPool.keys();
int member = 0;
if (eConnections.hasMoreElements()) {
con = (Connection) eConnections.nextElement();
if (con.isClosed()) {
connectionPool.remove(con);
currCon--;
//递归调用自己，尝试再次获取可用连接
con = requestCon();
}
else if (con == null) {
con = requestCon();
}
else {
//成功请求连接
connectionPool.remove(con);
releaseconnectionPool.put(con, new java.util.Date());
System.out.println("requestCon:" + con);
}
}
else {
if (currCon >= maxCon) {
boolean overtimecon = false;
eConnections = releaseconnectionPool.keys();
for (; eConnections.hasMoreElements(); ) {
con = (Connection) eConnections.nextElement();
java.util.Date date = (java.util.Date) releaseconnectionPool.get(con);
java.util.Date crrdate = new java.util.Date();
long time = (crrdate.getTime() - date.getTime()) / 1000;
if (time > overtime && con != null) {
releaseconnectionPool.remove(con);
connectionPool.put(con, new Integer(0));
overtimecon = true;
}
}
con = null;
if (overtimecon) {
con = requestCon();
}
else {
System.err.println("No connection available!");
Exception e = new Exception("No connection available!");
throw e;
}
}
else {
int iTotal = currCon + incCon;
iTotal = iTotal <= maxCon ? iTotal : maxCon;
try {
for (int i = currCon; i < iTotal; i++) {
con = DriverManager.getConnection(url, user, pwd);
connectionPool.put(con, new Integer(0));
currCon++;
System.out.println("Create connection " + i + " :" + con);
member++;
if (member == 10) {
break;
} }
//递归调用自己，尝试再次获取可用连接
}
catch (Exception e) {
ErrorLog.writeLog(e);
System.err.println(
"connectionMgr.requestCon():failed to create connection " +
(currCon + 1));
}
finally {
//con = requestCon(); }
}
}
return con;
}/////
public synchronized void releaseCon(Connection con) {
try {
if (con != null) {
if (con.isClosed()) {
currCon--;
releaseconnectionPool.remove(con);
}
else {
releaseconnectionPool.remove(con);
connectionPool.put(con, new Integer(0));
}
}
}
catch (Exception e) {ErrorLog.writeLog(e);}
finally {
System.out.println("releaseCon:" + con);
}
} public void closeConnections() throws Exception {
System.out.println("closing connection......");
Enumeration[] Connections = {
connectionPool.keys(), releaseconnectionPool.keys()};
for (int i = 0; i < Connections.length; i++) {
while (Connections[i].hasMoreElements()) {
Connection con = (Connection) Connections[i].nextElement();
con.close();
}
}
currCon = 0;
connectionPool.clear();
releaseconnectionPool.clear();
} private void jbInit() throws Exception {
}
}package jserver;import java.sql.*;
//import java.io.*;
//import oracle.sql.*;
//import oracle.jdbc.driver.*;
import jserver.tools.*;public class DBConnection {//连接调用
final String DbServer = "192.192.192.162"; //数据库服务器IP地址
final String SPort = "1521"; //数据库服务器端口
final String SID = "test"; //数据库服务器server ID
final String UserId = "shp"; //数据库用户名
final String Password = "shp"; //用户密码
final String Password =FileConf.DBPass;
final String sDBDriver = "oracle.jdbc.driver.OracleDriver";
String sConnStr = "jdbc:oracle:thin:@" + DbServer + ":"+SPort+":" + SID;
public static ConnectionMgr dbconnectionMgr = null;
Connection conn = null;
Statement stmt = null;
CallableStatement stmtproc=null;
ResultSet rs = null;
int initCon = 6; //the connection number ,init when application start
int maxCon = 40; //the max connection number
int incCon = 6; //the connection incteasing number
boolean autoCommit = true;
public DBConnection() {
if (dbconnectionMgr == null) {
dbconnectionMgr = new ConnectionMgr(sDBDriver, sConnStr, UserId, Password,
initCon, maxCon, incCon);
}
} public void setAutoCommit(boolean autoCommit) throws Exception {
if (conn == null) {
conn = dbconnectionMgr.requestCon();
}
conn.setAutoCommit(autoCommit);
this.autoCommit = autoCommit;
} public void commit() throws Exception {
if (conn != null) {
conn.commit();
}
} public void rollback() throws Exception {
if (conn != null) {
conn.rollback();
}
}
public CallableStatement excuteProcedures(String proc) throws Exception{
stmtproc=null;
int intP=0;
try{
if(stmt!=null){
stmt.close();
stmt=null;
}
if(conn==null){
if(!this.autoCommit){
Exception ep=new Exception("Connection Error!");
throw ep;
}else{
conn=dbconnectionMgr.requestCon();
}
}
stmtproc=conn.prepareCall(proc);
}catch(Exception e){ErrorLog.writeLog(e);
e.printStackTrace();
}
return stmtproc;
} public Connection getConnection() throws Exception {
if (conn == null) {
conn = dbconnectionMgr.requestCon();
}
if (!this.autoCommit) {
conn.setAutoCommit(autoCommit);
}
return conn;
} public void close() throws Exception {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
if (!this.autoCommit) {
conn.setAutoCommit(autoCommit);
this.autoCommit = true;
}
dbconnectionMgr.releaseCon(conn);
conn = null;
}
}
catch (SQLException e) {ErrorLog.writeLog(e);
//System.err.println("DBConnection.close():" + e.getMessage());
throw e;
}
}
}
1.参数化
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test"; //协议；库或服务器名称；服务器IP，端口
String username = "root";
String password="";
/* Oracle的连接
String driverName = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.0.23:1521:ora10g";
String username = "openlab";
String password="open123";*/ /* SQL Server的连接
String driverName = "com.microsoft.jdbc.SQL Server.SQL ServerDriver";
String url = "jdbc:microsoft:SQL Server://localhost:1433:DatabaseName=test";
String username = "openlab";
String password="open123";*/
//以下这些都需要写在有异常的代码块里，所以需要提取出来。
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;//建议用PreparedStatement
2.加载和注册数据库驱动
Class.forName(driverName);//自动注册；需要把驱动的jar包导进来；需处理异常
/*方法二：实例化具体的Driver驱动，这写法一般不用(不能参数化驱动名，不够灵活)
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver); //将驱动交于DriverManager托管*/
/*方法三：Dos运行时，java -Djdbc.drives = oracle.jdbc.driver.OracleDriver; --可多个 */
3.连接数据库
conn = DriverManager.getConnection(url, username, password);//需处理异常
//Connection返回数据库连接，如：“com.mysql.jdbc.Connection@1ffb8dc”；连接不成功则返回 null
4.创建Statement对象 //为了类型安全和批量更新的效率，改用PreparedStatement
stmt = conn.createStatement();//需处理异常
//返回其生成结果的对象"oracle.jdbc.driver.OracleStatement@198dfaf"
5.操作数据库，执行SQL语句
String sql = "select * from tableName";//SQL语句里不需要写分号
rs = stmt.executeQuery(sql); //executeQuery(sqlString) 查询 返回查询结果集
/* String sql = "insert into tableName values(?,?)"; // ?占位符
int number = stmt.executeUpdate(sql);//更新，再返回int(更新、修改影响的条数) */
6.处理数据(游标)
StringBuffer sb = new StringBuffer(); //缓存；用它可提高读取速度。当然，不用也可以。
ResultSetMetaData md = rs.getMetaData(); //ResultSetMetaData可获取列的类型和属性信息
int col = md.getColumnCount(); //获取列的数目
while(rs.next()){ //rs.next()使游标下移一位，返回boolean，没有下一个结果则返回false
for(int i=1; i<=col;i++){ // index(JDBC 的下标从1开始)
sb.append(md.getColumnName(i)+"="+rs.getString(i)+" ");
} sb.append("\n");
}System.out.println(sb);
//1.游标的初始位置在第一条记录的前面，使第一次调用next()后，刚好拿到第一个结果。
//2.游标的最终位置在最后一条记录的后面(结果集的前面和后面留空，真正存在)
7.释放资源，断开与数据库的连接
//先判断是否有引用资源，再释放(释放空资源会抛异常)；注意顺序
if(rs!=null)try{rs.close();}catch(Exception e){e.printStackTrace();}
if(stmt!=null)try{stmt.close();}catch(Exception e){e.printStackTrace();}
if(conn!=null)try{conn.close();}catch(Exception e){e.printStackTrace();}
//这些异常没法处理，处理只为方便调试。所以这些异常处理也只是打印。
/*要按先ResultSet结果集，后Statement，最后Connection的顺序关闭资源，
因为ResultSet需要Statement和Connection连接时才可以用的；Statement也需要Connection才可用；
结束Statement之后有可能其它的Statement还需要连接，因此不能先关闭Connection。ResultSet同理。*/ 步骤 2、3、7 每次都一样，可以重构。
因为加载驱动是个一次性工作，所以可以采用静态初始化块来加载驱动；
连接数据库的方法应该自己负责，获取数据库连接信息和驱动的信息，并处理相关异常；
释放数据库资源的方法要考虑到ResultSet、Statement、Connection的不同情况，并处理相关异常。
-------------------------------------
上班中，没时间帮LZ分析，自己看看代码，别搞错就行
同事分享下我以前做的一个例子。。
这句应该反0改为i以在循环中不改为0将只创建最后一个Connection.
还有connectionPool.remove(con); 当向连接池取时直接remove掉。但在关闭时没有还回来。
OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();ocpds.setDriverType("oci");
ocpds.setServerName("dlsun999");
ocpds.setNetworkProtocol("tcp");
ocpds.setDatabaseName("816");
ocpds.setPortNumber(1521);
ocpds.setUser("scott");
ocpds.setPassword("tiger");PooledConnection pc = ocpds.getPooledConnection();Connection conn = pc.getConnection();