mybatis-02 关联查询，动态sql

1 resultType和resultMap的区别

ResultType相对与ResultMap而言更简单一点。只有满足ORM（Object Relational Mapping，对象关系映射）时，即数据库表中的字段名和实体类中的属性完全一致时，才能使用，否则会出现数据不显示的情况。

<select id="findAll" resultType="xxx">
select * from emp
</select> 

resultMap主要用在数据库列名和实体类属性名不一致的情况下。复杂sql语句一般使用resultMap。

查询所有的部门的信息
实体类：
private Integer deptno;
private String dname;
private String loc;
 
 
数据库字段：deptid，dname，loc
 
<select id="findAll" resultMap="testDept">
select * from dept
</select>
                                                                    
<resultMap id="testDept" type="com.aaa.entity.Dept" autoMapping="true">
<result column="deptid" property="deptno"></result>
</resultMap>

没有实体类对应的情况。可以使用resultType=“map”

2 关联查询

2.1 多对一（manyTOone）

以员工表emp为主，dept为副表。
实体类：

在多的一方设置一
public class Emp {
 
private Integer empno;
private String ename;
private String job;
private String mgr;
private java.sql.Date hiredate;
private String sal;
private String comm;
/*员工对部门
是一个多对一的关系 谁是1 部门 emp是多
在多的一方配置1
直接把1的那一方写一个实体存放到多的那一方
*/
//private String deptno;
private Dept dept;
//get set省略
}

映射文件：EmpMapper.xml

List<Emp> findAllEmp();
<!--type emp 多对一的关系 autoMapping 属性名和列名一致的时候 自动映射 -->
<!--association property 指的是一的那一方在多的那一方里面的属性 javaType 指的是dept指向的实体类是什么 -->
<select id="findAllEmp" resultMap="getEmp">
select * from emp e
join dept d on e.deptno=d.deptno
</select>
<!--定义一个resultMap id=getEmp type="" 类型 java对象 是什么-->
<resultMap id="getEmp" type="entity.Emp" autoMapping="true">
<!--id 代表配置的是主键 emp表里面主键是empid-->
<!--column 代表的是从数据库里面查出来的列名是什么-->
<!--property 代表的是对象的属性名-->
<!--id一定要配置的-->
<id column="empid" property="empid"></id>
<!--普通列-->
<!--<result column="ename" property="ename"></result>-->
 
<!--dept-->
<!--emp对dept 多对一的关系-->
<!--property 属性 代表的是一的那一方在多的那一方 里面的属性名-->
<!--一的那一方指向的对象是 谁-->
<association property="dept" javaType="entity.Dept" autoMapping="true">
<!--dept表里面的主键-->
<id column="deptno" property="deptno"></id>
</association>
 
</resultMap>
>

@Test
public void queryEmp(){
List<Emp> allEmp = mapper.findAllEmp();
for (Emp emp : allEmp) {
System.out.println("员工的信息"+emp+"部门的信息"+emp.getDept());
}
 
}

下面的异常原因是result的类型只能接受一行数据，但是sql返回多行数据。

2.2 一对多（oneTOmany）

以员工表dept为主，emp为副表。
实体类：dept

public class Dept {
 
private String deptno;
private String dname;
private String loc;
//一对多
//在一的那一方配置 多 List
private List<Emp> emps=new ArrayList<>();
//get set 省略
}

映射文件：deptMapper.xml

<select id="queryAllDept"  resultMap="queryDeptMap">
        select * from dept  d
left join emp e
on e.deptno=d.deptno
    </select>
    <resultMap id="queryDeptMap" type="dept">
        <!--主要对应的是表里面的  主键  primary key
         property  指的是实体里面的主键对应的属性名
         column    指的是数据库里面的表的主键的列名
         -->
        <id property="deptno" column="deptno"></id>
        <result property="dname" column="dname"></result>
        <result property="loc" column="loc"></result>
      <collection property="emps" ofType="emp">
          <id property="empno" column="empno"></id>
          <result property="ename" column="ename"></result>
          <result property="sal" column="sal"></result>
          <result property="comm" column="sal"></result>
      </collection>
    </resultMap>

2.3：多对多（了解）

老师（t_teacher）和班级(t_class)

