MyBatis新增数据时自增id的两种写法

MyBatis新增数据时自增id的两种写法

• 一、单个插入

1. 方式一
2. 方式二

• 二、批量插入
• 三、注意

一、单个插入

接口方法：

 public interface PlayerDao {
        int insertOnePlayer(Player player);
        int insertOnePlayer2(Player player);
    }

1.1 方式一

public void testInsertGenerateId1() throws IOException {
           // 2.获取sqlSession
           SqlSession sqlSession = sqlSessionFactory.openSession();
           // 3.获取对应mapper
           PlayerDao mapper = sqlSession.getMapper(PlayerDao.class);
           // 4.执行查询语句并返回结果
           Player player = new Player();
           player.setPlayName("Allen Iverson");
           player.setPlayNo(3);
           player.setTeam("76ers");
           player.setHeight(1.83F);
           mapper.insertOnePlayer(player);
           sqlSession.commit();
           System.out.println(player.getId());
       }

Mapper文件：

<insert id="insertOnePlayer" parameterType="Player" useGeneratedKeys="true" keyProperty="id">
 		insert into tb_player (id, playName, playNo,team, height)
 		values (
             #{id,jdbcType=INTEGER},
             #{playName,jdbcType=VARCHAR},
             #{playNo,jdbcType=INTEGER},
             #{team,jdbcType=VARCHAR},
             #{height,jdbcType=DECIMAL}
 		)
</insert>

方式一配置：useGeneratedKeys=“true” keyProperty=“id” 即可

1.2 方式二

 public void testInsertGenerateId2() throws IOException {
            // 2.获取sqlSession
            SqlSession sqlSession = sqlSessionFactory.openSession();
            // 3.获取对应mapper
            PlayerDao mapper = sqlSession.getMapper(PlayerDao.class);
            // 4.执行查询语句并返回结果
            Player player = new Player();
            player.setPlayName("Tony Parker");
            player.setPlayNo(9);
            player.setTeam("spurs");
            player.setHeight(1.88F);
            mapper.insertOnePlayer2(player);
            sqlSession.commit();
            System.out.println(player.getId());
        }

Mapper文件：

   <insert id="insertOnePlayer2" parameterType="Player">
           <selectKey  keyProperty="id" order="AFTER" resultType="int">
               select LAST_INSERT_ID()
           </selectKey>
           insert into tb_player (id, playName, playNo,team, height)
           values (
           #{id,jdbcType=INTEGER},
           #{playName,jdbcType=VARCHAR},
           #{playNo,jdbcType=INTEGER},
           #{team,jdbcType=VARCHAR},
           #{height,jdbcType=DECIMAL}
           )
       </insert>

方式二通过 selectKey 标签完成 ,selectKey 更加灵活，支持一定程度的自定义

二、批量插入

Java文件省略了，这里直接给出Mapper文件， Mapper 文件如下，其实就是：useGeneratedKeys=“true” keyProperty=“id”，其中id是JavaBean的主键id

<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
   INSERT INTO partition_info (id, node_ip_id, init_schema_info_id,
   prefix_table_index, partition_num, start_time,
   end_time, create_time, is_delete
   )
   values
   <foreach collection="list" item="item" index="index" separator=",">
     (#{item.id,jdbcType=INTEGER}, #{item.nodeIpId,jdbcType=INTEGER}, #{item.initSchemaInfoId,jdbcType=INTEGER},
     #{item.prefixTableIndex,jdbcType=VARCHAR}, #{item.partitionNum,jdbcType=VARCHAR}, #{item.startTime,jdbcType=TIMESTAMP},
     #{item.endTime,jdbcType=TIMESTAMP}, #{item.createTime,jdbcType=TIMESTAMP}, #{item.isDelete,jdbcType=TINYINT}
     )
   </foreach>
 </insert>

Java代码

System.out.println("before insert ...");
    for (PartitionInfo p: list) {
        System.out.println(p);
    }

    PartitionInfoMapper mapper = sqlSession.getMapper(PartitionInfoMapper.class);
    int i = mapper.insertBatch(list);
    System.out.println("The rows be affected :" + i);

    System.out.println("after insert ...");
    for (PartitionInfo p: list) {
        System.out.println(p);
    }

输出

before insert ...
PartitionInfo(id=null, nodeIpId=1, initSchemaInfoId=1, prefixTableIndex=1, partitionNum=null, startTime=null, endTime=null, createTime=Fri Dec 13 18:26:46 CST 2019, isDelete=null)
PartitionInfo(id=null, nodeIpId=2, initSchemaInfoId=2, prefixTableIndex=2, partitionNum=null, startTime=null, endTime=null, createTime=Fri Dec 13 18:26:46 CST 2019, isDelete=null)
PartitionInfo(id=null, nodeIpId=3, initSchemaInfoId=3, prefixTableIndex=3, partitionNum=null, startTime=null, endTime=null, createTime=Fri Dec 13 18:26:46 CST 2019, isDelete=null)
The rows be affected :3
after insert ...
PartitionInfo(id=701, nodeIpId=1, initSchemaInfoId=1, prefixTableIndex=1, partitionNum=null, startTime=null, endTime=null, createTime=Fri Dec 13 18:26:46 CST 2019, isDelete=null)
PartitionInfo(id=702, nodeIpId=2, initSchemaInfoId=2, prefixTableIndex=2, partitionNum=null, startTime=null, endTime=null, createTime=Fri Dec 13 18:26:46 CST 2019, isDelete=null)
PartitionInfo(id=703, nodeIpId=3, initSchemaInfoId=3, prefixTableIndex=3, partitionNum=null, startTime=null, endTime=null, createTime=Fri Dec 13 18:26:46 CST 2019, isDelete=null)

这里其他的代码都省略了，基本上就是： useGeneratedKeys=“true” keyProperty=“id” 这两个标签起作用
另外我用的mybatis版本是 3.4.1

三、注意

注意Mapper文件中的 insert into tb_player (id, playName, playNo,team, height)，这里不要多了一个逗号，之前height后面还有一个逗号导致一直空指针的错误。
————————————————
版权声明：本文为CSDN博主「惑边」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/my_momo_csdn/article/details/88535993