1.动态sql语句
//需求:动态按照用户名和姓名查询用户列表
(1)用户名等值匹配
//映射文件
<select id="getUserByNameOrAddr" parameterType="user" resultType="user">
select * from user
<where>
<if test="uu.username!=null">
username =#{username}
</if>
<if test="age!=0">
and age=#{uu.age}
</if>
</where>
</select>
//接口
public List<User> getUserByNameOrAddr(User user);
//测试
IUserMapper mapper=session.getMapper(IUserMapper.class);
User user=new User();
user.setAge(12);
user.setUsername("bbb");
List<User> list=mapper.getUserByNameOrAddr(user);
for(User u:list){
System.out.println(u);
}
(2)用户名模糊匹配
//映射文件
<select id="getUserByNameOrAddr" parameterType="java.util.HashMap" resultType="user">
select * from user
<where>
<if test="vv!=null">
username like '%${vv}%'
</if>
<if test="age!=0">
and age=#{uu.age}
</if>
</where>
</select>
//接口
public List<User> getUserByNameOrAddr(Map map);
//测试
User user=new User();
user.setAge(12);
Map map=new HashMap();
map.put("vv", "b");
map.put("uu", user);
List<User> list=mapper.getUserByNameOrAddr(map);
for(User u:list){
System.out.println(u);
}
<trim>格式化:前缀prefix 前缀覆盖prefixOverrides 后缀suffix 后缀覆盖suffixOverrides
//上例中查询可以等效于
<trim prefix="where" prefixOverrides="and|or">
<if test="vv!=null">
username like '%${vv}%'
</if>
<if test="age!=0">
and age=#{uu.age}
</if>
</trim>
适用场合:插入和修改中
<insert id="addUser" parameterType="user">
insert into user <!-- (username,age) values(?,?) -->
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="username!=null"> username,</if>
<if test="age!=0">age,</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="username!=null"> #{username},</if>
<if test="age!=0"> #{age},</if>
</trim>
</insert>
<set>修改
<update id="upUser" parameterType="user">
update user
<set>
<if test="username!=null">username=#{username} ,</if>
<if test="password!=null">password=#{password} ,</if>
<if test="age!=0">age=#{age} </if>
</set>
<where>
<if test="uid!=0">uid =#{uid}</if>
</where>
</update>
//等效于
<update id="upUser" parameterType="user">
update user
<trim prefix="set" suffixOverrides=",">
<if test="username!=null">username=#{username},</if>
<if test="password!=null">password=#{password}, </if>
<if test="age!=0">age=#{age}, </if>
</trim>
<where>
<if test="uid!=0">uid =#{uid}</if>
</where>
</update>
foreach 循环 collection:要循环的集合
item:循环项
index:循环项的下标字符串形式,或者map集合的键
open:开头
separator:分割
close:结尾
<delete id="delbyids" parameterType="userCustomer">
delete from user
<if test="ids!=null">
<where>
uid
<foreach collection="ids" item="userid" open="in(" separator="," close=")">
#{userid}
</foreach>
</where>
</if>
</delete>
<choose>选择,相当于switch,选择其中一条,只要有一项成立,choose结束
2.sql片段
重复性的sql代码,存放在sql标签中,使用时include导入即可
<sql id="sqlselet">
username,password,age
</sql>
<select id="getUserByNameOrAddr" parameterType="java.util.HashMap" resultType="user">
select
<include refid="sqlselet"></include>
from user
</select>