mybatis关联查询(个人笔记)

ゞ浴缸里的玫瑰 2023-10-05 11:27 31阅读 0赞

#### mybatis关联查询 ####

*  一对一(association)
 *   *  pojo实体类：
     *  接口：
     *  mapper映射文件：
 *  一对多(collection)
 *   *  pojo实体类:
     *  接口：
     *  mapper映射文件：
 *  多对多
 *   *  pojo实体类(两个)：
     *  接口(查询员工)：
     *  mapper映射文件（查询员工）：

## 一对一(association) ##

### pojo实体类： ###

public class Employee implements Serializable {
        
    	private static final long serialVersionUID = 1L;
    	private Integer empId;
    	private String name;
    	private Integer gender;
    	private Date birthday;
    	private String mobile;
    	private String email;
    	private String position;
    	private String note;
    	private WorkCard workcard;
    ...
    }

### 接口： ###

public Employee selectEmployeeByid(int id);

### mapper映射文件： ###

<select id="selectEmployeeByid" parameterType="integer" 
    resultMap="employeeByid">
    	select employee.*,workcard.realname,workcard.address from employee,workcard where employee.empid=workcard.empid and employee.empid=#{empid};
    </select>
    
    <resultMap type="employee" id="employeeByid">
    <id property="empId" column="empid"/>
    <result property="name" column="name"/>
    <result property="gender" column="gender"/>
    <result property="birthday" column="birthday"/>
    <result property="mobile" column="mobile"/>
    <result property="email" column="email"/>
    <result property="note" column="note"/>
    <association property="workcard"  javaType="com.zwh.pojo.WorkCard">
    <id property="cardid" column="cardid"/>
    <result property="empid" column="empid"/>
    <result property="realname" column="realname"/>
    <result property="address" column="address"/>
    </association>
    </resultMap>

## 一对多(collection) ##

### pojo实体类: ###

public class EmployeeList implements Serializable{
        
    
    	private static final long serialVersionUID = 1L;
    	private Integer empId;
    	private String name;
    	private Integer gender;
    	private Date birthday;
    	private String mobile;
    	private String email;
    	private String position;
    	private String note;
    	private List<salary> salaries;
    ...
    }

### 接口： ###

public EmployeeList selectEmployeeList(int id);

### mapper映射文件： ###

<select id="selectEmployeeList" parameterType="integer" resultMap="relationmap">
    	SELECT employee.*,employee_salary.month,employee_salary.salary 
    	FROM employee LEFT JOIN employee_salary ON employee.empid=employee_salary.empid 
    	where employee.empid=#{id}
    </select>
    
    <resultMap type="employeelist" id="relationmap">
    <id property="empId" column="empid"/>
    <result property="name" column="name"/>
    <result property="gender" column="gender"/>
    <result property="birthday" column="birthday"/>
    <result property="mobile" column="mobile"/>
    <result property="email" column="email"/>
    <result property="note" column="note"/>
    <collection property="salaries" ofType="salary" >
    <id property="salaryid" column="salaryid"/>
    <result property="empid" column="empid"/>
    <result property="month" column="month"/>
    <result property="salary" column="salary"/>
    </collection>
    </resultMap>

## 多对多 ##

一个员工可以对应多个角色  
一个角色也可以对应多个员工  
所以他们时多对多关系

当我们查询员工时，应该映射出该员工的多个角色  
当我们查询角色时，应该映射出该角色对应的所有员工  
所以我们要再两个实体类中添加对应的List< Object >属性

### pojo实体类(两个)： ###

EmployeeDdd

public class EmployeeDdd implements Serializable {
        
    
    	private static final long serialVersionUID = 8284315305946783432L;
    	private Integer empId;
    	private String name;
    	private Integer gender;
    	private Date birthday;
    	private String mobile;
    	private String email;
    	private String position;
    	private String note;
    	private List<Role> roles;
    ...
    }

Role

public class Role implements Serializable{
        
    
    	private static final long serialVersionUID = -149256487064553912L;
    	private int roleid;
    	private int empid;
    	private String rolename;
    	private List<Employee> employees;
    ...
    }

### 接口(查询员工)： ###

public EmployeeDdd selectEmployeeddd(int id);

### mapper映射文件（查询员工）： ###

<select id="selectEmployeeddd" parameterType="integer" resultMap="dddmap">
    SELECT * FROM employee,role WHERE employee.empid=role.empid and employee.empid=#{id}
    </select>
    
    <resultMap type="employeeddd" id="dddmap">
    <id property="empId" column="empid"/>
    <result property="name" column="name"/>
    <result property="gender" column="gender"/>
    <result property="birthday" column="birthday"/>
    <result property="mobile" column="mobile"/>
    <result property="email" column="email"/>
    <result property="note" column="note"/>
    <collection property="roles" ofType="role">
    <id property="roleid" column="roleid"/>
    <result property="empid" column="empid"/>
    <result property="rolename" column="rolename"/>
    </collection>
    </resultMap>

按照角色查询出员工信息也是类似，这里就不写了。