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>
按照角色查询出员工信息也是类似,这里就不写了。