目录
if
格式
<if test="条件">
sql 语句
</if>
当条件成立的时候,会执行sql语句
if (条件){
sql 语句
}
案例
<select id="queryUserByCondition" resultType="com.cccp.pojo.User" parameterType="com.cccp.pojo.User">
select id,username,password,age,phone from user where 1=1
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</select>
java代码
@Test
public void test02(){
User user = new User();
user.setPhone("120");
user.setAge(11);
List<User> users = userMapper.queryUserByCondition(user);
System.out.println(users);
}
choose...when...otherwise...
格式
<choose>
<when test="条件1">
sql语句1
</when>
<when test="条件2">
sql语句2
</when>
<otherwise>
sql语句3
</otherwise>
</choose>和我们java的if...else if ...else格式一样
当条件1成立,那么就不会执行后面的代码
案例
<select id="queryUserByCondition" resultType="com.cccp.pojo.User" parameterType="com.cccp.pojo.User">
select id,username,password,age,phone from user where 1=1
<choose>
<when test="age!=null and age!=''">
and age=#{age}
</when>
<when test="phone!=null and phone!=''">
and phone=#{phone}
</when>
<otherwise>
and password='112233'
</otherwise>
</choose>
</select>
where
# 说明
- 标签里边的if 至少有一个成立,就会动态添加一个where,如果都不成立,不添加where
- 第一个if条件成立的,会自动去除连接符and 或者 or
案例
<select id="queryUserByCondition" resultType="com.cccp.pojo.User" parameterType="com.cccp.pojo.User">
select id,username,password,age,phone from user
<where>
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</where>
</select>
set
说明
说明: 动态添加了set字段,也会动态的去掉最后一个逗号
案例
<update id="updateUser" parameterType="com.cccp.pojo.User">
update user
<set>
<if test="username!=null and username!=''">username=#{username},</if>
<if test="password!=null and password!=''">password=#{password},</if>
</set>
where id=#{id}
</update>
代码
@Test
public void test03(){
User user = new User();
user.setId(1);
user.setUsername("最高苏维埃213");
user.setPassword("8058900");
String message = userMapper.updateUser(user)>0?"成功":"失败";
System.out.println(message);
}
foreach
说明: 适用于 id in (x,x,x)
格式
循环遍历标签。适用于多个参数或者的关系。
<foreach collection=“”open=“”close=“”item=“”separator=“”>
获取参数
</foreach>
案例
<select id="queryUsersByIds" resultType="com.cccp.pojo.User"
parameterType="java.lang.Integer">
select id,username,password,age,phone from user
<where>
<foreach collection="list" item="id" open="id in (" close=")" separator=",">
#{id}
</foreach>
</where>
</select>
代码
@Test
public void test04(){
ArrayList<Integer> ids = new ArrayList<Integer>();
Collections.addAll(ids, 1, 2, 3, 4);
List<User> users = userMapper.queryUsersByIds(ids);
System.out.println(users);
}
trim
格式 pre- presay
格式 <trim prefix=前缀'' prefixoverrides=''
suffix=后缀'' suffixoverrides=''>
替换where
<select id="queryUserByCondition" resultType="com.cccp.pojo.User" parameterType="com.cccp.pojo.User">
select id,username,password,age,phone from user
<trim prefix="where" prefixOverrides="and">
<if test="age!=null and age!=''">
and age=#{age}
</if>
<if test="phone!=null and phone!=''">
and phone=#{phone}
</if>
</trim>
</select>
替换set
<update id="updateUser" parameterType="com.cccp.pojo.User">
update user
<trim prefix="set" suffixOverrides=",">
<if test="password!=null and password!=''">
password=#{password},
</if>
<if test="age!=null and age!=''">
age=#{age},
</if>
</trim>
where id=#{id}
</update>
Sql片段
格式
<sql id="别名">
查询的所有字段
</sql>使用的时候 <include refid="别名"/>