数据库C3P0学习

使用到的库 c3p0-0.9.5.2.jar mchange-commons-java-0.2.11.jar ojdbc6.jar

package testng;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Manager {

	private static ComboPooledDataSource ds;
	private static ThreadLocal<Connection> connHodlder = new ThreadLocal<Connection>();
	private static C3p0Manager cManager = null;

	public static C3p0Manager getInstance() {
		if (null == cManager) {
			synchronized (cManager) {
				cManager = new C3p0Manager();
			}
		}

		return cManager;
	}

	// 创建连接池
	static {
		ds = new ComboPooledDataSource();
	}

	// 获取连接
	public static Connection getConnect() {
		Connection conn = connHodlder.get();
		try {
			if (null == conn) {
				conn = ds.getConnection();
				connHodlder.set(conn);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	// 关闭连接
	public static void closeConnection(Connection conn) {
		if (null != conn) {
			try {
				conn.close();

			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				connHodlder.remove();
			}
		}
	}

	// 准备好需要执行的oracle
	public static PreparedStatement getPreparedStatement(Connection conn, String sql, Object[] values)
			throws SQLException {

		PreparedStatement pStatement = conn.prepareStatement(sql);

		if (null == values || values.length == 0) {
			pStatement.setNull(1, java.sql.Types.VARCHAR);
		} else {
			for (int i = 0; i < values.length; i++) {

				pStatement.setObject(i + 1, values[i]);
			}
		}

		return pStatement;

	}

	// 查询数据
	public static ResultSet executeQuery(Connection conn, String sql, Object[] values) throws SQLException {

		PreparedStatement pStatement = null;
		try {
			pStatement = getPreparedStatement(conn, sql, values);
			return pStatement.executeQuery();

		} catch (SQLException e) {
			throw new RuntimeException();
		}

	}

	// 更新数据
	public static int executeUpdate(Connection conn, String sql, Object[] values) throws SQLException {
		PreparedStatement pStatement = null;
		try {
			pStatement = getPreparedStatement(conn, sql, values);
			return pStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException();
		} finally {
			pStatement.close();
		}
	}

	// 回滚
	public static void rollback(Connection conn) {
		if (null != conn) {
			try {
				if (!conn.getAutoCommit()) {
					conn.rollback();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 事务提交
	public static void commit(Connection conn) throws SQLException {
		if (null != conn && !conn.getAutoCommit()) {
			try {
				conn.commit();
			} catch (SQLException e) {
				throw new SQLException();
			}
		}
	}

	// 设置手动提交
	public static void setManualcommit(Connection conn) {
		if (null != conn) {
			try {
				if (conn.getAutoCommit()) {
					conn.setAutoCommit(false);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}