MyBatis模糊查询详解(3种主流写法语句)

mybatis模糊查询使用频率很高，下面重点详解mybatis模糊查询的3种主流方式写法语句。

第一种：直接将封装好的条件传给 sql 语句

1.增加配置

<!--    根据姓名查询学生信息-->
<select id="selectStuByName" resultType="com.mikechen.mybatis.domain.Student">
    select * from student where name like #{name}
</select>

 

2.调用接口

/**
 * 根据姓名模糊查询学生信息
 * @param name 学生名字
 * @return
 */
public  List<Student> selectStuByName(@Param("name") String name);

 

3.代码实现

@Test
public void testLikeOne() {
    //        读取配置文件到数据流
    InputStream stream = Student.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
    //        创建SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
    //创建SqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //获取StudentMapper
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    //执行查询语句
    String name = "%小%";
    List<Student> students = mapper.selectStuByName(name);
}

 

第二种：使用字符串连接符 ${} 来实现

1.增加配置

根据姓名查询学生信息，如下所示：

<select id="selectStuByName" resultType="com.mikechen.mybatis.domain.Student">
    select * from student where name like "%" #{name} "%"
</select>

 

2.代码实现

@Test
public void testLike() {
    //读取配置文件到数据流
    InputStream stream = Student.class.getClassLoader().getResourceAsStream("mybatis-config.xml");

    //创建SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);

    //创建SqlSession
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //获取StudentMapper
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    //执行查询语句
    String name = "小";
    List<Student> students = mapper.selectStuByName(name);

}

 

1.增加配置

<select id="findByName" parameterType="string" resultType="User">
        select * from t_user where name like concat(#{name},'%')
    </select>

 

2.代码实现

@Test
    public void testFindLike() throws IOException{
        SqlSession session = MybatisUtil.getSqlSession();
        List<User> list = session.selectList("cn.mikechen.vo.UserMapper.findByName","张");
        for(User u:list){
            System.out.println(u);
        }
        session.close();
    }

 

mikechen睿哥

10年+一线大厂架构实战专家，就职于阿里、淘宝等一线大厂，操盘多个亿级大厂核心项目。