package jdbcPoolTest;
 
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.Properties;
import java.util.logging.Logger;
 
import javax.sql.DataSource;
 
import org.omg.PortableServer.POA;
 
public class JdbcPool implements DataSource{
	
	
	private static LinkedList<Connection> listConnections = new LinkedList<>();
	
	static {
		//靜態(tài)代碼塊中加載db.properties配置文件
		InputStream in = JdbcPool.class.getClassLoader().getResourceAsStream("db.properties");
		
		Properties prop = new Properties();
		try {
			//讀取文件內(nèi)容
			prop.load(in);
			String driver = prop.getProperty("driver");
			String url = prop.getProperty("url");
			String username = prop.getProperty("username");
			String password = prop.getProperty("password");
			
			//數(shù)據(jù)庫(kù)連接池的初始化連接大小
			int jdbcPoolInitSize = Integer.parseInt(prop.getProperty("jdbcPoolInitSize"));
			//加載數(shù)據(jù)庫(kù)驅(qū)動(dòng)		
			Class.forName(driver);	
			for(int i=0; i<jdbcPoolInitSize; i++) {
			//獲取連接	
			Connection conn = DriverManager.getConnection(url, username, password);
			System.out.println("獲取連接："+conn);
			//將conn連接加入listConnections集合中，此時(shí)的listConnections就是一個(gè)連接池
			listConnections.add(conn);
			
				
			}
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
	}
 
	@Override
	public Connection getConnection() throws SQLException {
		// TODO Auto-generated method stub
		//如果數(shù)據(jù)庫(kù)連接池的連接數(shù)大于0
		System.out.println("進(jìn)來(lái)了");
		if (listConnections.size()>0) {
			//從listConnections中獲取一個(gè)連接
			final Connection conn = listConnections.removeFirst();
			System.out.println("數(shù)據(jù)庫(kù)連接池的大小為："+listConnections.size());
			//返回Connection的代理，利用代理可以處理一些橫切事件
			System.out.println("取出的連接為："+conn);
			return (Connection) Proxy.newProxyInstance(JdbcPool.class.getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {
				
				@Override
				public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
					// TODO Auto-generated method stub
					//如果不是執(zhí)行關(guān)閉操作，則通過(guò)反射執(zhí)行相應(yīng)方法
					if (!method.getName().equals("close")) {
												
						return method.invoke(conn, args);
						
					}else {
						//否則，將conn歸還給連接池
						listConnections.add(conn);
						System.out.println("歸還連接："+conn);
						System.out.println("連接池大小為："+listConnections.size());
						return null;
					}
					
				}
			});
			
		}else {
			throw new RuntimeException("對(duì)不起，數(shù)據(jù)庫(kù)正忙");
		}
		
	}
	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
 
	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}
 
	@Override
	public Logger getParentLogger() throws SQLFeatureNotSupportedException {
		// TODO Auto-generated method stub
		return null;
	}
 
	@Override
	public void setLogWriter(PrintWriter arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}
 
	@Override
	public void setLoginTimeout(int arg0) throws SQLException {
		// TODO Auto-generated method stub
		
	}
 
	@Override
	public boolean isWrapperFor(Class<?> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}
 
	@Override
	public <T> T unwrap(Class<T> arg0) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
 
	
 
	@Override
	public Connection getConnection(String username, String password) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
 
}

package jdbcPoolTest;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JdbcUtil {
 
	
	private static JdbcPool jdbcPool = new JdbcPool();
	
	public static Connection getConnection() throws SQLException {
		
		return jdbcPool.getConnection();
		
	}
	//釋放的資源包括Connection數(shù)據(jù)庫(kù)連接對(duì)象，負(fù)責(zé)執(zhí)行SQL命令的Statement對(duì)象，存儲(chǔ)查詢結(jié)果的ResultSet對(duì)象
	public static void release(Connection conn,Statement st ,ResultSet rs) {
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rs = null;
		}
	}
}

driver=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/student
 username=root
 password=123456
 jdbcPoolInitSize=10

