1、useGeneratedKeys="true" 可以获取自增长的ID 只支持具有自增长方式的那种数据库(mysql, mssql 等 但 oracle 就不支持了 ),如mysql:
现userId为主键,且设置了自增长,现在插入一条数据并且获取这条数据的userId:
<insert id="insertAndGetId" useGeneratedKeys="true" keyProperty="userId" parameterType="User">
insert into user(userName,password,comment)
values(#{userName},#{password},#{comment})
</insert>
User user = new User();
user.setUserName("wtyy");
user.setPassword("xxxx");
user.setComment("测试插入数据返回主键功能");
System.out.println("插入前主键为:"+user.getUserId()); //没有 0
userDao.insertAndGetId(user);//插入操作
System.out.println("插入后主键为:"+user.getUserId()); //userId 的值
2.对于不支持自动生成主键(如Oracle),可以采用以下方式
<insert id="insert" parameterType="com.test.User">
<selectKey resultType="INTEGER" order="BEFORE" keyProperty="userId">
SELECT SEQ_USER.NEXTVAL as userId from DUAL
</selectKey>
insert into user (user_id, user_name, modified, state)
values (#{userId,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{modified,jdbcType=TIMESTAMP}, #{state,jdbcType=INTEGER})
</insert>
注:
mybatis默认不支持批量插入返回主键id,如需支持,需要满足以下条件:mybatis版本升到3.3.1,dao.java不加@Param注解,dao.xml参数写为list。
现Mysql举例如下:
service:
import java.util.List;
public interface TableService {
/**
* @date: 2018-10-30 上午9:26:17
* @Description: 新增,新增后需要返回id
*/
MetaTable insert(MetaTable metaTable);
/**
* @date: 2018-10-30 上午9:26:17
* @Description: 批量新增,新增后需要返回id
* @param @param metaTables metaTable集合
*/
List<MetaTable> batchInsert(List<MetaTable> metaTables);
}
serviceImpl:
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
@Service("tableService")
public class TableServiceImpl implements TableService{
@Autowired
private MetaTableDao metaTableDao;
@Autowired
private MetaTableMapper metaTableMapper;
private static Logger logger = LoggerFactory.getLogger(TableServiceImpl.class);
@Override
public MetaTable insert(MetaTable metaTable) {
Assert.notNull(metaTable, "metaTable is required");
Assert.hasLength(metaTable.getTableName(), "metaTable tableName is required");
metaTable.setCreateTime(new Date());
metaTable.setUpdateTime(new Date());
metaTableMapper.insert(metaTable);
metaTable.setId(metaTable.getId());
return metaTable;
}
@Override
public List<MetaTable> batchInsert(List<MetaTable> metaTables) {
MetaTable metaTable = new MetaTable();
metaTable.setCreateTime(new Date());
metaTable.setUpdateTime(new Date());
metaTableMapper.batchInsert(metaTables);
for(MetaTable table:metaTables){
table.setId(table.getId());
}
return metaTables;
}
}
dao:
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface MetaTableMapper {
void insert(MetaTable metaTable);
void batchInsert(List<MetaTable> metaTables);
}
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">
<mapper namespace="com.table.admin.dao.MetaTableMapper">
<resultMap id="BaseResultMap" type="com.table.admin.dto.MetaTable">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="table_name" jdbcType="VARCHAR" property="tableName" />
<result column="database_id" jdbcType="BIGINT" property="databaseId" />
<result column="table_comment" jdbcType="VARCHAR" property="tableComment" />
<result column="status" jdbcType="TINYINT" property="status" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="updateTime" jdbcType="TIMESTAMP" property="updateTime" />
<result column="is_delete" jdbcType="TINYINT" property="isDelete" />
</resultMap>
<sql id="Base_Column_List">
id, `table_name`, database_id, table_comment, status, create_time, updateTime, is_delete
</sql>
<select id="selectByDatabaseId" resultMap="BaseResultMap">
SELECT
<include refid="Base_Column_List" />
FROM
meta_table
WHERE
database_id = #{databaseId,jdbcType=BIGINT}
AND
is_delete = 0
</select>
<insert id="insert" parameterType="com.table.admin.dto.MetaTable" useGeneratedKeys="true" keyProperty="id">
INSERT INTO
meta_table ( `table_name`, database_id, table_comment, status, create_time, updateTime, is_delete)
VALUES
(#{tableName,jdbcType=VARCHAR}, #{databaseId,jdbcType=BIGINT},
#{tableComment,jdbcType=VARCHAR}, #{status,jdbcType=TINYINT}, #{createTime,jdbcType=TIMESTAMP},
#{updateTime,jdbcType=TIMESTAMP}, #{isDelete,jdbcType=TINYINT})
</insert>
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO
meta_table (`table_name`, database_id, table_comment, status, create_time, updateTime, is_delete)
VALUES
<foreach collection="list" item="item" index="index" separator="," >
(#{item.tableName,jdbcType=VARCHAR}, #{item.databaseId,jdbcType=BIGINT},
#{item.tableComment,jdbcType=VARCHAR}, #{item.status,jdbcType=TINYINT}, #{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}, #{item.isDelete,jdbcType=TINYINT})
</foreach>
</insert>
</mapper>
单元测试:
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
public class TableServiceTest extends TestBase{
@Autowired
private TableService tableService;
@Test
public void insertTest() {
MetaTable metaTable = new MetaTable();
metaTable.setTableName("test tableName 4");
MetaTable metaTable1 = tableService.insert(metaTable);
System.out.println("单个插入"+metaTable1.getId());
}
@Test
public void batchInsertTest () {
List<MetaTable> metaTables = new ArrayList<MetaTable>();
MetaTable metaTable= new MetaTable();
metaTable.setDatabaseId(1L);
metaTable.setUpdateTime(new Date());
metaTable.setCreateTime(new Date());
metaTable.setTableComment("测试");
metaTable.setTableName("111");
metaTable.setIsDelete((short)0);
metaTable.setStatus((short)1);
metaTables.add(metaTable);
List<MetaTable> batchInsert = tableService.batchInsert(metaTables);
for(MetaTable metaTable1:batchInsert){
System.out.println("结果:" + metaTable1.getId());
}
}
}