亲宝软件园·资讯

展开

association和collection用法

Javxuan 人气:1

association和collection用法

1.单个关联查询association

1.1实体之间的关联表示

package com.worldly.config.entity;
import java.io.Serializable;
/**
 * @Description
 * @Author xiaoqx <worldly_xuan@163.com>
 * @Version V1.0.0
 * @Since 2017/11/26
 */
public class Employee implements Serializable {
    private Integer id;
    private String name;
    private String email;
    private String tel;
    //关联的部门实体,查询某个人的时候可以把所在部门信息查询出来
    private Department dep;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public String getTel() {
        return tel;
    }
    public void setTel(String tel) {
        this.tel = tel;
    }
    public Department getDep() {
        return dep;
    }
    public void setDep(Department dep) {
        this.dep = dep;
    }
    @Override
    public String toString() {
        return "{\"Employee\":{"
                + "\"id\":\"" + id + "\""
                + ", \"name\":\"" + name + "\""
                + ", \"email\":\"" + email + "\""
                + ", \"tel\":\"" + tel + "\""
                + ", \"dep\":" + dep
                + "}}";
    }
}

1.2 两种关联查询方式

//第一中方式:直接进行关联查询把关联实体的属性在xml中配置
//然后关联查出来
<resultMap id="emp2ResultMap" type="com.worldly.config.entity.Employee">
        <id column="emp_id" property="id"></id>
        <result column="emp_name" property="name"/>
        <result column="emp_email" property="email"/>
        <result column="emp_tel" property="tel"/>
        <association property="dep" column="emp_dep" javaType="com.worldly.config.entity.Department">
            <id column="dep_id" property="id"/>
            <result column="dep_name" property="name"/>
            <result column="dep_addr" property="addr"/>
        </association>
    </resultMap>
    <select id="selectEmployAll" resultMap="emp2ResultMap">
        SELECT
            *
        FROM
            t_emp e
        INNER JOIN t_dep d ON e.emp_dep = d.dep_id
    </select>
//第二中查询方式,采用 association中的select来查询
<resultMap id="empResultMap" type="com.worldly.config.entity.Employee">
        <id column="emp_id" property="id"></id>
        <result column="emp_name" property="name"/>
        <result column="emp_email" property="email"/>
        <result column="emp_tel" property="tel"/>
        <association column="emp_dep" property="dep" javaType="com.worldly.config.entity.Department" select="selectDepByCondition"></association>
    </resultMap>
    <select id="selectEmployeeList" resultMap="empResultMap" databaseId="mysql">
        select * from t_emp
    </select>
    <resultMap id="depResultMap" type="com.worldly.config.entity.Department">
        <id column="dep_id" property="id"></id>
        <result column="dep_name" property="name"/>
        <result column="dep_addr" property="addr"/>
    </resultMap>
    <select id="selectDepByCondition" resultMap="depResultMap">
        SELECT
        *
        FROM
        t_dep d
        WHERE
        d.dep_id = #{emp_dep}
    </select>

1.3 两种方式的优劣

a.查询条件相同,所用的时间:从测试结果显示,关联查询要比嵌套查询快一点(结果不一定准确,可能关联表多的时候,结果会有所变化)

a.查询条件相同,所用的时间:从测试结果显示,关联查询要比嵌套查询快一点(结果不一定准确,可能关联表多的时候,结果会有所变化)

这里写图片描述

这里写图片描述

b.适用的情况

2.多个关联查询 collection

2.1实体之间的关联表示

package com.worldly.config.entity;
import java.util.List;
/**
 * @Description
 * @Author xiaoqx <worldly_xuan@163.com>
 * @Version V1.0.0
 * @Since 1.0
 * @Date 2017/12/16
 */
public class Department {
    private int id;
    private String name;
    private String addr;
    List<Employee> employeeList;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getNamel() {
        return name;
    }
    public void setNamel(String name) {
        this.name = name;
    }
    public String getAddr() {
        return addr;
    }
    public void setAddr(String addr) {
        this.addr = addr;
    }
    public List<Employee> getEmployeeList() {
        return employeeList;
    }
    public void setEmployeeList(List<Employee> employeeList) {
        this.employeeList = employeeList;
    }
    @Override
    public String toString() {
        return "{\"Department\":{"
                + "\"id\":\"" + id + "\""
                + ", \"name\":\"" + name + "\""
                + ", \"addr\":\"" + addr + "\""
                + ", \"employeeList\":" + employeeList
                + "}}";
    }
}

