Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to implement JDBC database connection pool". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Under what circumstances is connection pooling used?

For a simple database application, the access to the database is not very frequent. At this point, you can simply create a new connection when you need to access the database and close it when you are done, with no significant performance overhead. But for a complex database application, the situation is completely different. Frequently establishing and closing connections will greatly reduce the performance of the system, because the use of connections has become the bottleneck of system performance.

Benefits of using connection pooling

Connection multiplexing. By establishing a database connection pool and a set of connection usage management strategy, a database connection can be reused efficiently and safely, and the overhead of frequent establishment and closure of database connection can be avoided.

For shared resources, there is a famous design pattern: resource pooling. The purpose of this model is to solve the problems caused by frequent allocation and release of resources. The application of this pattern to the field of database connection management is to establish a database connection pool to provide a set of efficient connection allocation and use strategy, and the ultimate goal is to achieve efficient and secure connection reuse.

Implementation of connection Pool

The basic principle of database connection pool is to maintain a certain number of database connections in the internal object pool and expose the methods of obtaining and returning database connections.

External users can obtain the connection through the getConnection method, and then return the connection through the close method after use. Note that the connection is not closed at this time, but is reclaimed by the connection pool manager and ready for next use.

There is a DataSource interface in Java, and database connection pool is an implementation of DataSource.

Let's implement a database connection pool ourselves:

First implement DataSource, where BlockingQueue is used as the pool (only the key code is retained)

