Database connection pool
Normal JDBC database connections (Connection objects) are obtained using DriverManager. Each time a Connection is established to the database, the Connection is loaded into memory. Re-verifying the username and password (which takes 0.05s to 1s)—-> The overhead (cost) of establishing a JDBC connection is considerable. When you need a database connection, you ask the database for one, and then disconnect when it’s done. This way will consume a lot of resources and time. The connection resources of the database are not well reused. If hundreds or even thousands of people are online at the same time, frequent database connection operations will occupy a lot of system resources, and even cause server crash.
For each database connection, disconnect after use. Otherwise, if the program fails to close due to an exception, it will cause a memory leak in the database system, which will eventually cause the database to restart. This development does not control the number of connection objects that are created, and system resources can be allocated recklessly, such as too many connections, memory leaks, and server crashes. The biggest benefit of Connection pooling: reusing each Connection object saves performance overhead.
The solution: Store the Connection object in a buffer after each operation, not close it directly, for repeated use. Next time you need to retrieve the Connection pool object, not from the DBMS, but directly from the buffer. Release the Connection object, or return it to the buffer. –> Database connection pool
Overview of connection pools
In Java, connection pooling uses the javax.sql.DataSource interface to represent the connection pool/DataSource. Note that DataSource, like JDBC, is just an interface implemented by various server vendors (Tomcat,JBoss, etc.). Common DataSource implementation: C3P0: Hibernate recommended, but the connection pool has not been updated since 2007, not recommended: poor performance
DBCP: Apache-organized project, recommended by Spring. It’s really nice. Druid: Alibaba project (Druid), the best connection pool in the world. A DataSource is the same as a Connection Pool.
What is the difference between using and not using connection pooling? Get the connection object: no connection pool: use DriverManager to get the connection directly to the DBMS. Connection pooling exists: Connection objects are obtained directly from the connection pool. Connection conn = DataSource object.getConnection ();
Release connection object: No connection pool: conn.close(): Disconnects from the database server (DBMS). Connection pooling exists: conn.close(): Returns the Connection object to the Connection pool without disconnecting from the DBMS.
DBCP connection pool
Environment Preparation:
Commons DBCP – 1.4. The jar
The Commons – the pool – 1.5.6. Jar
Code demo:
package com.hyxy.util; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; import org.apache.commons.dbcp.BasicDataSource; public class DBCPUtils { private static String driver; private static String url; private static String username; private static String password; Private static BasicDataSource BDS = new BasicDataSource(); static { try { InputStream is = DBCPUtils.class.getClassLoader().getResourceAsStream("db.properties"); //InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("dbcp.properties"); Properties prop = new Properties(); prop.load(is); driver = prop.getProperty("driver"); url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); // Set the connection pool parameter bds.setDriverclassName (driver); bds.setUrl(url); bds.setUsername(username); bds.setPassword(password); // Initialize the number of connection pools bds.setInitialSize(5); // Maximum active count bds.setMaxActive(8); BDS. SetMaxIdle (4); BDS. SetMinIdle (4); BDS. SetMaxWait (30); System.out.println(bds); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConn(){ Connection conn = null; try { conn = bds.getConnection(); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void closeConn(Connection conn){ if(conn! =null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void closeAll(Connection conn,Statement stat,ResultSet rs){ try { if(conn! =null){ conn.close(); } if(stat! =null){ stat.close(); } if(rs! =null){ rs.close(); } } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { System.out.println(DBCPUtils.getConn()); }}Copy the codeCode demo:
package com.hyxy.util; 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.dbcp.BasicDataSource; import org.apache.commons.dbcp.BasicDataSourceFactory; import org.apache.commons.dbcp.DataSourceConnectionFactory; Public class DBCPUtils2 {private static DataSource BDS; static { try { InputStream is = DBCPUtils2.class.getClassLoader().getResourceAsStream("dbcp.properties"); //InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("dbcp.properties"); Properties prop = new Properties(); prop.load(is); bds = BasicDataSourceFactory.createDataSource(prop); System.out.println(bds); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConn(){ Connection conn = null; try { conn = bds.getConnection(); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void closeAll(Connection conn,PreparedStatement ps,ResultSet rs){ try { if(conn! =null){ conn.close(); } if(ps! =null){ ps.close(); } if(rs! =null){ rs.close(); } } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { System.out.println(DBCPUtils2.getConn()); }}Copy the codeDruid connection pool
Druid connection pool is the best connection pool in the world and the best performance database connection pool in the Java language.
Environment :druid-1.0.15.jar
The connection properties of Druid and DBCP are identical, and the objects used to create the connection pool are different.
Code demo:
package com.hyxy.util; import java.io.InputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Properties; import javax.sql.DataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; Public class DruidUtils {private static DataSource BDS; static { try { InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"); Properties prop = new Properties(); prop.load(is); //bds = BasicDataSourceFactory.createDataSource(prop); bds = DruidDataSourceFactory.createDataSource(prop); System.out.println(bds); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConn(){ Connection conn = null; try { conn = bds.getConnection(); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void closeAll(Connection conn,PreparedStatement ps,ResultSet rs){ try { if(conn! =null){ conn.close(); } if(ps! =null){ ps.close(); } if(rs! =null){ rs.close(); } } catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) { System.out.println(DruidUtils.getConn()); }}Copy the codeCode result:
Druid Parameter list
Don’t forget to give me a little “like” when you see this. I have compiled more previous articles into PDF and put them in my community. I have also compiled some Java learning ZL, click on the portal if you want