1, What is Druid?

1.1 concept

Druid is a database connection pool implementation on Alibaba's open source platform. It combines the advantages of C3P0, DBCP, Proxool and other DB pools, and adds log monitoring. It can well monitor the connection of DB pool and the execution of SQL. It can be said that it is a DB connection pool for monitoring, which can be said to be one of the best connection pools at present.

1.2 Druid advantages

① It combines the advantages of C3P0, DBCP, Proxool and other DB pools;

② Fast speed and good stability;

③ It can well monitor the connection of DB pool and the execution of SQL.

2, How do I connect to the Druid database connection pool?

2.1 import jar package

Put the jar package into the lib directory under the current project, and right-click the driver jar -- > build path -- > add to build path.

2.2 configuration file information under SRC: [druid.properties]

url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall

Detailed configuration parameters:

to configure	default	explain
name		The significance of configuring this attribute is that if there are multiple data sources, they can be distinguished by name during monitoring. If there is no configuration, a name will be generated in the format of "DataSource -" + system identityHashCode(this)
url		The url to connect to the database is different from database to database. For example: MySQL: JDBC: mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		User name to connect to the database
password		Password to connect to the database. If you don't want the password written directly in the configuration file, you can use ConfigFilter. See here for details: https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter
driverClassName		Automatic identification according to url is optional. If druid is not configured, dbType will be automatically identified according to url, and then corresponding driverclassname will be selected (under recommended configuration)
initialSize	0	The number of physical connections established during initialization. Initialization occurs when the display calls the init method or the first getConnection
maxActive	8	Maximum number of connection pools
maxIdle	8	It is no longer used, and the configuration has no effect
minIdle		Minimum number of connection pools
maxWait		The maximum waiting time to get a connection, in milliseconds. After maxWait is configured, the fair lock is enabled by default, and the concurrency efficiency will be reduced. If necessary, you can use the unfair lock by configuring the useUnfairLock attribute to true.
poolPreparedStatements	false	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 under mysql.
maxOpenPreparedStatements	-1	To enable PSCache, it must be configured to be greater than 0. When greater than 0, poolPreparedStatements will be automatically triggered and modified to true. In Druid, there will be no problem that PSCache in Oracle occupies too much memory. You can configure this value to be larger, such as 100
validationQuery		The sql used to check whether the connection is valid requires a query statement. If validationQuery is null, testonmirror, testOnReturn and testwhiteidle will not work.
testOnBorrow	true	When applying for a connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance.
testOnReturn	false	When returning the connection, execute validationQuery to check whether the connection is valid. This configuration will reduce the performance
testWhileIdle	false	It is recommended to configure to true, which will not affect performance and ensure security. Check when applying for connection. If the idle time is greater than timebetween evictionrunsmillis, execute validationQuery to check whether the connection is valid.
timeBetweenEvictionRunsMillis		It has two meanings: 1) the destroy thread will detect the connection interval; 2) the judgment basis of testwhiteidle. See the description of testwhiteidle attribute for details
numTestsPerEvictionRun		No longer used, a DruidDataSource only supports one EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		sql executed during physical connection initialization
exceptionSorter		According to dbType, the connection will be discarded when the database throws some unrecoverable exceptions
filters		The attribute type is string. The extension plug-ins are configured by alias. The commonly used plug-ins are: filter for monitoring statistics: stat, filter for log: log4j, filter for defending sql injection: wall
proxyFilters		The type is List. If filters and proxyFilters are configured at the same time, it is a combination relationship, not a replacement relationship

2.3 it is encapsulated in a tool class, and its code is as follows:

  /*
    *Using Druid database connection pool technology
     */
    private static DataSource source1;//You only need to create a database connection pool
    static {
        try {
            Properties pros = new Properties();
            FileInputStream is = new FileInputStream(new File("src/druid.properties"));
            pros.load(is);
            source1 = DruidDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection3() throws Exception {
        Connection conn = source1.getConnection();
        return conn;
    }
}

3, Use Druid to add, delete, modify and query

3.1 add, delete and modify

The code is as follows:

//    Test insertion
    @Test
    public void testInsert() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "delete from customers where id = ?";
            runner.update(conn,sql,14);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

Note: deletion and modification are only different from sql statements, which will not be demonstrated here.  

3.2 query operation

Query a record with the following code:

 /*
    *BeanHandler Is the implementation class of ResultSetHandler interface, which is used to encapsulate a record in the table
     */
    @Test
    public void testQuery1() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id = ?";
            BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
            Customer customer = runner.query(conn, sql, handler, 13);
            System.out.println(customer);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }

Query multiple records with the following code:

/*
    *BeanListHandler It is the implementation class of ResultSetHandler interface, which is used to encapsulate the collection composed of multiple records in the table
     */
    @Test
    public void testQuery2() {
        Connection conn = null;
        try {
            QueryRunner runner = new QueryRunner();
            conn = JDBCUtils.getConnection3();
            String sql = "select id,name,email,birth from customers where id < ?";
            BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
            List<Customer> list = runner.query(conn, sql, listHandler, 13);
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn,null);
        }
    }