directory

  • 1. Introduce dependencies in POM
  • 2. Configure properties
  • 3. Practice expansion
    • SpringBoot integration Druid: How to encrypt database passwords in real enterprise applications?

Druid, alibaba’s open source Druid, is available on GitHub.

  • Druid: github.com/alibaba/dru…
  • English: github.com/alibaba/dru…
  • English document: github.com/alibaba/dru…
  • SpringBoot integration Druid: github.com/alibaba/dru…

1. Introduce dependencies in POM

Maven

< the dependency > < groupId > com. Alibaba < / groupId > < artifactId > druid - spring - the boot - starter < / artifactId > < version > 1.1.10 < / version >  </dependency>Copy the code

Gradle

compile 'com. Alibaba: druid - spring - the boot - starter: 1.1.10'
Copy the code

2. Configure properties

The druid configuration properties in application.yml are as follows:

Spring: a datasource: druid: url: JDBC: mysql: / / 127.0.0.1:3306 /test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
      Initialize the number of physical connections
      initial-size: 1
      # Maximum number of connection pools
      max-active: 20
      Minimum number of connection pools
      min-idle: 5
      Maximum waiting time (ms)
      max-wait: 60000
      PreparedStatement (PSCache)
      pool-prepared-statements: true
      After PSCache is enabled, specify the size of PSCache on each connection
      max-pool-prepared-statement-per-connection-size: 20
      SQL to check whether the connection is valid
      validation-query: select 'x'
      Do not check whether the connection is valid when requesting a connection
      test-on-borrow: false
      Return the connection without checking whether the connection is valid
      test-on-return: false
      # when applying for connection testing, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is effective (does not affect performance)
      test-while-idle: true
      # test connection interval time, if the connection free time > = minEvictableIdleTimeMillis, then close the physical connection
      time-between-eviction-runs-millis: 60000
      Minimum time for a connection to remain idle without being expelled (ms)
      min-evictable-idle-time-millis: 300000
      Filter :stat, log filter:log4j, and filter:wall to prevent SQL injection
      filters: stat.log4j,wall
      DruidDataSource supports merging multiple DruidDataSource data
      use-global-data-source-stat: true
      Enable mergeSql via connectProperties; Slow SQL records (more than 5 seconds is slow, default is 3 seconds)
      connection-properties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000Copy the code

Druid GitHub addresses: github.com/alibaba/dru…

Reference value of each configuration attribute, address: github.com/alibaba/dru…

DruidDataSource data merge, DruidDataSource data merge, DruidDataSource data merge

Notes on some configurations:

  1. Some people may have questions about the PSCache configuration.

    PreparedStatement (PSCache)
pool-prepared-statements: true
After PSCache is enabled, specify the size of PSCache on each connection
max-pool-prepared-statement-per-connection-size: 20
Copy the code

    Druid recommends that the preparedStatement cache (PSCache) be disabled for MySQL databases, and pool-prepared-statements be set to false. The reason is that PSCache greatly improves the performance of databases that support cursors, such as Oracle. You are advised to disable PSCache in mysql.

    InfoQ has an article on whether to enable PSCache for MySQL at www.infoq.cn/article/201…

  2. Description of url and driver-class-name configuration items.

    Url: JDBC: mysql: / / 127.0.0.1:3306 /test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code

    • Driver = com.mysql.cj.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver = com.mysql.jdbc.driver

      The reason is: starting with mysql6, the Driver package starts using a new Driver, com.mysql.cj.jdbc.driver. If you are using a version of mysql later than 6.0 but using an older version 5.0 Driver package, configure the Driver as com.mysql.jdbc.driver. However, this may cause some unexpected problems, so it is recommended to upgrade the Driver package and change the Driver value.

      Solutions:

      <1> Check your mysql version first.

      Mysql: select VERSION();

      Related article introduction link address: blog.csdn.net/lamp_yang_3…

      <2> If the mysql version is later than 5.0, set driver-class-name to com.mysql.cj.jdbc.driver

      Related exception information: Loading class com.mysql.jdbc.Driver. This is deprecated. The new driver class is com.mysql.cj.jdbc.Driver. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

    • The url configuration item is serverTimezone=GMT%2B8

      The time zone must be specified serverTimezone=GMT%2B8. UTC stands for global standard time. GMT%2B8 is 8 hours ahead of UTC.

      If serverTimezone=UTC is configured, the connection does not fail, but the actual time displayed in the database is 8 hours later than the time we inserted.

      8 East Beijing time
serverTimezone=GMT%2B8
# Shanghai time
serverTimezone=Asia/Shanghai
# Celebration time
serverTimezone=Asia/Chongqing
Copy the code

      You can refer to the following article:

      A JDBC connection Mysql database problems summary: www.jianshu.com/p/bbc76bc5b…

      Mysql error zones and solution: www.jianshu.com/p/0d53218da…

      Why is there no Asia/Beijing time zone? : www.cnblogs.com/softidea/p/…

Now that the SpringBoot project integration with the Druid connection pool is complete, start the SpringBoot application, open a browser, and type:http://127.0.0.1:8080/druid/index.html,The Druid Monitor monitoring page is displayed.

3. Practice expansion

SpringBoot integration Druid: How to encrypt database passwords in real enterprise applications?

