SpringBoot整合mybatis使用Druid做连接池

我会带着你远行 2022-02-24 08:38 335阅读 0赞

## 项目构建 ##

使用的spring官方的eclipse版本为sts-4.0.1.RELEASE

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70][]

下一步

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 1][]

下一步

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 2][]

finish

## pom文件修改 ##

增加配置

<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid</artifactId>
    			<version>1.1.0</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<version>1.18.6</version>
    		</dependency>

完整pom如下

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.1.3.RELEASE</version>
    		<relativePath /> 
    	</parent>
    	<groupId>com.waterelephant</groupId>
    	<artifactId>mybatis-demo</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>mybatis-demo</name>
    	<description>Demo project for Spring Boot</description>
    
    	<properties>
    		<java.version>1.8</java.version>
    	</properties>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>org.mybatis.spring.boot</groupId>
    			<artifactId>mybatis-spring-boot-starter</artifactId>
    			<version>2.0.0</version>
    		</dependency>
    
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<scope>runtime</scope>
    		</dependency>
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid</artifactId>
    			<version>1.1.0</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<version>1.18.6</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    	</dependencies>
    
    	<build>
    		<plugins>
    			<plugin>
    				<groupId>org.springframework.boot</groupId>
    				<artifactId>spring-boot-maven-plugin</artifactId>
    			</plugin>
    		</plugins>
    	</build>
    
    </project>

## 修改配置文件  ##

修改application.yml

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 3][]

application.yml配置如下

server:
      port: 8089
    
    #默认使用配置
    spring:
      profiles:
        active: dev
    
    #公共配置与profiles选择无关 mapperLocations指的路径是src/main/resources
    #公共配置与profiles选择无关
    mybatis:
    #配置别名扫描包
      typeAliasesPackage: com.example.demo.model
    #配置mapper.xml文件的位置
      mapperLocations: classpath:mapper/*.xml
    
    ---
    
    #开发配置
    spring:
      profiles: dev
    
      datasource:
        url: jdbc:mysql://ip:3306/beadwalletloan?autoReconnect=true&useUnicode=true&zeroDateTimeBehavior=convertToNull&useServerPrepStmts=false&rewriteBatchedStatements=true&serverTimezone=GMT%2B8
        username: username
        password: password
        driverClassName: com.mysql.jdbc.Driver
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        # 配置获取连接等待超时的时间
        # 下面为连接池的补充设置，应用到上面所有数据源中
        # 初始化大小，最小，最大
        initialSize: 1
        minIdle: 3
        maxActive: 20
        # 配置获取连接等待超时的时间
        maxWait: 60000
        # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一个连接在池中最小生存的时间，单位是毫秒
        minEvictableIdleTimeMillis: 30000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打开PSCache，并且指定每个连接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
        filters: stat,wall,slf4j
        # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        # 合并多个DruidDataSource的监控数据
        useGlobalDataSourceStat: true

增加Druid的配置

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 4][]

package com.example.demo.config;
    
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import lombok.extern.log4j.Log4j2;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    
    /**
     * @ClassName DatasourceConfig
     * @Description TODO
     * @Author yueyiming
     * @Date 2019/4/3 15:20
     */
    @Log4j2
    @Configuration
    public class DatasourceConfig {
        @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.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.poolPreparedStatements}")
        private boolean poolPreparedStatements;
        @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
        private int maxPoolPreparedStatementPerConnectionSize;
        @Value("${spring.datasource.filters}")
        private String filters;
        @Value("${spring.datasource.connectionProperties}")
        private String connectionProperties;
        @Value("${spring.datasource.useGlobalDataSourceStat}")
        private boolean useGlobalDataSourceStat;
        @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);
            datasource.setUseGlobalDataSourceStat(useGlobalDataSourceStat);
            try {
                datasource.setFilters(filters);
            } catch (SQLException e) {
    
            }
            datasource.setConnectionProperties(connectionProperties);
    
            return datasource;
        }
    
    
        @Bean
        public ServletRegistrationBean druidServlet() {
            log.info("init Druid Servlet Configuration ");
            ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
            // IP白名单
           // servletRegistrationBean.addInitParameter("allow", "192.168.2.25,127.0.0.1");
            // IP黑名单(共同存在时，deny优先于allow)
            //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
            //控制台管理用户
            servletRegistrationBean.addInitParameter("loginUsername", "admin");
            servletRegistrationBean.addInitParameter("loginPassword", "9527");
            //是否能够重置数据 禁用HTML页面上的“Reset All”功能
            servletRegistrationBean.addInitParameter("resetEnable", "false");
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean filterRegistrationBean() {
            FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
            filterRegistrationBean.addUrlPatterns("/*");
            filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
            return filterRegistrationBean;
        }
    
    
    
    
    
    }