班级：
@NoArgsConstructor
@AllArgsConstructor
@Data
public class TClass implements Serializable {
  private Integer id;
  private String name;
  //双向的一对多
  private List<TTeacher> teachers=new ArrayList<>();
}

老师:
@NoArgsConstructor
@AllArgsConstructor
@Data
public class TTeacher implements Serializable {
  private Integer id;
  private String name;
  //一个老师教多个班级
  private List<TClass> classes=new ArrayList<>();
}

ClassDao接口:
public interface ClassDao {
    List<TClass> getAllClass();
}

mapper文件：
<mapper namespace="dao.ClassDao">
    <select id="getAllClass"  resultMap="getClass">
      select * from t_class
    </select>
    <resultMap id="getClass" type="entity.TClass" autoMapping="true">
        <id column="id" property="id"></id>
        <collection property="teachers" ofType="entity.TTeacher" select="getClassTeacher" column="cid=id">
        </collection>
    </resultMap>
    <select id="getClassTeacher" resultType="entity.TTeacher">
        select * from t_teacher t join t_class_teacher ct
        on t.id=ct.tid where cid=#{cid}
    </select>
</mapper>

TeacherDao接口：
public interface TeacherDao {
    List<TTeacher> getAllTeacher();
}

<mapper namespace="dao.TeacherDao">
    <select id="getAllTeacher" resultMap="getAllTeacher">
        select t.id tid,c.id cid ,t.name tname,c.name cname from t_teacher t
        join t_class_teacher ct
        on t.id=ct.tid
        join t_class c
        on ct.cid=c.id
    </select>
    <resultMap id="getAllTeacher" type="entity.TTeacher" autoMapping="true">
      <id column="tid" property="id"></id>
        <result property="name" column="tname"></result>
<!--班级-->
        <collection property="classes" ofType="entity.TClass" autoMapping="true">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </collection>
    </resultMap>
</mapper>

2.4 懒加载：

​Mybatis中的延迟加载，也称为懒加载，是指在进行关联查询时，按照设置的延迟规则推迟对关联对象的查询。延迟加载可以有效的减少数据库的压力。延迟加载只是针对有延迟设置的关联对象的推迟查询，对于主主查询是直接进行执行SQL语句。

1.MyBatis关联查询加载时机
直接加载：执行完主对象的查询后，马上执行对关联对象的查询语句
侵入式延迟：执行完对主对象对查询后，不会执行对关联对象的查询，但当访问主对象的属性详情是，就会执行关联对象的查询
深度延迟：只有当真正访问关联对象的详情时，才会执行查询语句
2.MyBatis延迟加载实现方式

.全局延迟
在MyBatis核心配置类中添加标签

       <!-- 延迟加载总开关 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 侵入式延迟加载开关 -->
<!--        <setting name="aggressiveLazyLoading" value="false"/>-->
<!--        <setting name="lazyLoadTriggerMethods" value=""/>-->
<!--        <setting name="lazyLoadingEnabled" value="true"/>-->
<!--        <setting name="aggressiveLazyLoading" value="true"/>-->

Ⅱ.部分延迟
在关联查询collection、association标签上添加 fetchType 属性，lazy表示延迟加载，eager表示立即加载，指定属性后，将在映射中忽略全局配置参数 lazyLoadingEnabled

实体:
public class Dept {
    private Integer deptno;
    private String dname;
    private String loc;
    private List<Emp> emps=new ArrayList<>();
}
public class Emp {
    private Integer empno;
    private String ename;
    private String job;
    private Integer mgr;
    private Date hiredate;
    private Double sal;
    private String comm;
    private Integer deptno;
}
接口:
List<Dept> findAllDept();

<select id="findAllDept" resultMap="AllDept">
    select * from dept
</select>
<resultMap id="AllDept" type="Dept" autoMapping="true">
    <id property="deptno" column="deptno"></id>
    <collection property="emps" ofType="Emp" autoMapping="true"
                column="deptno" select="findEmp" fetchType="lazy" >
        <id property="empno" column="empno"></id>
    </collection>
</resultMap>
<select id="findEmp" resultType="Emp">
    select * from emp where deptno=#{deptno}
</select>

