Mybatis:Mapper代理开发中使用<insert>标签插入数据时,数据库中的数据表主键(id)自增不连续问题的解决方法
问题场景:
在数据库定义表时,使用AUTO_INCREMENT关键字(自增长序列)让主键(此处指id)自增,即每次插入一行数据时,不需要插入id值,id值会自动执行id++操作
数据表:
CREATE TABLE tb_brand1(
-- 设置主键自增长
id INT PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(30),
company_name VARCHAR(30),
ordered INT,
description VARCHAR(100),
status INT
)
INSERT
INTO tb_brand1(brand_name, company_name, ordered, description, status)
VALUES('某某未知品牌', 'frim1', 1, '天下第一', '1'),
('某某未知品牌', 'frim2', 10, '天下第一', '1'),
('某某未知品牌', 'frim3', 100, '天下第一', '1');
SELECT * FROM tb_brand1;
运行结果:
通过Mybatis对数据表插入数据:
1、配置接口(接口和映射文件要在同一目录)
package com.aimin.mapper;
//这里导入自己创建的Brand类,注意路径
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);
}
2.配置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">
<!--ins-->
<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>
</mapper>
测试方法:
//添加
@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">
<!--ins-->
<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>
<update id="auto">
alter table tb_brand1 auto_increment = 1;
</update>
</mapper>
再次执行测试方法:
//添加
@Test
public void testAdd() throws IOException {
//存入参数
int id = 1;
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(true);
//3.获取mapper对象
BrandMapper mapper = sqlSession.getMapper(BrandMapper.class);
//4.执行方法
//执行新增方法
mapper.auto();
mapper.add(brand);
//释放资源
sqlSession.close();
}
执行结果:
注意!!!
不能在<insert>标签中直接添加 ”alter table tb_brand auto_increment = 1;“
原因:
MyBatis 的
<insert>
标签插入数据的语句中包含了 SQL 的 DDL 操作,即alter table
语句,而 MyBatis 在执行插入操作时,会先执行 SQL 的 DML 操作,再执行 DDL 操作。因此,如果在<insert>
标签中包含 DDL 操作,会导致 MyBatis 报错。解决这个问题的方法是,将 DDL 操作放在
<insert>
标签之外,例如可以将 DDL 操作放在一个单独的 SQL 文件中,在需要执行 DDL 操作时再单独执行。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
二维码