Spring boot Druid监控、Mybatis、pageHelper集成

简介

Druid是Java语言中最好的数据库连接池，并且能够提供强大的监控和扩展功能，并且是号称最快的数据库连接池。
MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集，是现在web编程中最流行的数据库框架之一。
pageHelper 是MyBatis 分页插件。

项目结构

（一）添加依赖

     <!--mybatis依赖包-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <!--mysql依赖包-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>
             <!--pagehelper依赖包-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.3</version>
        </dependency>
        <!--alibaba-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.2</version>
        </dependency>

（二）添加相关配置到配置文件

#数据库
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#端口
server.port=80

# 下面为连接池的补充设置，应用到上面所有数据源中
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
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
spring.datasource.logSlowSql=true

#mybatis
mybatis.type-aliases-package=com.example.lesson3.domain
mybatis.mapper-locations=classpath:com.example.lesson3.mapper/*.xml

#pagehelper
pagehelper.autoDialect=true  
pagehelper.closeConn=false  
pagehelper.reasonable=true  

（三）利用MyBatis Generator生成Dao、Model、Mapping

因为这不属于本文的重点内容，所以略过。有兴趣的童靴请自行谷歌。

（四）写出UserController 进行测试

@RestController
@RequestMapping("/user/")
public class UserController {

    @Autowired
    private IUserService userService;

    @GetMapping("get/{id}")
    public User getUser(@PathVariable Long id){
        User user = userService.getUserById(id);
        return user;
    }

    @PutMapping("update/{id}")
    public String updateUser(@PathVariable Long id, User user ){
        userService.updateUser(user);
        return "success";
    }

    @DeleteMapping("delete/{id}")
    public String deleteUser(@PathVariable Long id){
        userService.deleteUserById(id);
        return "success";
    }

    @PostMapping("save")
    public String saveUser(User user){
        userService.saveUser(user);
        return "success";
    }

    @GetMapping("list")
    public PageInfo<User> getList(@RequestParam(defaultValue = "1") Integer pageNum, @RequestParam(defaultValue = "20")Integer pageSize){
        PageHelper.startPage(pageNum,pageSize);
        PageHelper.orderBy("id DESC");
        List<User> userlist = userService.getUserlist();
        PageInfo<User> pageInfo = new PageInfo<>(userlist);
        return pageInfo;
    }
}

注意：这里使用了PageHelper进行分页，pageNum默认值是1，pageSize默认是20，意思是从第1页开始，每页显示20条记录

附上PageInfo的参数详解

    //当前页
    private int pageNum;

    //每页的数量
    private int pageSize;

    //当前页的数量
    private int size;

    //当前页面第一个元素在数据库中的行号
    private int startRow;

    //当前页面最后一个元素在数据库中的行号
    private int endRow;

    //总记录数
    private long total;

    //总页数
    private int pages;

    //结果集
    private List<T> list;

    //第一页
    private int firstPage;

    //前一页
    private int prePage;

    //下一页
    private int nextPage;

    //最后一页
    private int lastPage;

    //是否为第一页
    private boolean isFirstPage = false;

    //是否为最后一页
    private boolean isLastPage = false;

    //是否有前一页
    private boolean hasPreviousPage = false;

    //是否有下一页
    private boolean hasNextPage = false;

    //导航页码数
    private int navigatePages;

    //所有导航页号
    private int[] navigatepageNums;

测试结果

（五）写Druid配置文件

@Configuration
public class DruidConfig {

    private Logger logger = LoggerFactory.getLogger(DruidConfig.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.driver-class-name}")
    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.filters}")
    private String filters;

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

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", username);
        reg.addInitParameter("loginPassword", password);
        reg.addInitParameter("logSlowSql", logSlowSql);
        return reg;
    }

    @Bean
    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");
        return filterRegistrationBean;
    }

    @Bean
    public DataSource druidDataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(dbUrl);
        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);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            logger.error("druid configuration initialization filter", e);
        }
        return datasource;
    }
}

访问http://localhost/druid/login.html 查看结果

引用参考

http://blog.youkuaiyun.com/u012728960/article/details/50791343 Mybatis分页插件-PageHelper的使用
http://www.cnblogs.com/liuchuanfeng/p/7002046.html Spring Boot Druid数据源配置