查询所有员工信息
/**
*
* 查询所有员工信息
*/
List<Emp> getAllEmp();
<select id="getAllEmp" resultType="emp">
select * from t_emp
</select>
查询员工以及员工所对应的部门信息
/**
*查询员工以及员工所对应的部门信息
*/
Emp getEmpAndDept(@Param("eid") Integer eid);
<!-- 处理多对一映射关系方式一:级联属性赋值-->
<resultMap id="getEmpAndDeptResultMapOne" 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>
<!-- 处理多对一映射关系方式二 -->
<resultMap id="getEmpAndDeptResultMapTwo" 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:需要处理多对的映射关系的属性名
javaType:该属性的类型
-->
<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 id);-->
<select id="getEmpAndDept" resultMap="getEmpAndDeptResultMapTwo">
select *
from t_emp left join t_dept
on t_emp.did = t_dept.did
where t_emp.eid = #{eid};
</select>
通过分布查询员工以及员工所对应的部门信息
分布查询第一步,查询员工信息
/**
* 通过分布查询员工以及员工所对应的部门信息
* 分布查询第一步,查询员工信息
*/
Emp getEmpAndDeptByStepOne(@Param("eid") Integer eid);
<resultMap id="empAndDeptByStepResuleMap" 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>
<!--
select:设置分布查询的sql的唯一标识(namespace.SQLId获mapper接口的全类名.方法名)
column:设置分布查询的条件
fetchType:当开启了全局的延迟加载后,可通过此属性手动控制延迟加载的效果
fetchType="lazy|eager" lazy标识延迟加载,eager标识立即加载
-->
<association property="dept"
select="com.atguigu.mybatis.mapper.DeptMapper.getEmpAndDeptByStepTwo"
column="did"
fetchType="lazy"
>
</association>
</resultMap>
<!-- Emp getEmpAndDeptByStepOne(@Param("eid") Integer id);-->
<select id="getEmpAndDeptByStepOne" resultMap="empAndDeptByStepResuleMap">
select * from t_emp where eid = #{eid}
</select>
第二步,通过did查询员工所对应的部门
/**
* 通过分布查询员工以及员工所对应的部门信息
* 分布查询第二步,通过did查询员工所对应的部门
*/
Dept getEmpAndDeptByStepTwo(@Param("did") Integer did);
<!-- Dept getEmpAndDeptByStepTwo(@Param("did") Integer did);-->
<select id="getEmpAndDeptByStepTwo" resultType="Dept">
select * from t_dept
where did = #{did};
</select>
获取部门以及部门中所有的员工信息
/**
* 获取部门以及部门中所有的员工信息
*/
Dept getDeptAndEmp(@Param("did")Integer did);
<resultMap id="deptAndEmpResultMap" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<!--
collection:处理一对多的映射关系
ofType:表示该属性所对应的集合中存储的数据类型
-->
<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 getDeptAndEmp(@Param("did")Integer did);-->
<select id="getDeptAndEmp" resultMap="deptAndEmpResultMap">
select * from t_dept left join t_emp
on t_dept.did = t_emp.did
where t_dept.did = #{did}
</select>
通过分布查询部门以及部门中所有员工信息
分布查询第一步,查询部门信息
/**
* 通过分布查询部门以及部门中所有员工信息
* 分布查询第一步,查询部门信息
*/
Dept getDeptAndEmpByStepOne(@Param("did") Integer did);
<resultMap id="deptAndEmpByStepResultMap" type="Dept">
<id property="did" column="did"></id>
<result property="deptName" column="dept_name"></result>
<collection property="emps"
select="com.atguigu.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
column="did">
</collection>
</resultMap>
<!-- Dept getDeptAndEmpByStepOne(@Param("did") Integer did);-->
<select id="getDeptAndEmpByStepOne" resultMap="deptAndEmpByStepResultMap">
select * from t_dept where did = #{did}
</select>
分布查询第二步,根据did查询
/**
* 通过分布查询部门以及部门中所有员工信息
* 分布查询第二步,根据did查询
*/
List<Emp> getDeptAndEmpByStepTwo(@Param("eid")Integer eid);
<!-- Emp getDeptAndEmpByStepTwo(@Param("eid")Integer eid);-->
<select id="getDeptAndEmpByStepTwo" resultType="emp">
select * from t_emp where did = #{did}
</select>
多条件查询
/**
* 多条件查询
*/
List<Emp> getEmpByCondition(Emp emp);
<!-- 多条件查询 List<Emp> getEmpByCondition(Emp emp); -->
<select id="getEmpByCondition" resultType="Emp">
select * from t_emp
<!--
prefix|suffix: 将trim标签中内容前面或后面添加指定内容
prefixOverrides|suffixOverrides:将trim标签中内容前面或后面去掉指定内容
-->
<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} and
</if>
<if test="sex != null and sex != ''">
sex = #{sex} and
</if>
<if test="email != null and email != ''">
email = #{email}
</if>
</trim>
</select>
<!-- 多条件查询 List<Emp> getEmpByCondition(Emp emp);-->
<select id="getEmpByConditionTwo" resultType="Emp">
select * from t_emp
<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>
</select>
<!-- 多条件查询 List<Emp> getEmpByConditionOne()-->
<select id="getEmpByConditionOne" resultType="Emp">
select * from t_emp where 1=1
<if test="empName != null and empName != ''">
and 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>
测试 choose、when、otherwise
/**
* 测试 choose、when、otherwise
*/
List<Emp> getEmpByChoose(Emp emp);
<!-- List<Emp> getEmpByChoose(Emp emp);-->
<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>
测试数组实现批量删除
/**
* 测试数组实现批量删除
*/
int deleteMoreByArray(@Param("eids") Integer[] eids);
<!-- int deleteMoreByArray(Integer[] eids);-->
<!-- or 情况 -->
<delete id="deleteMoreByArray">
delete from t_emp where
<foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
</delete>
<!-- and 情况 -->
<delete id="deleteMoreByArrayOne">
delete from t_emp where eid in
<foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
</foreach>
</delete>
批量添加
/**
* 批量添加
*/
int insertMoreByList(@Param("emps") List<Emp> emps);
<!-- int insertMoreByList(List<Emp> emps);-->
<insert id="insertMoreByList">
insert into t_emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.empName},#{emp.age},#{emp.sex},#{emp.email},null)
</foreach>
</insert>