Jar folder, open the cli, and run the following command: druid-1.1.10.jar

Java - cp druid - 1.0.16. Jar com. Alibaba. The druid. Filter. Config. ConfigTools 123456 description: 123456 is to encrypt plaintext passwordCopy the code

The output

privateKey:MIIBVAIBADANBgkqhkiG9w0BAQEFAASCAT4wggE6AgEAAkEAljkI+2y1h27So4a0+OyZGp093q0VrMZKKgZHCX1X3VHdG914tZsQIpm4SJ9gA QDCeRh5ZcxOI2v2inD+0AQN0QIDAQABAkAmkXom4yAwJ1nZNiqchlJArQJHe3THKRm9dGMkopO+dHN2lKqP1X4FUS7/sPf08uQ+ijH063St+YrNk6qj3nUpAiEAxfGjUG2ZbIYjKq Ax9NhGFi1s4xwzp0kK6hUkvRhpKI8CIQDCSFHQVWT2PopjaLWaDTVYUebLZlSOuz8rU6LayFrTnwIhALCIYG5KUJpIxvQtCmCiwV7mBEZH0IlQyzsYwDskmi RhAiBv4Q2cF1DorOMsk+PgLrMFhUSO5QLUnueln6gsrSwAqQIgH0jlqWKuwuuTwZSM4P/HS4YaacrLL70Fe101YF4fjCs= publicKey:MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJY5CPtstYdu0qOGtPjsmRqdPd6tFazGSioGRwl9V91R3RvdeLWbECKZuEifYAEAwnkYeWXMTiNr9opw/tAEDdECAwEAAQ== Password: O50UxdSUY9ZGfafYEh9 ovo5Lb8b/FaGlOaZstW + UqMrrz + fanCSALo9OJHrpykY2fyrKdcjp5aE57dzhFRMPQ = = description: PrivateKey is the privateKey, publicKey is the publicKey, and password is the encrypted passwordCopy the code

In application. Yml, the druid configuration properties are as follows:

Spring: a datasource: druid: url: JDBC: mysql: / / 127.0.0.1:3306 /test? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=false
      username: root
      password: O50UxdSUY9ZGfafYEh9/ovo5Lb8b/FaGlOaZstW+UqMrrz+fanCSALo9OJHrpykY2fyrKdcjp5aE57dzhFRMPQ==
      driver-class-name: com.mysql.cj.jdbc.Driver
      Initialize the number of physical connections
      initial-size: 1
      # Maximum number of connection pools
      max-active: 20
      Minimum number of connection pools
      min-idle: 5
      Maximum waiting time (ms)
      max-wait: 60000
      PreparedStatement (PSCache)
      pool-prepared-statements: true
      After PSCache is enabled, specify the size of PSCache on each connection
      max-pool-prepared-statement-per-connection-size: 20
      SQL to check whether the connection is valid
      validation-query: select 'x'
      Do not check whether the connection is valid when requesting a connection
      test-on-borrow: false
      Return the connection without checking whether the connection is valid
      test-on-return: false
      # when applying for connection testing, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is effective (does not affect performance)
      test-while-idle: true
      # test connection interval time, if the connection free time > = minEvictableIdleTimeMillis, then close the physical connection
      time-between-eviction-runs-millis: 60000
      Minimum time for a connection to remain idle without being expelled (ms)
      min-evictable-idle-time-millis: 300000
      Filter :stat, log filter:log4j, and filter:wall to prevent SQL injection
      filters: config,stat.log4j,wall
      DruidDataSource supports merging multiple DruidDataSource data
      use-global-data-source-stat: true
      Enable mergeSql via connectProperties; Slow SQL records (more than 5 seconds is slow, default is 3 seconds); Decryption can make
      connection-properties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000; config.decrypt=true; config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJY5CPtstYdu0qOGtPjsmRqdPd6tFazGSioGRwl9V91R3RvdeLWbECKZuEifYAEAwnkYeWXMTiNr9opw/tAEDdECAwEAAQ==
Copy the code

Configuration change description:

  • First change the password to the encrypted password

    password: O50UxdSUY9ZGfafYEh9/ovo5Lb8b/FaGlOaZstW+UqMrrz+fanCSALo9OJHrpykY2fyrKdcjp5aE57dzhFRMPQ==
Copy the code

  • Add config to the filters configuration value

    filters: config,stat.log4j,wall
Copy the code

    Description: Druid on database password encryption and decryption are com. Alibaba. Druid. Filter. Config. ConfigFilter this class, so the data source configuration items must be added on the filters on the config value, otherwise it will always be database connection exception is thrown.

  • Added the connection-properties connection property configuration item

    connection-properties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000; config.decrypt=true; config.decrypt.key=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAJY5CPtstYdu0qOGtPjsmRqdPd6tFazGSioGRwl9V91R3RvdeLWbECKZuEifYAEAwnkYeWXMTiNr9opw/tAEDdECAwEAAQ = = note: connection - the properties configuration values can configure multiple, use ";" Separate config. The decrypt =trueIndicates that config.decrypt.key= is enabled for decryption${publickey}Decrypt using the publicKey obtained aboveCopy the code

Druid GitHub provides an introduction to database encryption at github.com/alibaba/dru…