测试：
List<Dept> allDept = mapper.findAllDept();
System.out.println(allDept.get(0).getDname());

3 动态sql

3.1 动态sql

3.3.1 if
当传入的参数是一个int类型的数据时：

方案一：使用注解param来绑定参数名
@Param

List<Emp> findAll(@Param("eno") Integer empno);

<select id="findAll" resultMap="empAll" parameterType="int">
select * FROM
dept d
join emp e
on e.deptno=d.deptid
<where>
<if test="eno!=null and eno!=''">
and e.empno=#{eno}
</if>
 
</where>
</select>
 
 
原始的方法：
List<Emp> findAll(Integer empno);
<select id="findAll" resultMap="empAll" parameterType="int">
select * FROM
dept d
join emp e
on e.deptno=d.deptid
<where>
<if test="_parameter!=null and _parameter!=''">
and e.empno=#{1}
</if>
</where>
</select>

传递多个参数的方法：

/**
 * 根据部门的名称和地址  查询  部门的信息
 * 1.@Param  绑定参数
 */
List<Dept> queryByManyPram(@Param("dname") String dname, @Param("loc") String loc);
/**
 * 2.传过去一个实体
 */
List<Dept> queryByManyPram2(Dept dept);
/**
 * 3.传过去一个map
 */
List<Dept> queryByManyPram3(Map dept);
/**
 * 4.传两个参数
 */
List<Dept> queryByManyPram4( String dname,String loc);