2.2 两种关联查询方式

//第一种方式嵌套查询
<resultMap id="depResultMap2" type="com.worldly.config.entity.Department">
        <id column="dep_id" property="id"></id>
        <result column="dep_name" property="name"/>
        <result column="dep_addr" property="addr"/>
        <collection column="dep_id" property="employeeList" javaType="java.util.List" ofType="com.worldly.config.entity.Employee"
           select="selectEmpBydepId"/>
    </resultMap>
    <select id="selectDepByCondition" resultMap="depResultMap2">
        SELECT
            *
        FROM
            t_dep d
        WHERE
            d.dep_id = #{param}
    </select>
    <resultMap id="empResultMap" type="com.worldly.config.entity.Employee">
        <id column="emp_id" property="id"></id>
        <result column="emp_name" property="name"/>
        <result column="emp_email" property="email"/>
        <result column="emp_tel" property="tel"/>
    </resultMap>
    <select id="selectEmpBydepId" resultMap="empResultMap">
        SELECT
            *
        FROM
            t_emp e
        WHERE
            e.emp_dep = #{dep_id}
    </select>
//第二中方式关联查询
<resultMap id="dep2ResultMap" type="com.worldly.config.entity.Department">
        <id column="dep_id" property="id"></id>
        <result column="dep_name" property="name"/>
        <result column="dep_addr" property="addr"/>
        <collection property="employeeList" ofType="com.worldly.config.entity.Employee">
            <id column="emp_id" property="id"></id>
            <result column="emp_name" property="name"/>
            <result column="emp_email" property="email"/>
            <result column="emp_tel" property="tel"/>
        </collection>
    </resultMap>
    <select id="selectDepWithEmp" resultMap="dep2ResultMap">
        SELECT
            *
        FROM
            t_dep d
        INNER JOIN t_emp e ON d.dep_id = e.emp_dep
        WHERE
            d.dep_id = #{param}
    </select>

2.3 多条件查询

    <resultMap id="depResultMap2" type="com.worldly.config.entity.Department">
        <id column="dep_id" property="id"></id>
        <result column="dep_name" property="name"/>
        <result column="dep_addr" property="addr"/>
        <result column="dep_status" property="status"/>
        <collection column="{depId=dep_id,status=dep_status}" property="employeeList" javaType="java.util.List" ofType="com.worldly.config.entity.Employee"
           select="selectEmpBydepId"/>
    </resultMap>
    <select id="selectDepByCondition" resultMap="depResultMap2">
        SELECT
            *
        FROM
            t_dep d
        WHERE
            d.dep_id = #{param}
    </select>
    <resultMap id="empResultMap" type="com.worldly.config.entity.Employee">
        <id column="emp_id" property="id"></id>
        <result column="emp_name" property="name"/>
        <result column="emp_email" property="email"/>
        <result column="emp_tel" property="tel"/>
    </resultMap>
    <select id="selectEmpBydepId" resultMap="empResultMap">
        SELECT
            *
        FROM
            t_emp e
        WHERE
            e.emp_dep = #{depId} AND e.emp_status=#{status}
    </select>

多条件查询,用{}来包装方法

这里写图片描述

3.鉴别器discriminator

3.1 鉴别器适用的场景

3.2 鉴别器的实现 

association和collection关联查询用法

这里只做最简单的用法,其它方法请自行查询;

一对多 collection

 <collection property="要查询的实体集合" javaType="java.util.List"
                    ofType="要查询的实体所在包路径"
                    select="要查询的mapper方法"
                    column="关联的实体中的字段=关联的数据库中的字段"/>

举例

 <collection property="stsManageStudentList" javaType="java.util.List"
                    ofType="com.crm.project.domain.StsManageStudent"
                    select="com.crm.project.mapper.StsManageStudentMapper.selectStsManageStudentList"
                    column="manageId=manage_id"/>

一对一 & 多对一

<association property="要查询的实体" column="数据库中的关联字段"
                     javaType="要查询的实体所在包路径"
                     select="要查询的mapper方法"/>

举例

<association property="stsStudent" column="student_id"
                     javaType="com.crm.project.domain.StsStudent"
                     select="com.crm.project.mapper.StsStudentMapper.selectStsStudentById"/>

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。

加载全部内容

相关教程
猜你喜欢
用户评论