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

布满荆棘的人生 2024-03-22 10:13 21阅读 0赞

![在这里插入图片描述][705b09a0a4b4446da256d4f8accbfb92.jpeg_pic_center]

#### 文章目录 ####

*  六、MyBatis特殊的SQL
 *   *  6.1 模糊查询
     *  6.2 动态设置表名
     *  6.3 校验名称唯一性
 *  本人其他相关文章链接

## 六、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);

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

[705b09a0a4b4446da256d4f8accbfb92.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/7a6e203a154b460fa0226b933042d57e.png
[MyBatis_MyBatis_MyBatis_Mybatis]: https://blog.csdn.net/a924382407/article/details/130416347
[MyBatis_xml_CRUD]: https://blog.csdn.net/a924382407/article/details/130416378
[MyBatis]: https://blog.csdn.net/a924382407/article/details/130416456
[MyBatis 1]: https://blog.csdn.net/a924382407/article/details/130416542
[MyBatis_String_Map]: https://blog.csdn.net/a924382407/article/details/123235050
[MyBatis_SQL]: https://blog.csdn.net/a924382407/article/details/130416575
[MyBatis_resultMap]: https://blog.csdn.net/a924382407/article/details/130505016
[MyBatis 2]: https://blog.csdn.net/a924382407/article/details/130505098
[MyBatis_SQL 1]: https://blog.csdn.net/a924382407/article/details/130505189
[MyBatis 3]: https://blog.csdn.net/a924382407/article/details/130505224
[MyBatis 4]: https://blog.csdn.net/a924382407/article/details/130505309
[MyBatis 5]: https://blog.csdn.net/a924382407/article/details/130505368