Using Durid for database connection pool management in Java API development

During the development process of Java API, connecting to the database is a common requirement. Since using DriverManager to obtain a database connection every time will bring high overhead, it is necessary to use connection pooling technology. Druid is a high-performance JDBC connection pool with a series of powerful functions such as monitoring, statistics, and expansion. This article will introduce how to use Druid in the development of Java API.

1. Introduction to Druid
Druid is a database connection pool developed by Alibaba. It has the characteristics of excellent performance, rich functions, and simple use. The advantages of Druid connection pool are as follows:

1. Quickly respond to SQL requests
2. Provide SQL monitoring capabilities
3. Provide the average, maximum, minimum value of SQL execution time, etc. Statistics
4. Enable anti-SQL injection function
5. Druid’s code is as small as the database driver, no more than 2MB, which is very suitable for embedded applications.

2. Use Druid for connection pool management
Let’s look at how to use Druid for connection pool management in Java applications.

1. Introduce Druid dependencies
   Introduce Druid related dependencies in pom.xml

<!-- druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>

2. Configure Druid connection pool
   Before using Druid connection pool , which needs to be configured. Druid configuration can be achieved through two methods: code and configuration files. Here we take the code method as an example.

//创建 Properties 对象用于存储配置信息
Properties props = new Properties();

//设置连接池基本属性
props.put("driverClassName", "com.mysql.jdbc.Driver");
props.put("url", "jdbc:mysql://localhost:3306/test");
props.put("username", "root");
props.put("password", "root");

//连接属性配置
props.put("initialSize", "5");
props.put("minIdle", "5");
props.put("maxActive", "20");
props.put("maxWait", "60000");
props.put("timeBetweenEvictionRunsMillis", "60000");
props.put("minEvictableIdleTimeMillis", "300000");
props.put("validationQuery", "SELECT 'x'");
props.put("testWhileIdle", "true");
props.put("testOnBorrow", "false");
props.put("testOnReturn", "false");
props.put("poolPreparedStatements", "true");
props.put("maxOpenPreparedStatements", "20");
props.put("filters", "stat,wall,log4j");

//创建 DruidDataSource 实例
DruidDataSource dataSource = new DruidDataSource();
dataSource.configFromPropety(props);

In the above code, we set the database connection URL, user name, password, as well as the initial number of connections, the maximum number of connections and other configuration information. Among them, the meaning of each configuration is as follows:

• driverClassName: JDBC driver full class name
• url: Database connection URL
• username: Database connection user name
• password: Database connection password
• initialSize: The number of physical connections established during initialization. Initialization occurs at startup.
• maxActive: Maximum number of connection pools
• minIdle: Minimum number of connection pools
• maxWait: Maximum waiting time when obtaining a connection, in milliseconds.
• timeBetweenEvictionRunsMillis: has two meanings: 1) The Destroy thread will detect the connection interval. 2) The judgment basis of testWhileIdle, please refer to the description of testWhileIdle attribute for details

• minEvictableIdleTimeMillis: The minimum survival time of the connection in the pool, the unit is milliseconds.
  validationQuery’ is used to check whether the connection is valid. The requirement is a query statement (the execution result of the statement does not matter).
  -testWhileIdle: Tested when applying for a connection. If the idle time is greater than timeBetweenEvictionRunsMillis, execute validationQuery to check whether the connection is valid.
  -testOnBorrow: When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce performance.
  -testOnReturn: When returning the connection, execute validationQuery to check whether the connection is valid. Doing this configuration will reduce performance and add a judgment to the connection pool.
  -poolPreparedStatements: whether to cache preparedStatement, that is, PSCache. PSCache greatly improves the performance of databases that support cursors, such as Oracle. It is recommended to close it under mysql.
  -maxOpenPreparedStatements: After turning on poolPreparedStatements, you need to specify the number of cached statements on each connection
  -filters: The attribute type is a string, and the extension plug-in is configured through an alias. Commonly used plug-ins are:

    监控统计用的filter:stat
    日志用的filter:log4j
    防御SQL注入的filter:wall
  

3. Get the database connection
   After the DruidDataSource instance is created, we can get the connection through the DruidDataSource.getConnection() method.

Connection conn = dataSource.getConnection();

4. Implementation of monitoring statistics and SQL execution logs
   In addition to the basic functions of the connection pool, Druid also provides practical functions such as monitoring statistics and SQL execution logs. We can achieve this by setting Filter when developing code.

// 创建 Druid 连接池
DruidDataSource dataSource = new DruidDataSource();
//... 配置数据库连接池信息

// 配置监控统计
// 设置监控统计用的 Filter，用于统计监控信息
WallFilter wallFilter = new WallFilter();
wallFilter.setDbType("mysql");

StatFilter statFilter = new StatFilter();
statFilter.setSlowSqlMillis(1000);
statFilter.setLogSlowSql(true);

// 设置 Druid 连接池的 Filters
dataSource.setProxyFilters(Arrays.asList(wallFilter, statFilter));

// 打印 SQL 执行日志
Log4jFilter logFilter = new Log4jFilter();
logFilter.setStatementExecutableSqlLogEnable(true);
dataSource.setProxyFilters(Collections.singletonList(logFilter));

During the specific implementation process, we can choose to use monitoring statistics or log functions as needed.

3. Conclusion
This article introduces how to use Druid for connection pool management in the development of Java API. By using Druid, we can not only improve the performance of the application, but also gain a more comprehensive understanding of the operation of the application through functions such as monitoring statistics and SQL execution logs.

The above is the detailed content of Using Durid for database connection pool management in Java API development. For more information, please follow other related articles on the PHP Chinese website!