Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the ways of database connection pooling". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought. Let's study and learn "what are the ways of database connection pooling?"

I. introduction

Database connection is a very critical, limited and expensive resource, which is particularly prominent in multi-user web applications.

I remember a project I did before. The c3p0 database connection pool was configured by the application at that time, and the maximum number of connections allowed was 500. as a result, not long after the launch, the concurrent volume came up directly, resulting in a large number of data insertion failures. You can imagine the mood of that night.

Since that accident, I have a deep understanding of the number of database connections in the application. In order to prevent another stumble, I specially took a time to write a program test case to test the stability of each data source connection pool. To prevent yourself from stepping in the hole again!

If you don't say much, just shake it up!

II. Program examples

Students who are familiar with web system development basically know that there are several common database connection pools that are open source in Java ecology:

Dbcp:DBCP is a database connection pool that relies on Jakarta commons-pool object pooling mechanism. DBCP can be used directly in applications. The data source of Tomcat uses DBCP.

C3p0:c3p0 is an open source JDBC connection pool that is released with Hibernate in the lib directory and includes DataSources objects that implement the Connection and Statement pools specified by the jdbc3 and jdbc2 extension specifications

Druid: Ali, Taobao and Alipay dedicated database connection pool, but it is not just a database connection pool, it also contains a ProxyDriver, a series of built-in JDBC component libraries, a SQL Parser. Supports all JDBC-compatible databases, including Oracle, MySql, Derby, Postgresql, SQL Server, H2, and so on.

Today, let's compare the stability of these three data source connection pools.

2.1. Create a test table

Let's take the mysql database as an example, first create a t_test table, and then insert the data.

CREATE TABLE t_test (id bigint (20) unsigned NOT NULL COMMENT 'primary key ID', name varchar (32) NOT NULL COMMENT' name', PRIMARY KEY (id)) ENGINE=InnoDB COMMENT=' test table'

2.2. Write test cases

Take dbcp as an example, first create a dbcp-jdbc.properties configuration file.

Username=root password=Hello@123456 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.31.200:3306/testdb?useUnicode=true&characterEncoding=UTF-8 initialSize=5 maxActive=1000 maxIdle=5 removeAbandoned=ture removeAbandonedTimeout=20 logAbandoned=true maxWait=100

Next, create a connection pooling tool, DbcpJdbcUtil.

