mybatis之批量插入

最新推荐文章于 2024-07-03 21:10:34 发布

逐墨飞扬

最新推荐文章于 2024-07-03 21:10:34 发布

阅读量1.4k

点赞数 2

分类专栏： mybatis 文章标签：批量插入动态SQL mybatis foreach

本文链接：https://blog.csdn.net/ZXY_9264_ANG/article/details/93844849

版权

mybatis 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

通过动态SQL方式实现

通过动态SQL方式，Executor的类型不需要特别设置，用默认的SIMPLE就可以。
具体步骤如下：

第一步：定义Mapper映射文件和接口类

映射文件中定义动态SQL语句

<insert id="insertBatch" parameterType="list" useGeneratedKeys="true" keyProperty="id">
        insert into student( sname, age, gender, nick_name ) values
        <foreach collection="list" item="stu" index="index" separator=",">
            (#{stu.sname}, #{stu.age}, #{stu.gender}, #{stu.nickName})
        </foreach>
    </insert>

Mapper接口

public interface StudentMapper {
    int insertBatch(List<Student> studentList);
}

第二步：调用

/**
 * 使用默认的Executor类型SIMPLE，通过在Mapper文件中的<forEach>标签，拼接动态SQL，完成批量操作
 */
@Test
public void testBatchInsertStudentByForEachSqlMapper() {
    SqlSession sqlSession = MybatisUtil.getSessionFactory().openSession(false);
    try {
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        System.out.println("影响行数： " + studentMapper.insertBatch(studentList));

        // 提交事务
        sqlSession.commit();

        // 从本批次插入数据中随机抽查10条数据的自增id
        Random random = new Random();
        for(int i = 0; i < 10; i++) {
            int idx = random.nextInt(studentList.size());
            log.info("第"+(idx+1)+"条数据自增id: " + studentList.get(idx).getId());
        }

    } catch (Exception e) {
        e.printStackTrace();
        sqlSession.rollback();
    } finally {
        sqlSession.close();
    }
}

示例

jdbc.properties数据源

jdbc.url=jdbc:mysql://localhost:3306/yanfa5
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
jdbc.characterEncoding=utf8

CityMapper.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">

<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.CityDao">
	<!-- 迭代List类型参数 -->
	<insert id="insertByMap" parameterType="hashmap">
        insert into city (cname,pid) values
        <!-- 迭代list类型参数时，collection的值写list， 这时index就是迭代次数，item是迭代的元素 -->
        <foreach collection="map" index="cname" item="pid" separator="," close=";">
            (#{cname},#{pid})
        </foreach>
    </insert>

	<!-- 迭代Map类型参数 -->
	<insert id="insertByList">
        insert into city (cname,pid) values 
        <!-- 迭代map类型参数时，collection写接口中通过@Param注解指定的map参数名称，这时index就是map的key，item就是map的value -->
        <foreach collection="list" item="city" separator="," close=";">
            (#{city.cname},#{city.pid})
        </foreach>
    </insert>
</mapper>

mybatis.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" />
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/CityMapper.xml" />
    </mappers>
</configuration>

对应数据库表的java类

@Getter
@Setter
public class City {
    private Integer id;
    private String cname;
    private Integer pid;

    @Override
    public String toString() {
        return "City{" +
                "id=" + id +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                "}\n";
    }
}

dao层接口（操作数据库）

public interface CityDao {
	//迭代List类型参数
	int insertByList(List<City> cityList);
	//迭代Map类型参数
    int insertByMap(@Param("map") Map<String,Integer> map);
}

程序运行入口

public class App {
    public static void main(String[] args) throws IOException {
        //1、读入配置文件
        String confPath = "mybatis.xml";
        InputStream is = Resources.getResourceAsStream(confPath);

        //2、构建SqlSessionFactory(用于获取sqlSession)
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is,"development");

        //3、获取sqlSession对象（用于具体的RUID)
        SqlSession sqlSession = sessionFactory.openSession(true);

        //4、具体的RUID
        CityDao cityDao = sqlSession.getMapper(CityDao.class);
        String[] arr = new String[]{"金","木","水","火","土"};
        Random random = new Random();

		//迭代List类型参数
        List<City> cityList = new ArrayList<>();
        for(int i = 0;i < 10 ;i++) {
            City city = new City();
            city.setCname(arr[random.nextInt(arr.length)] + "_" + (i + 1));
            cityList.add(city);
        }
        System.out.println("插入行数:" + cityDao.insertByList(cityList));

		//迭代Map类型参数
        Map<String,Integer> map = new HashMap<>();
        for(int i = 10;i < 20 ;i++) {
            map.put(arr[random.nextInt(arr.length)] + "_" + (i + 1),(i + 1));
        }
        System.out.println("插入行数:" + cityDao.insertByMap(map));
        
    }
}

通过将Executor类型设置为BATCH实现

此种方式需要将Executor类型设置为BATCH，而在映射文件中不需要拼接批量SQL，用普通单条插入的SQL即可。具体步骤如下：
第一步：定义映射文件和映射接口类
映射文件：

<insert id="insert" parameterType="Student" useGeneratedKeys="true" keyProperty="id">
        insert into student(sname, age, gender, nick_name) values (#{sname}, #{age}, #{gender}, #{nickName})
</insert>

映射接口类：

public interface StudentMapper {
    int insert(Student student);
}