Mybatis的模糊查询写法

所需的maven依赖

<dependencies>
        <!-- Mybatis核心 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>

        <!-- junit测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <!-- MySQL驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
    
    	<!-- lombok，自己生成实体类的getter和setter以及toString等方法 -->
    	<dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
</dependencies>

模糊查询的三种实现方式

MybatisSQL语句一

此写法是错误的，此写法会报如下的错误，原因是"%#{mohu}%"中的“#”号会被mybatis解析为占位符“？”从而导致SQL语句出错，编译后的SQL语句变成了

select * from t_user where username like "%?%"

这与预期的结果不一致，自然就会报如下的错误，该写法要特别注意%和#的位置

<!--错误的写法-->
<select id="getUserByLike" resultType="User">
        select * from t_user where username like "%#{mohu}%"
</select>

方式一正确的写法

<select id="getUserByLike" resultType="User">
        select * from t_user where username like "%"#{mohu}"%"
</select>

MybatisSQL语句二

<select id="getUserByLike" resultType="User">
        select * from t_user where username like '%${mohu}%'
</select>

MybatisSQL语句三

<select id="getUserByLike" resultType="User">
        select * from t_user where username like concat('%',#{mohu},'%')
       
</select>

mapper层接口

public interface SpecialSQLMapper {

    /**
     * 通过用户名模糊查询用户信息
     * @param mohu
     * @return
     */
    List<User> getUserByLike(@Param("mohu") String mohu);
}

注意：select标签中的id必须是mapper接口的方法名，resultType可以是实体类起别名后的名称，要不然就是实体类的全限定名称,即（com.xxx.entity.xxx,若resultType使用别名时必须在mybatis的核心配置文件配置如下

mybatis的核心配置文件

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

    <!--
        MyBatis核心配置文件中的标签必须要按照指定的顺序配置：
        properties?,settings?,typeAliases?,typeHandlers?,
        objectFactory?,objectWrapperFactory?,reflectorFactory?,
        plugins?,environments?,databaseIdProvider?,mappers?
    -->
    <typeAliases>
        <package name="com.example.mybatis.entity"/>
    </typeAliases>
    <!--引入mybatis的映射文件-->
    <mappers>
        <package name="com.example.mybatis.mapper"/>
    </mappers>
</configuration>

实体类

@Data
public class User {

    private Integer id;

    private String username;

    private String password;

    private Integer age;

    private String gender;

    private String email;
}

工具类

public class SqlSessionUtil {

    public static SqlSession getSqlSession(){
        SqlSession sqlSession = null;
        try {
            //获取核心配置文件的输入流
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            //获取SqlSessionFactoryBuilder
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            //获取SqlSessionFactory
            SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(is);
            //获取SqlSession对象
            sqlSession = sqlSessionFactory.openSession(true);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sqlSession;
    }

}

测试

public class SpecialSQLMapperTest {

    @Test
    public void testGetUserByLike(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        List<User> list = mapper.getUserByLike("a");
        list.forEach(System.out::println);
    }
}