6 插入数据的时候获取自增的id

张创琦 2022.03.12

1. 在navicat上创建一个sql文件

CREATE TABLE `gamerecord` (
`recordId` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`homeTeamId` int DEFAULT NULL COMMENT '主队ID',
`gameDate` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '比赛日期',
`score` int DEFAULT NULL COMMENT '得分',
`visitingTeamId` int DEFAULT NULL COMMENT '客队ID', PRIMARY KEY (`recordId`),
KEY `homeTeamId` (`homeTeamId`),
KEY `visitingTeamId` (`visitingTeamId`),
CONSTRAINT `gamerecord_ibfk_1` FOREIGN KEY (`homeTeamId`) REFERENCES `team` (`teamId`), CONSTRAINT `gamerecord_ibfk_2` FOREIGN KEY (`visitingTeamId`) REFERENCES `team` (`teamId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1. 添加实体类

package com.kkb.pojo;
import java.util.Date;
/**
 * ClassName: GameRecord
 * 球队记录实体类
 * @author wanglina
 * @version 1.0
 */
public class GameRecord {
    private String recordId;
    private Integer homeTeamId;
    private Date gameDate;
    private Integer score;
    private Integer visitingTeamId;
    //省略set get
    public String getRecordId() {
        return recordId;
    }
    public void setRecordId(String recordId) {
        this.recordId = recordId;
    }
    public Integer getHomeTeamId() {
        return homeTeamId;
    }
    public void setHomeTeamId(Integer homeTeamId) {
        this.homeTeamId = homeTeamId;
    }
    public Date getGameDate() {
        return gameDate;
    }
    public void setGameDate(Date gameDate) {
        this.gameDate = gameDate;
    }
    public Integer getScore() {
        return score;
    }
    public void setScore(Integer score) {
        this.score = score;
    }
    public Integer getVisitingTeamId() {
        return visitingTeamId;
    }
    public void setVisitingTeamId(Integer visitingTeamId) {
        this.visitingTeamId = visitingTeamId;
    }
}

1. 添加mapper接口

package com.kkb.mapper;
import com.kkb.pojo.GameRecord;
/**
 * ClassName: GameRecord
 * GameRecordMapper接口
 * @author wanglina
 * @version 1.0
 */
public interface GameRecordMapper {
    int add(GameRecord record);
}

1. 添加 TeamRecordMapper.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">
<!--namespace="名称必须与映射的类的名字一致，是完全限定名"-->
<mapper namespace="com.kkb.mapper.GameRecordMapper">
    <!-- 添加一条比赛记录 -->
    <insert id="add" parameterType="com.kkb.pojo.GameRecord" >
        <!--插入数据之前先获取36位字符串作为id放入属性recordId中order="AFTER/BEFORE" 在insert执行之前还是之后resultType="返回值的类型" -->
        <selectKey keyProperty="recordId" order="BEFORE" resultType="java.lang.String"> select uuid()
        </selectKey>
        INSERT INTO `ssm1`.`gamerecord` (`recordId`, `homeTeamId`, `gameDate`, `score`, `visitingTeamId`) VALUES (#{recordId}, #{homeTeamId},default, #{score}, #{visitingTeamId})
    </insert>
</mapper>

1. mybatis.xml 中注册映射文件

<!-- 注册映射文件 -->
    <mappers>
        <mapper resource="com/kkb/pojo/Team.xml"/>
        <mapper resource="com/kkb/mapper/TeamMapper.xml"></mapper>
        <mapper resource="com/kkb/mapper/GameRecordMapper.xml"></mapper>
    </mappers>

1. 编写测试类
   但是会乱码

package com.kkb.test;
import com.kkb.mapper.GameRecordMapper;
import com.kkb.pojo.GameRecord;
import com.kkb.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
/**
 * ClassName: GameRecordMapperTest
 * GameRecordMapper测试类
 * @author wanglina
 * @version 1.0
 */
public class GameRecordMapperTest {
    private SqlSession sqlSession = MybatisUtil.getSqlSession();
    // 调试成功
    @Test
    public void testAdd(){
        GameRecordMapper mapper = sqlSession.getMapper(GameRecordMapper.class);
        GameRecord record = new GameRecord();
        record.setHomeTeamId(1007);
        record.setVisitingTeamId(1002);
        record.setScore(118);
        int num = mapper.add(record); sqlSession.commit(); // 必须提交才能让增删改生效
        System.out.println("add结果："+num);
        System.out.println(record.getRecordId());
    }
}

运行结果说明：

先获取 suuid（主键）

在执行插入语句。