EmployeeMapperDynamicSql.java
package com.gong.mybatis.mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.MapKey; import org.apache.ibatis.annotations.Param; import com.gong.mybatis.bean.Employee; public interface EmployeeMapperDynamicSql { public List<Employee> getEmpByConditionIf(Employee employee); public void updateEmp(Employee employee); public List<Employee> getEmpByForeach(@Param("ids") List<Integer> ids); public List<Employee> getEmpByForeach2(List<Employee> employee); }
EmployeeMapperDynamicSql.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.gong.mybatis.mapper.EmployeeMapperDynamicSql"> <!-- 查询,要查那个就带上那个条件 --> <select id="getEmpByConditionIf" resultType="com.gong.mybatis.bean.Employee"> select * from tbl_employee <where> <choose> <when test="id!=null"> id=#{id} </when> <when test="lastName!=null"> last_name like #{lastName} </when> <when test="email!=null"> email=#{email} </when> <otherwise> </otherwise> </choose> </where> </select> <update id="updateEmp"> update tbl_employee <trim prefix="set" suffixOverrides=","> <if test="lastName!=null"> last_name=#{lastName}, </if> <if test="gender!=null and gender == 0 or gender == 1"> gender=#{gender}, </if> <if test="email!=null"> email=#{email} </if> </trim> where id=#{id} </update> <select id="getEmpByForeach" resultType="com.gong.mybatis.bean.Employee"> select * from tbl_employee where id in <foreach collection="ids" item="item_id" separator="," open="(" close=")"> #{item_id} </foreach> </select> <select id="getEmpByForeach2" resultType="com.gong.mybatis.bean.Employee"> select * from tbl_employee where id in <foreach collection="list" item="item" separator="," open="(" close=")"> #{item.id} </foreach> </select> </mapper>
说明,现在我们使用public List<Employee> getEmpByForeach2(List<Employee> employee);,传入的是一个Employee集合,那么此时我们应该怎么使用foreach呢?
我们可以在collection中对应着list类型,即collection="list"。
测试:
package com.gong.mybatis.test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.Map; 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.Test; import com.gong.mybatis.bean.Employee; import com.gong.mybatis.mapper.EmployeeMapperDynamicSql; public class TestMybatis3 { public SqlSessionFactory getSqlSessionFactory() throws IOException { String resource = "mybatis-config.xml"; InputStream is = Resources.getResourceAsStream(resource); return new SqlSessionFactoryBuilder().build(is); } @Test public void test() throws IOException { SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); SqlSession openSession = sqlSessionFactory.openSession(); try { EmployeeMapperDynamicSql mapper = openSession.getMapper(EmployeeMapperDynamicSql.class); List<Employee> emps = new ArrayList<Employee>(); Employee e1 = new Employee(); e1.setId(1); Employee e2 = new Employee(); e2.setId(2); emps.add(e1); emps.add(e2); List<Employee> es = mapper.getEmpByForeach2(emps); for(Employee e:es) { System.out.println(e); } openSession.commit(); } finally { openSession.close(); } } }
结果:
DEBUG 01-21 16:21:28,692 ==> Preparing: select * from tbl_employee where id in ( ? , ? ) (BaseJdbcLogger.java:145) DEBUG 01-21 16:21:28,783 ==> Parameters: 1(Integer), 2(Integer) (BaseJdbcLogger.java:145) DEBUG 01-21 16:21:28,813 <== Total: 2 (BaseJdbcLogger.java:145) Employee [id=1, lastName=dema, gender=1, email=dema@qq.com, dept=null] Employee [id=2, lastName=jack, gender=1, email=675544321@qq.com, dept=null]
成功的通过id将对象取出来。
当然,我们也可以取别名:
public List<Employee> getEmpByForeach2(@Param("emps") List<Employee> employee);
然后在使collection="emps",其余不变,还是可以达到一样的效果。