Mybatis语法（三）Mybatis insert操作 返回插入的那条数据的id

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());
		}

	}

	
}