mybatis_SQL映射(6)_动态SQL

通常有这样一种场景，界面的条件查询客户是可选的。根据用户选择的条件动态的拼接SQL，mybatis的动态SQL为你解决这个问题。

1.表数据

2. if

a)  XML配置：如果传入的quant值不为空，则选择该条件。

<select id="findBoatWithQuantLike" parameterType="Boat" resultMap="boatResult">    SELECT * FROM vehicle WHERE vehicle_type = '2'    <if test="quant != null">       AND quant like #{quant}    </if></select>

b) 测试代码

public List<Boat> findBoatWithQuantLike(Boat b) {SqlSession session = DbFactory.getInstance().openSession();List<Boat> boats = (List<Boat>) session.selectList("com.yjq.entity.Vehicle.findBoatWithQuantLike", b);session.commit();session.close();return boats;}public static void printBoat(List<Boat> lists) {for(Boat b : lists) {System.out.println("Boat: [id=" + b.getId() + ", vehicleType="+ b.getVehicleType() + ", quant=" + b.getQuant()+ ", color="+ b.getColor()+ ", vin=" + b.getVin() + "]");}} public static void main(String[] args) {VehicleDao dao = new VehicleDao();Boat b = new Boat();b.setQuant("y");List<Boat> boats = dao.findBoatWithQuantLike(b);VehicleDao.printBoat(boats);}

//output(带quant条件)Boat: [id=3, vehicleType=2, quant=y, color=#321aaa, vin=13245]

//  b.setQuant("y");   注释掉条件

//output(不带quant条件)Boat: [id=2, vehicleType=2, quant=lxj, color=#123ccc, vin=12346]Boat: [id=3, vehicleType=2, quant=y, color=#321aaa, vin=13245]

2. choose, when, otherwise

a) XML配置：条件选择，相当于switch；写在前面的条件优先级高。

<select id="findBoatWithLike" parameterType="Boat" resultMap="boatResult">SELECT * FROM vehicle WHERE vehicle_type = '2'<choose><when test="quant != null">AND quant like #{quant}</when><when test="vin != null">AND vin like #{vin}</when><otherwise>AND color = #{color}</otherwise></choose></select>

b)测试代码

public List<Boat> findBoatWithLike(Boat b) {SqlSession session = DbFactory.getInstance().openSession();List<Boat> boats = (List<Boat>) session.selectList("com.yjq.entity.Vehicle.findBoatWithLike", b);session.commit();session.close();return boats;}public static void main(String[] args) {VehicleDao dao = new VehicleDao();Boat b = new Boat();b.setColor("#321aaa");  //优先级3b.setQuant("y");        //优先级1b.setVin("12346");      //优先级2List<Boat> boats = dao.findBoatWithLike(b);   //注释掉b的不同属性，查询的SQL发生变化VehicleDao.printBoat(boats);}

//outputBoat: [id=3, vehicleType=2, quant=y, color=#321aaa, vin=13245]

3. trim，where，set

a) 为了拼接SQL的正确，引入where,trim

b) 3种正确的写法

<select id="findBoatWithQuantLike" parameterType="Boat" resultMap="boatResult">SELECT * FROM vehicle WHERE 1=1<if test="quant != null">AND quant like #{quant}</if></select>

<select id="findBoatWithQuantLike" parameterType="Boat" resultMap="boatResult">SELECT * FROM vehicle<where><if test="quant != null">AND quant like #{quant}</if></where></select>

<select id="findBoatWithQuantLike" parameterType="Boat" resultMap="boatResult">SELECT * FROM vehicle<trim prefix="WHERE" prefixOverrides="AND |OR "><if test="quant != null">AND quant like #{quant}</if></trim></select>

c) set用于update语句

i. XML配置

<update id="updateVehicleById" parameterType="Vehicle">update vehicle<set><if test="vin != null">vin=#{vin},</if><if test="color != null">color=#{color},</if><if test="vendor != null">vendor=#{vendor}</if></set>where id=#{id}</update>

ii. 测试代码

public boolean updateVehicleById(Vehicle v) {SqlSession session = DbFactory.getInstance().openSession();int result = session.update("com.yjq.entity.Vehicle.updateVehicleById", v);session.commit();session.close();return (result==0 ? false : true);}public static void main(String[] args) {VehicleDao dao = new VehicleDao();Vehicle v = new Vehicle();v.setId(1);v.setVendor("haha");v.setColor("#aaabbb");v.setVin("54321");System.out.println(dao.updateVehicleById(v));}

//outputtrue

iii. 另一种XML配置

<update id="updateVehicleById" parameterType="Vehicle">update vehicle<trim prefix="SET" suffixOverrides=","><if test="vin != null">vin=#{vin},</if><if test="color != null">color=#{color},</if><if test="vendor != null">vendor=#{vendor}</if></trim>where id=#{id}</update>

4. 迭代：foreach元素是非常强大的，它允许你指定一个集合，声明集合项和索引变量，它们可以用在元素体内。它也允许你指定开放和关闭的字符串，在迭代之间放置分隔符。

a) 注意：你可以传递一个List实例或者数组作为参数对象传给MyBatis。当你这么做的时候，MyBatis会自动将它包装在一个Map中，用名称在作为键。List实例将会以“list”作为键，而数组实例将会以“array”作为键。

b) XML配置

<select id="findVehicleByList" resultMap="vehicleResult">      select * from vehicle where id in      <foreach collection="list" index="index" item="item" open="(" separator="," close=")">          #{item}      </foreach>  </select>  

c) 测试代码

public List<Vehicle> findVehicleByList(List<Integer> list) {SqlSession session = DbFactory.getInstance().openSession();List<Vehicle> results = (List<Vehicle>) session.selectList("com.yjq.entity.Vehicle.findVehicleByList", list);session.commit();session.close();return results;}public static void printVehicle(List<Vehicle> lists) {for(Vehicle v : lists) {System.out.println("Vehicle: [id=" + v.getId() + ", vehicleType="+ v.getVehicleType() + ", color="+ v.getColor()+ ", vin=" + v.getVin() + "]");}} public static void main(String[] args) {VehicleDao dao = new VehicleDao();List<Integer> list = new ArrayList<Integer>(Arrays.asList(1,2));List<Vehicle> results = dao.findVehicleByList(list);VehicleDao.printVehicle(results);}

//outputVehicle: [id=1, vehicleType=1, color=#aaabbb, vin=54321]Vehicle: [id=2, vehicleType=2, color=#123ccc, vin=12346]