springboot配置druid数据源和监控

最新推荐文章于 2024-05-30 11:05:32 发布

漂漂1

最新推荐文章于 2024-05-30 11:05:32 发布

阅读量1.7k

点赞数

数据源使用的是阿里的Driud。

1.配置之前先看一下xml的配置。

-------------------spring-mybatis.xml文件--------------------------
<!-- 引入属性文件 -->
<context:property-placeholder location="classpath:config.properties" />
<!-- 配置数据源 -->
<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
    init-method="init" destroy-method="close">
    <property name="url" value="${jdbc_url}" />
    <property name="username" value="${jdbc_username}" />
    <property name="password" value="${jdbc_password}" />
    <!-- 初始化连接大小 -->
    <property name="initialSize" value="0" />
    <!-- 连接池最大使用连接数量 -->
    <property name="maxActive" value="20" />
    <!-- 连接池最大空闲 -->
    <property name="maxIdle" value="20" />
    <!-- 连接池最小空闲 -->
    <property name="minIdle" value="0" />
    <!-- 获取连接最大等待时间 -->
    <property name="maxWait" value="60000" />
    <!-- <property name="poolPreparedStatements" value="true" /> <property 
        name="maxPoolPreparedStatementPerConnectionSize" value="33" /> -->
    <property name="validationQuery" value="${validationQuery}" />
    <property name="testOnBorrow" value="false" />
    <property name="testOnReturn" value="false" />
    <property name="testWhileIdle" value="true" />
    <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
    <property name="timeBetweenEvictionRunsMillis" value="60000" />
    <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
    <property name="minEvictableIdleTimeMillis" value="25200000" />
    <!-- 打开removeAbandoned功能 -->
    <property name="removeAbandoned" value="true" />
    <!-- 1800秒，也就是30分钟 -->
    <property name="removeAbandonedTimeout" value="1800" />
    <!-- 关闭abanded连接时输出错误日志 -->
    <property name="logAbandoned" value="true" />
    <!-- 监控数据库 -->
    <!-- <property name="filters" value="stat" /> -->
    <!-- <property name="filters" value="mergeStat" />-->
</bean>
<!-- myBatis文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
    <property name="mapperLocations" value="classpath:/mybatis/*Mapper.xml" />
    <property name="configLocation" value="classpath:mybatis-interceptor.xml" /> 
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.lcl.springBoot.dao" />
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
-------------------mybatis-interceptor.xml文件------------------------------
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
"http://mybatis.org/dtd/mybatis-3-config.dtd">  
<!-- 配置管理器 -->  
<configuration>  
<plugins>  
    <!-- com.github.pagehelper为PageHelper类所在包名 -->  
    <plugin interceptor="com.github.pagehelper.PageHelper">  
        <!-- 4.0.0以后版本可以不设置该参数 -->  
        <property name="dialect" value="mysql"/>  
        <!-- 该参数默认为false -->  
        <!-- 设置为true时，会将RowBounds第一个参数offset当成pageNum页码使用 -->  
        <!-- 和startPage中的pageNum效果一样-->  
        <property name="offsetAsPageNum" value="true"/>  
        <!-- 该参数默认为false -->  
        <!-- 设置为true时，使用RowBounds分页会进行count查询 -->  
        <property name="rowBoundsWithCount" value="true"/>  
        <!-- 设置为true时，如果pageSize=0或者RowBounds.limit = 0就会查询出全部的结果 -->  
        <!-- （相当于没有执行分页查询，但是返回结果仍然是Page类型）-->  
        <property name="pageSizeZero" value="true"/>  
        <!-- 3.3.0版本可用 - 分页参数合理化，默认false禁用 -->  
        <!-- 启用合理化时，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页 -->  
        <!-- 禁用合理化时，如果pageNum<1或pageNum>pages会返回空数据 -->  
        <property name="reasonable" value="true"/>  
        <!-- 3.5.0版本可用 - 为了支持startPage(Object params)方法 -->  
        <!-- 增加了一个`params`参数来配置参数映射，用于从Map或ServletRequest中取值 -->  
        <!-- 可以配置pageNum,pageSize,count,pageSizeZero,reasonable,orderBy,不配置映射的用默认值 -->  
        <!-- 不理解该含义的前提下，不要随便复制该配置 -->  
        <property name="params" value="pageNum=start;pageSize=limit;"/>  
        <!-- 支持通过Mapper接口参数来传递分页参数 -->  
        <property name="supportMethodsArguments" value="true"/>  
        <!-- always总是返回PageInfo类型,check检查返回类型是否为PageInfo,none返回Page -->  
        <property name="returnPageInfo" value="check"/>  
    </plugin>  
</plugins>  
</configuration> 

  
  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

这里面可以看到配置了DruidDataSource、SqlSessionFactoryBean、MapperScannerConfigurer三个bean，其中DruidDataSource配置了最基本的链接属性；SqlSessionFactoryBean配置数据源、mapper.xml路径和PageHelper插件填充；MapperScannerConfigurer设置的自动扫描的基本包basePackage跟sqlSessionFactory。
对应的在springBoot中，我们如果不用xml配置，则可以用@Configuration、@bean来进行注解配置。
@Configuration注解，等价与XML中配置beans；用@Bean标注方法等价于XML中配置bean。

