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");  //优先级3
	b.setQuant("y");        //优先级1
	b.setVin("12346");      //优先级2
	List<Boat> boats = dao.findBoatWithLike(b);   //注释掉b的不同属性，查询的SQL发生变化
	VehicleDao.printBoat(boats);
}

//output
Boat: [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));
}

//output
true

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);
}

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