Mybatis动态sql

一、if

1.动态sql编写

<select id="findByIf" resultType="user" parameterType="map" >
    select * from user
    <where>
        <if test="username !=null">
            and username=#{username}
        </if>
        <if test="password !=null">
            and password = #{password}
        </if>
    </where>
</select>

2.测试查询

@Test
public void fun(){
    SqlSession sqlsession = MybatisUtil.getSqlsession();
    UserMapper mapper = sqlsession.getMapper(UserMapper.class);
    Map map = new HashMap();
    //map.put("username","张三");
    map.put("password","123");
    List<User> userList = mapper.findByIf(map);
    for (User user : userList) {
        System.out.println(user);
    }
}

3.结果输出

==>  Preparing: select * from user WHERE password = ? 
==> Parameters: 123(String)
<==    Columns: id, username, password
<==        Row: 1, 张三, 123
<==      Total: 1
User{id=1, username='张三', password='123'}

二、choose-when-otherwise

1.编写动态sql

<select id="findByChoose" parameterType="map" resultType="user">
    select * from user
    <where>
        <choose>
            <when test="username !=null">
                username =#{username}
            </when>
            <when test="password !=null">
                password = #{password}
            </when>
            <otherwise>
                1 = 1
            </otherwise>
        </choose>
    </where>
</select>

2.测试查询

@Test
public void fun2(){
    SqlSession sqlsession = MybatisUtil.getSqlsession();
    UserMapper mapper = sqlsession.getMapper(UserMapper.class);
    Map map = new HashMap();
    //map.put("username","张三");
    //map.put("password","123");
    List<User> userList = mapper.findByChoose(map);
    for (User user : userList) {
        System.out.println(user);
    }
}

3.结果输出

==>  Preparing: select * from user WHERE 1 = 1 
==> Parameters: 
<==    Columns: id, username, password
<==        Row: 1, 张三, 123
<==        Row: 2, 李四, 444
<==        Row: 3, 王五, 111
<==        Row: 4, 赵六, 222
<==      Total: 4
User{id=1, username='张三', password='123'}
User{id=2, username='李四', password='444'}
User{id=3, username='王五', password='111'}
User{id=4, username='赵六', password='222'}

三、set

1.编写动态sql

<update id="updataBySet"  parameterType="map">
    update user
    <set>
        <if test="username !=null">
            username = #{username},
        </if>
        <if test="password !=null">
            password = #{password},
        </if>
    </set>
    <where>
        id = #{id}
    </where>
</update>

2.测试查询

@Test
public void fun3(){
    SqlSession sqlsession = MybatisUtil.getSqlsession();
    UserMapper mapper = sqlsession.getMapper(UserMapper.class);
    Map map = new HashMap();
    //map.put("username","张三2");
    map.put("password","1212");
    map.put("id",1);
    Integer integer = mapper.updataBySet(map);
    sqlsession.commit();
    sqlsession.close();
}

3.结果输出

==>  Preparing: update user SET password = ? WHERE id = ? ==> Parameters: 1212(String), 1(Integer)<==    Updates: 1true

四、foreach

1.编写动态sql

<select id="findByForEach"  parameterType="map" resultType="user">    select * from user    where id in    <foreach collection="ids" item="id" open="(" separator="," close=")">        #{id}    </foreach></select>

2.测试查询

@Testpublic void fun4(){    SqlSession sqlsession = MybatisUtil.getSqlsession();    UserMapper mapper = sqlsession.getMapper(UserMapper.class);    Map map = new HashMap();    List<Integer> list = new ArrayList<Integer>();    list.add(1);    list.add(2);    list.add(4);    map.put("ids",list);    List<User> userList = mapper.findByForEach(map);    for (User user : userList) {        System.out.println(user);    }    sqlsession.commit();    sqlsession.close();}

3.结果输出

==>  Preparing: select * from user where id in ( ? , ? , ? ) ==> Parameters: 1(Integer), 2(Integer), 4(Integer)<==    Columns: id, username, password<==        Row: 1, 张三, 1212<==        Row: 2, 李四, 444<==        Row: 4, 赵六, 222<==      Total: 3User{id=1, username='张三', password='1212'}User{id=2, username='李四', password='444'}User{id=4, username='赵六', password='222'}