1、if标签
<!--SQL语句:select * from user where 1=1 and username=? and sex=?-->
select * from user where 1=1
<iftest="username !=null">
and username = #{username}
</if><iftest="sex">
and sex=#{sex}
</if>
2、where标签
<!-- SQL语句:Preparing: select * from user WHERE username = ? and sex=? -->
select * from user
<where><iftest="username !=null">
and username = #{username}
</if><iftest="sex">
and sex=#{sex}
</if></where>
3、foreach标签
SQL语句:select 字段 from user where id in {?}
<foreach>标签用于遍历集合,他是属性:
collection:代表要遍历的集合元素,注意是不要写成#{}
open:代表语句的开始部分。
close:代表结束部分
item:代表遍历集合的每个元素,生成的变量名
speractor:代表分隔符。
示例代码:
<!--SQL语句:Preparing: select * from user WHERE id in ( ? , ? , ? , ? , ? ) -->
select * from user
<where><iftest="ids!=null and ids.size>0"><foreachcollection="ids"open="and id in ("close=")"item="id"separator=",">
#{id}
<!-- item="id" #{id}这两个参数必须一样 是同一个值--></foreach></if></where>
4、封装重复的SQL语句
定义:
<!-- 了解的内容,抽取重复的SQL语句 --><sqlid="defaultUser">select * from user</sql>
使用:
<!-- 根据id查询用户 --><selectid="findById"parameterType="INT"resultType="com.song.domain.User"><includerefid="defaultUser"></include> where id=#{uid}
</select>
1、实体类User、QueryVo
publicclassUserimplementsSerializable{private Integer id;private String username;private Date birthday;private String sex;private String address;}publicclassQueryVo{private User user;}
<!-- 根据条件查询 --><selectid="findUserByCondition"resultType="com.song.domain.User"parameterType="com.song.domain.User">
select * from user
<where><iftest="username !=null">and username = #{username}</if><iftest="sex">and sex=#{sex}</if></where><!-- Preparing: select * from user WHERE username = ? and sex=? --><!-- select * from user where 1=1
<if test="username !=null">
and username = #{username}
</if>
<if test="sex">
and sex=#{sex}
</if>
--></select><!-- 根据Queryvo中的集合 --><selectid="findUserInIds"resultType="com.song.domain.User"parameterType="com.song.domain.QueryVo">
select * from user
<where><iftest="ids!=null and ids.size>0"><foreachcollection="ids"open="and id in ("close=")"item="id"separator=",">
#{id}
<!-- item="id" #{id}这两个参数必须一样 是同一个值--></foreach></if></where></select>
4、编写测试类
/**
* 测试查询所有
*/@TestpublicvoidtestFindByCondition(){
User u =newUser();
u.setUsername("小马宝莉");
u.setSex("女");
List<User> users = userDao.findUserByCondition(u);for(User user:users){
System.out.println(user);}}/**
* 测试foreach标签的使用所有
*/@TestpublicvoidtestFindInIds(){
QueryVo vo =newQueryVo();
List<Integer> list =newArrayList<Integer>();
list.add(41);
list.add(43);
list.add(45);
list.add(46);
list.add(48);
vo.setIds(list);
List<User> users = userDao.findUserInIds(vo);for(User user:users){
System.out.println(user);}}