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
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
spring.druid.testWhileIdle=true
spring.druid.testOnBorrow=false
spring.druid.testOnReturn=false
spring.druid.poolPreparedStatements=false
spring.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.druid.filters=stat,wall,log4j
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);
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"); servletRegistrationBean.addInitParameter("deny", "192.168.0.19"); 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
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
spring.druid.testWhileIdle=true
spring.druid.testOnBorrow=false
spring.druid.testOnReturn=false
spring.druid.poolPreparedStatements=false
spring.druid.maxPoolPreparedStatementPerConnectionSize=20
spring.druid.filters=stat,wall,log4j
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();
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'}
|