<select id="queryByManyPram" resultType="com.aaa.entity.Dept">

    select * from dept
    <where>

    <if test="dname!=null and dname!=''">
        dname like concat('%',#{dname},'%')
    </if>
    <if test="loc!=null and loc!=''">
        and loc like concat('%',#{loc},'%') 
    </if>
    </where>
</select>
<select id="queryByManyPram2" resultType="com.aaa.entity.Dept">
    select * from dept
    <where>

        <if test="dname!=null and dname!=''">
            dname like concat('%',#{dname},'%')
        </if>
        <if test="loc!=null and loc!=''">
            and loc like concat('%',#{loc},'%') 
        </if>
    </where>
</select>
<select id="queryByManyPram3" resultType="com.aaa.entity.Dept">
    select * from dept
    <where>

    <if test="dname1!=null and dname1!=''">
        dname like  concat('%',#{dname},'%')
    </if>
    <if test="loc1!=null and loc1!=''">
        and loc like  concat('%',#{loc},'%') 
    </if>
    </where>
</select>
<select id="queryByManyPram4" resultType="com.aaa.entity.Dept">
    select * from dept
    <where>
        <if test="param1!=null and param1!=''">
            dname like  concat('%',#{param1},'%') 
        </if>
        <if test="param2!=null and param2!=''">
            and loc like  concat('%',#{param2},'%') 
        </if>
    </where>
</select>

测试：
@Test
public void testQuery2(){
    //
    List<Dept> empByNo = mapper.queryByManyPram("t","t");
    System.out.println(empByNo);
}
@Test
public void testQuery3(){
    //
    Dept d=new Dept();
    d.setDname("t");
    d.setLoc("t");
    List<Dept> empByNo = mapper.queryByManyPram2(d);
    System.out.println(empByNo);
}

@Test
public void testQuery4(){
    Map d=new HashMap();
    d.put("dname1","t");
    d.put("loc1","t");
    List<Dept> empByNo = mapper.queryByManyPram3(d);
    System.out.println(empByNo);
}

@Test
public void testQuery5(){
    List<Dept> empByNo = mapper.queryByManyPram4("t","t");
    System.out.println(empByNo);
}

3.3.2 choose, when, otherwise
传入一个emp对象，如果ename有值，按照ename进行查询。如果empno有值按照empno查询，其他情况按照工资sal大于3000的查询。

<select id="findEmpByEmp" parameterType="com.aaa.entity.Emp" resultType="com.aaa.entity.Emp">
select * from emp
<where>
<choose>
<when test="ename1!=null and ename1!=''">
and ename=#{ename1}
</when>
<when test="empno!=null and empno!=''">
and empno=#{empno}
</when>
<!--除此之外的情况-->
<otherwise>
and sal>3000
</otherwise>
</choose>
</where>
</select>

choose相当于java中的switch语句，每次只走一个分支。

在xml文件中尽量避免写大于，小于，大于等于，小于等于 &

解决方案

备选方案

<![CDATA[ 含有特殊符号的代码 > ]]>

3.3.3 trim, where, set
trim
trim标签一般用于去除sql语句中多余的and关键字，逗号，或者给sql语句前拼接 “where“、“set“以及“values(“ 等前缀，或者添加“)“等后缀，可用于选择性插入、更新、删除或者条件查询等操作。

int updateDept(Dept d);
xml文件：
<update id="updateDept" parameterType="dept">
    update dept
    <trim prefix="set" prefixOverrides=",">
        <if test="dname!=null and dname!=''">
            ,dname=#{dname}
        </if>
        <if test="loc!=null and loc!=''">
            ,loc=#{loc}
        </if>
    </trim>
    <where>
        deptno=#{deptno}
    </where>
 
 
UPDATE DEPT SET ,DNAME=? ,LOC=? WHERE DEPTNP=?

where 自动去除and

<select id="findDept"
     resultType="com.aaa.entity.Dept">
  SELECT * FROM Dept
 
  <where> 
    <if test="deptno!= null">
       and  deptid = #{deptno}
    </if> 
    <if test="loc != null">
        AND loc like #{loc}
    </if>
</where>
</select>

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。
如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>

<update id="updateDept">
update dept
<set>
<if test="dname!= null">dname=#{dname},</if>
<if test="loc!= null">loc=#{loc}</if>
</set>
where deptno=#{deptno}
</update>

3.3.4 foreach
批量删除
使用foreach实现批量删除

Collection 传入的参数是数组且没有别名 array
Open 一般 (
Close  一般)
separator 分割符号  ，
String【i】
 
item  数组每一次循环出来的值起的名字 跟#{}里面的那个变量保持一致
 
int delDeptByDeptno(int[] deptnos);
<!--参数 是一个数组 数组没有绑定参数的名字 collection=array-->
 
<delete id="delDeptByDeptno" >
delete from dept where deptno in
<foreach collection="array" separator="," open="(" close=")" item="deptno" index="i">
#{deptno}
</foreach>
</delete>

传入的数据是list的时候
int delDeptByDeptno(List<Integer> deptnos);
<!--参数 是一个list list没有绑定参数的名字 collection=list-->
<delete id="delDeptByDeptno" >
delete from dept where deptno in
<foreach collection="list" separator="," open="(" close=")" item="deptno" index="i">
#{deptno}
</foreach>
</delete>

3.2 mybatis调用存储过程

根据部门的编号获取部门的名称

存储过程：
 
 create or replace procedure pro_getNameByNo
(v_deptno dept.deptno%type,v_dname out dept.dname%type)
as
begin
    select dname into v_dname from dept where deptno=v_deptno;
end;

Map callPro(Map m);
调用存储过程的关键字是 call  mode=IN ,jdbcType
<select id="callPro" parameterType="map" resultType="map" statementType="CALLABLE">
    {call pro_getNameByNo(#{deptno,mode=IN,jdbcType=INTEGER},#{dname,mode=OUT,jdbcType=VARCHAR})}
</select>

@Test
public void callpro(){
     //调用存储过程
    Map m=new HashMap();
    m.put("deptno",10);
    m.put("dname","");
    //
    mapper.callPro(m);
    System.out.println(m.get("dname"));
}

3.3 返回自动增长的主键

oracle
接口：
int insertDept(Dept dept);
mapper文件：
<insert id="insertDept">
    <selectKey keyProperty="deptno" order="BEFORE" resultType="long" keyColumn="deptno" >
    select SEQ_QY121.nextval from dual
    </selectKey>
    insert into dept(deptno,dname,loc) values(#{deptno},#{dname},#{loc})

</insert>
测试类：
public void  insertDept(){
     Dept d=new Dept();
     d.setDname("test");
     d.setLoc("zhenghen");
     mapper.insertDept(d);
    System.out.println(d.getDeptno());
}
mysql：
<insert id="insertDept" useGeneratedKeys="true"  keyProperty="deptno">
    insert into dept(deptno,dname,loc) values(#{deptno},#{dname},#{loc})
</insert>