This is the sixth day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021
Introduction to the
After reading about Alibaba Druid’s database connection pool last week, I felt I didn’t understand the essence of it just by looking at it. In the next few articles, I will try to implement a database connection pool Demo by myself
Native JDBC and Alibaba Druid use
The JDBC and Druid sample code can be used to test JDBC and Druid
After implementing a simple custom connection pool, run the three to compare
Native JDBC
We first initialize the database data using native JDBC and then query it as follows:
public class Main {
static final String DB_URL = "jdbc:h2:file:./demo-db";
static final String USER = "sa";
static final String PASS = "sa";
/** * Generate data */
private static void initData(a) {
final String drop = "drop table `user_example` if exists;";
final String createTable = "CREATE TABLE IF NOT EXISTS `user_example` (" +
"`id` bigint NOT NULL AUTO_INCREMENT, " +
"`name` varchar(100) NOT NULL" +
");";
final String addUser = "insert into user_example (name) values(%s)";
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
stmt.execute(drop);
stmt.execute(createTable);
for (int i=0; i<10; i++) {
stmt.execute(String.format(addUser, i));
}
conn.commit();
} catch(SQLException e) { e.printStackTrace(); }}}Copy the codeThen the raw violence query:
public class Main {
/**
* 原生JDBC查询
*/
private static void rawExample(a) {
for (int i=0; i<queryAmount; i++) {
// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(QUERY);) {
// Extract data from result set
while (rs.next()) {
// Retrieve by column name
System.out.print("ID: " + rs.getInt("id"));
System.out.print(", name: " + rs.getString("name"));
System.out.print(";");
}
System.out.println();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeSingle connection query:
/** * native JDBC query single connection query */
private static void rawSingleExample(a) {
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
for (int i=0; i<queryAmount; i++) {
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(QUERY);) {
while (rs.next()) {
System.out.print("ID: " + rs.getInt("id"));
System.out.print(", name: " + rs.getString("name"));
System.out.print(";"); } System.out.println(); }}}catch(SQLException e) { e.printStackTrace(); }}Copy the codeAlibaba, a Druid query
Use Alibaba Druid to perform the same query:
public class Main {
/** * Alibaba Druid query */
private static void druidExample(a) throws Exception {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setInitialSize(1);
dataSource.setMaxActive(1);
dataSource.setMinIdle(1);
dataSource.setDriverClassName("org.h2.Driver");
dataSource.setUrl(DB_URL);
dataSource.setUsername(USER);
dataSource.setPassword(PASS);
for (int i=0; i<queryAmount; i++) {
// Open a connection
try(Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(QUERY)) {
// Extract data from result set
while (rs.next()) {
// Retrieve by column name
System.out.print("ID: " + rs.getInt("id"));
System.out.print(", name: " + rs.getString("name"));
System.out.print(";");
}
System.out.println();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeWrite the simplest version query
Related ideas are as follows:
- 1. Write a Database SelfDatasource based on the Database interface
- Implement your own getConnection method
- 2. Based on the interface: javax.mail. SQL. A javax.sql.pooledconnection, Connection, a custom data physical Connection
- Recycle the database connection to customize the close method
The custom Database
The other methods default for now:
In the test code, a getConnection function was involved, along with custom connection pool reuse
public class SelfDataSource implements DataSource {
/** * place free available connections */
private final Queue<SelfPoolConnection> idle = new LinkedList<>();
/** * places the connection */ that is in use
private final Set<SelfPoolConnection> running = new HashSet<>();
private final String url;
private final String username;
private final String password;
public SelfDataSource(final String url, final String username, final String password) {
this.url = url;
this.username = username;
this.password = password;
}
/** * initially removed Connection exception from running pool, put in free pool * Removed Connection pool in use, put in free pool *@paramSelfPoolConnection User-defined Connection */
public void recycle(final SelfPoolConnection selfPoolConnection) {
running.remove(selfPoolConnection);
idle.add(selfPoolConnection);
System.out.println("Reclaim connection");
}
/** * Custom get database physical connection * 1. If there is no free connection, create a new physical connection and put it into the connection pool * 2. Gets, if there are free connections, and puts them in the connection pool * that is in use@returnCustom database physical connection (custom to be able to customize the Close method) *@throws SQLException
*/
@Override
synchronized public Connection getConnection(a) throws SQLException {
if (idle.isEmpty()) {
System.out.println("Make a new physical connection");
SelfPoolConnection conn = new SelfPoolConnection(this, url, username, password);
running.add(conn);
return conn.getConnection();
}
SelfPoolConnection conn = idle.poll();
running.add(conn);
returnconn.getConnection(); }}Copy the codeCustom Connection
Because in the test code, the functions involved need to be implemented as follows, the other default implementation is ok
- createStatement
- Close (try automatically called)
- GetConnection (custom DataSource call)
public class SelfPoolConnection implements javax.sql.PooledConnection.Connection {
private final SelfDataSource selfDatasource;
private Connection connection;
public SelfPoolConnection(final SelfDataSource selfDatasource, final String url, final String username, final String password) {
this.selfDatasource = selfDatasource;
System.out.println("Initializing a physical connection");
try {
connection = DriverManager.getConnection(url, username, password);
} catch(SQLException e) { e.printStackTrace(); }}@Override
public Statement createStatement(a) throws SQLException {
return connection.createStatement();
}
@Override
synchronized public Connection getConnection(a) throws SQLException {
return this;
}
/** * Close the connection, call the custom DataSource for reuse ** *@throws SQLException
*/
@Override
public void close(a) throws SQLException {
selfDatasource.recycle(this); }}Copy the codeA test run
The test function is as follows:
public class Main {
/** * Custom database connection pool query */
private static void selfExample(a) {
final SelfDataSource dataSource = new SelfDataSource(DB_URL, USER, PASS);
for (int i=0; i<queryAmount; i++) {
// Open a connection
try(Connection conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(QUERY)) {
// Extract data from result set
while (rs.next()) {
// Retrieve by column name
System.out.print("ID: " + rs.getInt("id"));
System.out.print(", name: " + rs.getString("name"));
System.out.print(";");
}
System.out.println();
} catch(SQLException e) { e.printStackTrace(); }}}}Copy the codeRun the contrast
The running function and result are as follows:
public class Main {
/** * Single-threaded test code * Note: Do not run tests together, feeling cached, resulting in faster queries to be run later * need to run that alone release, other comments out *@param args args
* @throws Exception e
*/
public static void main(String[] args) throws Exception {
// initData();
final StringBuilder result = new StringBuilder();
long current = System.currentTimeMillis();
// rawExample();
Format (" Native query Time: %d ms \n", system.currentTimemillis () -current)); // result.append(string.format (" Native query time: %d ms \n", system.currentTimemillis () -current));
// rawSingleExample();
// result.append(string. format(" Native Jdbc single connection query time: %d ms \n", system.currentTimemillis () -current));
// druidExample();
Format ("Druid connection pool query time: %d ms \n", system.currentTimemillis () -current)); // result.append(string. format("Druid connection pool query time: %d ms \n", system.currentTimemillis () -current));
selfExample();
result.append(String.format("Write connection pool query time: %d ms \n", System.currentTimeMillis() - current)); System.out.println(result); }}Copy the codeResults:
ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Native query time: 1715 ms ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Native Jdbc single-connection query duration: 770 ms ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Druid Connection pool Query Time: 588 ms Generate a new physical connection. Initialize the physical connection ID: 1, name: 0. ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Reclaim connection ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Reclaim connection ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Reclaim connection ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Reclaim connection ID: 1, name: 0; ID: 2, name: 1; ID: 3, name: 2; ID: 4, name: 3; ID: 5, name: 4; ID: 6, name: 5; ID: 7, name: 6; ID: 8, name: 7; ID: 9, name: 8; ID: 10, name: 9; Reclaim connection The query time of the self-write connection pool is 473 msCopy the codeconclusion
From the interface point of view, part of this is what we expect: the performance of connection pooling is much better than without it
I didn’t expect custom connection pooling to be faster than Druid, so I was a little skeptical
But the relevant thing is that it was implemented, and from the log point of view, it was only initialized once, and no physical connection was initialized later
The current example is single-threaded, and does not consider locking, checking, exception handling, etc. It may be that these have an effect, which we will investigate later
The code reference address can be viewed at Tag V0.0.1: github.com/lw124392545…
Refer to the link
- Alibaba Druid source code reading (a) database connection pool preliminary
- Alibaba Druid source code reading (2) database connection pool implementation preliminary exploration
- Alibaba Druid source code reading (3) database connection pool initialization exploration
- Alibaba Druid source code reading (4) database connection pool access to explore
- Alibaba Druid source code reading (five) database connection pool connection closure exploration