## 配置mapperScan扫描 ##

在启动类上加上@MapperScan(basePackages = \{"com.example.\*.mapper" \}

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 5][]

至此配置完成

## 测试 ##

mapper.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.example.demo.mapper.OperateBasicMapper">
        <select id="getById" resultType="map">
            select *
              from
                bw_operate_basic
                where
                  id=#{id}
        </select>
    </mapper>

mapper接口

package com.example.demo.mapper;
    
    import org.apache.ibatis.annotations.Param;
    
    import java.util.Map;
    
    /**
     * @ClassName OperateBasicMapper
     * @Description TODO
     * @Author yueyiming
     * @Date 2019/4/3 15:52
     */
    public interface OperateBasicMapper {
         Map<String,Object> getById(@Param(value = "id") Long id);
    }

service

package com.example.demo.service;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.example.demo.mapper.OperateBasicMapper;
    
    import java.util.Map;
    
    /**
     * @ClassName OperateBasicService
     * @Description TODO
     * @Author yueyiming
     * @Date 2019/4/3 16:02
     */
    @Service
    public class OperateBasicService {
        @Autowired
        private OperateBasicMapper operateBasicMapper;
    
        public Map<String,Object> getById(Long id){
            return operateBasicMapper.getById(id);
        }
    
    }

controller

package com.example.demo.controller;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import com.example.demo.service.OperateBasicService;
    
    /**
     * @ClassName TestController
     * @Description TODO
     * @Author yueyiming
     * @Date 2019/4/3 15:49
     */
    @RestController
    public class TestController {
        @Autowired
    
        private OperateBasicService operateBasicService;
        @RequestMapping(value="get/{id}")
        public Object test(@PathVariable Long id) {
            return operateBasicService.getById(id);
        }
    }

访问

[http://127.0.0.1:8089/get/1][http_127.0.0.1_8089_get_1]

![20190403165333759.png][]

访问

[http://127.0.0.1:8089/druid][http_127.0.0.1_8089_druid]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 6][]

登陆后

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 7][] 测试成功！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70]: /images/20220224/c5e4a4ed5c514e49ba06ec6cc87cec69.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 1]: /images/20220224/c888863aca5b4982809bad0be7afb5b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 2]: /images/20220224/8a393b0f961845e0ad72317eb40da042.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 3]: /images/20220224/f4b4c98fbd0b4d19bb79f8c9c1431781.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 4]: /images/20220224/4ece7d88b8e54705a3fc4c0d1034baaa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 5]: /images/20220224/eb6130b502ba403ca246fe11c31aa56c.png
[http_127.0.0.1_8089_get_1]: http://127.0.0.1:8089/get/1
[20190403165333759.png]: /images/20220224/8da5db4abc7b4a0badceb75fa580d081.png
[http_127.0.0.1_8089_druid]: http://127.0.0.1:8089/druid/login.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 6]: /images/20220224/9d6b9d51126f4a8e92d59e0a759cc900.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h6YXVfaXRkb2c_size_16_color_FFFFFF_t_70 7]: /images/20220224/704d2c04105d46e695467c5a8521b218.png