[大数据]mybatis中动态Sql标签库和ResultMap

RESULTMAP

?作用:自定义映射关系，一般当表字段与实体类属性不一样时使用（例如 Emp实体类和表t_emp）

package com.atchengdu.mybatis.dao;

public class Emp {
    private  Integer eid;
    private String ename;
    private  Integer age;
    private  String  sex;
    private String email;
    private Integer did;
    private Dept dept;

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    public Emp(Integer eid, String ename, Integer age, String sex, String email, Integer did) {
        this.eid = eid;
        this.ename = ename;
        this.age = age;
        this.sex = sex;
        this.email = email;
        this.did = did;
    }

    public Emp() {

    }

    public Integer getEid() {
        return eid;
    }

    public void setEid(Integer eid) {
        this.eid = eid;
    }

    public String getEname() {
        return ename;
    }

    public void setEname(String ename) {
        this.ename = ename;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getDid() {
        return did;
    }

    public void setDid(Integer did) {
        this.did = did;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "eid=" + eid +
                ", ename='" + ename + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", email='" + email + '\'' +
                ", did=" + did +
                ", dept=" + dept +
                '}';
    }
}

?上面两个表的字段有一处不对应，在执行sql的时候如果不设置关系默认返回值：null

?

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--SQL标签-->
<mapper namespace="com.atchengdu.mybatis.mapper.Empmapper">
<!-- List<Emp>getallEmp();-->
    <select id="getallEmp" resultType="emp">
        select eid,e_name ename,age,sex,email,did from t_emp;
    </select>

    <!--List<Emp>getallEmpmap();-->
    <resultMap id="empmap" type="emp">
        <!--id设置主键关系-->
        <id property="eid" column="eid"></id>
        <!--result设置字段关系-->
        <result property="ename" column="e_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="did" column="did"></result>
    </resultMap>
    <select id="getallEmpmap" resultMap="empmap">
       select *from t_emp;
    </select>
   <!-- Emp getempanddept(Integer eid);-->
    <resultMap id="empmapanddept" type="emp">
        <!--id设置主键关系-->
        <id property="eid" column="eid"></id>
        <!--result设置字段关系-->
        <result property="ename" column="e_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="did" column="did"></result>
<!--        <result property="dept.did" column="did"></result>
        <result property="dept.dname" column="d_name"></result>-->
        <association property="dept" javaType="Dept">
            <id property="did" column="did"></id>
            <result property="dname" column="d_name"></result>
        </association>
    </resultMap>

    <select id="getempanddept" resultMap="empmapanddept">
     select *from  t_emp left join t_dept on t_emp.did =t_dept.did where t_emp.eid=#{eid};
    </select>


  <!--  Emp getempbystepone(Integer eid);-->
    <resultMap id="getempbystep" type="emp">
        <!--id设置主键关系-->
        <id property="eid" column="eid"></id>
        <!--result设置字段关系-->
        <result property="ename" column="e_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="did" column="did"></result>
        <association property="dept"  select="com.atchengdu.mybatis.mapper.Deptmapper.getDeptbystetwo"
               fetchType="eager"       column="did">
        </association>
        <!-- fetchType 设置是否延迟加载  select:执行的sql的方法名  column:条件  -->
    </resultMap>
    <select id="getempbystepone" resultMap="getempbystep">
        select *from t_emp where eid=#{eid};
    </select>

  <!-- List<Emp> getEmpbydid(@Param("did")Integer did);-->

    <select id="getEmpbydid" resultMap="empmap">
        select  *from t_emp where  did=#{did};
    </select>

</mapper>

package com.atchengdu.mybatis.mapper;

import com.atchengdu.mybatis.dao.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface Empmapper {
    //查询所有的信息
    List<Emp>getallEmp();
    List<Emp>getallEmpmap();

    //查询员工信息和部门信息
    Emp getempanddept(Integer eid);

    //分布查询
    Emp getempbystepone(@Param("eid") Integer eid);
    //查询员工信息
    List<Emp> getEmpbydid(@Param("did")Integer did);

}

动态SQL标签库（实现批量操作与多条件查询）

package com.atchengdu.mybatis.mapper;

import com.atchengdu.mybatis.dao.Emp;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface Dynaminmapper {
  //实现多条件查询
  List<Emp>getEmpbycondition(Emp emp);
  //测试chose when otherwise
  List<Emp>getEmpbychose(Emp emp);

  //实现批量删除
  void deleteEmpbyArray( @Param("eids") Integer [] eids);

  //实现批量添加的功能
  void insertmorebylist(@Param("emps") List<Emp>emps);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--SQL标签-->
<mapper namespace="com.atchengdu.mybatis.mapper.Dynaminmapper">
   <!-- List<Emp>getEmpbycondition(Emp emp);-->
    <resultMap id="empmap" type="emp">
        <!--id设置主键关系-->
        <id property="eid" column="eid"></id>
        <!--result设置字段关系-->
        <result property="ename" column="e_name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <result property="email" column="email"></result>
        <result property="did" column="did"></result>
    </resultMap>

    <!--动态Sql就是mybatis中的判断标签-->
  <!--  <select id="getEmpbycondition" resultMap="empmap">
        select  *from t_emp where 1=1
         <if test="ename!=null and ename!=''">
             e_name=#{ename}
         </if>
        <if test="age!=null and age!=''">
           and  age=#{age}
        </if>
        <if test="email!=null and email!=''">
            and  email=#{email}
        </if>
    </select>-->
       <!--where标签 当if条件有一个满足时就会生成where标签
          但是where标签不能将and写在条件后面
         -->
<!--    <select id="getEmpbycondition" resultMap="empmap">
        select  *from t_emp
        <where>
            <if test="ename!=null and ename!=''">
               and  e_name=#{ename}
            </if>
            <if test="age!=null and age!=''">
                and  age=#{age}
            </if>
            <if test="email!=null and email!=''">
                and  email=#{email}
            </if>
            <if test="did!=null and did!=''">
                or  did=#{did}
            </if>
        </where>
    </select>-->
    <select id="getEmpbycondition" resultMap="empmap">
        select  *from t_emp
        <trim prefix="where" suffixOverrides="and|or">
            <if test="ename!=null and ename!=''">
                  e_name=#{ename} and
            </if>
            <if test="age!=null and age!=''">
                  age=#{age} and
            </if>
            <if test="email!=null and email!=''">
                 email=#{email} and
            </if>
            <if test="did!=null and did!=''">
                 did=#{did}   or
            </if>
        </trim>
    </select>

  <!--  List<Emp>getEmpbychose(Emp emp);-->
    <select id="getEmpbychose" resultMap="empmap">
        select *from t_emp
        <where>
            <choose>
                <when test="ename!=null and ename !=''">
                    e_name=#{ename}
                </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=#{did}
                </otherwise>
            </choose>
        </where>
    </select>

    <!--void deleteEmpbyArray(Integer [] eids);-->
    <delete id="deleteEmpbyArray">
        delete  from t_emp where eid in
          <foreach collection="eids" item="eid" separator="," open="(" close=")">
              #{eid}
    </foreach>
    </delete>
    <!--void insertmorebylist( List<Emp>emps);-->
    <insert id="insertmorebylist">
        insert  into t_emp values
         <foreach collection="emps" item="emp" separator=",">
             (null,#{emp.ename},#{emp.age},#{emp.sex},#{emp.email},#{emp.did})
         </foreach>
    </insert>
</mapper>