@[toc]
六、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分页插件
重要信息
