[JDBC] database connection pool

osc_bjmmswh6 2020-11-08 09:00:02
jdbc database connection pool


JDBC The need for database connection pooling

  1. In use to develop database based web The program , The traditional model basically follows these steps :
  • In the main program ( Such as servlet、beans) Establishing database connection in
  • Conduct sql operation
  • Disconnect the database
  1. This mode of development , The problem is :
  • ordinary JDBC Database connection use DriverManager To get , Every time you establish a connection to the database, you need to Connection Load into memory , Then verify the user name and password ( It costs 0.05s~1s Time for ). When you need a database connection , Just ask the database for a , Disconnect after execution . This way will consume a lot of resources and time .** The connection resources of the database are not well reused .** If there are hundreds or even thousands of people online at the same time , Frequent database connection operations will take up a lot of system resources , Serious even can cause the server to crash .
  • ** For every database connection , They have to be disconnected after use .** otherwise , If the program fails to close due to an exception , Will cause memory leaks in the database system , It will eventually lead to a restart of the database .( memories : What is the Java Memory leak ?)
  • This development does not control the number of connection objects created , System resources will be allocated without consideration , If there are too many connections , It can also cause memory leaks , Server crash .

Database connection pool technology

Database connection pool concept

  1. To solve the problem of database connection in traditional development , Database connection pool technology can be used .
  2. The basic idea of database connection pool : A connection is a database “ Buffer pool ”. Put a certain number of connections in the buffer pool in advance , When you need to establish a database connection , Just from “ Buffer pool ” Take out one of , Put it back after use .
  3. Database connection pool To be responsible for the distribution of 、 Manage and release database connections , it Allow applications to reuse an existing database connection , Instead of re establishing a .
    When the database connection pool is initialized, a certain number of database connections will be created and put into the connection pool , The number of these database connections is made by Set the minimum number of database connections Of . Whether these database connections are used or not , Connection pools will always guarantee at least so many connections . The connection pool Maximum number of database connections It limits the maximum number of connections that this connection pool can occupy , When the number of connections the application requests from the connection pool exceeds the maximum number of connections , These requests will be added to the waiting queue .

java Database connection pool technology provided by

JDBC The database connection pool is used javax.sql.DataSource To express ,DataSource It's just an interface , The interface is usually provided by the server (Weblogic, WebSphere, Tomcat) Provide implementation , There are also open source organizations that provide implementations :

  • DBCP yes Apache Database connection pool provided .tomcat The server comes with dbcp Database connection pool . Speed is relative to c3p0 Faster , But because of its own existence BUG,Hibernate3 Support is no longer available .
  • C3P0 It is a database connection pool provided by an open source organization ,** Relatively slow , Stability is OK .**hibernate Official recommendation
  • Proxool yes sourceforge The next open source project database connection pool , It has the function of monitoring connection pool status , More stable c3p0 almost
  • BoneCP Is a database connection pool provided by an open source organization , Fast
  • Druid It is the database connection pool provided by Ali , It's said to be a collection DBCP 、C3P0 、Proxool All in one database connection pool , But the speed is not sure if there is BoneCP fast

C3P0

Connection mode one :

 @Test
public void testGetConnection() throws Exception {

// obtain c3p0 Connection pool 
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "com.mysql.jdbc.Driver" ); //loads the jdbc driver
cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
cpds.setUser("root");
cpds.setPassword("0930");
// By setting the relevant parameters , Manage the database connection pool 
// Set the initial number of connections to the database connection pool 
cpds.setInitialPoolSize(10);
Connection connection = cpds.getConnection();
System.out.println(connection);
}

Connection mode 2 : Configuration file mode

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- This app is massive! -->
<named-config name="helloc3p0">
<!-- Provides the 4 A basic message -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">0930</property>
<!-- Basic information of database connection pool management -->
<!-- When there are not enough connections in the database connection pool ,c3p0 The number of connections applied to the database server at one time -->
<property name="acquireIncrement">5</property>
<!-- Number of connections at initialization -->
<property name="initialPoolSize">10</property>
<!--c3p0 The minimum number of connections maintained by the database connection pool -->
<property name="minPoolSize">10</property>
<!--c3p0 The maximum number of connections maintained by the database connection pool -->
<property name="maxPoolSize">100</property>
<!--c3p0 The database connection pool maintains the most statement The number of -->
<property name="maxStatements">0</property>
<!-- The maximum number of... That can be used in each connection statement The number of -->
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>
 @Test
