Java Database Connection Pool

Java Database Connection Pool

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. prepareStat Ement ("select * from account"); rs = ps.exe cuteQuery (); 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 {// close the database connection 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;} * // the connection of the database connection object cannot be closed here, and it should be returned to the database 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. using list; import java. util. list; import java. util. logging. logger; import javax. SQL. dataSource; public class MyPool implements DataSource {// list set to save the connection object private static List in the database connection pool
 
  
Pool = new external list
  
   
(); // Static code block, used to initialize the list set, that is, to initialize the database connection pool. Five connection objects are created and saved for use in 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 conflict () throws SQLFeatureNotSupportedException {return null ;}@ Overridepublic
   
    
T unwrap (Class
    
     
Iface) throws SQLException {// TODO Auto-generated method stubreturn null;} @ Overridepublic boolean isWrapperFor (Class
     Iface) throws SQLException {// TODO Auto-generated method stubreturn false;} // rewrite the getConnection () method of the parent class and return a connection object in the database connection pool, // if all the connection objects in the database connection pool have been used, that is, they have been removed and not returned, three Connection objects will be created and saved for future use @ 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);} // create a new method to return the connection object of the database, because the dao layer should not destroy the connection object after the database connection is used up, instead, it should be returned to the database Connection pool public void returnConn (Connection conn) {pool. add (conn) ;}@ Overridepublic Connection getConnection (String username, String password) throws SQLException {// TODO Auto-generated method stubreturn null ;}}
    
   
  
 

The above code is a simple use of the database connection pool, but one problem is that when you use the database connection pool technology, you need to modify JDBCDemo. the code in the finally code block in the java file, that is, the conn object in the finally code block should not pass the conn. close (); Method closed, but should be returned to the database connection pool. Here we use the "dynamic proxy" method to solve this problem, that is, the finally code block still calls the conn. close (); method.

The code of the above two java files is changed as follows:

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.exe cuteQuery (); 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 {// close the database connection 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. using list; import java. util. list; import java. util. logging. logger; import javax. SQL. dataSource; public class MyPool implements DataSource {// list set to save the connection object private static List in the database connection pool
 
  
Pool = new external list
  
   
(); // Static code block, used to initialize the list set, that is, to initialize the database connection pool. Five connection objects are created and saved for use in 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 conflict () throws SQLFeatureNotSupportedException {return null ;}@ Overridepublic
   
    
T unwrap (Class
    
     
Iface) throws SQLException {// TODO Auto-generated method stubreturn null;} @ Overridepublic boolean isWrapperFor (Class
     Iface) throws SQLException {// TODO Auto-generated method stubreturn false;} // rewrite the getConnection () method of the parent class and return a connection object in the database connection pool, // if all the connection objects in the database connection pool have been used, that is, they have been removed and not returned, three Connection objects will be created and saved for future use @ 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); // use dynamic proxy to modify the close method // newProxyInstance (Class Loader, all interfaces implemented by the conn object to be transformed, anonymous internal class) 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 () {// if it is the close method, we will rewrite returnConn (conn); return null;} else {// if it is another method, directly call return method. invoke (conn, args) ;}}); System. out. println ("get a connection object, remaining connection object:" + pool. size (); return proxy;} // create a new method to return the connection object of the database. Because the dao layer should not destroy the connection object after the database connection is used up, instead, it should be returned to the database Connection pool public void returnConn (Connection conn) {pool. add (conn); System. out. println ("return a connection object, remaining connection object:" + pool. size () ;}@ Overridepublic Connection getConnection (String username, String password) throws SQLException {// TODO Auto-generated method stubreturn null ;}}
    
   
  
 

Running result:

Get a connection object. The remaining connection object: 4a: 1000.0b: 1000.0c: 1000.0 returns a connection object. The remaining connection object is 5.