springboot整合mybatis+Druid+声明式事务管理（日常踩坑）

整合MyBatis

搭建springboot环境

		<?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 https://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.3.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>cn.codewhite</groupId>
    <artifactId>springboot-mybatis-07</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-mybatis-07</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-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <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.1.3</version>
        </dependency>

        <!--druid数据库连接池-->
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

数据库环境：

CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` int(2) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

实体类：

public class Department {

    private Integer id;
    private String departmentName;

public class Employee {

    private Integer id;
    private String lastName;
    private Integer gender;
    private String email;
    private Integer dId;

纯注解版

//指定这是一个操作数据库的mapper
@Mapper
@Repository
public interface DepartmentMapper {


    @Select("select * from department where id = #{id}")
    Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    int deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into department(department_name) values(#{departmentName})")
    int insertDept(Department department);

    @Update("update department set department_name=#{departmentName} where id=#{id}")
    int updateDept(Department department);
}

使用组件整合：

@org.springframework.context.annotation.Configuration
public class MyBatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer(){

            @Override
            public void customize(Configuration configuration) {
                //开启驼峰命名
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

//可以不在每个Mapper中加入@Mapper，直接使用MapperScan批量扫描所有的Mapper接口；
@MapperScan("cn.codewhite.mapper")
@SpringBootApplication
public class SpringbootMybatis07Application {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatis07Application.class, args);
    }

}

配置文件版:application.yml

EmployeeMapper

@Repository("employeeMapper")
public interface EmployeeMapper {


    Employee findEmpById(Integer id);

    Employee findEmpByName(String name);
}

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="cn.codewhite.mapper.EmployeeMapper">
   <!--    public Employee getEmpById(Integer id);
    Employee findEmpById(Integer id);

    Employee findEmpByName(String name);
    public void insertEmp(Employee employee);-->
    <select id="findEmpById" parameterType="integer"  resultType="cn.codewhite.bean.Employee">
        SELECT * FROM employee WHERE id=#{id}
    </select>

    <select id="findEmpByName" parameterType="string" resultType="cn.codewhite.bean.Employee">
         SELECT * FROM employee WHERE last_name=#{name}
    </select>
</mapper>

application:yml指定

# 配置数据源
spring:
  datasource:
    # druid数据库连接池
    type: com.alibaba.druid.pool.DruidDataSource
    username: root
    password: 20010303
    url: jdbc:mysql://localhost:2001/spring?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    # druid设置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true

    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    #  filters: stat,wall,log4j 需要导入log4j

    filters: stat,wall
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    #    schema:
    #     - classpath:department.sql


mybatis:
# 指定配置文件的地址
# config-location: classpath:mybatis/mybatis-config.xml
  #指定sql映射文件的位置
 mapper-locations: classpath:mapper/*.xml
 type-aliases-package: cb.codewhite.bean  # 注意：对应实体类的路径
 configuration: # 驼峰命名需要
   map-underscore-to-camel-case: true    # 驼峰命名开启

#pagehelper分页插件
pagehelper:
  helperDialect: mysql
  reasonable: true
  supportMethodsArguments: true
  params: count=countSql

推荐：使用注解加配置文件：application.yml配置mybatis+mapper.xml+mapper.java

主配置类：

//指定mapper存在的地址
@MapperScan("cn.codewhite.mapper")
@SpringBootApplication
public class SpringbootMybatis07Application {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatis07Application.class, args);
    }

}

Springboot整合Mybatis事务机制：

第一种情况：@Transactional不仅可以注解在方法上，也可以注解在类上。当注解在类上时，意味着此类的所有public方法都是开启事务的。如果类级别和方法级别同时使用了@Transactional注解，则使用在类级别的注解会重载方法级别的注解。

第二种情况：只有运行时异常或者Error才会生效

@Service
@Transactional
public class DepartmentServiceImpl implements DepartmentService {

    @Autowired
    private DepartmentMapper departmentMapper;


    @Override
    public void insertDept(Department department) throws Exception {
        departmentMapper.insertDept(department);
        throw new RuntimeException("发生错误");
    }
}

第三种情况：手动抛出其它类型的异常却不会捕获

    @Override
    public void insertDept(Department department) throws Exception {
        departmentMapper.insertDept(department);
        throw new SQLException("发生错误");
    }

解决方法：在事务添加@Transactional(rollbackFor = Exception.class)指定rollback

第四种情况：异常被catch掉了，事务不会生效

事务有影响性能，最好在需要事务的方法上加上事务就好。