2.看springBoot配置，首先配置DruidDataSource，创建一个DruidDBConfig类，表注@Configuration。代码如下：

@Configuration  
public class DruidDBConfig {  
    private Logger logger = LoggerFactory.getLogger(DruidDBConfig.class);   
    @Value("${spring.datasource.url}")  
    private String dbUrl;   
    @Value("${spring.datasource.username}")  
    private String username;  
    @Value("${spring.datasource.password}")  
    private String password;  
    @Value("${spring.datasource.driverClassName}")  
    private String driverClassName;  
    @Value("${spring.datasource.initialSize}")  
    private int initialSize;   
    @Value("${spring.datasource.minIdle}")  
    private int minIdle;  
    @Value("${spring.datasource.maxActive}")  
    private int maxActive;  
    @Value("${spring.datasource.maxWait}")  
    private int maxWait;  
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")  
    private int timeBetweenEvictionRunsMillis;  
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")  
    private int minEvictableIdleTimeMillis;  
    @Value("${spring.datasource.validationQuery}")  
    private String validationQuery;   
    @Value("${spring.datasource.testWhileIdle}")  
    private boolean testWhileIdle;  
    @Value("${spring.datasource.testOnBorrow}")  
    private boolean testOnBorrow;  
    @Value("${spring.datasource.testOnReturn}")  
    private boolean testOnReturn;  
    @Value("${spring.datasource.poolPreparedStatements}")  
    private boolean poolPreparedStatements;   
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")  
    private int maxPoolPreparedStatementPerConnectionSize;  
    @Value("${spring.datasource.filters}")  
    private String filters;    
    @Value("{spring.datasource.connectionProperties}")  
    private String connectionProperties;   
    @Bean(value="dataSource")     //声明其为Bean实例  
    @Primary  //在同样的DataSource中，首先使用被标注的DataSource  
    public DataSource dataSource(){  
        DruidDataSource datasource = new DruidDataSource();  
        datasource.setUrl(this.dbUrl);  
        datasource.setUsername(username);  
        datasource.setPassword(password);  
        datasource.setDriverClassName(driverClassName);  
        //configuration  
        datasource.setInitialSize(initialSize);  
        datasource.setMinIdle(minIdle);  
        datasource.setMaxActive(maxActive);  
        datasource.setMaxWait(maxWait);  
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);  
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);  
        datasource.setValidationQuery(validationQuery);  
        datasource.setTestWhileIdle(testWhileIdle);  
        datasource.setTestOnBorrow(testOnBorrow);  
        datasource.setTestOnReturn(testOnReturn);  
        datasource.setPoolPreparedStatements(poolPreparedStatements);  
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);  
        try {  
            datasource.setFilters(filters);  
        } catch (SQLException e) {  
            logger.error("druid configuration initialization filter", e);  
        }  
        datasource.setConnectionProperties(connectionProperties);  
        return datasource;  
    }  
}

  
  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

至此，DruidDataSource数据源在系统运行的时候，会自动创建一个bean。

3.若想使用Druid的监控，则需要额外配置StatViewServlet 和 filterRegistrationBean，这里单独拎出来便于理解，此处配置也可以放置在DruidDBConfig类中。

