Comparison of databases connected before and after the connection pool and database connected before and after the connection pool

Comparison of databases connected before and after the connection pool and database connected before and after the connection pool

First, why JDBC?

JDBC refers to java database connection and is oriented to relational databases. It is actually a JAVA encapsulated API. We use Java to execute SQL statements and perform (various) database operations, cross-platform. No matter what the database is, we can use jdbc to connect to and operate the database.

Then, why use the connection pool?

We not only need to connect to the database, but also the connection efficiency. This requires the connection pool. That is to say, I connect n connections in advance. When necessary, I can retrieve them in the pool. I don't need to reproduce the current usage. The speed is faster and the efficiency is high. Connection pools include c3p0 and dbcp. Some Articles have specifically introduced the differences between several connection pools. The conclusion is that c3p0 is the most widely used.

The following compares the efficiency before and after using the connection pool

1. Create a pool management class Mpool

Package com. db;

Import java. beans. PropertyVetoException;
Import java. SQL. Connection;
Import java. SQL. SQLException;

Import com. mchange. v2.c3p0. ComboPooledDataSource;

Public class Mpool {
Private static Mpool mPool;
Private ComboPooledDataSource;

Static {
MPool = new Mpool ();
}
// Constructor
Public Mpool (){
Try {
DataSource = new ComboPooledDataSource ("mysql ");
DataSource. setUser ("root ");
DataSource. setPassword ("root123 ");
DataSource. setJdbcUrl ("jdbc: mysql: // localhost: 3306/test02 ");
DataSource. setDriverClass ("com. mysql. jdbc. Driver ");
DataSource. setInitialPoolSize (2 );
DataSource. setMinPoolSize (1 );
DataSource. setMaxPoolSize (10 );
DataSource. setMaxStatements (50 );
DataSource. setMaxIdleTime (60 );
} Catch (PropertyVetoException e ){
Throw new RuntimeException (e );
}
}

Public final static Mpool getInstance (){
Return mPool;
}

Public final Connection getConnection (){
Try {
Return dataSource. getConnection ();
} Catch (SQLException e ){
Throw new RuntimeException ("unable to obtain connection from data source", e );
}
}

}

2 test class main

Import java. SQL .*;
Import com. db. Mpool ;;

Public class Test {

Public static void main (String [] args ){
// TODO Auto-generated method stub
System. out. println ("using the connection pool .......................");
For (int I = 0; I <20; I ++ ){
Long beginTime = System. currentTimeMillis ();

// 1 get a connection
Connection conn = Mpool. getInstance (). getConnection ();
Try {

// 2 what is the connection going to do? It is encapsulated in the PreparedStatement preparation object. It cannot be spliced, and Placeholders are used.
/* PreparedStatement pstmt = conn
. PrepareStatement ("insert into user (userName, password) value (" + I + ", 111 )");
*/
PreparedStatement pstmt = conn
. PrepareStatement ("insert into user (userName, password) values (?,?) ");
Pstmt. setInt (1, I );
Pstmt. setInt (2, I );
// 3 after preparation, execute the preparation object

/* 3-1 execute the query object ResultSet rs = pstmt.exe cuteQuery ();*/

// 3-2 run insert update and other preparation objects
// Boolean a = pstmt.exe cute (); // returns true if the first result is a result set.
// We recommend that you use executeUpadate () to view the execution result. 0 indicates no execution.
Int a = pstmt.exe cuteUpdate ();
System. out. println ();

/* If it is a query statement, re will return results.
While (rs. next ()){
Re. get content

}*/
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
Long endTime = System. currentTimeMillis ();
System. out. println ("no." + (I + 1) + "the execution time is :"
+ (EndTime-beginTime ));
}

}

}

 

 

3. Just use jdbc for testing.

Import java. SQL .*;

Public class Test1 {

Public static void main (String [] args ){
// TODO Auto-generated method stub
System. out. println ("do not use the connection pool ................................ ");
For (int I = 0; I <20; I ++ ){
Long beginTime = System. currentTimeMillis ();
Try {
Class. forName ("com. mysql. jdbc. Driver"). newInstance ();
} Catch (Exception e1 ){
E1.printStackTrace ();
}
String url = "jdbc: mysql: // localhost: 3306/test02 ";
String user = "root ";
String password = "root123 ";
Connection conn = null;
Try {
// Obtain the conn directly using jdbc
Conn = DriverManager. getConnection (url, user, password );
} Catch (SQLException e1 ){
// TODO Auto-generated catch block
E1.printStackTrace ();
}
Try {
PreparedStatement pstmt = conn
. PrepareStatement ("insert into user (userName, password) values (?,?) ");
Pstmt. setInt (1, I );
Pstmt. setInt (2, I );

Int a implements pstmt.exe cuteUpdate ();
System. out. println ();
/* While (rs. next ()){
// Get content
}*/
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
Long endTime = System. currentTimeMillis ();
System. out. println ("no." + (I + 1) + "the execution time is :"
+ (EndTime-beginTime ));
}
}
}

It can be found that it takes about 40 seconds to connect to the database directly using jdbc. When using the connection pool, it takes a long time to connect for the first time, because it takes about one second to initialize the connection.

In addition, it also has an impact on database operations, such as query and write operations, the time is different.