Druid连接池

1.JDBC直接使用

1.1 pom.xml

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

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.19</version>
</dependency>

1.2.druid.properies

url=jdbc:mysql://127.0.0.1/servlet?serverTimezone=UTC
#这个可以缺省的，会根据url自动识别
driverClassName=com.mysql.cj.jdbc.Driver
username=root
password=lwxMysql

##初始连接数，默认0
initialSize=10
#最大连接数，默认8
maxActive=30
#最小闲置数
minIdle=10
#获取连接的最大等待时间，单位毫秒
maxWait=2000
#缓存PreparedStatement，默认false
poolPreparedStatements=true
#缓存PreparedStatement的最大数量，默认-1（不缓存）。大于0时会自动开启缓存PreparedStatement，所以可以省略上一句设置
maxOpenPreparedStatements=20

1.3 使用

@Test
public void DruidTest() throws Exception {
    //数据源配置
    Properties properties = new Properties();
    //通过当前类的class对象获取资源文件
    InputStream is = Test.class.getResourceAsStream("/druid.properties");
    properties.load(is);
    //返回的是DataSource，不是DruidDataSource
    DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

    //获取连接
    Connection connection = dataSource.getConnection();

    //Statement接口
    Statement statement = connection.createStatement();
    String sql1 = "select * from comment";
    statement.execute(sql1);

    //PreparedStatement接口
    String sql2 = "insert into user (username,password) values ('lwx','123456')";
    PreparedStatement preparedStatement = connection.prepareStatement(sql2);
    preparedStatement.execute();

    //关闭连接
    connection.close();
}

2. Mybatis中使用

2.1 db.properties

jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/servlet?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=GMT
jdbc.user=root
jdbc.password=lwxMysql

2.2 DruidDataSourceFactory配置类

public class DruidDataSourceFactory implements DataSourceFactory {

    private Properties properties;

    @Override
    public void setProperties(Properties properties) {
        this.properties = properties;
    }

    @Override
    public DataSource getDataSource() {
        DruidDataSource dds = new DruidDataSource();
        dds.setUrl(this.properties.getProperty("url"));
        dds.setUsername(this.properties.getProperty("username"));
        dds.setPassword(this.properties.getProperty("password"));
        try {
            dds.init();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dds;
    }
}

2.3 mybatisConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <!-- 外部 properties 配置文件  -->
    <properties resource="db.properties"/>

    <!--配置数据源-->
    <environments default="development-mysql">
        <environment id="development-mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="org.example.DruidDataSourceFactory">
                <property name="url" value="${jdbc.jdbcUrl}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- mappers：将 SQL 映射文件注册到全局配置中 -->
    <mappers>
        <!-- 引用类路径下的 SQL 映射文件 -->
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

2.4 resources目录下创建mapper目录下新建UserMapper.xml，pojo包下创建User，mapper包下UserMapper

2.5 使用

@Test
    public void DruidMybatisTest() throws Exception {
    SqlSession session = null;
    try {
        String resource = "mybatisConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();
      
        UserMapper userMapper = session.getMapper(UserMapper.class);
        userMapper.insertUser("lwx","123445");

        session.commit();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (session != null) {
            session.close();
        }
    }
}

3.Springboot集成

3.1 pom.xml

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

3.2 application.yml

datasource:
    type: com.alibaba.druid.pool.DruidDataSource
#   数据源其他配置
    #初始化时建立物理连接的个数
    initial-size: 5
    #最小连接池数量
    min-idle: 5
    #最大连接池数量 maxIdle已经不再使用
    max-active: 20
    #获取连接时最大等待时间，单位毫秒
    max-wait: 60000
    #申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
    test-while-idle: true
    #既作为检测的间隔时间又作为testWhileIdel执行的依据
    time-between-eviction-runs-millis: 60000
    #销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时，关闭当前连接
    min-evictable-idle-time-millis: 30000
    #用来检测连接是否有效的sql 必须是一个查询语句
    #mysql中为 select 'x'
    #oracle中为 select 1 from dual
    validation-query: select 'x'
    #申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
    test-on-borrow: false
    #归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
    test-on-return: false
    # 是否缓存preparedStatement
    pool-prepared-statements: true
#   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    #配置监拉统计挡成的filters. stat: 监控统计、Log4j:日志记录、waLL: 防御sqL注入
    #如果启用日志记录时报错java.lang.ClassNotFoundException: org.apache.log4j.Priority
    #则导入Log4j 依赖即时，Maven 地址: https://mvnrepository. com/artifact/log4j/log4
    filters: stat,wall,1og4j
    max-pool-prepared-statement-per-connection-size: 20
    use-global-data-source-stat: true
    connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

3.3 配置类

配置了druid连接池的其它属性，但是不会生效。
因为默认是使用的java.sql.Datasource的类来获取属性的，有些属性datasource没有。
如果我们想让配置生效，需要手动创建Druid的配置文件。

@Configuration
public class DruidConfig {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource druidDataSource() {
        return new DruidDataSource();
    }
}

3.4 后台监控配置

http://localhost:8080/druid/
在DruidDataSource里面配置监控中心

@Bean
public ServletRegistrationBean statViewServlet() {
    ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    //ServletRegistrationBean reg = new ServletRegistrationBean();
    //reg.setServlet(new StatViewServlet());
    //reg.addUrlMappings("/druid/*");
    // 添加IP白名单
    servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
    // 添加IP黑名单，当白名单和黑名单重复时，黑名单优先级更高
    //servletRegistrationBean.addInitParameter("deny", "127.0.0.1");
    // 添加控制台管理用户
    servletRegistrationBean.addInitParameter("loginUsername", "admin");
    servletRegistrationBean.addInitParameter("loginPassword", "123456");
    // 是否能够重置数据
    servletRegistrationBean.addInitParameter("resetEnable", "false");
    return servletRegistrationBean;
}
    /**
     * 配置服务过滤器
     *
     * @return 返回过滤器配置对象
     */
@Bean
public FilterRegistrationBean statFilter() {
    FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
    // 添加过滤规则
    filterRegistrationBean.addUrlPatterns("/*");
    // 忽略过滤格式
    filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*,");
    return filterRegistrationBean;
}