Public class DbcpJdbcUtil {private static final Logger logger = LoggerFactory.getLogger (DbcpJdbcUtil.class); / * * jdbc configuration file * / private static Properties prop = new Properties (); private static BasicDataSource dataSource = null; / / it is a transaction dedicated connection! Private static ThreadLocal tl = new ThreadLocal (); static {classPathSourceRead ();} private static void classPathSourceRead () {/ / read the configuration document at the specified location (read the class directory file) try {logger.info ("jdbc path:" + SysConstants.getValue ()); prop.load (DbcpJdbcUtil.class.getClassLoader (). GetResourceAsStream (SysConstants.getValue (); logger.info ("data configuration Information" + JSON.toJSONString (prop)) Logger.info ("failed to initialize default jdbc profile!");} catch (Exception e) {logger.error ("failed to initialize default jdbc file!", e) }} / * get data source from connection pool * @ return * @ throws Exception * / public static BasicDataSource getDataSource () throws Exception {try {if (dataSource = = null) {synchronized (DbcpJdbcUtil.class) {if (dataSource = = null) {dataSource = new BasicDataSource (); dataSource.setUsername (prop.getProperty ("username")); dataSource.setPassword (prop.getProperty ("password")) DataSource.setDriverClassName (prop.getProperty ("driverClassName")); dataSource.setUrl (prop.getProperty ("url")); dataSource.setInitialSize (Integer.valueOf (prop.getProperty ("initialSize"); dataSource.setMaxActive (Integer.valueOf (prop.getProperty ("maxActive"); dataSource.setMaxIdle (Integer.valueOf (prop.getProperty ("maxIdle"); dataSource.setRemoveAbandoned (Boolean.valueOf (prop.getProperty ("removeAbandoned") DataSource.setRemoveAbandonedTimeout (Integer.valueOf (prop.getProperty ("removeAbandonedTimeout")); dataSource.setLogAbandoned (Boolean.valueOf (prop.getProperty ("logAbandoned"); dataSource.setMaxWait (Integer.valueOf (prop.getProperty ("maxWait");}} return dataSource;} catch (Exception e) {logger.error ("failed to get database resource based on database name,", e) Throw new Exception ("failed to get database resources based on database name");}} / * use connection pooling to return a connection object * * @ return * @ throws SQLException * / public static Connection getConnection () throws Exception {try {Connection con = tl.get (); / / when con is not equal to null, beginTransaction () has been called, indicating that the transaction has been opened! If (con! = null) return con; return getDataSource () .getConnection ();} catch (Exception e) {logger.error ("failed to get database connection!" , e); throw new SQLException ("failed to get database connection!") ;}} / * * start transaction 1. Get a Connection and set its setAutoComnmit (false) * 2. Also make sure that the connection used in dao is the one we just created! -* 3. Create a Connection and set it to manually submit * 4. Give this Connection to dao! * 5. And let commitTransaction or rollbackTransaction get it! * * @ throws SQLException * / public static void beginTransaction () throws Exception {try {Connection con = tl.get (); if (con! = null) {con.close (); tl.remove (); / / throw new SQLException ("Don't open the transaction again!") ;} con = getConnection (); con.setAutoCommit (false); tl.set (con);} catch (Exception e) {logger.error ("Database transaction start failed!" , e); throw new SQLException ("database transaction failed to open!") ;}} / * commit transaction 1. Get the Connection provided by beginTransaction, and then call the commit method * * @ throws SQLException * / public static void commitTransaction () throws SQLException {Connection con = tl.get (); try {if (con = = null) throw new SQLException ("transaction has not been opened yet and cannot be committed!") ; con.commit ();} catch (Exception e) {logger.error ("database transaction submission failed!" , e); throw new SQLException ("database transaction submission failed!") ;} finally {if (con! = null) {con.close ();} tl.remove ();}} / * rollback transaction 1. Get the Connection provided by beginTransaction, and then call the rollback method * * @ throws SQLException * / public static void rollbackTransaction () throws SQLException {Connection con = tl.get (); try {if (con = = null) throw new SQLException ("transaction has not been opened yet and cannot be rolled back!") ; con.rollback ();} catch (Exception e) {logger.error ("database transaction rollback failed!" , e); throw new SQLException ("database transaction rollback failed!") ;} finally {if (con! = null) {con.close ();} tl.remove ();}} / * release connection * @ param connection * @ throws SQLException * / public static void releaseConnection (Connection connection) throws SQLException {try {Connection con = tl.get (); / / determine whether it is transaction-specific, and if so, do not close it! If it is not transaction-specific, then close it! / / if con = = null, which means there is no transaction now, then connection must not be transaction-specific! / / if con! = null means there is a transaction, then you need to determine whether the parameter connection is equal to con. If not, the parameter connection is not a transaction-specific connection if (con = = null | | con! = connection) connection.close ();} catch (Exception e) {logger.error ("Database connection release failed!" , e); throw new SQLException ("database connection release failed!") ;}

Finally, write the unit test program DBCPTest.

Public class DBCPTest {private static final int sumCount = 1000000; private static final int threadNum = 600; private void before (String path) {SysConstants.putValue (path); new DBCPService (). Insert ("delete from t_test");} @ Test public void testMysql () {long start = System.currentTimeMillis (); String path = "config/mysql/dbcp-jdbc.properties"; before (path); for (int I = 0; I)

< 1; i++) { String sql = "insert into t_test(id,name) values('" +i+ "','dbcp-mysql-" + i + "')"; new DBCPService().insert(sql); } System.out.println("耗时：" + (System.currentTimeMillis() - start)); } @Test public void testThreadMysql() throws InterruptedException { String path = "config/mysql/dbcp-jdbc.properties"; before(path); BlockingQueue queue = new LinkedBlockingQueue(); for (int i = 0; i < sumCount; i++) { String sql = "insert into t_test(id,name) values('" +i+ "','dbcp-mysql-" + i + "')"; queue.put(sql); } long start = System.currentTimeMillis(); final CountDownLatch countDownLatch = new CountDownLatch(threadNum); for (int i = 0; i < threadNum; i++) { final int finalI = i + 1; new Thread(new Runnable() { @Override public void run() { System.out.println("thread " + finalI + " start"); boolean isGo = true; while (isGo) { String sql = queue.poll(); if(sql != null) { new DBCPService().insert(sql); }else { isGo =false; System.out.println("thread " + finalI + " finish"); countDownLatch.countDown(); } } } }).start(); } countDownLatch.await(); System.out.println("耗时：" + (System.currentTimeMillis() - start)); } } c3p0、druid的配置也类似，这里就不在重复介绍了! 三、性能测试 程序编写完成之后，下面我们就一起来结合各种不同的场景来测试一下各个数据连接池的表现。 为了进一步扩大测试范围，本次测试还将各个主流的数据库也拉入进去，测试的数据库分别是：mysql-5.7、oracle-12、postgresql-9.6 3.1、插入10万条数据 首先，我们来测试一下，各个数据库插入10万条数据，采用不同的数据源连接池，看看它们的表现如何? 测试dbcp执行结果 测试c3p0执行结果 测试druid执行结果

From the above test results, we can basically draw the following conclusions:

From the point of view of data connection pool performance: dbcp > = druid > c3p0

From the perspective of database performance: oracle > postgresql > mysql

Among them, the performance of druid supporting postgresql is the best, while the performance of c3p0 is relatively poor!

Insert 1 million pieces of data

There may be some students who do not quite approve of it. Let's test the insertion of 1 million items and see how they perform.

Test dbcp execution results

Test c3p0 execution results

Test druid execution results

From the above test results, we can basically draw the following conclusions:

From the point of view of the performance of data connection pool, the performance of druid is relatively stable, and both dbcp and c3p0 fail to some extent.

From the perspective of database performance: postgresql > oracle > mysql

Or the same conclusion, druid support for postgresql performance is the best, c3p0 performance is relatively poor!

IV. Summary

From the above test results, we can clearly see that druid and dbcp are equal in terms of data connection pool, while druid is more stable than dbcp,c3p0 in terms of concurrency and weaker in stability and execution speed than druid and dbcp.

In terms of database, the speed of postgresql is better than that of oracle, while oracle has advantages in support and stability of various data sources. Mysql has a weaker execution speed than oracle and postgresql.

If in the actual development, the data source connection pool is recommended to use druid, the selection of the database postgresql > oracle > mysql.

Thank you for your reading. The above is the content of "what are the ways of database connection pooling?" after the study of this article, I believe you have a deeper understanding of the ways of database connection pooling. The specific use of the situation also needs to be verified by practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.