1.使用映射器Mapper
–配置xxxMapper.xml
<!--这个命名空间的名称就是咱们Mapper接口的全限定名-->
<mapper namespace="cn.itsource._02_mapper.mapper.EmployeeMapper">
<!--这个id的名称必需和映射的EmployeeMapper的方法名一致-->
<insert id="save" parameterType="employee">
...
</insert>
<select id="findAll" resultType="employee">
...
</select>
</mapper>
–创建接口xxxMapper,在接口中添加方法,方法名与映射器中id保持一致
package cn.itsource._02_mapper.mapper;
public interface EmployeeMapper {
void save(Employee employee);
// @Select("select * from employee")
List<Employee> findAll();
}
–调用Mapper
SqlSession session = MyBatisUtil.openSession();
EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
mapper.findAll().forEach(e -> System.out.println(e));
2.完成高级查询
1.准备一个Query对象,用来处理高级查询的字段
2.模糊查询
<if test="name!=null and name!=''">
and name LIKE concat('%',#{name},'%')
</if>
3.特殊符号的处理
a.使用转义符
b.使用CDATA段
4.高级查询有多个条件的时候,使用<where>标签,可以将第一个and转换成where
<where>
<if test="name!=null and name!=''">
and name LIKE concat('%',#{name},'%')
</if>
<if test="minAge!=null">
and age>=${minAge}
</if>
<if test="maxAge!=null">
and <![CDATA[age<=${maxAge}]]>
</if>
</where>
5.如果if的判断有多个条件,用 and、or连接
6.如果出现有相同的sql,可以使用<sql>标签抽取出来
<sql id="whereSql">
<where>
<if test="name!=null and name!=''">
and name LIKE concat('%',#{name},'%')
</if>
<if test="minAge!=null">
and age>=${minAge}
</if>
<if test="maxAge!=null">
and <![CDATA[age<=${maxAge}]]>
</if>
</where>
</sql>
通过<include>标签引用抽取的sql
3.批量操作
–批量删除
首先知道sql delete from 表名 where id in (?,?,…)
collection="":代表你要循环的是什么? array/list
如果传过来的是数据,写array(集合就写list)
item:循环的每一个数据
open:拼接字符串以什么开始
close:拼接字符串以什么结尾
separator:拼接的时候每个值使用,隔开
index:遍历的下标
-使用传入数组的方法
<delete id="batchDelete" parameterType="long[]">
delete from employee where id in
<foreach collection="array" item="v" open="(" close=")" separator=",">
#{v}
</foreach>
</delete>
-使用传入集合的方法
<delete id="batchDelete" parameterType="list">
delete from employee where id in
<foreach collection="list" item="v" open="(" close=")" separator=",">
#{v}
</foreach>
</delete>
–批量添加
首先知道sql `insert into 表名 (p1,p2,…) values (#{p1},#{p2}),(#{p1},#{p2}),…
<insert id="batchSave" parameterType="list">
insert into employee (name,age,sex) values
<foreach collection="list" item="emp" separator=",">
(#{emp.name},#{emp.age},#{emp.sex})
</foreach>
</insert>
–动态修改,可以解决数据丢弃问题
<update id="update" parameterType="cn.itsource._02_mapper.domain.Employee">
UPDATE employee
<set>
<if test="name!=null and name!=''">
name = #{name},
</if>
<if test="age!=null">
age=#{age},
</if>
</set>
WHERE id=#{id}
</update>
4.多对一
–准备domain
public class Employee {
private Long id;
private String name;
private Integer age;
private Boolean sex;
private Department dept;
//... getter,setter与toString
}
public class Department {
private Long id;
private String name;
//... getter,setter与toString
}
-嵌套结果的方式
查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)
当我们使用了association 后默认的映射失败,需要自己手动完成映射
<select id="findAll" resultMap="employeeMap">
select e.id eid,e.name ename,e.age,e.sex,d.id did,d.name dname
from employee e join department d on d.id = e.dept_id
</select>
<resultMap id="employeeMap" type="employee">
<!-- 手动完成基本映射 -->
<id property="id" column="eid" />
<result property="name" column="ename" />
<result property="sex" column="sex" />
<result property="age" column="age" />
<!-- property:属性名,javaType:属性类型 -->
<association property="dept" javaType="department">
<id property="id" column="did" />
<result property="name" column="dname" />
</association>
</resultMap>
-嵌套查询的方式
会产生n+1条sql
需要去找到对应的那条sql并且执行
保证MyBatis能找到这两个xml
##EmployeeMapper.xml:
<select id="findAll" resultMap="employeeMap">
SELECT * FROM employee
</select>
<!--嵌套查询方案-->
<resultMap id="employeeMap" type="employee">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
<!--
查询相应的部门
column:数据库对应的列 select:查询对应的sql的路径
-->
<association property="dept" javaType="department"
column="dept_id" select="cn.itsource._04_many2one.DepartmentMapper.findById" />
</resultMap>
##departmentMapper.xml
<mapper namespace="cn.itsource._04_many2one.DepartmentMapper">
<select id="findById" parameterType="long" resultType="department">
select * from department where id = #{id}
</select>
</mapper>
5.一对多
–准备domain
public class Employee {
private Long id;
private String name;
private Integer age;
private Boolean sex;
//... getter,setter与toString
}
public class Department {
private Long id;
private String name;
private List<Employee> employees = new ArrayList<>();
-级联保存
准备两个Mapper
保存部门后需要马上拿到它的id
保存员工传的是Map{List,deptId}
departmentMapper.xml
<!--保存后需要拿到id-->
<insert id="save" parameterType="department"
useGeneratedKeys="true" keyProperty="id" keyColumn="id">
insert into department (name) values (#{name})
</insert>
employeeMapper.xml
<insert id="batchSave" parameterType="map">
insert into employee (name,age,sex,dept_id) values
<foreach collection="list" item="emp" separator=",">
(#{emp.name},#{emp.age},#{emp.sex},#{deptId})
</foreach>
</insert>
-嵌套结果
查询的sql要关连多张表(一定要取别名,不然有些名称会产生冲突)
当我们使用了collection 后默认的映射失败,需要自己手动完成映射
<resultMap id="departmentMap" type="department">
<id property="id" column="did" />
<result property="name" column="dname" />
<collection property="employees" ofType="employee">
<id property="id" column="eid" />
<result property="name" column="ename" />
<result property="age" column="age" />
<result property="sex" column="sex" />
</collection>
</resultMap>
<select id="findAll" resultMap="departmentMap">
select d.id did,d.name dname,e.id eid,e.name ename,e.age,e.sex
from department d left join employee e on e.dept_id = d.id
</select>
-嵌套查询
departmentMapper.xml
<resultMap id="departmentMap" type="department">
<id property="id" column="id" />
<result property="name" column="name" />
<collection property="employees" ofType="employee" column="id" select="cn.itsource._05_one2many.EmployeeMapper.findByDeptId">
</collection>
</resultMap>
<select id="findAll" resultMap="departmentMap">
select * from department
</select>
employeeMapper.xml
<!--这个命名空间的名称就是咱们Mapper接口的全限定名-->
<mapper namespace="cn.itsource._05_one2many.EmployeeMapper">
<select id="findByDeptId" resultType="employee" parameterType="long">
select * from employee where dept_id = #{deptId}
</select>
</mapper>
6.缓存
自带一级级联
二级缓存需要加上标签
二级缓存的对象必需是序列化对象 … implements Serializable