mybatis关联查询(个人笔记)

一对一(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>

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