MyBatis笔记06 动态sql语句
coconutnut
 2020-03-30 10:47:35      

https://www.bilibili.com/video/av47952553
p45~47

if标签

根据传入参数条件查询

条件可能是用户名、性别、地址，或者都有、都没有

1
2
3
// IUserDao.java

List<User> findUserByCondition(User user);

与要用and，不能用&&

sql语句上的内容无关大小写

1
2
3
4
5
6
7
8
9
<select id="findUserByCondition" resultMap="userMap" parameterType="user">
  select * from user where 1=1
  <if test="userName != null">
    and username = #{userName}
  </if>
  <if test="userSex != null">
    and sex = #{userSex}
  </if>
</select>

测试

1
2
3
4
5
6
7
8
@Test
public void testFindByCondition(){
  User u = new User();
  u.setUserName("老王");
  u.setUserSex("女");

  List<User> users = userDao.findUserByCondition(u);
}

不写 where 1=1 ？

改进 -> where标签

where标签

1
2
3
4
5
6
7
8
9
10
11
<select id="findUserByCondition" resultMap="userMap" parameterType="user">
  select * from user
  <where>
    <if test="userName != null">
      and username = #{userName}
    </if>
    <if test="userSex != null">
      and sex = #{userSex}
    </if>
  </where>
</select>

foreach标签

根据queryvo中的Id集合实现查询用户列表

QueryVo.java

1
2
3
4
5
6
7
8
public class QueryVo {

    private User user;
    private List<Integer> ids;

    // get set ...
  
}

IUserDao.java

1
List<User> findUserInIds(QueryVo vo);

IUserDao.xml

1
2
3
4
5
6
7
8
9
10
<select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
  select * from user
  <where>
    <if test="ids != null and ids.size()>0">
      <foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
        #{uid}
      </foreach>
    </if>
  </where>
</select>

#{uid}是由上面item=”uid”决定的

测试

1
2
3
4
5
6
7
8
9
10
11
@Test
public void testFindInIds(){
  QueryVo vo = new QueryVo();
  List<Integer> list = new ArrayList<Integer>();
  list.add(41);
  list.add(42);
  list.add(46);
  vo.setIds(list);

  List<User> users = userDao.findUserInIds(vo);
}

sql标签

抽取重复的sql语句

（本来写不写;都可以，但是抽取sql语句时，为了防止后续的拼接出问题，最好不要写;）

1
2
3
<sql id="defaultUser">
  select * from user
</sql>

就可以把

1
2
3
<select id="findAll" resultMap="userMap">
  select * from user
</select>

改成

1
2
3
<select id="findAll" resultMap="userMap">
  <include refid="defaultUser"></include>
</select>