Import javax.sql.DataSource;import java.io.PrintWriter;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.SQLFeatureNotSupportedException;import java.util.concurrent.ArrayBlockingQueue;import java.util.concurrent.BlockingQueue;import java.util.logging.Logger;public class MyDataSource implements DataSource {static {Class.forName ("com.mysql.jdbc.Driver");} catch (Exception e) {e.printStackTrace () } / / there is a hole / / MySQL is using 5.5.5.The driver is up-to-date / / when you connect, you will report The server time zone value 'thanks thanks' / / is unrecognized or represents more than one time zone / / solution: / / 1. Add serverTimezone=UTC / / 2 to the connection string. Set the time zone in mysql. Default is SYSTEM / / set global time_zone='+8:00' private String url = "jdbc:mysql://localhost:3306/test?serverTimezone=UTC"; private String user = "root"; private String password = "123456"; private BlockingQueue pool = new ArrayBlockingQueue (3); public MyDataSource () {initPool ();} private void initPool () {try {for (int I = 0; I)

< 3; i++) { pool.add(new MyConnection( DriverManager.getConnection(url, user, password), this)); } } catch (SQLException e) { e.printStackTrace(); } } /* 从池中获取连接 */ @Override public synchronized Connection getConnection() throws SQLException { try { return pool.take(); } catch (InterruptedException e) { e.printStackTrace(); } throw new RuntimeException("get connection failed!"); } public BlockingQueue getPool() { return pool; } public void setPool(BlockingQueue pool) { this.pool = pool; }} 实现自己的连接, 对原生连接进行封装, 调用 close 方法的时候将连接放回到池中 import java.sql.*; import java.util.Map; import java.util.Properties; importjava.util.concurrent.Executor; public class MyConnection implements Connection { //包装的连接private Connection conn; private MyDataSource dataSource; public MyConnection(Connection conn, MyDataSource dataSource) { this.conn = conn; this.dataSource = dataSource; } @Overridepublic Statement createStatement() throws SQLException { return conn.createStatement(); }@Override public PreparedStatement prepareStatement(String sql) throws SQLException { returnconn.prepareStatement(sql); } @Override public boolean getAutoCommit() throws SQLException {return conn.getAutoCommit(); } @Override public void setAutoCommit(boolean autoCommit) throwsSQLException { conn.setAutoCommit(autoCommit); } @Override public void commit() throwsSQLException { conn.commit(); } @Override public void rollback() throws SQLException { conn.rollback(); } @Override public void close() throws SQLException { //解决重复关闭问题 if(!isClosed()) { dataSource.getPool().add(this); } } @Override public boolean isClosed()throws SQLException { return dataSource.getPool().contains(this); } } main 方法 import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException;import java.sql.Statement; public class Main { public static void main(String[] args) { DataSource source = new MyDataSource(); try { Connection conn = source.getConnection(); Statement st = conn.createStatement(); st.execute("INSERT INTO USER (name,age) values('bob',12)"); conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }数据源连接池的原理及Tomcat中的应用 在Java Web开发过程中，会广泛使用到数据源。 我们基本的使用方式，是通过Spring使用类似如下的配置，来声明一个数据源： 在之后应用里对于数据库的操作，都基于这个数据源，但这个数据源连接池的创建、销毁、管理，对于用户都是近乎透明的，甚至数据库连接的获取，我们都看不到Connection对象了。 这种方式是应用自身的数据库连接池，各个应用之间互相独立。 在类似于Tomcat这样的应用服务器内部，也有提供数据源的能力，这时的数据源，可以为多个应用提供服务。 这一点类似于以前写过关于Tomcat内部的Connector对于线程池的使用，可以各个Connector独立使用线程池，也可以共用配置的Executor。( Tomcat的Connector组件 ) 那么，在Tomcat中，怎么样配置和使用数据源呢？ 先将对应要使用的数据库的驱动文件xx.jar放到TOMCAT_HOME/lib目录下。 编辑TOMCAT_HOME/conf/context.xml文件，增加类似于下面的内容： 需要提供数据源的应用内，使用JNDI的方式获取 Context initContext = new InitialContext();Context envContext = (Context)initContext.lookup("java:/comp/env");DataSource ds = (DataSource)envContext.lookup("jdbc/TestDB");Connection conn = ds.getConnection(); 愉快的开始使用数据库… 我们看，整个过程也并不比使用Spring等框架进行配置复杂，在应用内获取连接也很容易。多个应用都可以通过第3步的方式获取数据源，这使得同时提供多个应用共享数据源很容易。 这背后的是怎么实现的呢？ 这个容器的连接池是怎么工作的呢，我们一起来看一看。 在根据context.xml中配置的Resouce初始化的时候，会调用具体DataSource对应的实现类，Tomcat内部默认使用的BasicDataSource，在类初始化的时候，会执行这样一行代码DriverManager.getDrivers()，其对应的内容如下，主要作用是使用 java.sql.DriverManager实现的Service Provider机制，所有jar文件包含META-INF/services/java.sql.Driver文件的，会被自动发现、加载和注册，不需要在需要获取连接的时候，再手动的加载和注册。 public static java.util.Enumeration getDrivers() { java.util.Vector result = new java.util.Vector(); for(DriverInfo aDriver : registeredDrivers) { if(isDriverAllowed(aDriver.driver, callerClass)) { result.addElement(aDriver.driver); } else { println(" skipping: " + aDriver.getClass().getName()); } } return (result.elements()); } 之后DataSourceFactory会读取Resouce中指定的数据源的属性，创建数据源。 在我们的应用内getConnection的时候，使用ConnectionFactory创建Connection, 注意在创建Connection的时候，重点代码是这个样子： public PooledObject makeObject() throws Exception { Connection conn = _connFactory.createConnection(); initializeConnection(conn); PoolableConnection pc = new PoolableConnection(conn,_pool, connJmxName); return new DefaultPooledObject(pc); 这里的_pool是GenericObjectPool，连接的获取是通过其进行的。 public Connection getConnection() throws SQLException { C conn = _pool.borrowObject();} 在整个pool中包含几个队列，其中比较关键的一个定义如下： private final LinkedBlockingDeque idleObjects; 我们再看连接的关闭， public void close() throws SQLException { if (getDelegateInternal() != null) { super.close(); super.setDelegate(null); }} 这里的关闭，并不会真的调用到Connection的close方法，我们通过上面的代码已经看到，Connection返回的时候，其实是Connection的Wrapper类。在close的时候，真实的会调用到下面的代码 // Normal close: underlying connection is still open, so we // simply need to return this proxy to the pool try { _pool.returnObject(this); } catch(IllegalStateException e) {} 所谓的return,是把连接放回到上面我们提到的idleObjects队列中。整个连接是放在一个LIFO的队列中，所以如果没有关闭或者超过最大空闲连接，就会加到队列中。而允许外的连接才会真实的销毁destory。 int maxIdleSave = getMaxIdle(); if (isClosed() || maxIdleSave >

-1 & & maxIdleSave

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.