3、特殊sql
3.1 模糊查询
- 三种方式
- 用占位符
- concat拼接
- “” 用的最多
public interface SqlMapper {
List<User> findUser(@Param("username") String username);
}
<!-- List<User> findUser(@Param("username") String username); -->
<select id="findUser" resultType="com.melon.mybatis.pojo.User">
<!-- select * from t_user where username like '%${username}%'-->
<!-- select * from t_user where username like concat('%',#{username},'%'); -->
select * from t_user where username like "%"#{username}"%"
</select>
测试:
@Test
public void test01(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);
List<User> user = sqlMapper.findUser("张");
System.out.println(user);
}
3.2 批量删除
- 用$大括号,因为#{}会被自动解析未’‘,’'在sql语句中是不正确的
int deleteMore(@Param("ids") String ids);
<!-- int deleteMore(@Param("ids") String ids); -->
<delete id="deleteMore">
delete from t_user where id in (${ids})
</delete>
测试:
@Test
public void test02(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);
int i = sqlMapper.deleteMore("5,6");
System.out.println(i);
}
4、自定义映射关系ResultMap
4.1 解决字段名和数据库不匹配的问题(3)
- 设置别名
- 在Mybatis的配置文件中开启驼峰
- resultMap处理字段和属性的映射关系
1、别名
<select id="getAllEmp" resultType="Emp">
select eid,emp_name empName,age,sex,email from t_emp
</select>
2、mybatis中开启驼峰
<!-- 自动映射成驼峰 -->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"></setting>
</settings>
3、resultMap处理字段和属性的映射关系
- resultMap:设置自定义映射
- 属性:
- id:表示自定义映射的唯一标识,不能重复
- type:查询的数据要映射的实体类的类型
- 子标签:
- id:设置主键的映射关系
- result:设置普通字段的映射关系
- 子标签属性:
- property:设置映射关系中实体类中的属性名
- column:设置映射关系中表中的字段名
<resultMap id="empResultMap" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</resultMap>
<select id="getAllEmp" resultMap="empResultMap">
select * from t_emp
</select>
4.2 多对一查询的三种方式
- 级联方式处理映射关系
- 使用association处理映射关系
- 分步查询
1、级联方式处理映射关系
- 返回类型用resultMap
- 额外添加两行
<resultMap id="empAndDeptResultMapOne" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<result property="dept.did" column="did"></result>
<result property="dept.deptName" column="dept_name"></result>
</resultMap>
<!--Emp getEmpAndDept(@Param("eid")Integer eid);-->
<select id="getEmpAndDept" resultMap="empAndDeptResultMapOne">
select * from t_emp left join t_dept on t_emp.eid = t_dept.did where t_emp.eid = #{eid}
</select>
2、使用association处理映射关系
- association:处理多对一的映射关系
- property:需要处理多对的映射关系的属性名
- javaType:该属性的类型
<resultMap id="empAndDeptResultMapTwo" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<association property="dept" javaType="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
</association>
</resultMap>
<!--Emp getEmpAndDept(@Param("eid")Integer eid);-->
<select id="getEmpAndDept" resultMap="empAndDeptResultMapTwo">
select * from t_emp left join t_dept on t_emp.eid = t_dept.did where t_emp.eid = #{eid}
</select>
3、分步查询
- 第一步查询员工的信息
- 第二步查询部分的信息
1. 查询员工信息
- association表示映射的属性
- select:设置分布查询的sql的唯一标识(namespace.SQLId或mapper接口的全类名.方法名)
- column:两个表的连接条件,即分步查询的条件
<!-- Emp getEmpAndDeptByStep(@Param("eid") Integer eid); -->
<select id="getEmpAndDeptByStep" resultMap="empResultMapDeptByStep">
select * from t_emp where eid = #{eid}
</select>
<resultMap id="empResultMapDeptByStep" type="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
<association property="dept"
select="com.melon.mybatis.mapper.DeptMapper.getEmpAndDeptStepTwo" column="did">
</association>
</resultMap>
2.查询部门信息
<!-- Dept getEmpAndDeptStepTwo(@Param("did") Integer did); -->
<select id="getEmpAndDeptStepTwo" resultType="com.melon.mybatis.pojo.Dept">
select * from t_dept where did = #{did}
</select>
4.3 一对多查询
1、collection查询
- collection:用来处理一对多的映射关系
- ofType:表示该属性对饮的集合中存储的数据的类型
@Data
public class Dept {
private Integer did;
private String deptName;
private List<Emp> emps;
}
<resultMap id="DeptAndEmpResultMap" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<collection property="emps" ofType="Emp">
<id property="eid" column="eid"></id>
<result property="empName" column="emp_name"></result>
<result property="age" column="age"></result>
<result property="sex" column="sex"></result>
<result property="email" column="email"></result>
</collection>
</resultMap>
<!-- Dept getDeptAndEmpOne(@Param("did") Integer did); -->
<select id="getDeptAndEmpOne" resultMap="DeptAndEmpResultMap">
select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did = #{did}
</select>
2、分步查询
<resultMap id="DeptAndEmpStep" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<collection property="emps"
select="com.melon.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
column="did">
</collection>
</resultMap>
<!-- Dept getDeptAndEmpByStep(@Param("did") Integer did); -->
<select id="getDeptAndEmpByStep" resultMap="DeptAndEmpStep">
select * from t_dept where did = #{did}
</select>
<!-- List<Emp> getDeptAndEmpByStepTwo(@Param("did") Integer did); -->
<select id="getDeptAndEmpByStepTwo" resultType="com.melon.mybatis.pojo.Emp">
select * from t_emp where did = #{did}
</select>
5、动态SQL
5.1 if标签
- if标签可通过test属性(即传递过来的数据)的表达式进行判断,若表达式的结果为true,则标签中的内容会执行;反之标签中的内容不会执行
- 加上1=1是为了方便添加字符串的拼接
- select * from t_emp where emp_name = ? and age = ? and sex = ? and email = ?,这是正常的sql语句,如果出现某个字符串不满足条件,where后面会直接跟and,那么就会报错,加上1=1这个恒成立的条件不影响查询的结果
、<!-- List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="com.melon.mybatis.pojo.Emp">
select * from t_emp where 1=1
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</select>
5.2 where标签
- where标签用来自动生成where关键字
- 若where标签中的if条件都不满足,则where标签没有任何功能,即不会添加where关键字
- 注意:where标签不能去掉条件后多余的and/or
<where>
<if test="empName != null and empName != ''">
emp_name = #{empName}
</if>
<if test="age != null and age != ''">
and age = #{age}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="email != null and email != ''">
and email = #{email}
</if>
</where>
5.3 trim标签
- trim用于去掉或添加标签中的内容
- 常用属性
- prefix:在trim标签中的内容的前面添加某些内容
- suffix:在trim标签中的内容的后面添加某些内容
- prefixOverrides:在trim标签中的内容的前面去掉某些内容
- suffixOverrides:在trim标签中的内容的后面去掉某些内容
- 若trim中的标签都不满足条件,则trim标签没有任何效果,也就是只剩下select * from t_emp
<select id="getEmpByCondition" resultType="com.melon.mybatis.pojo.Emp">
select * from t_emp
<trim prefix="where" suffixOverrides="and|or">
<if test="empName != null and empName != ''">
emp_name = #{empName} and
</if>
<if test="age != null and age != ''">
age = #{age}
</if>
<if test="sex != null and sex != ''">
sex = #{sex}
</if>
<if test="email != null and email != ''">
email = #{email}
</if>
</trim>
</select>
5.4 choose、when、otherwise
- choose、when、otherwise相当于if…else if…else
- when至少要有一个,otherwise至多只有一个
<select id="getEmpByChoose" resultType="Emp">
select * from t_emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="age != null and age != ''">
age = #{age}
</when>
<when test="sex != null and sex != ''">
sex = #{sex}
</when>
<when test="email != null and email != ''">
email = #{email}
</when>
<otherwise>
did = 1
</otherwise>
</choose>
</where>
</select>
@Test
public void getEmpByChoose() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
List<Emp> emps = mapper.getEmpByChoose(new Emp(null, "张三", 23, "男", "123@qq.com", null));
System.out.println(emps);
}