#連接設(shè)置   報(bào)錯(cuò)com.mysql.jdbc.Connection.isValid(I)Z,更新mysql包
driverClassName=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/student
 username=root
 password=123456
 #初始化連接池大小
 jdbcPoolInitSize=10
 #最大連接數(shù)量
 maxActive=50
 
 #<!-- 最大空閑連接 -->
 maxIdle=20
 
 #<!-- 最小空閑連接 -->
 minIdle=5
 
 #<!-- 超時(shí)等待時(shí)間以毫秒為單位 6000毫秒/1000等于60秒 -->
 maxWait=60000
 
 
 #JDBC驅(qū)動(dòng)建立連接時(shí)附帶的連接屬性的格式必須為這樣：[屬性名=property;] 
 #注意："user" 與 "password" 兩個(gè)屬性會(huì)被明確地傳遞，因此這里不需要包含他們。
 connectionProperties=useUnicode=true;characterEncoding=UTF8
 
 #指定由連接池所創(chuàng)建的連接的自動(dòng)提交（auto-commit）狀態(tài)。
 defaultAutoCommit=true
 
 #driver default 指定由連接池所創(chuàng)建的連接的只讀（read-only）狀態(tài)。
 #如果沒(méi)有設(shè)置該值，則“setReadOnly”方法將不被調(diào)用。（某些驅(qū)動(dòng)并不支持只讀模式，如：Informix）
 defaultReadOnly=
 
 #driver default 指定由連接池所創(chuàng)建的連接的事務(wù)級(jí)別（TransactionIsolation）。
 #可用值為下列之一：（詳情可見(jiàn)javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
 defaultTransactionIsolation=READ_UNCOMMITTED

package zdb.jdbc.util;
 
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
import javax.sql.DataSource;
 
import org.apache.commons.dbcp2.BasicDataSourceFactory;
 
 
public class jdbcUtils_DBCP {
 
	/**
	      * 在java中，編寫數(shù)據(jù)庫(kù)連接池需實(shí)現(xiàn)java.sql.DataSource接口，每一種數(shù)據(jù)庫(kù)連接池都是DataSource接口的實(shí)現(xiàn)
	      * DBCP連接池就是java.sql.DataSource接口的一個(gè)具體實(shí)現(xiàn)
	      */
	private  static DataSource ds = null;
	
	
	static {
	InputStream in = jdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
	Properties prop = new Properties();
	try {
		prop.load(in);
		//創(chuàng)建數(shù)據(jù)源
		ds = BasicDataSourceFactory.createDataSource(prop);	 
		
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	}
	public static Connection getConnection() throws SQLException{
		return ds.getConnection();
		
	}
	//釋放的資源包括Connection數(shù)據(jù)庫(kù)連接對(duì)象，負(fù)責(zé)執(zhí)行SQL命令的Statement對(duì)象，存儲(chǔ)查詢結(jié)果的ResultSet對(duì)象
	public static void release(Connection conn,PreparedStatement ps ,ResultSet rs) {
		if (conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rs = null;
		}
	}
	
}

package zdb.jdbc.util;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
import javafx.css.PseudoClass;
 
public class DataSourceTest {
	 
	public static void jdbcDataSource() {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			//conn = jdbcUtils_DBCP.getConnection();
			conn = jdbcUtils_DBCP.getConnection();
			String sql = "insert into user(uname) values(?)";
			//由于版本兼容問(wèn)題：必須加上PreparedStatement.RETURN_GENERATED_KEYS
			ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, "lonewolf");
			ps.executeUpdate();
			//獲得主鍵
			rs = ps.getGeneratedKeys();
			if (rs.next()) {
				System.out.println(rs.getInt(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch blockrs
			e.printStackTrace();
		}
			finally {
			jdbcUtils_DBCP.release(conn, ps, rs);
			//jdbcUtils_C3P0.release(conn, ps, rs);
		}
		
		
		
	}
 
	public static void main(String[] args) {
		new DataSourceTest().jdbcDataSource();
	}
	
	
}

<c3p0-config>
     <!--
     C3P0的缺省(默認(rèn))配置，
     如果在代碼中“ComboPooledDataSource ds = new ComboPooledDataSource();”這樣寫就表示使用的是C3P0的缺省(默認(rèn))配置信息來(lái)創(chuàng)建數(shù)據(jù)源
     -->
     <default-config>
         <property name="driverClass">com.mysql.jdbc.Driver</property>
         <property name="jdbcUrl">jdbc:mysql://localhost:3306/student</property>
         <property name="user">root</property>
         <property name="password">123456</property>
         
         <property name="acquireIncrement">5</property>
         <property name="initialPoolSize">10</property>
         <property name="minPoolSize">5</property>
         <property name="maxPoolSize">20</property>
     </default-config>
 
     <!--
     C3P0的命名配置，
     如果在代碼中“ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");”這樣寫就表示使用的是name是MySQL的配置信息來(lái)創(chuàng)建數(shù)據(jù)源
     -->
     <named-config name="MySQL">
         <property name="driverClass">com.mysql.jdbc.Driver</property>
         <property name="jdbcUrl">jdbc:mysql://localhost:3306/student</property>
         <property name="user">root</property>
         <property name="password">123456</property>
         
         <property name="acquireIncrement">5</property>
         <property name="initialPoolSize">10</property>
         <property name="minPoolSize">5</property>
         <property name="maxPoolSize">20</property>
     </named-config>
 
 </c3p0-config>

package zdb.jdbc.util;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.mchange.v2.c3p0.ComboPooledDataSource;
 
public class jdbcUtils_C3P0 {
	