@Configuration
public class DruidConfiguration { 
   /**
    * 注册一个StatViewServlet
    * @return
    */
   @Bean
   public ServletRegistrationBean DruidStatViewServle2(){
          //org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
          ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
          //添加初始化参数：initParams
          //白名单：
          servletRegistrationBean.addInitParameter("allow","127.0.0.1");
          //IP黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
          servletRegistrationBean.addInitParameter("deny","192.168.1.73");
          //登录查看信息的账号密码.
          servletRegistrationBean.addInitParameter("loginUsername","admin2");
          servletRegistrationBean.addInitParameter("loginPassword","123456");
          //是否能够重置数据.
          servletRegistrationBean.addInitParameter("resetEnable","false");
          return servletRegistrationBean;
   }   
   /**
    * 注册一个：filterRegistrationBean
    * @return
    */
   @Bean
   public FilterRegistrationBean druidStatFilter2(){       
          FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
          //添加过滤规则.
          filterRegistrationBean.addUrlPatterns("/*");
          //添加不需要忽略的格式信息.
          filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid2/*");
          return filterRegistrationBean;
   } 
 }

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

这里的配置类同等于xml配置文件：

<filter>
<filter-name>DruidWebStatFilter</filter-name>
<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
<init-param>
    <param-name>exclusions</param-name>
    <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
</init-param>
</filter>
<filter-mapping>
    <filter-name>DruidWebStatFilter</filter-name>
    <url-pattern>/*</url-pattern>
</filter-mapping>
<servlet>
    <servlet-name>DruidStatView</servlet-name>
    <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
</servlet>
<servlet-mapping>
    <servlet-name>DruidStatView</servlet-name>
    <url-pattern>/druid/*</url-pattern>
</servlet-mapping>

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

同样的还有另外一种方式：

1)创建DruidStatFilter。

@WebFilter(filterName="druidWebStatFilter",urlPatterns="/*",  
initParams={  
    @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")//忽略资源  
}  
)  
public class DruidStatFilter extends WebStatFilter {
}

 
 1
2
3
4
5
6
7
8

2)创建DruidStatViewServlet。

@WebServlet(urlPatterns="/druid/*",  
initParams={  
     @WebInitParam(name="allow",value="127.0.0.1,192.168.163.1"),// IP白名单(没有配置或者为空，则允许所有访问)  
     @WebInitParam(name="deny",value="192.168.1.73"),// IP黑名单 (存在共同时，deny优先于allow)  
     @WebInitParam(name="loginUsername",value="admin"),// 用户名  
     @WebInitParam(name="loginPassword",value="123456"),// 密码  
     @WebInitParam(name="resetEnable",value="false")// 禁用HTML页面上的“Reset All”功能  
})  
public class DruidStatViewServlet extends StatViewServlet {  
    private static final long serialVersionUID = -2688872071445249539L; 
}

 
 1
2
3
4
5
6
7
8
9
10
11
12

4.配置sqlSessionFactory，此bean是SqlSessionFactoryBean类型。

@Configuration
public class MyBatisConfig implements TransactionManagementConfigurer{
    private Logger logger = LoggerFactory.getLogger(MyBatisConfig.class);
    //@Autowired 
    //private DruidDataSource dataSource;
    @Value("${spring.datasource.type}")  
    private Class<? extends DataSource> dataSourceType;  
    @Bean(name="dataSource", destroyMethod = "close", initMethod="init")  
    @ConfigurationProperties(prefix = "spring.datasource")  
    public DataSource dataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();  
    }  
    @Bean(name = "sqlSessionFactory")//3
    public SqlSessionFactory sqlSessionFactoryBean() {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource());
        //bean.setTypeAliasesPackage("com.hjf.boot.demo.boot_mybatis.domain");
        //分页插件 //4
        PageHelper pageHelper = new PageHelper();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);
        //添加插件
        bean.setPlugins(new Interceptor[]{pageHelper});
        //添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        try {
            bean.setMapperLocations(resolver.getResources("classpath:/mybatis/*Mapper.xml"));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    @Bean
    public PlatformTransactionManager annotationDrivenTransactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
}

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

5.配置MapperScannerConfigurer，设置自动扫描。

@Configuration
@AutoConfigureAfter(MyBatisConfig.class)//见文思意，在MyBatisConfig配置之后加载
public class MyBatisMapperScannerConfig {
    @Bean
    public MapperScannerConfigurer mapperScannerConfigurer() {
        MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
        //对应<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
        //对应<property name="basePackage" value="com.lcl.springBoot.dao" />
        mapperScannerConfigurer.setBasePackage("com.lcl.springBoot.dao");
        return mapperScannerConfigurer;
    }
}

 
 1
2
3
4
5
6
7
8
9
10
11
12
13
14

至此配置完毕，对于资源文件在resources目录下：

 resources
   --application.properties//存放资源文件
   --mybatis//文件夹存放mapper文件
       --DemoMapper.xml //本人用的测试mapper

  
  1
2
3
4
5

application.properties文件内容(千篇一律，满大街都是= =！)：

##DATASOURCE（DataSourceAutoConfiguration＆DataSourceProperties）
# 驱动配置信息   
spring.datasource.url = jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
spring.datasource.username= root
spring.datasource.password=
spring.datasource.driverClassName=com.mysql.jdbc.Driver 
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource 
##连接池属性配置
spring.datasource.initialSize=5  
spring.datasource.minIdle=5  
spring.datasource.maxActive=20  
spring.datasource.maxWait=60000  
spring.datasource.timeBetweenEvictionRunsMillis=60000  
spring.datasource.minEvictableIdleTimeMillis=300000  
spring.datasource.validationQuery=SELECT 1 FROM DUAL  
spring.datasource.testWhileIdle=true  
spring.datasource.testOnBorrow=false  
spring.datasource.testOnReturn=false  
spring.datasource.poolPreparedStatements=true  
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20  
spring.datasource.filters=stat,wall,log4j  
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000  

  
  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

结尾：在MyBatisConfig中关于DruidDataSource，一开始使用的形式是：

    @Autowired 
    private DruidDataSource dataSource;

 
 1
2
3

此时发现不能自动实例化bean，而后改为一下形式解决，没有看源码不知何故，以后有时间在看源码进行分析：

    @Value("${spring.datasource.type}")  
    private Class<? extends DataSource> dataSourceType;  
    @Bean(name="dataSource", destroyMethod = "close", initMethod="init")  
    @ConfigurationProperties(prefix = "spring.datasource")  
    public DataSource dataSource() {
        return DataSourceBuilder.create().type(dataSourceType).build();  
    }