java資料庫連接池技術簡單使用

標籤：j2ee   資料庫連接池   

JDBCDemo.java：package com.itheima.jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.itheima.pool.MyPool;public class JDBCDemo {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;MyPool pool = new MyPool();try {conn = pool.getConnection();ps = conn.prepareStatement("select * from account");rs = ps.executeQuery();while(rs.next()) {String name = rs.getString(2);String salary = rs.getString(3);System.out.println(name + " : " + salary);}} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {//關閉資料庫連接if(rs != null) {try {rs.close();} catch (SQLException e) {rs = null;}}if(ps != null) {try {ps.close();} catch (SQLException e) {ps = null;}}/*if(rs != null) {try {rs.close();} catch (SQLException e) {rs = null;}}*///這裡不能關閉資料庫連接對象connection，應該將其返還給資料庫連接池pool.returnConn(conn);}}}

MyPool.java：

package com.itheima.pool;import java.io.PrintWriter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.SQLFeatureNotSupportedException;import java.util.LinkedList;import java.util.List;import java.util.logging.Logger;import javax.sql.DataSource;public class MyPool implements DataSource{//list集合儲存資料庫連接池中的connection對象private static List<Connection> pool = new LinkedList<Connection>();//靜態代碼塊，用於初始化list集合，即初始化資料庫連接池，建立5個connection對象儲存其中以備使用static {try {Class.forName("com.mysql.jdbc.Driver");for(int i = 0; i < 5; i++) {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");pool.add(conn);}} catch (ClassNotFoundException e) {e.printStackTrace();throw new RuntimeException(e);} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);}}@Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}@Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}@Overridepublic void setLoginTimeout(int seconds) throws SQLException {}@Overridepublic int getLoginTimeout() throws SQLException {return 0;}@Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}@Overridepublic <T> T unwrap(Class<T> iface) throws SQLException {// TODO Auto-generated method stubreturn null;}@Overridepublic boolean isWrapperFor(Class<?> iface) throws SQLException {// TODO Auto-generated method stubreturn false;}//重寫父類的getConnection()方法，返回資料庫連接池中的一個connection對象，//如果資料庫連接池中connection對象都已被使用，即都被取走未返還,則建立3個connection對象儲存其中供以後使用@Overridepublic Connection getConnection() throws SQLException {if(pool == null) {for(int i = 0; i < 3; i++) {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");pool.add(conn);}}return pool.remove(0);}//建立新方法，用於返回資料庫連接對象connection，因為dao層用完資料庫的串連後，不應該將其銷毀，而是應該將其返還給資料庫連接池public void returnConn(Connection conn) {pool.add(conn);}@Overridepublic Connection getConnection(String username, String password)throws SQLException {// TODO Auto-generated method stubreturn null;}}

以上代碼是資料庫連接池的簡單使用，但是有一個問題就是當你使用資料庫連接池技術時，你需要修改JDBCDemo.java檔案中finally代碼塊中的代碼，即conn對象在finally代碼塊裡不應該通過conn.close();方法關閉，而是應該返還給資料庫連接池。這裡我們採用“動態代理”的方式，解決該問題，即finally代碼塊裡依然調用conn.close();方法。

此時以上兩個java檔案的代碼更改如下：

package com.itheima.jdbc;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.itheima.pool.MyPool;public class JDBCDemo {public static void main(String[] args) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;MyPool pool = new MyPool();try {conn = pool.getConnection();ps = conn.prepareStatement("select * from account");rs = ps.executeQuery();while(rs.next()) {String name = rs.getString(2);String salary = rs.getString(3);System.out.println(name + " : " + salary);}} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);} finally {//關閉資料庫連接if(rs != null) {try {rs.close();} catch (SQLException e) {rs = null;}}if(ps != null) {try {ps.close();} catch (SQLException e) {ps = null;}}if(conn != null) {try {conn.close();} catch (SQLException e) {conn = null;}}}}}

package com.itheima.pool;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.List;import java.util.logging.Logger;import javax.sql.DataSource;public class MyPool implements DataSource{//list集合儲存資料庫連接池中的connection對象private static List<Connection> pool = new LinkedList<Connection>();//靜態代碼塊，用於初始化list集合，即初始化資料庫連接池，建立5個connection對象儲存其中以備使用static {try {Class.forName("com.mysql.jdbc.Driver");for(int i = 0; i < 5; i++) {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");pool.add(conn);}} catch (ClassNotFoundException e) {e.printStackTrace();throw new RuntimeException(e);} catch (SQLException e) {e.printStackTrace();throw new RuntimeException(e);}}@Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}@Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}@Overridepublic void setLoginTimeout(int seconds) throws SQLException {}@Overridepublic int getLoginTimeout() throws SQLException {return 0;}@Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}@Overridepublic <T> T unwrap(Class<T> iface) throws SQLException {// TODO Auto-generated method stubreturn null;}@Overridepublic boolean isWrapperFor(Class<?> iface) throws SQLException {// TODO Auto-generated method stubreturn false;}//重寫父類的getConnection()方法，返回資料庫連接池中的一個connection對象，//如果資料庫連接池中connection對象都已被使用，即都被取走未返還,則建立3個connection對象儲存其中供以後使用@Overridepublic Connection getConnection() throws SQLException {if(pool == null) {for(int i = 0; i < 3; i++) {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day11", "root", "root");pool.add(conn);}}final Connection conn = pool.remove(0);//使用動態代理改造close方法//newProxyInstance(類載入器, 要改造的conn對象所實現的所有介面,  匿名內部類)Connection proxy = (Connection) Proxy.newProxyInstance(conn.getClass().getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {@Overridepublic Object invoke(Object proxy, Method method, Object[] args)throws Throwable {if("close".equals(method.getName())) {//如果是close方法，我們進行重寫returnConn(conn);return null;} else {//如果是其他方法，直接調用return method.invoke(conn, args);}}});System.out.println("擷取一個連線物件,剩餘連線物件：" + pool.size());return proxy;}//建立新方法，用於返回資料庫連接對象connection，因為dao層用完資料庫的串連後，不應該將其銷毀，而是應該將其返還給資料庫連接池public void returnConn(Connection conn) {pool.add(conn);System.out.println("返還一個連線物件,剩餘連線物件：" + pool.size());}@Overridepublic Connection getConnection(String username, String password)throws SQLException {// TODO Auto-generated method stubreturn null;}}

運行結果：

擷取一個連線物件,剩餘連線物件：4a : 1000.0b : 1000.0c : 1000.0返還一個連線物件,剩餘連線物件：5

java資料庫連接池技術簡單使用