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

一、单个插入

接口方法：

    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)