springboot 集成 DruidDataSource +mybatis

小灰灰 2022-01-26 13:57 248阅读 0赞

#### [源码][Link 1] ####

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>
                
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>8.0.11</version>
                </dependency>
        
                
                <dependency>
                    <groupId>com.alibaba</groupId>
                    <artifactId>druid</artifactId>
                    <version>1.1.21</version>
                </dependency>
        
                
                <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>
        
            
            <settings>
                <setting name="autoMappingBehavior" value="FULL"/>
                
                <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配置文件）][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>
              
              <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）][XML _mappers.xml]
        7.  [动态sql(if,foreach) 等][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>

[Link 1]: https://gitee.com/lzq199528/springboot-integrate/tree/master/springboot-mybatis
[mybatis-config.xml_mybatis]: http://www.mybatis.org/mybatis-3/zh/configuration.html#settings
[XML _mappers.xml]: http://www.mybatis.org/mybatis-3/zh/sqlmap-xml.html#select
[sql_if_foreach_]: http://www.mybatis.org/mybatis-3/zh/dynamic-sql.html#