C3P0 Database Connection Pool Realization principle notes __ Database

Why use a connection pool

-Our initial JDBC Connection database requires
1. Installation drive
2. Get Connected
3. Open the Window
4. Writing statements, executing statements, and viewing results
5. Close the connection to the database
1, 2, and 5 are completely reusable, which accelerates execution. Also namely:
 The same connection can open multiple windows
 the same window can execute statements multiple times
We found that JDBC connected to the database for the same project, if each operation of the database, every time the query to add ..., using the above method is to establish multiple database connections, and each access to the connection for Java code is repetitive action, not only occupy space, And it also has a waste of execution efficiency. Solution Analysis of connection reuse
We imagine the railway station ticket hall situation, peacetime passenger flow is relatively young, we only open a few ticket window, whether or not customers to buy tickets: more time in the idle state. All ticket windows are enabled during peak periods, such as holidays. In particular, there may be temporary windows at the peak of the Spring Festival. But the temporary window is not unrestricted to open more, open the window to expend the material manpower. This can only allow customers to wait a little longer at peak times. Queues at the peak of the Spring Festival, this time customers will need to wait.
We can also use a similar scenario when reusing a database connection. We first open a batch of connections to wait for the customer to use (as in off-peak hours we open a few windows), regardless of the use of no customers. When the rush hour comes, we can open a few more connections for our browser customers to use. It's not as likely as the Spring Festival. In order for everyone to have a window service, our database connection cannot be opened indefinitely (the database connection consumes resources such as memory, and the number of concurrent connections that each database can support is not unlimited). Customers can only wait in line when they are not ready to serve the data that they need. If the user has been unable to get a connection object, don't let the user wait indefinitely.
As you can see from the above, we reuse the connection scheme and need to provide the following information:
Maximum number of connections that can be opened by a number of pre-open connections a connection is to be used by more than one client, so the connection cannot be closed for a timeout, and if this time is exceeded, the user cannot get the connection and get the data.
In the connection reuse scheme of the JDBC Connection database, we are called connection pooling.
Connection objects and statement objects that can be reused in a connection pool

the function and principle of connection pool
1. Number of pre-open connections
2. Maximum number of connections that can be opened
3. A connection to be used by multiple customers, so the connection can not be closed
4. A timeout time, if more than this time, the user can not get the connection and get the data. Custom connection Pooling

Principle
Define a set to hold a certain number of connection connections
Rewrite the Close method for connection-not really shut down but put it into the connection pool to import the associated jar pack
-The Oracle database is used here and we will import the Ojdbc6.jar jdbc.properties configuration file
-url=jdbc:oracle:thin: @localhost: 1521:ORCL
User=lgh
Password=lgh
Classname=oracle.jdbc.oracledriver
Writing the database Connection tool class

