Mybatis Mapper代理开发中使用＜insert＞标签插入数据时，数据库中的数据表主键（id）自增不连续问题的解决方法_mybatis mapper insert

        2.配置sql映射文件


     

<insert id="add" useGeneratedKeys="true" keyProperty="id">

    insert into tb_brand1 (brand_name, company_name, ordered, description, status)
    values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status});
</insert>

```

测试方法：

//添加
   @Test
   public void testAdd() throws IOException {
      //存入参数
 
      int status = 1;
      String companyName = "firm4";
      String brandName = "某某品牌";
      String description = "天下第一";
      int ordered = 300;

      Brand brand = new Brand();
      brand.setStatus(status);
      brand.setCompanyName(companyName);
      brand.setBrandName(brandName);
      brand.setStatus(status);
      brand.setOrdered(ordered);
      brand.setDescription(description);


      //1.加载mybatis的核心配置文件，获取SqlSessionFactory
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

      //2.获取执行SqlSession的对象
//      SqlSession sqlSession = sqlSessionFactory.openSession();

      //开启自动提交事务
      SqlSession sqlSession = sqlSessionFactory.openSession(true);

      //3.获取mapper对象
      BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

      //4.执行方法
      mapper.auto();

      //5.传入band类
      mapper.add(brand);



      //释放资源
      sqlSession.close();
   }

Brand类：

public class Brand {
  
     private Integer id;
     private String brandName ;
     private String companyName    ;
  // 排序字段
     private Integer ordered         ;
     private String description     ;
  // 状态信息
     private Integer status          ;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status='" + status + '\'' +
                '}';
    }
}

---

问题描述

这里我们模拟一次数据插入失败，即不提交事务，然后再开启提交事务，即成功提交一次事务

        1、测试类如下：

        

//添加
   @Test
   public void testAdd() throws IOException {
      //存入参数
      int status = 1;
      String companyName = "frim4";
      String brandName = "某某品牌";
      String description = "天下第一";
      int ordered = 300;

      Brand brand = new Brand();
      brand.setStatus(status);
      brand.setCompanyName(companyName);
      brand.setBrandName(brandName);
      brand.setStatus(status);
      brand.setOrdered(ordered);
      brand.setDescription(description);


      //1.加载mybatis的核心配置文件，获取SqlSessionFactory
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

      //2.获取执行SqlSession的对象
      SqlSession sqlSession = sqlSessionFactory.openSession();

      //开启自动提交事务
//      SqlSession sqlSession = sqlSessionFactory.openSession(true);

      //3.获取mapper对象
      BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

      //4.执行方法
      mapper.auto();
      mapper.add(brand);


      //释放资源
      sqlSession.close();
   }

执行改方法后，发现数据表没有任何改变（未提交事务，自动回滚）

开启自动提交事务，再次提交数据

        测试类：

        

//添加
   @Test
   public void testAdd() throws IOException {
      //存入参数
      int status = 1;
      String companyName = "frim4";
      String brandName = "某某品牌";
      String description = "天下第一";
      int ordered = 300;

      Brand brand = new Brand();
      brand.setStatus(status);
      brand.setCompanyName(companyName);
      brand.setBrandName(brandName);
      brand.setStatus(status);
      brand.setOrdered(ordered);
      brand.setDescription(description);


      //1.加载mybatis的核心配置文件，获取SqlSessionFactory
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

      //2.获取执行SqlSession的对象
//      SqlSession sqlSession = sqlSessionFactory.openSession();

      //开启自动提交事务
      SqlSession sqlSession = sqlSessionFactory.openSession(true);

      //3.获取mapper对象
      BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);

      //4.执行方法
      mapper.auto();
      mapper.add(brand);



      //释放资源
      sqlSession.close();
   }

运行结果：

        

---

原因分析：

唯一键冲突、事务回滚、批量写库操作

---

解决方案：

1.先删除掉错误的数据（id为5的那一行数据）

2.增加sql语句：

                alter table tb_brand auto_increment = 1;

新插入的数据会从当前表最大的id开始+1;

在Mybatis中，

        我们在接口中写一个执行上述sql语句的方法：

        BrandMape接口如下：

        

package com.aimin.mapper;

import com.aimin.pojo.Brand;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface BrandMapper {


//   添加
   void add(Brand brand);

//解决主键自增不连续
   void  auto();
}

sql映射文件如下：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aimin.mapper.BrandMapper">