FOREACH traversal collection

FOREACH tags:

Collection attributes: Specify the collection of traversing; the parameters of the list type will be specially processed in the map, and the key of the map is called List

Item Properties: Assign the elements that are currently traversed to the specified variable, and then use # {variable name}: you can take out the value of the variable

Separator Properties: The separators between each element

OPEN attribute: traversing all the results to stitch a beginning of the character

Close attribute: traversing all the results to stitch a ending character

Index Properties: Index.

When

traversed through the list, INDEX indicates indexes, and the ITEM attribute represents the current value;

When the

traversed MAP, INDEX indicates the key of the map, and here, the Item property represents the value of the map.

<select id="getEmpsByConditionForeach" resultType="com.mybatis.bean.Employee">
    select * from tbl_employee where id in
    <foreach collection="ids" item="item_id" separator=","
        open="(" close=")"  index="i">
        #{item_id}
    </foreach>
</select>

Test

@Test
public void test(){
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper =                                                 
                                  openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1, "Admin", null, null);

            List<Empolyee> list =                                             
                               mapper.getEmpsByConditionForeach(Arrays,asList(1,2,3,4));
            for (Employee emp : list){
                System.out.println(emp);
            }
         }finally{    
                openSession.close();
            }
}

result

FOREACH batch preservation

Demonstration of batch insertion in the database

interface

public interface EmployeeMapperDynamicSQL{
    public void  addEmps(@Param("emps")list<Employee> emps);
}

mapping file

<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>

Test

@Test
public void testBatchSave() throws IOException{
    SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper =                                                 
                                  openSession.getMapper(EmployeeMapperDynamicSQL.class);

            List<Empolyee> emps = new ArrayList<>(); 
            emps.add(new Employee(null,"smith","[email protected]","1",new Department(1)));
            emps.add(new Employee(null,"allen","[email protected]","1",new Department(0)));
            mapper.addEmps(emps);
            openSession.commit();
         }finally{    
                openSession.close();
            }
}

result

mysql in batches:

1. Put the values () template to be inserted traversed in the foreach tag. MySQL supports Values (), (), () grammar () grammar () grammar () grammar () grammar ()It is recommended to use this method）

2. Put the entire SQL statement saved data into the foreach label, but the database connection attribute is required allowMultiQueries=true support

Second method demonstration

Map file

<insert id="addEmps">
    <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>

But to add a connection attribute

result data is preserved normally

Oracle database batch preservation:

First of all, Oracle does not support Values (), (), ();

1. Oracle supports multiple INSERTs in Begin -END

begin
    insert into employee(employee_id,last_name,email)
    values(employee_seq.nextval,'test_001','[email protected]')
     insert into employee(employee_id,last_name,email)
    values(employee_seq.nextval,'test_002','[email protected]')
end;

2, Oracle uses the intermediate watch for batch insertion

insert into employees(employee_id,last_name,email)
    select employees_swq.nextval,lastName,email 
    from(
        select 'test_a_01' lastName,'test_a_e01' email from dual
        union
        select 'test_a_02' lastName,'test_a_e02' email from dual
        union
        select 'test_a_03' lastName,'test_a_e03' email from dual    
    )