【Mybatis】映射文件-动态sql实现模糊查询

上次只是说到查询所有或者根据某一属性查询，然而当遇到模糊查询时就不能直接在映射文件中定义sql语句了。我们需要判断是否存在属性，然后对sql语句进行扩充。

UserMapper.xml如下

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.znan.dao.UserMapper">
    <select id="findByCondition" parameterType="user" resultType="user">
        select * from user where 1=1
        <if test="id!=0">
            and id=#{id}
        </if>
        <if test="username!=null">
            and username=#{username}
        </if>
        <if test="password!=null">
            and password=#{password}
        </if>
    </select>
</mapper>

接口实现如下，记住接口的返回以及输入参数都必须和映射文件中一致

package com.znan.dao;

import com.znan.domain.User;

import java.util.List;

public interface UserMapper {
    public List<User> findByCondition(User user);
}

测试

package com.znan;

import com.znan.dao.UserMapper;
import com.znan.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class MapperTest {
    @Test
    public void Test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        //模拟条件
        User condition = new User();
        condition.setId(2);
        condition.setUsername("lisi");
//        condition.setPassword("123");

        List<User> userList = mapper.findByCondition(condition);
        System.out.println(userList);
    }
}