MyBatis插入和批量插入

本文介绍了使用MyBatis进行批量插入操作的具体方法,包括不同参数类型的插入方式、动态SQL的应用及返回主键的实现。并通过示例展示了如何在实际项目中应用这些技术。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

注意事项:

批量插入的时候,需要注意动态SQL的写法:即拼接出来的SQL语句和常规的SQL语句语法一致。

批量插入的时候,需要注意,在后台要写成user.name或者map.name,否则会出错

插入返回主键需要通过entity.getId()来进行返回。

概述

使用sqlsession来进行删除的时候,只有两个重载的方法 insert(String statement); int insert(String statement, Object parameter);,两者均返回受影响的条数,底层使用的是update方法(这个在后面会有说明)

本实例包含以下插入:

  1. 没有参数
  2. 根据实体类插入(一个参数,实体类)
  3. 根据Map插入(一个参数,是Map类型)
  4. 批量插入(一个参数,参数是数组List。使用了动态SQL)
  5. 批量插入(一个参数,参数是List<map)

动态SQL说明:


<!-- 批量插入 ,传递过来的参数是list类型 -->
 <foreach collection="list" item="user" separator=",">(
     #{user.name} ,#{user.age})
   </foreach>

最后拼接的sql语句是:


insert into user(name,age) values     (    ?    ,?)    , (    ?    ,?)    , (    ?    ,?)

mybatis-config.xml: 在src/main/resources/chapter03/mybatis-config.xml



<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <properties resource="jdbc.properties">
    </properties>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/chapter03/UserMapper.xml" />
    </mappers>

</configuration>

注意,在批量插入的时候,有一点不好的地方在于,如果传过来的是List<map> 类型,在进行插入的时候,两边的字段类型不对等,不好搞。因为trim的语法不好在上面用。

映射文件UserMapper.xml: src/main/resources/mapper/chapter03/UserMapper.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="cn.liuyiyou.mybatis.mapper.chapter03.UserMapper">

  <!-- 没有参数的插入 -->
  <insert id="insertUser">
    insert into user(name) values ('test')
  </insert>

  <!-- 参数是实体 -->
  <insert id="insertUserEntity" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User">
    insert into
    user(name,age) values (#{name},#{age})
  </insert>

  <!-- 插入返回主键1: -->
  <insert id="insertReturnId" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User">
    insert into
    user(name,age) values (#{name},#{age})
    <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
      SELECT
      LAST_INSERT_ID()
    </selectKey>
  </insert>
  
  <!-- 插入返回主键2: -->
  <insert id="insertReturnId2" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User" useGeneratedKeys="true">
    insert into
    user(name,age) values (#{name},#{age})
  </insert>


  <!-- 参数是Map -->
  <insert id="insertUserMap" parameterType="java.util.Map">
    insert into
    user(name,age) values (#{name},#{age})
  </insert>

  <!-- 批量插入,传递过来的参数是List<User> -->
  <insert id="batchInsertList" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User">
    insert into user(name,age) values
    <foreach collection="list" item="user" separator=",">(
      #{user.name} ,#{user.age})
    </foreach>

  </insert>

  <!-- 批量插入,传递过来的参数是List<Map<String,Object> -->
  <insert id="batchInsertMap">
    insert into user(name,age) values
    <foreach collection="map" item="map" separator=",">
      (
      #{map.name}<!-- 不能直接是name,因为myBatis无法识别 -->
      ,#{map.age}   )
    </foreach>

  </insert>

 </mapper>

测试类:UserMapperTest


package cn.liuyiyou.mybatis.chapter03;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;

import cn.liuyiyou.mybatis.domain.chapter02.User;

public class UserMapperTest {

  private static SqlSessionFactory sqlSessionFactory;

  @Before
  public void setUp() throws Exception {
    String resource = "chapter03/mybatis-config.xml";
    InputStream inputStream = null;
    try {
      inputStream = Resources.getResourceAsStream(resource);
    } catch (IOException e) {
      e.printStackTrace();
    }
    sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

  }

  /**
    * 没有参数
   */
  @Test
  public void insertUserTest() {
    SqlSession session = sqlSessionFactory.openSession();
    try {
      int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.insertUser");
      Assert.assertEquals(1, result);
      session.commit();
    } finally {
      session.close();
    }
  }
  
  /**
    * 参数是实体类型:
   */
  @Test
  public void insertUserEntityTest() {
    SqlSession session = sqlSessionFactory.openSession();
    try {
      User user =new User();
      user.setName("insert1");
      user.setAge(1);
      int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.insertUserEntity",user);
      Assert.assertEquals(1, result);
      session.commit();
    } finally {
      session.close();
    }
  }
  
  /**
   * 参数是实体类型:
   */
  @Test
  public void insertUserEntityAndReturnIdTest() {
    SqlSession session = sqlSessionFactory.openSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    try {
      User user =new User();
      user.setName("insert1");
      user.setAge(1);
      int result = userMapper.insertReturnId(user);
      System.out.println("result::"+result);
      System.out.println("id::"+ user.getId());
      Assert.assertEquals(1, result);
      session.commit();
    } finally {
      session.close();
    }
  }
  
  /**
   * 参数是实体类型:
   */
  @Test
  public void insertUserEntityAndReturnIdTest2() {
    SqlSession session = sqlSessionFactory.openSession();
    UserMapper userMapper = session.getMapper(UserMapper.class);
    try {
      User user =new User();
      user.setName("insert1");
      user.setAge(1);
      int result = userMapper.insertReturnId(user);
      System.out.println("result::"+result);
      System.out.println("id::"+ user.getId());
      Assert.assertEquals(1, result);
      session.commit();
    } finally {
      session.close();
    }
  }
  
  /**
    * 参数是Map类型:
   */
  @Test
  public void insertUserMapTest() {
    SqlSession session = sqlSessionFactory.openSession();
    try {
      Map<String,Object> user =new HashMap<String,Object>();
      user.put("name","insert2");
      user.put("age",2);
      int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.insertUserMap",user);
      Assert.assertEquals(1, result);
      session.commit();
    } finally {
      session.close();
    }
  }
  
  
  /**
    * 批量插入,参数是List<User>数组
   */
  @Test
  public void batchInsertListTest() {
    SqlSession session = sqlSessionFactory.openSession();
    try {
      
      List<User> users =new ArrayList<User>();
      for(int i=3; i<=5;i++){
        User user =new User();
        user.setName("insert"+i);
        user.setAge(i);
        users.add(user);
      }
      int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.batchInsertList",users);
      Assert.assertEquals(3, result);
      session.commit();
    } finally {
      session.close();
    }
  }
  
   /**
    * 批量插入,参数是Map
   */
  @Test
  public void batchInsertMapTest() {
    SqlSession session = sqlSessionFactory.openSession();
    try {
      
      List<Map<String,Object>> users =new ArrayList<Map<String,Object>>();
      for(int i=6; i<=8;i++){
        Map<String,Object> user =new HashMap<String,Object>();
        user.put("name","insert"+i);
        user.put("age",i);
        users.add(user);
      }
      int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.batchInsertMap",users);
      Assert.assertEquals(3, result);
      session.commit();
    } finally {
      session.close();
    }
  }
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值