mybatis之动态sql

现mybatis数据库中存在user表：

 

现实现需求：查询某个用户根据用户名(username)、或性别(sex)、密码(pswd)

用普通sql语句：

selece * from user where 要查询的字段 = 查询的依据

使用mybatis动态查询，传递什么字段与值就查询什么字段下的数据：

javabean：User

@Data
public class User {
    private String username;
    private String pswd;
    private String sex;
}

UserMapper接口：

List<User> getUserByParams(Map<String,Object> map);

UserMapper.xml文件：

<select id="getUserByParams" resultType="user">
    select * from user
    <where>
        <if test="null != username">
            username = #{username}
        </if>
        <if test="null != pswd">
            and pswd = #{pswd}
        </if>
        <if test="null != sex">
            and sex = #{sex}
        </if>
    </where>
</select>

第二种方式：

<select id="getUserByParams" resultType="User">
        select * from user
        <where>
            <choose>
                <when test="null != username">
                    username = #{username}
                </when>
                <when test="null != pswd">
                    pswd = #{pswd}
                </when>
                <otherwise>
                    sex = #{sex}
                </otherwise>
            </choose>
        </where>
    </select>

区别是：第一种相当于if语句，第二种相当于switch语句

 

测试的java代码：

 public void test(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    Map<String,Object> map = new HashMap<String,Object>();
    map.put("username","小李子");
    map.put("sex","女");
    List<User> users = userMapper.getUserByParams(map);
    for (User user : users) {
        System.out.println(user);
    }
    sqlSession.close();
}

 

修改的操作：

 UserMapper接口

int update(Map<String,Object> map);

UserMapper.xml文件：

<update id="update">
        update user
        <set>
            <if test="null != sex">
                username = #{sex}
            </if>
            <if test="null != pswd">
                pswd = #{pswd}
            </if>
        </set>
        <where>
            username = #{username}
        </where>
    </update>

 

测试的java代码：

public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("username","张三");
        map.put("sex","男");
        int row = userMapper.update(map);
        sqlSession.commit();
        sqlSession.close();
    }

 

动态sql之foreach：

UserMapper接口：

List<User> getUserByUsernames(Map<String,Object> map);

 

UserMapper.xml：

<select id="getUserByUsernames" resultType="user">
        select * from user
        <where>
            <foreach collection="usernames" item="username" open=" " close=" " separator="or">
                username = #{username}
            </foreach>
        </where>
    </select>

 

测试的java代码：

public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Map<String,Object> map = new HashMap<String,Object>();
        ArrayList<String> usernames = new ArrayList<String>();
        usernames.add("小李子");
        usernames.add("晓晓");
        map.put("usernames",usernames);
        List<User> users = userMapper.getUserByUsernames(map);
        for (User user : users) {
            System.out.println(user);
        }
        sqlSession.close();
    }