MyBatis第三天（动态SQL）

if标签的使用

1.接口中定义

 List<User> findByCondition(User user);

2.mapper中定义

<select id="findByCondition" resultType="com.daniel.domain.User" parameterType="com.daniel.domain.User">
        select * from user where 1=1
        <if test="username!=null">
            and username=#{username}
        </if>
    </select>

3.测试代码

 @Test
    public void findByCondition(){
        User u =new User();
        u.setUsername("老王");
        List<User> users = userDao.findByCondition(u);
        for (User user:users
        ) {
            System.out.println(user);
        }
        sqlSession.commit();
    }

4.测试结果

where和foreach标签的使用

用于多个查询的sql
select * from user where id in(41,42,46)
通过一个类中传入集合的方法
QueryVo类

public class QueryVo {
  
    private List<Integer> ids;

    public List<Integer> getIds() {
        return ids;
    }

    public void setIds(List<Integer> ids) {
        this.ids = ids;
    }

}

1.接口中定义

 //根据QueryVo 中提供的id集合，查询用户信息
    List<User> findByIds(QueryVo vo);

2.mapper中定义

<select id="findByIds" resultType="com.daniel.domain.User" parameterType="com.daniel.domain.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>

第一次测试了很久都报错
注意if标签中的内容都是来源于parameterType! 参数

3.测试代码

 @Test
    public void findByIds(){
        QueryVo queryVo = new QueryVo();
        List<Integer> list = new ArrayList<Integer>();
        list.add(41);
        list.add(42);
        list.add(46);
        queryVo.setIds(list);

        List<User> users = userDao.findByIds(queryVo);
        for (User user:users) {
            System.out.println(user);
        }
        sqlSession.commit();
    }

4.测试结果

抽取重复的sql

1.定义

<!--抽取重复的sql语句-->
    <sql id="defaultUser">
        select * from user
    </sql>

2.使用

<include refid="defaultUser"></include>

!--抽取重复的sql语句-->
    <sql id="defaultUser">
        select * from user
    </sql>

    <select id="findByIds" resultType="com.daniel.domain.User" parameterType="com.daniel.domain.QueryVo">

        <include refid="defaultUser"></include>
        <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>

个人总结

建议在mapper中写的sql语句不要带 分号； 以便于拼接