抱歉,您的浏览器无法访问本站
本页面需要浏览器支持(启用)JavaScript
了解详情 >

SpringBoot整合Druid

springboot整合druid

Druid是一个非常优秀的连接池,非常好的管理了数据库连接,可以实时监控 数据库连接对象和应用程序的数据库操作记录

单数据源配置

导入POM
1
2
3
4
5
6
7
8
9
10
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.19</version>
</dependency>
数据源相关配置
数据源属性配置

可以直接使用上面在配置文件中的配置,然后借助springboot自动装配机制完成配置加载。但是直接默认使用springboot自动加载的话,有时候容易出现bug,最好自己封装下,还可以灵活配置调用。

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
# 数据库访问配置
# 主数据源
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root
######################### Druid连接池的配置信息 #################
#初始化连接大小
spring.druid.initialSize=5
#最小连接池数量
spring.druid.minIdle=5
#最大连接池数量
spring.druid.maxActive=20
#获取连接时最大等待时间,单位毫秒
spring.druid.maxWait=60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.druid.timeBetweenEvictionRunsMillis=60000
#配置一个连接在池中最小生存的时间,单位是毫秒
spring.druid.minEvictableIdleTimeMillis=300000
#测试连接
spring.druid.validationQuery=SELECT 1 FROM DUAL
#申请连接的时候检测,建议配置为true,不影响性能,并且保证安全性
spring.druid.testWhileIdle=true
#获取连接时执行检测,建议关闭,影响性能
spring.druid.testOnBorrow=false
#归还连接时执行检测,建议关闭,影响性能
spring.druid.testOnReturn=false
#是否开启PSCache,PSCache对支持游标的数据库性能提升巨大,oracle建议开启,mysql下建议关闭
spring.druid.poolPreparedStatements=false
#开启poolPreparedStatements后生效
spring.druid.maxPoolPreparedStatementPerConnectionSize=20
#配置扩展插件,常用的插件有=>stat:监控统计 log4j:日志 wall:防御sql注入
spring.druid.filters=stat,wall,log4j
#通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.druid.connectionProperties='druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000'

数据源配置类

