mybatis 一对一 一对多映射关系

package com.manage.custorm.dao;

import java.util.List;

/**
 * @author bwx686385 bianqiang
 * @create 2020/1/17
 */
public class Dept {
    private String ID;
    private String NAME;
    private String DESCR;
    private List<MappingUser> allUser;
    public Dept() {
    }

    public String getID() {
        return ID;
    }

    public void setID(String ID) {
        this.ID = ID;
    }

    public String getNAME() {
        return NAME;
    }

    public void setNAME(String NAME) {
        this.NAME = NAME;
    }

    public String getDESCR() {
        return DESCR;
    }

    public void setDESCR(String DESCR) {
        this.DESCR = DESCR;
    }

    public List<MappingUser> getAllUser() {
        return allUser;
    }

    public void setAllUser(List<MappingUser> allUser) {
        this.allUser = allUser;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "ID='" + ID + '\'' +
                ", NAME='" + NAME + '\'' +
                ", DESCR='" + DESCR + '\'' +
                ", allUser=" + allUser +
                '}';
    }
}

 

 

package com.manage.custorm.dao;

/**
 * @author bwx686385 bianqiang
 * @create 2020/1/17
 */
public class MappingUser {
    private String ID;
    private String NAME;
    private String AGE;
    private String SEX;

    private String DESCR;

    private Dept dept;
    public MappingUser() {
    }
    public Dept getDept() {
        return dept;
    }

    public String getSEX() {
        return SEX;
    }

    public void setSEX(String SEX) {
        this.SEX = SEX;
    }

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



    public String getID() {
        return ID;
    }

    public void setID(String ID) {
        this.ID = ID;
    }

    public String getNAME() {
        return NAME;
    }

    public void setNAME(String NAME) {
        this.NAME = NAME;
    }

    public String getAGE() {
        return AGE;
    }

    public void setAGE(String AGE) {
        this.AGE = AGE;
    }

    public String getDESCR() {
        return DESCR;
    }

    public void setDESCR(String DESCR) {
        this.DESCR = DESCR;
    }
}

 

 

-- Create table
create table MAPPING_USER
(
  id     VARCHAR2(64) not null,
  name   VARCHAR2(64),
  sex    VARCHAR2(64),
  age    VARCHAR2(64),
  descr  VARCHAR2(255),
  deptid VARCHAR2(5)
)
tablespace OWS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Create table
create table DEPT
(
  id    VARCHAR2(64) not null,
  name  VARCHAR2(64),
  descr VARCHAR2(255)
)
tablespace OWS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

package com.manage.custorm.mapper;

import com.manage.custorm.dao.Dept;
import com.manage.custorm.dao.MappingUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface DeptMapper {
    Dept getById(@Param("id") String id);

    MappingUser getDeptByIdWithSelect(@Param("id") String id);

    Dept getAllInfoByIdWithResult(@Param("id") String id);


    public Dept getAllInfoByIdWithSelect(@Param("id") String id);

    public List<MappingUser> findUserByDeptId(@Param(value="deptId") int deptId);


}

 

<?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" >
<mapper namespace="com.manage.custorm.mapper.DeptMapper">

    <resultMap type="com.manage.custorm.dao.MappingUser" id="userDeptResultMapWithSelect">
        <!-- 普通的员工属性查询 -->
        <id property="ID" column="ID"/>
        <result property="NAME" column="NAME"/>
        <result property="SEX" column="SEX"/>
        <result property="AGE" column="AGE"/>
        <result property="DESCR" column="DESCR"/>
        <!-- 一对一的部门查询 ,传入参数是 deptId-->
        <association property="dept" javaType="dept"
                     column="deptId" select="com.manage.custorm.mapper.DeptMapper.getById"></association>
    </resultMap>

    <select id="getDeptByIdWithSelect" parameterType="java.lang.String" resultMap="userDeptResultMapWithSelect">
		select * from mapping_user u where u.id=#{id}
	</select>
    <resultMap type="com.manage.custorm.dao.Dept" id="deptResultMap">
        <id property="ID" column="ID"/>
        <result property="NAME" column="NAME"/>
        <result property="DESCR" column="DESCR"/>
    </resultMap>

    <!-- 嵌套查询 -->
    <select id="getById" parameterType="java.lang.String" resultMap="deptResultMap">
		select * from dept where id=#{id}
	</select>

    <resultMap type="com.manage.custorm.dao.Dept" id="deptCollectionResultMapWithResult">
        <id property="ID" column="ID"/>
        <result property="NAME" column="NAME"/>
        <result property="DESCR" column="DESCR"/>
        <!-- 用的是ofType的类型。 -->
        <collection property="allUser" ofType="com.manage.custorm.dao.MappingUser">
            <id property="ID" column="ID"/>
            <result property="NAME" column="NAME"/>
            <result property="SEX" column="SEX"/>
            <result property="AGE" column="AGE"/>
            <result property="DESCR" column="DESCR"/>
        </collection>
    </resultMap>
    <select id="getAllInfoByIdWithResult" parameterType="java.lang.String"
            resultMap="deptCollectionResultMapWithResult">
		select * from dept d,mapping_user u where d.id=u.deptId and d.id=#{id}
	</select>


    <resultMap type="com.manage.custorm.dao.Dept" id="deptCollectionResultMapWithSelect">
        <id property="ID" column="ID"/>
        <result property="NAME" column="NAME"/>
        <result property="DESCR" column="DESCR"/>
        <!-- 用的是ofType的类型。 -->
        <collection property="allUser" ofType="com.manage.custorm.dao.MappingUser" column="id"
                    select="com.manage.custorm.mapper.DeptMapper.findUserByDeptId"></collection>
    </resultMap>

    <select id="getAllInfoByIdWithSelect" parameterType="java.lang.String"
            resultMap="deptCollectionResultMapWithSelect">
		select * from dept where id=#{id}
</select>


    <resultMap type="com.manage.custorm.dao.MappingUser" id="userResultMap">
        <id property="ID" column="ID"/>
        <result property="NAME" column="NAME"/>
        <result property="SEX" column="SEX"/>
        <result property="AGE" column="AGE"/>
        <result property="DESCR" column="DESCR"/>
    </resultMap>
    <select id="findUserByDeptId" parameterType="java.lang.String" resultMap="userResultMap">
		select * from mapping_user u where u.deptId=#{deptId}
</select>


</mapper>

 

 

@GetMapping("/getBaseInfo")
	public Result<Object> getBaseInfo(@RequestParam Map<String,Object> params) {

//		MappingUser mapUser=deptMapper.getDeptByIdWithSelect("1");

//		Dept dept=deptMapper.getAllInfoByIdWithResult("1");
		Dept dept=deptMapper.getAllInfoByIdWithSelect("1");
		return Result.buildResult(Result.Status.OK,dept);
	}