MyBatis动态SQL foreach标签实现批量插入

左手的ㄟ右手 2022-06-15 01:50 329阅读 0赞

需求:查出给定id的记录:

<select id="getEmpsByConditionForeach" resultType="com.test.beans.Employee">
            SELECT * FROM tb1_emplyee WHERE id IN
            <foreach collection="list" item="item_id" separator="," open="(" close=")">
                #{item_id}
            </foreach>
    </select>

关于foreach标签,有几个属性应该注意一下:

collection：指定要遍历的集合：
            list类型的参数会特殊处理封装在map中，map的key就叫list
            item：将当前遍历出的元素赋值给指定的变量
            separator:每个元素之间的分隔符
            open：遍历出所有结果拼接一个开始的字符
            close:遍历出所有结果拼接一个结束的字符
            index:索引。遍历list的时候是index就是索引，item就是当前值
            遍历map的时候index表示的就是map的key，item就是map的值
            #{变量名}就能取出变量的值也就是当前遍历出的元素

测试方法:

@Test
    	public void testDynamicSqlTest() throws IOException{
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		//1、获取到的SqlSession不会自动提交数据
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try
    		{
    			    EmployeeMapperDymanicSQL mapper=openSession.getMapper(EmployeeMapperDymanicSQL.class);
    				/*Employee employee=new Employee(1,"lili",null,"1");*/
    				List<Employee> emps=mapper.getEmpsByConditionForeach(Arrays.asList(1,2,3,4));
    			    for (Employee e:emps){
    					System.out.println(e);
    				}
    
    		}
    		finally {
    			openSession.close();
    		}
    	}

foreach标签也可以实现实现批量插入(删除)数据:

这里以批量插入数据为例:

<insert id="addEmps">
            INSERT INTO tb1_emplyee(last_name,email,gender,d_id)
            VALUES 
            <foreach collection="emps" item="emp" separator=",">
                (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
            </foreach>
    </insert>

对应的接口:

public void addEmps(@Param("emps")List<Employee> emps);

测试方法

@Test
    	public void testBatchSave() throws IOException{
    		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    		//1、获取到的SqlSession不会自动提交数据
    		SqlSession openSession = sqlSessionFactory.openSession();
    		try
    		{
    			EmployeeMapperDymanicSQL mapper=openSession.getMapper(EmployeeMapperDymanicSQL.class);
    			List<Employee> emps=new ArrayList<Employee>();
    			emps.add(new Employee(null,"Eminem","Eminem@126.com","1",new Department(1)));
    			emps.add(new Employee(null,"2Pac","2Pac@126.com","1",new Department(1)));
    			mapper.addEmps(emps);
    			openSession.commit();
    		}
    		finally {
    			openSession.close();
    		}
    
    	}