动态sql_＜chocse＞＜when＞＜otherwise＞

sql

mysql> use fzhl_
Database changed
mysql> select * from fz03;
+------+--------+------+------+
| id   | name   | age  | x    |
+------+--------+------+------+
|    1 | 小明   |   15 | 男   |
|    2 | 小黄   |   16 | 女   |
|   10 | 大明   |   15 | 男   |
+------+--------+------+------+
3 rows in set (0.00 sec)

mysql>

mapper接口

//Fzhl对象属性为mysql数据库对应的属性
List<Map<String,Object>> select_else_if(Fz03 fz03);

mapper.xml

<select id="select_else_if" resultType="map">
        select
        <choose>
            <when test="age == 15">
                age
            </when>
            <when test="age == 16">
                name
            </when>
            <otherwise>
                *
            </otherwise>
        </choose>
        from fz03
</select>

解析

该标签顺序为

<chcose>    外部包裹 其内标签为 when 与 otherwise

<when>     该标签作用与if....else if 作用相同

<otherwise>    该标签与else 作用相同

例

@Test
    public void a4() throws Exception{
        InputStream inputStream = Resources.getResourceAsStream("mybatis_x.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);

        Usermapper usermapper = session.getMapper(Usermapper.class);
        List<Map<String,Object>> list = usermapper.select_else_if(new Fz03(0,null,15,null));

        System.out.println(list);

        session.close();
        inputStream.close();
}

当传参满足xml文件中的

<when test="age == 15">

运行结果为(拼接出只查询age信息的sql语句)

当传参满足xml文件中的

<when test="age == 16">

运行结果(拼接出只查询name信息的sql语句)

当都不满足的情况就拼接otherwise标签的内容

<otherwise>
    *
</otherwise>

运行结果

该组合是可以配合where标签的

    <select id="select_else_if" resultType="map">
        select * from fz03
        <where>
            <choose>
                <when test="name != null ">
                    name = #{name}
                </when>
            </choose>
        </where>
    </select>

运行结果