Java c3p0 oracle 数据库连接池 代码实现 （二）

DatabaseAccessImpl.java
package com.database;
import java.io.*;
import java.sql.*;
import java.util.*;
import org.apache.commons.logging.*;
public class DatabaseAccessImpl implements DatabaseAccessInterface {
private static Log log = LogFactory.getLog(DatabaseAccessImpl.class);
public void executeSQL(String sqlStatement) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBConnectionManager.getConnection();
connection.setAutoCommit(true);
statement = connection.prepareStatement(sqlStatement);
statement.execute();
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return;
}

public void executeSQL(String sqlStatement, Object parameters[]) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBConnectionManager.getConnection();
connection.setAutoCommit(true);
statement = connection.prepareStatement(sqlStatement);
// 获取执行数据
if (parameters != null && parameters.length > 0) {
for (int i = 0; i < parameters.length; i++) {
statement.setObject(i + 1, parameters[i]);
}
}
statement.execute();
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return;
}

public void executeSQL(String sqlStatement, List parameters) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DBConnectionManager.getConnection();
connection.setAutoCommit(true);
statement = connection.prepareStatement(sqlStatement);
// 获取执行数据
int listsize = parameters.size();
for (int i = 0; i < listsize; i++) { statement.setObject(i + 1, parameters.get(i)); }
statement.execute();
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return;
}

public void executeSQL(String[] sqlStatement) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
if (sqlStatement == null || sqlStatement.length == 0) {
throw new SQLException();
}
try {
connection = DBConnectionManager.getConnection();
connection.setAutoCommit(false);
int slength = sqlStatement.length;// 事务中sql语句的个数
for (int i = 0; i < slength; i++) {
statement = connection.prepareStatement(sqlStatement[i]);
statement.execute();
}
connection.commit();
} catch (SQLException ex) {
connection.rollback();
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
connection.rollback();
ex.printStackTrace();
} finally {
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return;
}

public void executeSQL(String[] sqlStatement, List parameters) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
if (sqlStatement == null || sqlStatement.length == 0) {
throw new SQLException();
}
try {
connection = DBConnectionManager.getConnection();
connection.setAutoCommit(false);
int slength = sqlStatement.length;// 事务中sql语句的个数
for (int i = 0; i < slength; i++) {
statement = connection.prepareStatement(sqlStatement[i]);
if (parameters != null) {
Object[] pm = (Object[]) parameters.get(i);
// 获取执行数据
int pmsize = 0;// 每条sql数据对应的参数个数
if (pm != null) {
pmsize = pm.length;
}
for (int j = 0; j < pmsize; j++) {
statement.setObject(j + 1, pm[j]);
}
}
statement.execute();
}
connection.commit();
} catch (SQLException ex) {
connection.rollback();
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
connection.rollback();
ex.printStackTrace();
} finally {
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return;
}

public Vector executeQuerySQL(String sqlStatement) throws SQLException {
Vector resultVector = new Vector();
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBConnectionManager.getConnection(); // 获取数据库连接对象
connection.setAutoCommit(false);
statement = connection.prepareStatement(sqlStatement); // 获取statement对象
statement.execute();
rs = statement.getResultSet(); // 获取查询记录集
HashMap rowItem;
for (; rs.next(); resultVector.add(rowItem)) {
rowItem = new HashMap();
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Object cell = rs.getObject(i);
if (cell == null) {
cell = "";
} else if (cell instanceof Blob) {
try {
InputStreamReader in = new InputStreamReader(((Blob) cell).getBinaryStream());
char bufferString[] = new char[5000];
int readCharCount = in.read(bufferString);
StringBuffer bolbStringBuffer = new StringBuffer("");
for (; readCharCount == 5000; readCharCount = in.read(bufferString)) {
bolbStringBuffer.append(bufferString, 0, readCharCount);
}
if (readCharCount != -1) {
bolbStringBuffer.append(bufferString, 0, readCharCount);
}
cell = bolbStringBuffer.toString();
in.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
rowItem.put(rs.getMetaData().getColumnName(i), cell);
}
}
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return resultVector;
}

public Vector executeQuerySQL(String sqlStatement, Object parameters[]) throws SQLException {
Vector resultVector = new Vector();
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBConnectionManager.getConnection(); // 获取数据库连接对象
connection.setAutoCommit(false);
statement = connection.prepareStatement(sqlStatement); // 获取statement对象
// 获取查询参数
if (parameters != null && parameters.length > 0) {
for (int i = 0; i < parameters.length; i++) {
statement.setObject(i + 1, parameters[i]);
}
}
statement.execute();
rs = statement.getResultSet(); // 获取查询记录集
HashMap rowItem;
for (; rs.next(); resultVector.add(rowItem)) {
rowItem = new HashMap();
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Object cell = rs.getObject(i);
if (cell == null) {
cell = "";
} else if (cell instanceof Blob) {
try {
InputStreamReader in = new InputStreamReader(((Blob) cell).getBinaryStream());
char bufferString[] = new char[5000];
int readCharCount = in.read(bufferString);
StringBuffer bolbStringBuffer = new StringBuffer("");
for (; readCharCount == 5000; readCharCount = in.read(bufferString)) {
bolbStringBuffer.append(bufferString, 0, readCharCount);
}
if (readCharCount != -1) {
bolbStringBuffer.append(bufferString, 0, readCharCount);
}
cell = bolbStringBuffer.toString();
in.close();
} catch (Exception ex) {
ex.printStackTrace();
log.error(ex.getMessage());
}
}
rowItem.put(rs.getMetaData().getColumnName(i), cell);
}
}
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return resultVector;
}

public Vector executeQuerySQL(String sqlStatement, List parameters) throws SQLException {
Vector resultVector = new Vector();
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
try {
connection = DBConnectionManager.getConnection(); // 获取数据库连接对象
connection.setAutoCommit(false);
statement = connection.prepareStatement(sqlStatement); // 获取statement对象
// 获取查询数据
int listsize = parameters.size();
for (int i = 0; i < listsize; i++) {
statement.setObject(i + 1, parameters.get(i));
}
statement.execute();
rs = statement.getResultSet(); // 获取查询记录集
HashMap rowItem;
for (; rs.next(); resultVector.add(rowItem)) {
rowItem = new HashMap();
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Object cell = rs.getObject(i);
if (cell == null) {
cell = "";
} else if (cell instanceof Blob) {
try {
InputStreamReader in = new InputStreamReader(((Blob) cell).getBinaryStream());
char bufferString[] = new char[5000];
int readCharCount = in.read(bufferString);
StringBuffer bolbStringBuffer = new StringBuffer("");
for (; readCharCount == 5000; readCharCount = in.read(bufferString)) {
bolbStringBuffer.append(bufferString, 0, readCharCount);
}
if (readCharCount != -1) {
bolbStringBuffer.append(bufferString, 0, readCharCount);
}
cell = bolbStringBuffer.toString();
in.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
rowItem.put(rs.getMetaData().getColumnName(i), cell);
}
}
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return resultVector;
}

public String getSequenceNum(String tableName) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet rs = null;
String squenceNumber = "";
String sequenceStatement = "select " + tableName + "_seq.nextval from dual";
try {
connection = DBConnectionManager.getConnection();
statement = connection.prepareStatement(sequenceStatement);
rs = statement.executeQuery();
rs.next();
squenceNumber = rs.getBigDecimal(1).toString();
} catch (SQLException ex) {
ex.printStackTrace();
throw ex;
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
rs.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
statement.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return squenceNumber;
}
}