	private static ComboPooledDataSource ds = null;
	static {
		ds = new ComboPooledDataSource("MySQL");
		
	}
	public static Connection getConnection() throws SQLException{
	    //從數(shù)據(jù)源中獲取數(shù)據(jù)庫(kù)連接
	    return ds.getConnection();
}
	//釋放的資源包括Connection數(shù)據(jù)庫(kù)連接對(duì)象，負(fù)責(zé)執(zhí)行SQL命令的Statement對(duì)象，存儲(chǔ)查詢結(jié)果的ResultSet對(duì)象
		public static void release(Connection conn,PreparedStatement ps ,ResultSet rs) {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (ps!=null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				rs = null;
			}
		}
 
}

package zdb.jdbc.util;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
 
import javafx.css.PseudoClass;
 
public class DataSourceTest {
	 
	public static void jdbcDataSource() {
		
		PreparedStatement ps = null;
		ResultSet rs = null;
		Connection conn = null;
		try {
			//conn = jdbcUtils_DBCP.getConnection();
			conn = jdbcUtils_C3P0.getConnection();
			String sql = "insert into user(uname) values(?)";
			//由于版本兼容問(wèn)題：必須加上PreparedStatement.RETURN_GENERATED_KEYS
			ps = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setString(1, "lonewolf");
			ps.executeUpdate();
			//獲得主鍵
			rs = ps.getGeneratedKeys();
			if (rs.next()) {
				System.out.println(rs.getInt(1));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch blockrs
			e.printStackTrace();
		}
			finally {
			jdbcUtils_DBCP.release(conn, ps, rs);
			//jdbcUtils_C3P0.release(conn, ps, rs);
		}
		
		
		
	}
 
	public static void main(String[] args) {
		new DataSourceTest().jdbcDataSource();
	}
	
	
}

Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
dataSource = (DataSource)envCtx.lookup("jdbc/datasource");

<Resource  
    name="jdbc/mysql"   
    scope="Shareable"   
    type="javax.sql.DataSource"  
    factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"  
    url="jdbc:mysql://localhost:3306/test"  
    driverClassName ="com.mysql.jdbc.Driver"  
    username="root"  
    password="root"  
/>  

<ResourceLink global="jdbc/mysql" name="jdbc/mysql" type="javax.sql.DataSource"/>

 <resource-ref>
   <description>DB Connection</description>
   <res-ref-name>jdbc/mysql</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Container</res-auth>
 </resource-ref> 

package zdb.util.JNDI_tomcat;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
 
public class JdbcUtils_JNDI {
	private static DataSource ds = null;
	static {
		
		try {
			//初始化JNDI
			Context initCtx = new InitialContext();
			//得到JNDI容器
			Context envCtx = (Context) initCtx.lookup("java:comp/env");
			//從JNDI容器中檢索name為jdbc/datasource的數(shù)據(jù)源
			ds = (DataSource) envCtx.lookup("jdbc/mysql");
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	public static Connection getConnection() throws SQLException{
	    //從數(shù)據(jù)源中獲取數(shù)據(jù)庫(kù)連接
		
	    return ds.getConnection();
	   
}
	//釋放的資源包括Connection數(shù)據(jù)庫(kù)連接對(duì)象，負(fù)責(zé)執(zhí)行SQL命令的Statement對(duì)象，存儲(chǔ)查詢結(jié)果的ResultSet對(duì)象
		public static void release(Connection conn,PreparedStatement ps ,ResultSet rs) {
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (ps!=null) {
				try {
					ps.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				rs = null;
			}
 
}
}

package zdb.util.JNDI_tomcat;
 
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/JNDI_test")
public class JNDI_test extends HttpServlet{
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
	             throws ServletException, IOException {
	         Connection conn = null;
	         PreparedStatement st = null;
	         ResultSet rs = null;
	         try{
	             //獲取數(shù)據(jù)庫(kù)連接
	             conn = JdbcUtils_JNDI.getConnection();
	             System.out.println("mysql Connection pool connected !!");
	             String sql = "insert into user(uname) values(?)";
	             st = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
	             st.setString(1, "gacl");
	             st.executeUpdate();
	             //獲取數(shù)據(jù)庫(kù)自動(dòng)生成的主鍵
	             rs = st.getGeneratedKeys();
	             if(rs.next()){
	                 System.out.println(rs.getInt(1));
	             }
	         }catch (Exception e) {
	             e.printStackTrace();
	         }finally{
	             //釋放資源
	             JdbcUtils_JNDI.release(conn, st, rs);
	         }
	     }
	 public void doPost(HttpServletRequest request, HttpServletResponse response)
	              throws ServletException, IOException {
	          doGet(request, response);
	     }
 
}

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style>
	body{
	text-align:center;
	}	
</style>
</head>
<body>
	<a href="${pageContext.servletContext.contextPath }/JNDI_test" rel="external nofollow" >測(cè)試</a>
	
</body>