六、MyBatis特殊的SQL：模糊查询、动态设置表名、校验名称唯一性

六、MyBatis特殊的SQL

6.1 模糊查询

方式1：select * from litemall_user where username like ‘%${username}%’

(推荐)方式2：select * from litemall_user where username like concat(‘%’,#{username},‘%’)

@Test
public void getListByParam(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        System.out.println(mapper.getListByParam("同学"));
    }

List<User> getListByParam(@Param("username") String username);

<!--
        方式1：select * from litemall_user where username like '%${username}%'
        (推荐)方式2：select * from litemall_user where username like concat('%',#{username},'%')
    -->
<select id="getListByParam" resultType="User">
        select * from litemall_user where username like concat('%',#{username},'%')
</select>

6.2 动态设置表名

@Test
public void getListByTableName(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        System.out.println(mapper.getListByTableName("litemall_user"));
    }

List<User> getListByTableName(@Param("tableName") String tableName);

<select id="getListByTableName" resultType="User">
        select * from ${tableName}
</select>

6.3 校验名称唯一性

@Test
public boolean  checkNameVerify(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        String username = "同学1";
        Integer id = 9;
        int result = mapper.checkNameVerify(username, id);
        if(result >=1){
            return Boolean.TRUE;
        }
        return Boolean.FALSE;
    }

int checkNameVerify(@Param("username") String username, @Param("id") Integer id);

<select id="checkNameVerify" resultType="integer">
        SELECT count(1) FROM litemall_user where username=#{username}
        <if test="id != null and id != 0">
            and id != #{id}
        </if>
</select>

本人其他相关文章链接

1.一、MyBatis简介：MyBatis历史、MyBatis特性、和其它持久化层技术对比、Mybatis下载依赖包流程
2.二、搭建MyBatis采用xml方式，验证CRUD（增删改查操作）
3.三、MyBatis核心配置文件详解
4.四、MyBatis获取参数值的两种方式（重点）
5.五、MyBatis的增删改查模板（参数形式包括：String、对象、集合、数组、Map）
6.六、MyBatis特殊的SQL：模糊查询、动态设置表名、校验名称唯一性
7.七、MyBatis自定义映射resultMap
8.八、(了解即可)MyBatis懒加载(或者叫延迟加载)
9.九、MyBatis动态SQL
10.十、MyBatis的缓存
11.十一、MyBatis的逆向工程
12.十二、MyBatis分页插件