1. What is JDBC connection pool?
In traditional JDBC connections, each time you get a Connection connection, you need to load some complicated code to obtain, such as the following code:
public static Connection getConn(){ Connection conn = null; String url = "jdbc:mysql://localhost:3306/test"; String user = "root"; String password = "root"; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, user, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; }Such complicated operations are only for obtaining a connection. Of course, we can encapsulate it into a tool class to access (the above figure encapsulates the connection of Connection), but is it a waste of performance to load each connection? In order to optimize performance, a connection pool appears.
When the connection pool is initialized, several connections are created for us to use. When we need to connect, we only need to obtain existing connections from the connection pool. When there are no initialized connections, a connection will be recreated. After using the connection, the connection will not be destroyed, but will be returned to the connection pool for later use. (Of course, connection pooling is not just that simple, here are the introductions)
Commonly used connection pools include DBCP and C3P0. The most mainstream ones now are Alibaba's Druid connection pool, and tomcat's own JNDI connection pool.
2. Customize a simple connection pool
Analysis of custom connection pools:
1.2. Because it is a connection pool, we need to implement the DataSource interface and implement the methods in it. Based on our situation, we are about the getConnection() method;
2. Since we want to store several connection objects, we use a collection to store it. Based on the frequent operations of adding and deleting, then use LinkedList;
3. The destruction of a connection is not to destroy the connection, but to return the connection to the connection pool.
coding:
1. Create a class MyDataSource and implement the DataSource interface
When this class is loaded, it needs to have a container to store the Connection, so define a static property:
private static List<Connection> connectionList = new LinkedList<>();
2. Because we need to obtain the database connection, we encapsulate a method to obtain the database connection.
public Connection getOneConnection(){ Connection conn = null; try{ //The data obtained here is obtained through the external properties file, which is more flexible. InputStream in = MyDataSource.class.getClassLoader(). getResourceAsStream("jdbc/jdbc.properties"); Properties pro = new Properties(); pro.load(in); driver = pro.getProperty("driver"); url = pro.getProperty("url"); username = pro.getProperty("user"); password = pro.getProperty("password"); Class.forName(driver); conn = DriverManager.getConnection(url,username,password); }catch (Exception e){ e.getStackTrace(); } return conn; }Note that the data I obtained through the property file can be selected according to the actual situation.
3. Initialize several connections and put them into the container. It can be implemented using static code blocks, but if this data source is not used, it will cause resource waste, so I considered putting the implementation of initializing several connections into his constructor, that is, when this connection pool is needed, it will create several connections accordingly. as follows:
public MyDataSource() { for (int i = 0; i < 5; i++) { Connection conn = getOneConnection();//Call the method to create the connectionList.add(conn); } }4. Now start overwriting the external method to getConnection() to get the connection from this connection pool
@Override public Connection getConnection() throws SQLException { Connection conn = null; if(connectionList == null || connectionList.size() <= 0){ Connection connection = getConnection(); connectionList.add(connection); } conn = connectionList.remove(0); return conn; }5. Create an object return method, put the used connection into the connection pool
public void backConnection(Connection conn){ connectionList.add(conn); }OK, this completes a simple custom connection pool, and the test code is as follows:
public static void main( String[] args ) throws SQLException { MyDataSource dataSource = new MyDataSource(); Connection conn = dataSource.getConnection(); String sql = "select * from user where u_id = ?"; PreparedStatement ps = null; ResultSet rs = null; try { ps = conn.prepareStatement(sql); ps.setInt(1, 1); rs = ps.executeQuery(); while (rs.next()) { System.out.println("id="+rs.getInt(1)); System.out.println("username="+rs.getString(2)); System.out.println("password="+rs.getString(3)); } } catch (SQLException e) { e.printStackTrace(); } finally { dataSource.backConnection(conn); } }Because Ignore, there are no other two objects in my code to close.
Now there is a small problem that our closing connection is achieved through the connection pool method. However, if the user calls the close method of the Connection object, the connection is destroyed and not returned to the connection pool. Then we optimize it so that the user will not destroy the connection, but return the connection.
Among them, there are many solutions, and here is a decorative model.
optimization:
1. Create a new class MyConnection to implement the Connection interface, where its attribute types are Connection conn and a Liis<Connection>.
private Connection conn; private List<Connection> pool; public MyConnection(Connection conn, List<Connection> pool) { this.conn = conn; this.pool = pool; }2. Then implement the close method of the interface
@Override public void close() throws SQLException { System.out.println("Recycle connection"); pool.add(conn); }3. Then implement the method of obtaining the Statement. If it is not implemented, then a null pointer error will occur when obtaining this Statement. I only implement the method of obtaining the PreparedStatement.
@Override public PreparedStatement prepareStatement(String sql) throws SQLException { System.out.println("Get Statement"); return conn.prepareStatement(sql); }4. Then delete the method backConnection of the MyDataSource class to return the connection, and modify the constructor and the method to obtain the connection as follows
public MyDataSource2() { for (int i = 0; i < 5; i++) { Connection conn = getOneConnection(); MyConnection myConn = new MyConnection(conn, connectionList); connectionList.add(myConn); } } @Override public Connection getConnection() throws SQLException { Connection conn = null; if(connectionList == null || connectionList.size() <= 0){ Connection connection = getConnection(); MyConnection myConn = new MyConnection(connection, connectionList); connectionList.add(myConn); } conn = connectionList.remove(0); return conn; }OK, so that the user will not destroy the connection by calling our Connection close method directly, and will return it to the connection pool correctly. You can test it by making a little modification to the test code.
The above article is the entire content that I have shared with you. I hope it can give you a reference and I hope you can support Wulin.com more.