public void testGetConnection1() throws SQLException {

ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
Connection connection = cpds.getConnection();
System.out.println(connection);
}

Write it in the tool class :

public class JDBCUtils {

// Use C3P0 Database connection technology 
// The database connection pool only needs to provide one 
private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection1() throws SQLException {

Connection connection = cpds.getConnection();
return connection;
}
}

DBCP

Fast But not stable

dbcp Common configuration properties

dbcp Connection pooling is a common basic configuration property

1.initialSize : The number of initial connections created when the connection pool starts ( The default value is 0)

2.maxActive : The maximum number of connections in the connection pool that can be connected at the same time ( The default value is 8, Adjusted for 20, Peak single machine in 20 About concurrency , According to the application scenario )

3.maxIdle: The maximum number of free connections in the connection pool , More than idle connections will be released , If it is set to a negative number, there is no limit ( The default is 8 individual ,maxIdle Can't set too small , Because if the load is high , The connection opens faster than it closes , Will cause the connection pool to idle The number of Rise above maxIdle, This leads to frequent destruction and creation of connections , Be similar to jvm In the parameter Xmx Set up )

4.minIdle: The minimum number of free connections in the connection pool , Below this number, new connections will be created ( The default is 0, Adjusted for 5, The closer the parameter is to maxIdle, The better the performance , Because of the creation and destruction of connections , All of them need to consume resources ; But not too big , Because when the machine is idle , It also creates less than minidle Number of connections , Be similar to jvm In the parameter Xmn Set up )

5.maxWait : Maximum waiting time , When no connection is available , The maximum time the connection pool waits for a connection to be released , Exceeding this time limit will throw an exception , If you set -1 It means infinite waiting ( The default is infinite , Adjusted for 60000ms, Avoid not having enough thread pools , As a result, the request is suspended indefinitely )

6.poolPreparedStatements: Open the pool prepared( The default is false, Unadjusted , After testing , The performance after turning on is not as good as that when it is turned off .)

7.maxOpenPreparedStatements: Open the pool prepared Maximum number of simultaneous connections after ( Default limit , ditto , Not configured )

8.minEvictableIdleTimeMillis : Connection in the connection pool , Have been free for a period of time , Time to be evicted from connection pool

9.removeAbandonedTimeout : Beyond the time limit , Recycling doesn't work ( abandoned ) The connection of ( The default is 300 second , Adjusted for 180)

10.removeAbandoned : exceed removeAbandonedTimeout After time , Whether to enter No connection ( abandoned ) The recycling of ( The default is false, Adjusted for true)

Connection mode one Hard encoding Not recommended

public class DBCPTest {

// test DBCP Database connection pool technology of 
@Test
public void testGetConnection() throws SQLException {

// Created DBCP Database connection pool 
BasicDataSource source = new BasicDataSource();
// Setting basic information 
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql://localhost:3306/test");
source.setUsername("root");
source.setPassword("0930");
// Set other related properties of database connection pool management 
source.setInitialSize(10);
source.setMaxActive(10);
// Connect 
Connection connection = source.getConnection();
System.out.println(connection);
}
}

Connection mode 2 The configuration file

stay src Next build a src/dbcp.properties The configuration file , Profile contents

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=0930
initialSize=10
maxActive=10

Get the connection

 // Mode two : The configuration file 
@Test
public void testGetConnection1() throws Exception {

Properties properties = new Properties();
// Get stream mode one :
//InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
// Get stream mode 2 :
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
properties.load(is);
DataSource source = BasicDataSourceFactory.createDataSource(properties);
Connection connection = source.getConnection();
System.out.println(connection);
}

Put it in our JDBCUtils Class

Only one connection pool should be generated , So the operation of generating link pool should be placed in getConnection2() Outside of the method , Otherwise, if you put it inside, you will create a pool every time you connect

 // Use DBCP Database connection pool technology obtains database connection 
private static DataSource source;
static {

try {

Properties properties = new Properties();
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
properties.load(is);
// Create a DBCP Connection pool 
source = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {

e.printStackTrace();
}
}
public static Connection getConnection2() throws Exception {

Connection connection = source.getConnection();
return connection;
}

Druid Druid database connection pool

Druid It is a database connection pool on Alibaba open source platform , It is a combination of C3P0、DBCP、Proxool etc. DB The advantages of the pool , At the same time, log monitoring is added , It's a good monitor DB Pool connection and SQL Implementation of , It can be said that it was born for monitoring DB Connection pool , It's one of the best connection pools right now .

Detailed configuration parameters :

To configure default explain
name The point of configuring this property is , If there are multiple data sources , Monitoring can be distinguished by name . If not configured , Will generate a name , The format is :”DataSource-” + System.identityHashCode(this)
url Connected to the database url, Different databases are different . for example :mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username User name to connect to the database
password Password to connect to the database . If you don't want the password written directly in the configuration file , have access to ConfigFilter. Look here in detail :https://github.com/alibaba/druid/wiki/ Use ConfigFilter
driverClassName according to url Automatic identification This item can be matched or not , If you don't configure druid Will be based on url Automatic identification dbType, Then choose the appropriate driverClassName( It is recommended to configure )
initialSize 0 Number of physical connections established during initialization . Initialization occurs in the display call init Method , Or for the first time getConnection when
maxActive 8 Maximum number of connection pools
maxIdle 8 No longer in use , Configuration has no effect
minIdle Minimum number of connection pools
maxWait Maximum wait time when getting a connection , Unit millisecond . Configured with maxWait after , Fair lock is enabled by default , Concurrency efficiency will decrease , If necessary, it can be configured through useUnfairLock The attribute is true Use unfair locks .
poolPreparedStatements false Whether the cache preparedStatement, That is to say PSCache.PSCache Great improvement in database performance supporting cursors , for instance oracle. stay mysql The next suggestion is to close .
maxOpenPreparedStatements -1 To enable the PSCache, Must be configured greater than 0, When more than 0 when ,poolPreparedStatements Auto trigger changed to true. stay Druid in , No existence Oracle Next PSCache The problem of using too much memory , You can configure this value to be larger , for instance 100
validationQuery Used to check whether the connection is valid sql, The requirement is a query statement . If validationQuery by null,testOnBorrow、testOnReturn、testWhileIdle It doesn't work .
testOnBorrow true Execute on connection request validationQuery Check whether the connection is valid , This configuration will degrade performance .
testOnReturn false Execute... When returning the connection validationQuery Check whether the connection is valid , This configuration will degrade performance
testWhileIdle false Recommended configuration is true, No performance impact , And ensure safety . Check when applying for connection , If the free time is greater than timeBetweenEvictionRunsMillis, perform validationQuery Check whether the connection is valid .
timeBetweenEvictionRunsMillis There are two meanings : 1)Destroy The thread will detect the connection interval 2)testWhileIdle On the basis of , See in detail testWhileIdle Description of the property
numTestsPerEvictionRun No longer use , One DruidDataSource Only one... Is supported EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls When the physical connection is initialized sql
exceptionSorter according to dbType Automatic identification When the database throws some unrecoverable exceptions , Abandon the connection
filters Property type is string , Configure the extension by alias , Common plug-ins are : For monitoring statistics filter:stat It's for the log filter:log4j defense sql Injected filter:wall
proxyFilters The type is List, If it is configured at the same time filters and proxyFilters, It's a combination relationship , It's not a replacement relationship

Connect with a configuration file

stay src The write druid.properties The configuration file
The contents are as follows :

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=0930
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall

java Connection operation

public class DruidTest {

@Test
public void getConnection() throws Exception {

Properties properties = new Properties();
FileInputStream is = new FileInputStream(new File("src/druid.properties"));
properties.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(properties);
Connection connection = source.getConnection();
System.out.println(connection);
}
}

Put it in JDBCUtils Class

 // Use Druid Druid database connection pool technology 
private static DataSource source1;
static {

try {

Properties properties = new Properties();
FileInputStream is = new FileInputStream(new File("src/druid.properties"));
properties.load(is);
source1 = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {

e.printStackTrace();
}
}
public static Connection getConnection3() throws Exception {

Connection connection = source1.getConnection();
return connection;
}
版权声明
本文为[osc_bjmmswh6]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云