需求:查出给定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标签,有几个属性应该注意一下:
测试方法
@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();
}
}