springboot 集成 DruidDataSource +mybatis

源码
  1. 引入 Druid +mybatis 等开发包

    <?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">
        <parent>
            <artifactId>springboot-integrate</artifactId>
            <groupId>springboot-integrate</groupId>
            <version>1.0-SNAPSHOT</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <artifactId>springboot-mybatis</artifactId>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <!--mysql-->
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.11</version>
            </dependency>
    
            <!--druid(数据库连接池)-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.21</version>
            </dependency>
    
            <!--mybatis-->
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.2</version>
                <scope>provided</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <configuration>
                        <mainClass>com.integrate.mybatis.MybatisApplication</mainClass>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    </project>
    
  2. application.yml 文件配置 数据源信息 及 数据源连接池信息

    spring:
      application:
        name: springboot-mybatis
      #druid数据源相关配置配置
      datasource:
        url: jdbc:mysql://101.37.152.195:3306/consumer?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&serverTimezone=Asia/Shanghai&allowMultiQueries=true
        username: root
        password: lzq199528
        driver-class-name: com.mysql.cj.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
    
        #连接池的配置信息  Spring Boot 不支持 Druid 需要手动配置
        initialSize: 5
        minIdle: 10
        maxActive: 20
        maxWait: 6000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 30000
        validationQuery: SELECT 1
        validationQueryTimeout: 10000
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,wall
        connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
        useGlobalDataSourceStat: true
    
    #mybatis 配置信息
    mybatis:
      type-aliases-package: com.integrate.mybatis.domain
      config-location: classpath:mybatis/mybatis-config.xml
      mapper-locations: classpath:mappers/*.xml
    
    server:
      port: 10004
    
  3. 数据源 配置 Druid 连接池信息

    package com.integrate.mybatis.config.druid;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.CommandLineRunner;
    import org.springframework.core.env.Environment;
    import org.springframework.stereotype.Component;
    
    import javax.sql.DataSource;
    import java.util.Properties;
    
    /**
     * @author 刘志强
     * @date 2020/11/27 14:02
     * com.integrate.multiple.sources.mapper.consumer 生效范围
     */
    @Component
    @Slf4j
    public class DataSourceConfiguration implements CommandLineRunner {
    
        @Autowired
        private Environment env;
    
        @Autowired
        private DataSource dataSource;
    
    
        @Override
        public void run(String... args) throws Exception {
            // 配置链接池信息
            log.info("数据源连接池配置");
            String prefixPool = "spring.datasource.";
            Properties prop = new Properties();
            prop.put("druid.initialSize", env.getProperty(prefixPool + "initialSize", String.class));
            prop.put("druid.maxActive", env.getProperty(prefixPool + "maxActive", String.class));
            prop.put("druid.minIdle", env.getProperty(prefixPool + "minIdle", String.class));
            prop.put("druid.maxWait", env.getProperty(prefixPool + "maxWait", String.class));
            prop.put("druid.poolPreparedStatements", env.getProperty(prefixPool + "poolPreparedStatements", String.class));
            prop.put("druid.maxPoolPreparedStatementPerConnectionSize", env.getProperty(prefixPool + "maxPoolPreparedStatementPerConnectionSize", String.class));
            prop.put("druid.validationQuery", env.getProperty(prefixPool + "validationQuery", String.class));
            prop.put("druid.validationQueryTimeout", env.getProperty(prefixPool + "validationQueryTimeout", String.class));
            prop.put("druid.testOnBorrow", env.getProperty(prefixPool + "testOnBorrow", String.class));
            prop.put("druid.testOnReturn", env.getProperty(prefixPool + "testOnReturn", String.class));
            prop.put("druid.testWhileIdle", env.getProperty(prefixPool + "testWhileIdle", String.class));
            prop.put("druid.timeBetweenEvictionRunsMillis", env.getProperty(prefixPool + "timeBetweenEvictionRunsMillis", String.class));
            prop.put("druid.minEvictableIdleTimeMillis", env.getProperty(prefixPool + "minEvictableIdleTimeMillis", String.class));
            prop.put("druid.filters", env.getProperty(prefixPool + "filters", String.class));
    
            DruidDataSource druidDataSource = (DruidDataSource) dataSource;
            druidDataSource.configFromPropety(prop);
            // 数据库初始化
            druidDataSource.init();
            log.info("数据源初始化结束");
        }
    }
    
    
  4. 配置 Druid web 信息

    package com.integrate.mybatis.config.druid;
    
    
    import com.alibaba.druid.filter.stat.StatFilter;
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    import com.alibaba.druid.wall.WallConfig;
    import com.alibaba.druid.wall.WallFilter;
    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;
    
    
    /**
     * @author 刘志强
     * @date 2020/11/27 14:02
     * @ServletComponentScan // 用于扫描所有的Servlet、filter、listener
     */
    @Configuration
    public class DruidConfig {
    
    
        @Bean
        public ServletRegistrationBean<StatViewServlet> druidServlet() {
            ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
            
            servletRegistrationBean.addInitParameter("loginUsername", "root");
            servletRegistrationBean.addInitParameter("loginPassword", "1234");
            return servletRegistrationBean;
        }
    
        @Bean
        public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() {
            FilterRegistrationBean<WebStatFilter> 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 StatFilter statFilter() {
            StatFilter statFilter = new StatFilter();
            //slowSqlMillis用来配置SQL慢的标准,执行时间超过slowSqlMillis的就是慢。
            statFilter.setLogSlowSql(true);
            //SQL合并配置
            statFilter.setMergeSql(true);
            //slowSqlMillis的缺省值为3000,也就是3秒。
            statFilter.setSlowSqlMillis(1000);
            return statFilter;
        }
    
        @Bean
        public WallFilter wallFilter() {
            WallFilter wallFilter = new WallFilter();
            //允许执行多条SQL
            WallConfig config = new WallConfig();
            config.setMultiStatementAllow(true);
            wallFilter.setConfig(config);
            return wallFilter;
        }
    }
    
    
  5. mybatis-config.xml mybatis配置信息

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
    
        <!-- 配置mybatis的缓存,延迟加载等等一系列属性 -->
        <settings>
            <setting name="autoMappingBehavior" value="FULL"/>
            <!--开启驼峰映射 xml文件中 resultType 为实体类时, 将 如 user_name数据库字段 映射为 实体类 userName字段-->
            <setting name="mapUnderscoreToCamelCase" value="true"/>
            <!--  开启二级缓存  	-->
            <setting name="cacheEnabled" value="true"/>
            <!--  懒加载  -->
            <setting name="lazyLoadingEnabled" value="true"/>
            <setting name="aggressiveLazyLoading" value="false"/>
    
        </settings>
    </configuration>
    
    1. mybatis-config.xml详解(mybatis配置文件)
  6. 依次创建 controller mapper service domain(数据库实体类) 目录

    1. controller

          package com.integrate.mybatis.controller;
      
          import com.integrate.mybatis.domain.ConsumerUser;
          import com.integrate.mybatis.service.ConsumerUserService;
          import lombok.extern.slf4j.Slf4j;
          import org.springframework.beans.factory.annotation.Autowired;
          import org.springframework.web.bind.annotation.CrossOrigin;
          import org.springframework.web.bind.annotation.GetMapping;
          import org.springframework.web.bind.annotation.PostMapping;
          import org.springframework.web.bind.annotation.RestController;
          
          /**
           * @author 刘志强
           * @date 2020/11/27 14:18
           */
          @RestController
          @Slf4j
          @CrossOrigin
          public class TestController {
          
              @Autowired
              private ConsumerUserService consumerUserService;
          
          
              /**
               * 获取Consumer来源的用户
               *
               * @param id
               * @return
               */
              @GetMapping("getConsumerUser")
              public ConsumerUser getConsumerUser(Long id) {
                  return consumerUserService.getConsumerUser(id);
              }
          
          
              /**
               * 添加consumer用户
               *
               * @param consumerUser
               * @return
               */
              @PostMapping("addConsumerUser")
              public String addConsumerUser(ConsumerUser consumerUser) {
                  return consumerUserService.addConsumerUser(consumerUser);
              }
          
          }
      
      
    2. service

      package com.integrate.mybatis.service;
      
      import com.integrate.mybatis.domain.ConsumerUser;
      
      /**
       * @author 刘志强
       * @date 2020/11/27 14:20
       */
      public interface ConsumerUserService {
      
          /**
           * 获取消费库用户
           * @param id
           * @return
           */
          ConsumerUser getConsumerUser(Long id);
      
          /**
           * 添加消费库用户
           * @param consumerUser
           * @return
           */
          String addConsumerUser(ConsumerUser consumerUser);
      }
      
      
      package com.integrate.mybatis.service.impl;
      
      
      import com.integrate.mybatis.domain.ConsumerUser;
      import com.integrate.mybatis.mapper.ConsumerUserMapper;
      import com.integrate.mybatis.service.ConsumerUserService;
      import lombok.extern.slf4j.Slf4j;
      import org.springframework.beans.factory.annotation.Autowired;
      import org.springframework.stereotype.Service;
      import org.springframework.transaction.annotation.Transactional;
      
      /**
       * @author 刘志强
       * @date 2020/11/27 14:20
       */
      @Service
      @Slf4j
      public class ConsumerUserImpl implements ConsumerUserService {
      
          @Autowired
          private ConsumerUserMapper consumerUserMapper;
      
          @Override
          public ConsumerUser getConsumerUser(Long id) {
              return consumerUserMapper.getConsumerUserById(id);
          }
      
          @Override
          @Transactional(rollbackFor = Exception.class)
          public String addConsumerUser(ConsumerUser consumerUser) {
              int i = consumerUserMapper.addConsumerUser(consumerUser);
              log.info(consumerUser.toString());
              if (i > 0) {
                  return "添加成功";
              } else {
                  return "添加失败";
              }
          }
      }
      
      
    3. mapper

      package com.integrate.mybatis.mapper;
      
      import com.integrate.mybatis.domain.ConsumerUser;
      import org.apache.ibatis.annotations.Mapper;
      import org.apache.ibatis.annotations.Param;
      
      /**
       * @author 刘志强
       * @date 2020/11/27 14:22
       */
      @Mapper
      public interface ConsumerUserMapper {
      
          /**
           * 获取消费库用户
           *
           * @param id
           * @return
           */
          ConsumerUser getConsumerUserById(@Param("id") Long id);
      
          /**
           * 添加消费库用户
           * @param consumerUser
           * @return
           */
          int addConsumerUser(ConsumerUser consumerUser);
      }
      
      
      1. @Param(该注解属于MyBatis)作为Dao层的注解,作用是用于传递参数,从而可以与SQL中的的字段名相对应,一般在2=<参数数<=5时使用最佳。
      2. 如果你的映射方法接受多个参数,就可以使用这个注解自定义每个参数的名字。否则在默认情况下,除RowBounds以外的参数会以 “param” 加参数位置被命名。例如 #{param1}, #{param2}。如果使用了 @Param(“person”),参数就会被命名为 #{person}。(你可以传递多个参数给一个映射器方法。在多个参数的情况下,默认它们将会以 param 加上它们在参数列表中的位置来命名,比如:#{param1}、#{param2}等。如果你想(在有多个参数时)自定义参数的名称,那么你可以在参数上使用 @Param(“paramName”) 注解)。
    4. 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.integrate.mybatis.mapper.ConsumerUserMapper">
      
        <select id="getConsumerUserById" resultType="com.integrate.mybatis.domain.ConsumerUser">
          select a.* from user a where a.id = #{id}
        </select>
        <!--useGeneratedKeys="true" keyProperty="id" 添加成功后 主键id set进入consumerUser对象 切记参数不要用@Param注解,否则失效-->
        <insert id="addConsumerUser"
                parameterType="com.integrate.mybatis.domain.ConsumerUser"
                useGeneratedKeys="true" keyProperty="id">
          insert into user (user_name) values (#{userName});
        </insert>
      </mapper>        
      
      1. namespace 命名空间 在编译后 xml会转称对应接口得实现类

      2. id 和 Mapper 中的方法名对应,不可以重复

      3. resultType 返回结果类型 对应 Mapper 中对应方法中的返回类型或泛型

      4. parameterType 接受的参数类型。没有使用@Param 时使用

      5. useGeneratedKeys=“true” keyProperty=“id” 添加成功后 主键id set进入consumerUser对象 切记参数不要用@Param注解,否则失效

      6. XML 映射文件详解(mappers.xml)

      7. 动态sql(if,foreach) 等

    5. mapper.xml 注意事项

      1. mybatis 标签中 0 等于false的问题
        1. 在if标签中 如果将 整形当做字符串比较会出现此问题 也就是 添加 != ‘’ 判断时。对于非字符串的类型无需添加 != ‘’
        2. 有问题的写法
              <if test="state != null and state != ''">
          
        3. 没有问题的写法
              <if test="state != null">
          
      2. 在使用 foreach 时。如果要遍历对像是个二维数组使用时 使用$ 否则会报未定义错误
        1. 示例
          <foreach collection="indexScreenDto.memberLevel" item="arr" index="index" open="AND (" separator="or"
                         close=")">
                  (
                  a.member_level = ${arr[0]}
                  <if test="arr[1] != null">
                    AND a.beautiful_state = ${arr[1]}
                  </if>
                  )
          </foreach>                
          
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值