spring boot（六） 加入库连接池（alibaba druid 数据）及分页插件_spring alibaba 分页

前言

上一遍只是简单的说明了怎么加入Mybatis，但这远远还是不够，我们还需要加入数据库连池来帮助我们来管理数据库连接。
##1、加spring boot druid 依赖

<dependency>
 <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.8</version>
</dependency>
1
2
3
4
5

注意，你的spring boot版本，如果是spring boot 是2.x 以上那么druid-spring-boot-starter的版本号也要相应的比较高的版本，在引入druid-spring-boot-starter依赖的时候，我刚开始引入的比较低的版本1.1.5结果执行测试类时一直报错：

在升级完druid的版本号后，就不再报错了。

2、durid配置文件

在application.properties文件中添加配置

#连接池的配置信息
## 初始化大小，最小，最大
spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=5
spring.datasource.druid.maxActive=20
## 配置获取连接等待超时的时间
spring.datasource.druid.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.minEvictableIdleTimeMillis=300000
spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle=true
spring.datasource.druid.testOnBorrow=false
spring.datasource.druid.testOnReturn=false
spring.datasource.druid.poolPreparedStatements=true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.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

#3、添加数据源类

@Configuration
//在这里添加MapperScan 扫包，在启动类中就不用再添加了
@MapperScan(basePackages = "com.clockbone.dao",sqlSessionFactoryRef = "sqlSessionFactory")
public class DruidDatabaseConfiguration {

    /*@Value(value = "spring.datasource.url")
    private String dbUrl;
    @Value(value = "spring.datasource.username")
    private String username;
    @Value(value = "spring.datasource.password")
    private String password;
    @Value(value = "spring.datasource.driver-class-name")
    private String driverClassName;
    @Value(value = "spring.druid.initialSize")
    private int initialSize;
    @Value(value = "spring.druid.minIdle")
    private int minIdle;
    @Value(value = "spring.druid.maxActive")
    private int maxActive;
    @Value(value = "spring.druid.maxWait")
    private int maxWait;
    @Value(value = "spring.druid.timeBetweenEvictionRunsMillis")
    private int timeBetweenEvictionRunsMillis;
    @Value(value = "spring.druid.minEvictableIdleTimeMillis")
    private int minEvictableIdleTimeMillis;
    @Value(value = "spring.druid.validationQuery")
    private String validationQuery;
    @Value(value = "spring.druid.testWhileIdle")
    private boolean testWhileIdle;
    @Value(value = "spring.druid.testOnBorrow")
    private boolean testOnBorrow;
    @Value(value = "spring.druid.testOnReturn")
    private boolean testOnReturn;
    @Value(value = "spring.druid.poolPreparedStatements")
    private boolean poolPreparedStatements;
    @Value(value = "spring.druid.maxPoolPreparedStatementPerConnectionSize")
    private int maxPoolPreparedStatementPerConnectionSize;
    @Value(value = "spring.druid.filtersl")
    private String filters;
    @Value(value = "spring.druid.connectionProperties")
    private String connectionProperties;*/

    @Bean
    //统一配置名前缀，可以减少上面配置代码
    @ConfigurationProperties(prefix = "spring.datasource")
    @Primary
    public DruidDataSource dataSource() throws SQLException {
        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);
        datasource.setFilters(filters);*/
        return datasource;
    }

    @Bean
    public DataSourceTransactionManager transactionManager() throws SQLException {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactory.setDataSource(dataSource);  
              sessionFactory.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
        /*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);
        sessionFactory.setPlugins(new Interceptor[]{pageHelper});*/

        return sessionFactory.getObject();
    }

}
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
89
90
91
92
93

4、mapper类及文件

//UserMapper.java

//@Mapper  可以去掉这个注解了
public interface UserMapper {
    @Select("SELECT ID,USER_ID as userId,USER_NAME as userName FROM Userinfo where id=#{id}")
    User getUserInfo(long id);
    User selectByPrimaryKey(Long id);
}
1
2
3
4
5
6

//UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.clockbone.dao.UserMapper" >

    <resultMap id="BaseResultMap" type="com.clockbone.model.User" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="user_name" property="userName" jdbcType="VARCHAR" />
        <result column="user_id" property="userId" jdbcType="VARCHAR" />
    </resultMap>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >
        select
        user_id as userId,user_name userName,id
        from User
        where id = #{id}
    </select>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

##5、加spring boot 加入pagehelper分页插件
pagehelper这个插件也是比较常用的数据分页插件，在spring boot只需要加入下面依赖，就会自动在sqlSessionFactory层面自动加入pagehelper的插件配置，而不需要再另外配置插件代码：

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

测试类：

 @Test
    public void userPageInfoTest(){
        User user = new User();
        user.setUserName("test");
        //直接设置分页属性，为了保证安全分页，后面一条一定是要分页的sql，
        PageInfo<User> pageInfo = userService.getUserList(user,1,10);
        log.info("getTotal:{}",pageInfo.getTotal());
        log.info("getPageNum:{}",pageInfo.getPageNum());
        log.info("getPageSize:{}",pageInfo.getPageSize());
        log.info("page list:{}",pageInfo.getList());
    }
1
2
3
4
5
6
7
8
9
10
11

结果：

2018-05-09 17:46:04.528  INFO 4628 --- [           main] com.clockbone.web.biz.UserServiceTest    : getTotal:1
2018-05-09 17:46:04.528  INFO 4628 --- [           main] com.clockbone.web.biz.UserServiceTest    : getPageNum:1
2018-05-09 17:46:04.528  INFO 4628 --- [           main] com.clockbone.web.biz.UserServiceTest    : getPageSize:10
1
2
3

如果不想spring boot自动注入，可以在sqlSessionFactory的代码中设置pagehelper插件：

 @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
         //添加Pagehelper插件
        Interceptor pageHelper = new PageInterceptor();
        Properties properties = new Properties();
        properties.setProperty("reasonable", "true");
        properties.setProperty("supportMethodsArguments", "true");
        properties.setProperty("returnPageInfo", "check");
        properties.setProperty("params", "count=countSql");
        pageHelper.setProperties(properties);
        sessionFactory.setPlugins(new Interceptor[]{pageHelper});

        return sessionFactory.getObject();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

*** 这时如果直接启动测类，会报如下错误： ***

会报有多个分页插件，这时需要在启动类上面加入注解 ，去掉pagehelper插件的自动注入如下：

@SpringBootApplication(scanBasePackages = "com.clockbone"/*,
        exclude={DataSourceAutoConfiguration.class}*/ ,exclude = {PageHelperAutoConfiguration.class}) 
1
2

pagehelper插件官网参考：
https://github.com/abel533/MyBatis-Spring-Boot