可以直接使用上面在配置文件中的配置,然后借助springboot自动装配机制完成配置加载。但是直接默认使用springboot自动加载的话,有时候容易出现bug,最好自己封装下,还可以灵活配置调用。

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
88
@Configuration
public class DruidConfiguration {
@Value("${spring.datasource.url}")
private String url;

@Value("${spring.datasource.username}")
private String username;

@Value("${spring.datasource.password}")
private String password;

@Value("${spring.datasource.driverClassName}")
private String driverClassName;

@Value("${spring.druid.initialSize}")
private int initialSize;

@Value("${spring.druid.minIdle}")
private int minIdle;

@Value("${spring.druid.maxActive}")
private int maxActive;

@Value("${spring.druid.maxWait}")
private int maxWait;

@Value("${spring.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;

@Value("${spring.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;

@Value("${spring.druid.validationQuery}")
private String validationQuery;

@Value("${spring.druid.testWhileIdle}")
private boolean testWhileIdle;

@Value("${spring.druid.testOnBorrow}")
private boolean testOnBorrow;

@Value("${spring.druid.testOnReturn}")
private boolean testOnReturn;

@Value("${spring.druid.poolPreparedStatements}")
private boolean poolPreparedStatements;

@Value("${spring.druid.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;

@Value("${spring.druid.filters}")
private String filters;

@Value("{spring.druid.connectionProperties}")
private String connectionProperties;

@Bean
@Primary
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();

datasource.setUrl(url);
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) {

}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}
配置druid相关的sql监控
StatViewServlet

StatViewServlet是一个标准的javax.servlet.http.HttpServlet,使用时候需要注入

  • 根据配置中的url-pattern来访问内置监控页面,如果是上面的配置/druid/*,内置监控页面的首页是/druid/index.html

  • 配置allow和deny。deny优先于allow,如果在deny列表中,就算在allow列表中,也会被拒绝。如果allow没有配置或者为空,则允许所有访问

  • 配置resetEnable,在StatViewSerlvet输出的html页面中,有一个功能是Reset All,执行这个操作之后,会导致所有计数器清零,重新计数。你可以通过配置参数关闭它。

statFilter

statFilter用于采集web-jdbc关联监控的数据。

  • exlusions配置经常需要排除一些不必要的url,比如.js,/jslib/等等。配置在init-param中。
关键代码
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
@Configuration
public class DuridMonitorConfiguration {
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
servletRegistrationBean.addInitParameter("allow", "127.0.0.1"); //设置ip白名单
servletRegistrationBean.addInitParameter("deny", "192.168.0.19");//设置ip黑名单,优先级高于白名单
//设置控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", "root");
servletRegistrationBean.addInitParameter("loginPassword", "root");
//是否可以重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}

@Bean
public FilterRegistrationBean statFilter() {
//创建过滤器
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
//设置过滤器过滤路径
filterRegistrationBean.addUrlPatterns("/*");
//忽略过滤的形式
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
测试
配置log4j

开启应用,发现出错了显示,Caused by: java.lang.ClassNotFoundException: org.apache.log4j.Priority,这是由于druid打印sql语句时候依赖于log4j,所以导入log4j依赖包:

1
2
3
4
5
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
访问监控页面

启动后直接访问地址 http://localhost:8080/druid/

测试数据库连接
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
@RunWith(value = SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = {DemoApplication.class})
public class DemoTest {
@Autowired
private DataSource dataSource;


@Test
public void test() throws SQLException {
System.out.println("测试数据库");
testDataSource(dataSource);
}

public void testDataSource(DataSource dataSource) throws SQLException {
Connection connection = dataSource.getConnection();
CallableStatement callableStatement = connection.prepareCall("SELECT * FROM test");
ResultSet resultSet = callableStatement.executeQuery();
while (resultSet.next()) {
String value = resultSet.getString("name");
System.out.println(value);
System.out.println("===========");
}
resultSet.close();
connection.close();
}
}

打印数据

1
2
3
测试数据库
test1数据库
===========

多数据源配置

数据准备阶段

先在数据库中创建好test1库和test2库,然后分别插入数据

数据源配置
属性文件配置

在application.properties中配置

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
# 数据库访问配置
# 主数据源
spring.datasource.primary.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.primary.username=root
spring.datasource.primary.password=root
spring.datasource.primary.driverClassName=com.mysql.jdbc.Driver
#从数据源
spring.datasource.second.url=jdbc:mysql://localhost:3306/test2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
spring.datasource.second.username=root
spring.datasource.second.password=root
spring.datasource.second.driverClassName=com.mysql.jdbc.Driver

######################### Druid连接池的配置信息 #################
#初始化连接大小
spring.druid.initialSize=5
#最小连接池数量
spring.druid.minIdle=5
#最大连接池数量
spring.druid.maxActive=20
#获取连接时最大等待时间,单位毫秒
spring.druid.maxWait=60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.druid.timeBetweenEvictionRunsMillis=60000
#配置一个连接在池中最小生存的时间,单位是毫秒
spring.druid.minEvictableIdleTimeMillis=300000
#测试连接
spring.druid.validationQuery=SELECT 1 FROM DUAL
#申请连接的时候检测,建议配置为true,不影响性能,并且保证安全性
spring.druid.testWhileIdle=true
#获取连接时执行检测,建议关闭,影响性能
spring.druid.testOnBorrow=false
#归还连接时执行检测,建议关闭,影响性能
spring.druid.testOnReturn=false
#是否开启PSCache,PSCache对支持游标的数据库性能提升巨大,oracle建议开启,mysql下建议关闭
spring.druid.poolPreparedStatements=false
#开启poolPreparedStatements后生效
spring.druid.maxPoolPreparedStatementPerConnectionSize=20
#配置扩展插件,常用的插件有=>stat:监控统计 log4j:日志 wall:防御sql注入
spring.druid.filters=stat,wall,log4j
#通过connectProperties属性来打开mergeSql功能;慢SQL记录
spring.druid.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
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
@Configuration
public class DruidConfiguration {
@Value("${spring.druid.initialSize}")
private int initialSize;

@Value("${spring.druid.minIdle}")
private int minIdle;

@Value("${spring.druid.maxActive}")
private int maxActive;

@Value("${spring.druid.maxWait}")
private int maxWait;

@Value("${spring.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;

@Value("${spring.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;

@Value("${spring.druid.validationQuery}")
private String validationQuery;

@Value("${spring.druid.testWhileIdle}")
private boolean testWhileIdle;

@Value("${spring.druid.testOnBorrow}")
private boolean testOnBorrow;

@Value("${spring.druid.testOnReturn}")
private boolean testOnReturn;

@Value("${spring.druid.poolPreparedStatements}")
private boolean poolPreparedStatements;

@Value("${spring.druid.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;

@Value("${spring.druid.filters}")
private String filters;

@Value("{spring.druid.connectionProperties}")
private String connectionProperties;

public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();

//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) {

}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}

@Bean(name = "primaryDatasource")
@ConfigurationProperties(prefix = "spring.datasource.primary")
@Primary
public DataSource primaryDatasource() {
return dataSource();
}

@Bean(name = "secondDatasource")
@ConfigurationProperties(prefix = "spring.datasource.second")
public DataSource secondDatasource() {
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
@RunWith(value = SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = {DemoApplication.class})
public class DemoTest {
@Resource(name="primaryDatasource")
private DataSource primaryDatasource;
@Resource(name="secondDatasource")
private DataSource secondDatasource;

@Test
public void test() throws SQLException {
System.out.println("测试数据库1");
testDataSource(primaryDatasource);
System.out.println("测试数据库12");
testDataSource(secondDatasource);
}

public void testDataSource(DataSource dataSource) throws SQLException {
Connection connection = dataSource.getConnection();
CallableStatement callableStatement = connection.prepareCall("SELECT * FROM test");
ResultSet resultSet = callableStatement.executeQuery();
while (resultSet.next()) {
String value = resultSet.getString("name");
System.out.println(value);
System.out.println("===========");
}
resultSet.close();
connection.close();
}
}

打印数据

1
2
3
4
5
6
测试数据库1
test1数据库
===========
测试数据库12
test2数据库
===========

PageHelper分页

导入POM

1
2
3
4
5
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>

分页代码

TestService
1
2
3
4
5
public interface TestService {

public PageInfo<Entity> queryForPage(int pageIndex, int pageSize);
}

TestServiceImpl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Service
public class TestServiceImpl implements TestService {

@Autowired
private TestMapper testMapper;

@Override
public PageInfo<Entity> queryForPage(int pageIndex, int pageSize) {
//开始进行分页
PageHelper.startPage(pageIndex, pageSize);
List<Entity> entityList = testMapper.getAllDatas();
//获取分页数据
PageInfo<Entity> pageInfo = new PageInfo<Entity>(entityList);
return pageInfo;
}
}

​ PageHelper.startPage(int PageNum,int PageSize):用来设置页面的位置和展示的数据条目数,我们设置每页展示5条数据。PageInfo用来封装页面信息,返回给前台界面。PageInfo中的一些我们需要用到的参数如下表:

PageInfo.list 结果集
PageInfo.pageNum 当前页码
PageInfo.pageSize 当前页面显示的数据条目
PageInfo.pages 总页数
PageInfo.total 数据的总条目数
PageInfo.prePage 上一页
PageInfo.nextPage 下一页
PageInfo.isFirstPage 是否为第一页
PageInfo.isLastPage 是否为最后一页
PageInfo.hasPreviousPage 是否有上一页
PageHelper.hasNextPage 是否有下一页

编写测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@RunWith(value = SpringJUnit4ClassRunner.class)
@SpringBootTest(classes = {DemoApplication.class})
public class DemoTest {

@Autowired
private TestService testService;

@Test
public void test() throws SQLException {
PageInfo<Entity> pageInfo = testService.queryForPage(1, 10);
List<Entity> dataList = pageInfo.getList();
System.out.println("总页数:" + pageInfo.getPages());
System.out.println("总条数:" + pageInfo.getTotal());
System.out.println("当前页:" + pageInfo.getPageNum());
System.out.println("上一页:" + pageInfo.getPrePage());
System.out.println("下一页:" + pageInfo.getNextPage());
System.out.println("===========================输出结果---------------------");
for (Entity entity : dataList) {
System.out.println(entity);
}
}
}
测试结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
总页数:2
总条数:20
当前页:1
上一页:0
下一页:2
===========================输出结果---------------------
Entity{id=1, name='111'}
Entity{id=2, name='222'}
Entity{id=3, name='333'}
Entity{id=4, name='444'}
Entity{id=5, name='555'}
Entity{id=6, name='666'}
Entity{id=7, name='777'}
Entity{id=8, name='888'}
Entity{id=9, name='999'}
Entity{id=10, name='000'}

评论