2020-11-27_MyBatis ——XXXMapper.xml提升

MyBatis ——XXXMapper.xml提升

多条语句插入

<!--
		注意：(collecion的参数只能小写)
		collection根据参数写：如果是list集合写list，如果是数组写	array;
		item="obj",用obj循环接收list集合中的每一个对象;
		separator="," 循环里面的元素用,号隔开;
		INSERT INTO dept(name)  VALUES ("技术部"),("小卖部")
-->
<insert id="batchAdd">
	insert into dept(name) values
	<foreach cllection="list" item="dept" separator=",">
		(#{dept.name})
	</foreach>
</insert>

多条数据删除

<delete id="batchDelete">
	delete from dept where id in
    /** open以什么开始，close以什么结束 */
    <foreach cllection="array" item="id" open="(" close=")" separator=",">
    	#{id}
    </foreach>
</delete>

关联查询

一对多（部门有多个员工）

• 方式一（关联查询）(查询每个部门所对应的员工信息)

  <select id="searchAll" resultMap="deptMap">
  	select d.*,e.id eid,e.name ename
  		from dept d join emp e
  		on d.id = e.dept_id
  </select>
  <resultMap id="deptMap" type="Dept">
  	<id column="id" property="id"></id>
      <result column="name" property="name"></result>
      <collection property="emps" ofType="Emp">
      	<id column="eid" property="id"></id>
          <result column="ename" property="name"></result>
      </collection>
   </resultMap>
  

• 方式二（分离查询）

  <select id="searchAll" resultMap="deptMap">
  	select * from dept
  </select>
  <resultMap id="deptMap" type="Dept">
  	<id column="id" property="id"></id>
      <result column="name" property="name"></result>
      <!-- 
  		
  		ofType:返回类型
  		select的值在同一个xml中可以省略全限定名，不在同一个xml中要加上全限定名
  	-->
      <collection property="emps" ofType="Emp" column="id" select="searchEmpById">		</collection>
   </resultMap>
  <!-- 员工的数据库 -->
   <select id="searchEmpById" resultType="Emp">
  	select * from emp where dept_id=#{id}
   </select>
  

多对一 （员工对应一个部门）

• 方式一（关联查询）

  <!-- <select id="searchAll" resultMap="empMap">
          select e.*, d.name dname
              from emp e join dept d
              on e.dept_id = d.id
      </select>
      <resultMap id="empMap" type="Emp">
          <id column="id" property="id"></id>
          <result column="name" property="name"></result>
          <result column="dept_id" property="dept_id"></result>
          <association property="dept" javaType="Dept">
              <result column="dname" property="name"></result>
          </association>
      </resultMap>-->
  

• 方式二

  <select id="searchAll" resultMap="empMap">
          select * from emp
  </select>
  <resultMap id="empMap" type="Emp">
      <id column="id" property="id"></id>
      <result column="name" property="name"></result>
      <result column="dept_id" property="dept_id"></result>
      <!-- select 全限定名 -->
      <association property="dept" javaType="Dept" column="dept_id"     						select="com.zhiji.mapper.DeptMapper.searchOne">
      </association>
  </resultMap>
  
  <!--另一个xml-->
  <select id="searchOne" resultType="Dept">
      select * from dept where id=#{dept_id}
  </select>