JDBC类的封装 + 数据库连接池

context.xml

<?xml version='1.0' encoding='utf-8'?>
<Context>
    
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

	<Resource name="jdbc/oracles"
		auth="Container" type="javax.sql.DataSource" 
		maxActive="100" maxIdle="30" maxWait="1000" 
		username="scott" password="tiger"
		driverClassName="oracle.jdbc.driver.OracleDriver"
		url="jdbc:oracle:thin:@localhost:1521:orcl" />
	
	<!-- 
		name : 数据源名称
		type : 数据源方式
		driverClassName : 驱动程序名称
		username : 数据库用户名
		password : 数据库密码
		url : 数据库路径
		
		maxActive : 并发连接最大数，0为无限制
		maxWait : 等待连接最大数
		maxIdle : 连接池中空闲的连接个数
	 -->

</Context>

package com.yyb.DB;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class DBCP {

	/**
	 * 通过数据库连接池获取数据库连接
	 */
	public static Connection getConnection() {
		Connection conn = null;
		try {
			Context context = new InitialContext();
			String name = "java:comp/env/jdbc/oracles";
			DataSource ds = (DataSource) context.lookup(name);
			conn = ds.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭数据库连接
	 */
	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行单条数据库语句
	 * @return 是否执行成功
	 */
	public static boolean execute(String sql, Object... objs) {
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			if (objs != null) {
				for (int i = 0; i < objs.length; i++) {
					pstmt.setObject(i + 1, objs[i]);
				}
			}
			pstmt.execute();
			System.out.println("***= " + sql);
			flag = true;
		} catch (SQLException e) {
			flag = false;
			e.printStackTrace();
		} finally {
			close(null, pstmt, conn);
		}
		return flag;
	}

	/**
	 * 执行单条数据库语句
	 * 一般是查询语句
	 * @return 返回结果集
	 */
	public static ResultSet executeQuery(String sql, Object... objs) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			if(objs != null){
				for (int i = 0; i < objs.length; i++) {
					pstmt.setObject(i + 1, objs[i]);
				}
			}
			rs = pstmt.executeQuery();
			System.out.println("***= " + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, pstmt, conn);
		}
		return rs;
	}

	/**
	 * 执行单条数据库语句
	 * 必须是DML（insert、delete或update等无返回内容的SQL语句）
	 * @return 返回受影响的记录条数
	 */
	public static int executeUpdate(String sql, Object... objs) {
		int count = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = getConnection();
			pstmt = conn.prepareStatement(sql);
			if (objs != null) {
				for (int i = 0; i < objs.length; i++) {
					pstmt.setObject(i + 1, objs[i]);
				}
			}
			count = pstmt.executeUpdate();
			System.out.println("***= " + sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(null, pstmt, conn);
		}
		return count;
	}

	/**
	 * 执行多条数据库语句
	 * @return 是否成功执行
	 */
	public static boolean exeBatch(String[] sql) {
		boolean flag = false;
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for (int i = 0; i < sql.length; i++) {
				stmt.addBatch(sql[i]);
			}
			stmt.executeBatch();
			conn.commit();
			flag = true;
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException ee) {
				ee.printStackTrace();
			}
			flag = false;
			e.printStackTrace();
		} finally {
			close(null, stmt, conn);
		}
		return flag;
	}

}