mybatis-210721-06---动态sql-foreach批量插入

mybatis-210721-06—动态sql-foreach批量插入

• EmployeeMapper.java
• EmployeeMapper.xml(笔记在这儿哦)
• MybatisTest.java
• dbconfig.properties

---

	插入多条员工数据
	
	这个MySQL的的批量插入
	
	如果在Oracle中批量插入，以下方法就不管用了，请参考尚硅谷mybatis视频
		https://www.bilibili.com/video/BV1mW411M737?p=46
		https://www.bilibili.com/video/BV1mW411M737?p=47

EmployeeMapper.java

package com.bgy.mybatis.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.bgy.mybatis.bean.Employee;

public interface EmployeeMapper {
	
	public void addEmps(@Param("emps")List<Employee> emps);
	
	public void addEmps02(@Param("emps")List<Employee> emps);
}

EmployeeMapper.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.bgy.mybatis.dao.EmployeeMapper">
	
	<!--
		批量保存方式01
			mysql下批量保存，可以使用foreach遍历
	-->
	<insert id="addEmps">
		insert into
			tbl_employee(last_name,email,gender,d_id)
		values
			<foreach collection="emps" item="emp" separator=",">
				(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
			</foreach>
	</insert>
	
	<!--
		批量保存方式02
		mysql下批量保存，可以使用foreach遍历
		这种方式需要在连接数据库属性中（dbconfig.properties）中添加
			&allowMultiQueries=true
		jdbc.url=jdbc:mysql://localhost:3306/mybatis_test_210716_01?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT&allowMultiQueries=true
	-->
	<insert id="addEmps02">
		<foreach collection="emps" item="emp" separator=";">
			insert into
				tbl_employee(last_name,email,gender,d_id)
			values
				(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
		</foreach>
	</insert>
</mapper>

MybatisTest.java

package com.bgy.mybatis.test;

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

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.jupiter.api.Test;

import com.bgy.mybatis.bean.Department;
import com.bgy.mybatis.bean.Employee;
import com.bgy.mybatis.dao.EmployeeMapper;

class MybatisTest {
	
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}

	
	/**·
	 * 测试sql——foreach批量插入方式01
	 * @throws IOException
	 */
	@Test
	public void test() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
			
			List<Employee> emps = new ArrayList<Employee>();
			emps.add(new Employee(null,"huangzhong","hz@qq.com","1",new Department(1)));
			emps.add(new Employee(null,"diaochan","dc@qq.com","0",new Department(2)));
			
			mapper.addEmps(emps);
			
			sqlSession.commit();
		} finally {
			sqlSession.close();
		}
	}
	
	
	/**·
	 * 测试sql——foreach批量插入方式02
	 * @throws IOException
	 */
	@Test
	public void test02() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		try {
			EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
			
			List<Employee> emps = new ArrayList<Employee>();
			emps.add(new Employee(null,"huangzhong02","hz02@qq.com","1",new Department(1)));
			emps.add(new Employee(null,"diaochan02","dc02@qq.com","0",new Department(2)));
			
			mapper.addEmps02(emps);
			
			sqlSession.commit();
		} finally {
			sqlSession.close();
		}
	}
}

dbconfig.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_test_210716_01?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT&allowMultiQueries=true
jdbc.username=root
jdbc.password=admin