（Mybatis）动态Sql

1、if 标签：需要做非空检验

<!-- 动态sql查询 -->
<select id="findUserByUsernameAndSex" parameterType="com.jadan.pojo.User" resultType="com.jadan.pojo.User">
	select * from user where 1 = 1 
		
	<if test="username != null and username != ''">
		and username like '%${username}%'
	</if>
		
	<if test="sex != null and sex != ''">
		and sex = #{sex}
	</if>
</select>

 

2、where 标签：

                   会自动向sql语句中添加where关键字

                   会去掉第一个条件的and关键字

<!-- 动态sql查询 -->
<select id="findUserByUsernameAndSex" parameterType="com.jadan.pojo.User" resultType="com.jadan.pojo.User">
	select * from user
	<!-- where标签作用:
	    1. 会自动向sql语句中添加where关键字
	    2. 会去掉第一个条件的and关键字
	-->
	<where>
		<if test="username != null and username != ''">
			and username like '%${username}%'
		</if>
			
		<if test="sex != null and sex != ''">
			and sex = #{sex}
		</if>
	</where>
</select>

 

3、foreach 标签：循环遍历传入的集合参数

<!-- 运用foreach标签 -->
<select id="findUserByIds" parameterType="com.jadan.pojo.QueryVo" resultType="com.jadan.pojo.User">
	select * from user 
	<where>
	    <if test="ids != null">
		<!-- foreach循环遍历传入的集合参数
			    collection: 传入集合的变量名称
			    item: 每次将循环出的数据放入这个变量中
				open: 循环开始拼接的字符串
			    close: 循环结束拼接的字符串
			    separator: 循环中拼接的字符串
	    -->
		<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
			#{id}
		</foreach>
	    </if>
	</where>
</select>

       QueryVo.java ：

package com.jadan.pojo;

import java.util.List;

public class QueryVo {

	private List<Integer> ids;

	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}
}

         User.java ：

package com.jadan.pojo;

import java.util.Date;

public class User {
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}

}

 

4、sql 标签（sql片段）：

<!-- 封装sql条件,封装后可以重用 
	   id: sql条件的唯一标识
-->
<sql id="user_Where">
    <!-- where标签作用:
	1. 会自动向sql语句中添加where关键字
	2. 会去掉第一个条件的and关键字
     -->
    <where>
	<if test="username != null and username != ''">
		and username like '%${username}%'
	</if>
		
	<if test="sex != null and sex != ''">
	 	and sex = #{sex}
	</if>
    </where>
</sql>

      通过 sql 的 id 调用：

<!-- 动态sql查询 -->
<select id="findUserByUsernameAndSex" parameterType="com.jadan.pojo.User" resultType="com.jadan.pojo.User">
	select * from user
		
	<!-- 调用sql条件 -->
	<include refid="user_Where"></include>
</select>

 

下一篇：单个对象的映射关系