MyBatis新增数据时自增id的两种写法
一、单个插入
public interface PlayerDao {
int insertOnePlayer ( Player player) ;
int insertOnePlayer2 ( Player player) ;
}
1.1 方式一
public void testInsertGenerateId1 ( ) throws IOException {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
PlayerDao mapper = sqlSession. getMapper ( PlayerDao. class ) ;
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 ( ) ) ;
}
< 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 {
SqlSession sqlSession = sqlSessionFactory. openSession ( ) ;
PlayerDao mapper = sqlSession. getMapper ( PlayerDao. class ) ;
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>
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后面还有一个逗号导致一直空指针的错误。