Mybatis insert操作返回插入的那条数据的id

旧城等待， 2022-05-14 23:50 203阅读 0赞

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举例如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70][]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70]: /images/20220515/27e374934d21441f83f8b707c3cf3840.png