mybatis动态标签
动态:对于要增删改查的数据库对应字段数量不定
if标签
在多条件联合查询中,需要根据查询内容判断相应sql语句内容,此时需要if标签
if标签用来做条件判断,test内是具体判断的内容,用and连接
mapper配置:
<select id="findUserByCondition" parameterType="user" resultMap="resMap">
select * from user where 1=1
<if test="name!=null and name!=''">
and name=#{name}
</if>
<if test="address !=null and address !=''">
and address=#{address}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</select>
注意- 这里用到where 1=1
来防止语法错误
Java调用:
@Test
public void t67() throws Exception{
User user=new User();
user.setSex("男");
user.setAddress("中国");
List<User> userByCondition = userDao.findUserByCondition(user);
userByCondition.forEach(System.out::println);
}
通过set方法来对应需要查找的条件
where标签
where标签可以对if标签进行简化,并动态地将if标签中的第一个and语句删除
mapper配置:
<select id="findUserByCondition" parameterType="user" resultMap="resMap">
select * from user
<where>
<if test="name!=null and name!=''">
and name=#{name}
</if>
<if test="address !=null and address !=''">
and address=#{address}
</if>
<if test="sex!=null and sex!=''">
and sex=#{sex}
</if>
</where>
</select>
set标签
mybatis自动将set加入sql语句中且自动删除最后一个逗号
mapper配置:
<update id="updateUserByCondition" parameterType="user">
update user
<set>
<if test="name!=null and name!=''">
name=#{name},
</if>
<if test="address !=null and address !=''">
address=#{address},
</if>
<if test="sex!=null and sex!=''">
sex=#{sex},
</if>
</set>
where id=#{id}
</update>
动态新增(最复杂
动态新增有多个括号对应,需要自己编写新增方法。用到了标签以及标签
sql片段
<sql id="key">
<trim suffixOverrides=",">
<if test="name!=null and name!=''">
username,
</if>
<if test="address !=null and address !=''">
address,
</if>
<if test="sex!=null and sex!=''">
sex,
</if>
</trim>
</sql>
<sql id="val">
<trim suffixOverrides=",">
<if test="name!=null and name!=''">
#{name},
</if>
<if test="address !=null and address !=''">
#{address},
</if>
<if test="sex!=null and sex!=''">
#{sex},
</if>
</trim>
</sql>
<insert id="addUserByCondition" parameterType="user">
insert into user(<include refid="key"/>) values(<include refid="val"/>)
</insert>
其中trim标签的suffixOverrides用于删除最后一个指定的内容。在本例中用trim包裹的if标签达到了删除最后一个逗号的功能
标签用于引入sql片段,使用id值引入
Choose when otherwise标签
对于list以及array来说,将其交给mybatis时,mybatis会将"array","list"作为key,其内容作为value存到map中,此时靠参数名是取不到的,只能通过相应key取值
也可以自定义参数值,在声明时加上@Param(“name”)即可通过name获取相应数组或list
类似switch用法
for-each标签
<delete id="deleteByList" parameterType="int">
delete from user where id in
(
<foreach collection="myList" item="id" separator=",">
#{id}
</foreach>
)
</delete>
<delete id="deleteByList" parameterType="int">
delete from user where id in
(
<foreach collection="list" item="id" separator=",">
#{id}
</foreach>
)
</delete>
java:
public void deleteByList(@Param("aList")List<Integer> aList);
}
public void deleteByList(List<Integer> aList);
}