springboot整合mybatis+druid+pageHelper配置

一、首先引入mybatis以及druid依赖:

         <!-- spring-boot mybatis支持-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
        <!-- alibaba的druid数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.11</version>
        </dependency>
        

       <!-- 分页插件  -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.1</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.0</version>
        </dependency>

二、application.properties文件中进行配置

##############################oracle数据源配置###########################
#数据库驱动类
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
#数据库连接
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:orcl
#数据库用户名
spring.datasource.username=scott
#数据库用户密码
spring.datasource.password=tiger

#连接池类型，druid连接池springboot暂无法默认支持，需要自己配置bean
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#连接池初始化连接数量
spring.datasource.initialSize=5
#连接池最大活跃连接数
spring.datasource.maxActive=100
#最小空闲数
spring.datasource.min-idle=5
#最大等待时间
spring.datasource.max-wait=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
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements: true
spring.datasource.maxPoolPreparedStatementPerConnectionSize: 50
spring.datasource.removeAbandoned: true
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
spring.datasource.useGlobalDataSourceStat:true
# SQL监控后台登录用户名
spring.datasources.druidLoginName=admin
# SQL监控后台登录用户密码
spring.datasources.druidPassword=admin
#################################mybatis配置##############################
#sql文件配置
mybatis.mapper-locations=classpath:mappers/*/*Mapper.xml
#实体类设置别名
mybatis.type-aliases-package=com/dataService/entry/*
#mybatis.config-location配置sqlMap-config
mybatis.config-location=classpath:/mybatis-config.xml

三、自定义bean进行druid数据源配置

@Configuration
/**开启事务管理的注解*/
@EnableTransactionManagement
@MapperScan(basePackages = "com.dataService.mappers.*" ,sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MyBatisConfiguration {
    private static Logger logger = LoggerFactory.getLogger(MyBatisConfiguration.class);
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClass;
    @Value("${spring.datasource.initialSize}")
    private int initialSize;
    @Value("${spring.datasource.min-idle}")
    private int minIdle;
    @Value("${spring.datasource.maxActive}")
    private int maxActive;
    @Value("${spring.datasource.max-wait}")
    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;
    /** 配置druid进行sql性能监控*/
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("{spring.datasource.connectionProperties}")
    private Properties connectionProperties;
    @Value("${spring.datasource.useGlobalDataSourceStat}")
    private boolean useGlobalDataSourceStat;
    @Value("${spring.datasources.druidLoginName}")
    private String druidLoginName;
    @Value("${spring.datasources.druidPassword}")
    private String druidPassword;
    /** 配置mybaitis */
    @Value("${mybatis.mapper-locations}")
    private  String mapperLocations;
    @Value("${mybatis.type-aliases-package}")
    private String typeAliasesPackage;
    @Value("${mybatis.config-location}")
    private String configLocation;
    /**
    *@Author: 
    *@Description: 数据源,也可以使用这个注解 @ConfigurationProperties(prefix = "")
    *@Date: 2018/4/15 17:15
    *@Modified by:
    *@params: * @Primary多数据源时必须加上，表示哪个为主
    *@return:
    */
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(this.url);
        druidDataSource.setUsername(this.username);
        druidDataSource.setPassword(this.password);
        druidDataSource.setDriverClassName(this.driverClass);
        druidDataSource.setInitialSize(this.initialSize);
        druidDataSource.setMaxActive(this.maxActive);
        druidDataSource.setMinIdle(this.minIdle);
        druidDataSource.setMaxWait(this.maxWait);
        druidDataSource.setTimeBetweenEvictionRunsMillis(this.timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(this.minEvictableIdleTimeMillis);
        druidDataSource.setValidationQuery(this.validationQuery);
        druidDataSource.setTestOnBorrow(this.testOnBorrow);
        druidDataSource.setTestOnReturn(this.testOnReturn);
        druidDataSource.setTestWhileIdle(this.testWhileIdle);
        druidDataSource.setPoolPreparedStatements(this.poolPreparedStatements);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(this.maxPoolPreparedStatementPerConnectionSize);
        druidDataSource.setConnectProperties(this.connectionProperties);
        druidDataSource.setUseGlobalDataSourceStat(this.useGlobalDataSourceStat);
        try{
            druidDataSource.setFilters(this.filters);
        }catch (SQLException e){
            logger.error("druid configuration initialization filter", e);
        }
        return  druidDataSource;
    }


    /**
     *@Author:
     *@Description: 配置mybatis的分页插件pageHelper
     *@Date: 2018/4/15 17:12
     *@Modified by:
     *@params: * @param
     *@return:com.github.pagehelper.PageInterceptor
     */
    @Bean(name = "pageHelper")
    public PageInterceptor pageHelper(){
        PageInterceptor pageHelper = new PageInterceptor();
        Properties properties = new Properties();
        /**默认false，设置为true时，会将RowBounds第一个参数offset当成pageNum页码使用*/
        properties.setProperty("offsetAsPageNum","true");
        /**默认false，设置为true时，使用RowBounds分页会进行count查询 */
        properties.setProperty("rowBoundsWithCount","true");
        /** 禁用合理化时，如果pageNum<1或pageNum>pages会返回空数据 */
        properties.setProperty("reasonable","true");
        /** always总是返回PageInfo类型,check检查返回类型是否为PageInfo,none返回Page */
        properties.setProperty("returnPageInfo","check");
        /** 支持通过Mapper接口参数来传递分页参数 */
        properties.setProperty("supportMethodsArguments","false");
        /**  配置数据库的方言  */
        properties.setProperty("helperDialect","oracle");
        pageHelper.setProperties(properties);
        return pageHelper;
    }
    /**
    *@Author: 
    *@Description: 会话工厂
    *@Date: 2018/4/15 17:15
    *@Modified by:
    *@params: * @param null
    *@return:
    */
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource,@Qualifier("pageHelper") PageInterceptor pageHelper) throws Exception {
        logger.info("load SpringBootVFS");
        /**DefaultVFS在获取jar上存在问题，使用springboot只能修改*/
        VFS.addImplClass(SpringBootVFS.class);
        final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(masterDataSource);
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageHelper});
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource[] resources = resolver.getResources(this.mapperLocations);
        sqlSessionFactoryBean.setMapperLocations(resources);
        sqlSessionFactoryBean.setTypeAliasesPackage(this.typeAliasesPackage);
        sqlSessionFactoryBean.setConfigLocation(new DefaultResourceLoader().getResource(this.configLocation));
        return sqlSessionFactoryBean.getObject();
    }
    /**
    *@Author:
    *@Description: 事务
    *@Date: 2018/4/15 17:16
    *@Modified by:
    *@params: * @param null
    *@return:
    */
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager(@Qualifier("masterDataSource") DataSource masterDataSource){
        return new DataSourceTransactionManager(masterDataSource);
    }

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        /** url 匹配 */
        reg.addUrlMappings("/druid/*");
        /** IP白名单 (没有配置或者为空，则允许所有访问) */
        reg.addInitParameter("allow", "1**.2**.1**.3*,127.0.0.1");
        /** IP黑名单 (存在共同时，deny优先于allow) */
        reg.addInitParameter("deny", "1**.2**.1**.3*");
        /** 登录名  */
        reg.addInitParameter("loginUsername", this.druidLoginName);
        /**  登录密码 */
        reg.addInitParameter("loginPassword", this.druidPassword);
        /**  禁用HTML页面上的“Reset All”功能 */
        reg.addInitParameter("resetEnable", "false");
        return reg;
    }

    @Bean(name="druidWebStatFilter")
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); //忽略资源
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
        filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
        return filterRegistrationBean;
    }

通过以上几步，整个整合配置都完成了，配置都有说明，在这就不进行累述了，唯一要说明的是分页插件的版本一定要和springboot的版本，mybatis的 版本兼容。