Package com.jt.demo07;
Import java.io.IOException;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import java.sql.Statement;
Import java.util.Properties;
    /** * Database Connection Tool class * This class is mainly load-driven create connection * * * */public class Jdbcunti {private static String URL = null;
    private static String user = null;
    private static String password = NULL;
    private static String className = null;
    private static properties into = new properties (); static {try {//Read files and classes together use this method Into.load (JDBCUnti.class.getResourceAsStream ("Jdbc.proper

            Ties "));
            url = into.getproperty ("url");

            user = Into.getproperty ("user");
            Password = into.getproperty ("password");
        ClassName = Into.getproperty ("ClassName");
        catch (IOException e) {e.printstacktrace (); } static {try {//drivermanager.registerdriver (new OrAcledriver ());
        This registration method is actually registered two times drive class.forname (className);
        catch (ClassNotFoundException e) {//TODO auto-generated catch block E.printstacktrace (); } public static Connection getconnection () throws SQLException {return drivermanager.getconnectio
    N (URL, user, password);
                public static void Free (Statement stmt, Connection conn) {if (stmt!= null) {try {
            Stmt.close ();
            catch (SQLException e) {e.printstacktrace ();
            } if (conn!= null) {try {conn.close ();
            catch (SQLException e) {e.printstacktrace (); }} public static void (ResultSet res, Statement stmt, Connection conn) {if (res!= null)
            {try {res.close (); catch (SQLException e) {E. Printstacktrace ();
    } free (stmt, conn);
 }

}

You need to implement the Java.sql.DataSource interface for writing connection pools. Two overloaded getconnection methods are defined in the DataSource interface:

Connection getconnection ()
Connection getconnection (string Username, string password)

Steps to implement the DataSource interface and implement the connection pooling function:

Create a connection to the database in bulk in the DataSource constructor and add the created connection to the LinkedList object.
implement the Getconnection method so that the Getconnection method takes a connection from the LinkedList and returns it to the user each time it is invoked.
when the user finishes using connection and calls the Connection.close () method, the collection object should ensure that it returns to the list instead of Conn back to the database. Collection guarantee to return to the list is the difficulty of programming here.

Package com.jt.demo07;
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.SQLException;
Import java.sql.SQLFeatureNotSupportedException;
Import java.util.ArrayList;
Import java.util.List;

Import Java.util.logging.Logger;
     Import Javax.sql.DataSource; /** * List Collection simulates a pool store Lianjie * Static code blocks are executed when the class is loaded * expected to deposit 5 database connections */public class myDataSource Impleme
    NTS DataSource {public static list<connection> pool = new arraylist<connection> ();
            static{for (int i=0;i<5;i++) {try {Pool.add (jdbcunti.getconnection ());
            catch (SQLException e) {e.printstacktrace (); @Override public PrintWriter Getlogwriter () throws SQLException {//TODO Auto-generat
    Ed method stub return null; } @Override PubLIC void Setlogwriter (PrintWriter out) throws SQLException {//TODO auto-generated method stub} @Overr

    IDE public void setLoginTimeout (int seconds) throws SQLException {//TODO auto-generated method stub} @Override public int getlogintimeout () throws SQLException {//TODO auto-generated method stub re
    Turn 0; @Override public Logger Getparentlogger () throws Sqlfeaturenotsupportedexception {//TODO Auto-generat
    Ed method stub return null; @Override public <T> T Unwrap (class<t> iface) throws SQLException {//TODO auto-generated
    Method stub return null; @Override public boolean iswrapperfor (Class<?> iface) throws SQLException {//TODO auto-generate
    D method stub return false; @Override public Connection getconnection () throws SQLException {if (pool.size () >0) {fi NAL Connection Conn = pool.remove (0);
            SYSTEM.OUT.PRINTLN ("conn de ++++" +conn.getclass ());

                Return (Connection) proxy.newproxyinstance (null, New Class[]{connection.class}, new Invocationhandler () { @Override public Object Invoke (Object proxy, methods method, object[] args) throws Throwable {//This is a method inner class The connection Close method is overridden by a dynamic proxy method to achieve a call to the closing method that does not turn off the connection, but instead places it in the connection pool System.out.println ("_____" +this.getclass (
                    ));
                        if (Method.getname (). Equals ("Close")) {System.out.println ("aaaaaaaa" +this.getclass ());
                    POOL.ADD (conn);
                    }else{return Method.invoke (conn, args);
                return null;
        }
            });
    return null;
        @Override Public Connection getconnection (string Username, string password) throws SQLException { TODO auto-generated mEthod stub return null;
 }

}

Writing Java program tests
to write query database operation code

Package com.jt.demo07;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import java.sql.Statement;

Import Oracle.jdbc.OracleDriver; public class Jdbcselect {/** * Test mydatasource Connection Pool * */public static void Main (string[) args) t
        Hrows SQLException {Connection conn = null;
        Statement stmt = null;
        ResultSet res = null;
        myDataSource MD = new myDataSource ();
            try {conn = md.getconnection ();
            stmt = Conn.createstatement ();
            String sql = "SELECT * from T_user";
            res = stmt.executequery (SQL);
            System.out.println ("Id\tname\tpwd\tage\tbirthday"); while (Res.next ()) {System.out.println (Res.getint ("id") + "\ T" + res.getstring ("n Ame ") +" T "+ res.getstring (" pwd ") +" T "+ res.getint (" age ") +" T "+ re S.getdatE ("Birthday"));
        } catch (Exception e) {e.printstacktrace ();
        Finally {Jdbcunti.free (res, stmt, conn);
 }
    }

}

Execution procedure

The first time to write a blog, there is no experience, mainly want to write the content of learning, to make a summary, what deficiencies also please understand