实体类
员工表
package com.Company.domain;
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private double mgr;
private Date hiredate;
private double sal;
private double comm;
private Dept dp;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public double getMgr() {
return mgr;
}
public void setMgr(double mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public Dept getDp() {
return dp;
}
public void setDp(Dept dp) {
this.dp = dp;
}
public Emp(int empno, String ename, String job, double mgr, Date hiredate, double sal, double comm, Dept dp) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.dp = dp;
}
public Emp() {
super();
}
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
+ ", sal=" + sal + ", comm=" + comm + ", dp=" + dp + "]";
}
}
部门表
package com.Company.domain;
import java.util.List;
public class Dept {
private int deptno;
private String dname;
private String loc;
private List<Emp> empList;
public List<Emp> getEmpList() {
return empList;
}
public void setEmpList(List<Emp> empList) {
this.empList = empList;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
@Override
public String toString() {
return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + ", empList=" + empList + "]";
}
public Dept() {
super();
}
public Dept(int deptno, String dname, String loc) {
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
}
一、Xml方式
1、多表连接查询,只需要查一次
一对一关系
EmpMapper.xml映射配置文件,查询员工对应的部门的信息。
<?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.Company.mapper.EmpMapper">
<resultMap type="com.Company.domain.Emp" id="empsMap">
<id column="empno" property="empno" />
<result column="ename" property="ename" />
<result column="job" property="job" />
<result column="mgr" property="mgr" />
<result column="hiredate" property="hiredate" />
<result column="sal" property="sal" />
<result column="comm" property="comm" />
<!-- 查询员工对应的部门表 -->
<association property="dp" javaType="com.Company.domain.Dept" column="deptno">
<id column="deptno" property="deptno" />
<result column="dname" property="dname" />
<result column="loc" property="loc" />
</association>
</resultMap>
<!-- sql多表连接查询语句 -->
<select id="findAllEmpsAndDept" resultMap="empsMap">
select * from emp,dept where emp.deptno = dept.deptno
</select>
</mapper>
一对多关系
DeptMapper.xml映射配置文件,查询部门下的所有员工信息。
<?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.Company.mapper.DeptMapper">
<resultMap type="com.Company.domain.Dept" id="deptsMap">
<id column="deptno" property="deptno" />
<result column="dname" property="dname" />
<result column="loc" property="loc" />
<!-- 一对多关系 -->
<!-- 每个部门所对应的员工集合 -->
<collection property="empList" ofType="com.Company.domain.Emp">
<id column="empno" property="empno" />
<result column="ename" property="ename" />
<result column="job" property="job" />
<result column="mgr" property="mgr" />
<result column="hiredate" property="hiredate" />
<result column="sal" property="sal" />
<result column="comm" property="comm" />
</collection>
</resultMap>
<!-- 一对多查询 -->
<select id="findAllEmpsInDept" resultMap="deptsMap">
select * from dept,emp where dept.deptno =emp.deptno </select>
</mapper>
2、多表子查询,需要查询两次
一对一关系
EmpMapper.xml映射配置文件,查询员工对应的部门的信息。
<?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.Company.mapper.EmpMapper">
<resultMap type="com.Company.domain.Emp" id="empsMap">
<id column="empno" property="empno" />
<result column="ename" property="ename" />
<result column="job" property="job" />
<result column="mgr" property="mgr" />
<result column="hiredate" property="hiredate" />
<result column="sal" property="sal" />
<result column="comm" property="comm" />
<!-- 一对一关系,使用的是DeptMapper.xml映射配置文件中的findDeptByDeptno查询语句 -->
<association property="dp" column="deptno"
javaType="com.Company.domain.Dept"
select="com.Company.mapper.DeptMapper.findDeptByDeptno">
</association>
</resultMap>
<select id="findAllEmpsAndDept" resultMap="empsMap">
select * from emp
</select>
<!-- 通过部门号查询emp员工信息 -->
<select id="findAllEmpsInDeptByDeptno" parameterType="Integer"
resultType="com.Company.domain.Emp">
select * from emp where deptno = #{deptno}
</select>
</mapper>
一对多关系
DeptMapper.xml映射配置文件,查询部门下的所有员工信息。
<?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.Company.mapper.DeptMapper">
<resultMap type="com.Company.domain.Dept" id="deptsMap">
<id column="deptno" property="deptno" />
<result column="dname" property="dname" />
<result column="loc" property="loc" />
<!-- 一对多关系,使用的是EmpMapper.xml映射配置文件中的findAllEmpsInDeptByDeptno语句 -->
<collection property="empList" column="deptno"
ofType="com.Company.domain.Emp"
select="com.Company.mapper.EmpMapper.findAllEmpsInDeptByDeptno">
</collection>
</resultMap>
<!-- 一对多查询 -->
<select id="findAllEmpsInDept" resultMap="deptsMap">
select * from dept
</select>
<!-- 通过部门号查询dept部门信息 -->
<select id="findDeptByDeptno" parameterType="Integer"
resultType="com.Company.domain.Dept">
select * from dept where deptno = #{deptno}
</select>
</mapper>
二、注解方式
只能使用多表子查询,需要查询两次
一对一关系
EmpMapper.java文件
package com.Company.mapper;
import java.util.List;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.Company.domain.Emp;
public interface EmpMapper {
@Select("select * from emp")
@Results(value = { @Result(column = "empno", property = "empno"),
@Result(column = "ename", property = "ename"),
@Result(column = "job", property = "job"),
@Result(column = "mgr", property = "mgr"),
@Result(column = "sql", property = "sql"),
@Result(column = "comm", property = "comm"),
@Result(column = "deptno", property = "dp",
//使用的是DeptMapper.java文件中的findDeptByNo方法
one = @One(select = "com.Company.mapper.DeptMapper.findDeptByNo")) })
public List<Emp> findAllEmpsAndDept();
// 通过部门编号查找员工
@Select("select * from emp where deptno=#{deptno}")
public List<Emp> findAllEmpsInDeptByNo(int deptno);
}
一对多关系
DeptMapper.java文件
package com.Company.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.Company.domain.Dept;
public interface DeptMapper {
@Select("select * from dept")
@Results(value= {
@Result(column="deptno",property="deptno"),
@Result(column="dname",property="dname"),
@Result(column="loc",property="loc"),
@Result(column="deptno",property="empList",
//使用的是EmpMapper.java文件中的findAllEmpsInDeptByNo方法
many=@Many(select="com.Company.mapper.EmpMapper.findAllEmpsInDeptByNo"))
})
public List<Dept> findAllEmpsInDept();
@Select("select * from dept where deptno=#{deptno}")
public Dept findDeptByNo(int deptno);
}