Alibaba Druid database connection pool take-off directly

1. What is a database connection pool

Why use connection pooling: When we develop in Java, we need to access the database, but Java can't access the database directly. We have to establish a connection between the program and the database through JDBC. Performing a transaction requires creating a connection. The process of establishing a connection between the program and the database is the most time consuming, and when the program becomes large, Frequent connections between programs and databases can be time consuming, so programmers have the concept of connection pools

Connection pool: Stores the channels to connect to the database, that is, Connections in JDBC. n connections are defined beforehand. These connections are initialized when the program starts, taken directly from the connection pool later on, and put back into the connection pool. A large number of repetitive connections and release operations are omitted.

2. Druid Connection Pool

Druid: The Druid connection pool was developed by the Alibaba team and introduced the concept of "slow loading".

Slow load: When creating a connection pool object, the database is not connected to the number of connection channels that have been defined beforehand. Instead, when the first connection channel is taken from the middle connection pool, the connection pool checks to see if there are any useful connection channels inside, and if so, if there are no connections to the database, it performs n connection channels that have been defined beforehand.

3. jar/dependency of Druid connection pool

1. Download Druid.jar

Druid.1.2.8.jar

2. Import Dependency

<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency>

Either way.

4. Code representation

4.1. Create a properties file

Create a properties Files, mainly for Druid Connection pool one constraint

driverClassName = com.mysql.cj.jdbc.Driver url = jdbc:mysql://localhost:3306/javaweb?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull username = root password = 123456 initialSize = 5 //Number of Initialized Connection Channels maxActive = 5 //Maximum number of available connection channels maxWait = 3000 //The maximum time, in milliseconds, to wait when all connection channels are occupied

4.2, Create a DruidUtils class

Read the configuration file into memory and wait for the connection channel to be fetched

public class DruidUtils { private static DataSource dataSource = null; private static Properties properties = null; static{ properties = new Properties(); try { //Load profile into memory using reflection technology properties.load(DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties")); //Get a connection pool object from factory design mode, get a connection pool object, not a connection object dataSource = DruidDataSourceFactory.createDataSource(properties); } catch (Exception e) { throw new RuntimeException("Connection pool profile loading failed"); } } //Open Connection Pool Objects for External Use public static DataSource getDataSource(){ return dataSource; } //Get Connection from Connection Pool Object public static Connection getConnection() throws SQLException { return dataSource.getConnection(); } }

4.3, Testing

1. You need to first get a connection pool object and then get a connection from the connection pool object
2. Once connected, the operation is the same as JDBC operation database

4.3.0, Print, View Connection Pool

private static DataSource dataSource = DruidUtils.getDataSource(); //Get Connection Pool Object public static void main(String[] args){ System.out.println(dataSource); }

Result

analysis

4.3.1, Add User Method

private static String addUser(User user){ String sql = "INSERT INTO user VALUES(?,?,?,?,?)";//SQL statement int result = 0; //Results after execution try { //Get Connection Channel from Connection Pool Object Connection connection = dataSource.getConnection(); //The following actions execute the SQL statement as in JDBC PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1,user.getId()); preparedStatement.setString(2,user.getUserName()); preparedStatement.setString(3,user.getPassword()); preparedStatement.setString(4,user.getSex()); preparedStatement.setString(5,user.getPhone()); result = preparedStatement.executeUpdate(); //Be sure to put the connection in the connection pool again after the operation is completed, or the connection pool will remain occupied and cannot be used by other things. connection.close(); } catch (SQLException e) { e.printStackTrace(); } return result > 0 ? "Successfully added user" : "Failed to add user"; }

4.3.1, Get all user information

private static List<User> getUserList() { List<User> userList = new ArrayList<>(); ResultSet result = null; String sql = "SELECT * FROM user"; try { //Get Connection Channel from Connection Pool Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql); result = preparedStatement.executeQuery(); User user = null; while (result.next()) { user = new User(result.getString("id"),result.getString("username"), result.getString("password"), result.getString("sex"), result.getString("phone")); userList.add(user); } connection.close(); } catch (SQLException e) { e.printStackTrace(); } return userList; }

4.3.3, Main method

public class DruidPoolTest { private static DataSource dataSource = null; public static void main(String[] args) { dataSource = DruidUtils.getDataSource(); //Get Connection Pool Object System.out.println("-----------Add a user to the database-----------------"); User user = new User("6","Boiling in King water","999999","female","13888887878"); System.out.println(addUser(user)); System.out.println("--------------Query all-------------"); List<User> userList = getUserList(); for(User user : userList){ System.out.println(user.toString()); } } }

Result

There are currently five pieces of user information in the database

After performing the operation in the Main method

There are six pieces of data, and the data was added successfully. Because of the "slow-loading" technology used by Druid connection pools, N connection channels need to be initialized when the program gets the connection channel for the first time because there is no connection in the connection pool, so it will start slowly.

5. Exception thrown by not returning connection to connection pool

The number of connection channel bars I defined in the properties file beforehand is five, and the maximum number of active bars is five.
And now I want to get six:

public static void main(String[] args) { dataSource = DruidUtils.getDataSource(); //Get Connection Pool Object try { Connection connection1 = dataSource.getConnection();//First Connection Connection connection2 = dataSource.getConnection();//Second Connection Connection connection3 = dataSource.getConnection();//Third Connection Connection connection4 = dataSource.getConnection();//Fourth Connection Connection connection5 = dataSource.getConnection();//Fifth Connection Connection connection6 = dataSource.getConnection();//Sixth Connection } catch (SQLException e) { e.printStackTrace(); } }

Direct error

Reason

Reason 1: There are no available connection channels in the connection pool. Five channels are defined. The maximum available is also five channels. All are not available.
Reason 2: I did not release the connection after a transaction, which prevented other transactions from getting the connection channel.

Solution

Solution 1: Expand the maximum number of connections available in the connection pool
initialSize = 5 //Initialize five connection channels
maxActive = 10 // Maximum available connection channels are 10

Solution 2: After the transaction is executed, the connection is returned to the connection pool.

Solution 2 is recommended because in large projects, it is not recommended to move around and increase the number of connection channels

6. Summary

Druid connection pooling is also a popular connection pooling technology, but it only solves the performance optimization of connection channels in connection and release. Other ways of operating databases are the same as JDBC, so there are framework technologies such as MyBatis on the market.