查询一个dept部门的部门信息和该部门下所有员工信息（这是一对多，一个部门下有多个员工）
Emp
package domain;
public class Emp {
//自有属性
private Integer empno;
private String ename;
private Float sal;
//关联属性 deptno-->外键-->对象
private Dept dept;
public Emp() {}
public Emp(Integer empno, String ename, Float sal, Dept dept) {
this.empno = empno;
this.ename = ename;
this.sal = sal;
this.dept = dept;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", sal=" + sal +
", dept=" + dept +
'}';
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Float getSal() {
return sal;
}
public void setSal(Float sal) {
this.sal = sal;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
}
Dept
package domain;
import java.util.List;
public class Dept {
//自有属性
private Integer deptno;
private String dname;
private String loc;
//为了根据deptno部门编号查询 部门中所有的人更加方便
//也存储一个关联属性 一个部门中有好多员工 List<Emp>
private List<Emp> empList;
public Dept() {}
public Dept(Integer deptno, String dname, String loc, List<Emp> empList) {
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
this.empList = empList;
}
@Override
public String toString() {
return "Dept{" +
"deptno=" + deptno +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
", empList=" + empList +
'}';
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer 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;
}
public List<Emp> getEmpList() {
return empList;
}
public void setEmpList(List<Emp> empList) {
this.empList = empList;
}
}
DeptDao
public class DeptDao {
private SqlSession sqlSession = MyUtil.getSqlSession(true);
//设计一个方法 根据给定的deptno部门编号 查询部门信息+这个部门中的所有员工信息
public Dept selectOne(Integer deptno){
return sqlSession.selectOne("selectOne",deptno);
}
//设计一个方法 查询所有部门的信息 + 每一个部门中的所有员工信息
public List<Dept> selectAll(){
return sqlSession.selectList("selectAll");
}
}
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="dao.DeptDao">
<!-- 方式一 利用两条语句 分别查询 也出现N+1问题 设置延迟加载的方式解决 -->
<!--<resultMap id="selectDept" type="domain.Dept">-->
<!--<id property="deptno" column="deptno"></id>-->
<!--<result property="dname" column="dname"></result>-->
<!--<result property="loc" column="loc"></result>-->
<!--<collection property="empList" javaType="list" ofType="domain.Emp" select="selectEmpForDept" column="deptno"></collection>-->
<!--</resultMap>-->
<!--<select id="selectEmpForDept" resultType="domain.Emp">-->
<!--select empno,ename,sal,deptno from emp where deptno = #{deptno}-->
<!--</select>-->
<!-- 方式二 利用联合查询 一次性将所有记录都查到 直接赋值就可以啦 -->
<resultMap id="selectDept" type="domain.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
<collection property="empList" javaType="list" ofType="domain.Emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="sal" column="sal"></result>
</collection>
</resultMap>
<!--========================================================-->
<select id="selectOne" resultMap="selectDept">
<!-- 方式一 -->
<!--select deptno,dname,loc from dept where deptno = #{deptno}-->
<!-- 方式二 -->
select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e right outer join dept d on e.deptno = d.deptno where d.deptno = #{deptno}
</select>
<select id="selectAll" resultMap="selectDept">
<!-- 方式一 -->
<!--select deptno,dname,loc from dept-->
<!-- 方式二 -->
select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e right outer join dept d on e.deptno = d.deptno
</select>
</mapper>
EmpDao
package dao;
import domain.Emp;
import org.apache.ibatis.session.SqlSession;
import util.MyUtil;
import java.util.List;
public class EmpDao {
//一个sqlSession对象
private SqlSession sqlSession = MyUtil.getSqlSession(true);
//设计一个方法 提供一个empno员工编号 查询员工的信息+部门信息
public Emp selectOne(Integer empno){
return sqlSession.selectOne("selectOne",empno);
}
//设计一个方法 提供一个查询所有员工信息+部门信息
public List<Emp> selectAll(){
return sqlSession.selectList("selectAll");
}
}
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="dao.EmpDao">
<!--自定义规则 方式一 两条语句查询-->
<!--<resultMap id="selectEmp" type="domain.Emp">-->
<!--<id property="empno" column="empno"></id>-->
<!--<result property="ename" column="ename"></result>-->
<!--<result property="sal" column="sal"></result>-->
<!--<association property="dept" javaType="domain.Dept" select="selectDeptForEmp" column="deptno"></association>-->
<!--</resultMap>-->
<!--<select id="selectDeptForEmp" resultType="domain.Dept">-->
<!--select deptno,dname,loc from dept where deptno = #{deptno}-->
<!--</select>-->
<!--自定义规则 方式二 一条语句查询 联合-->
<resultMap id="selectEmp" type="domain.Emp">
<id property="empno" column="empno"></id>
<result property="ename" column="ename"></result>
<result property="sal" column="sal"></result>
<association property="dept" javaType="domain.Dept">
<id property="deptno" column="deptno"></id>
<result property="dname" column="dname"></result>
<result property="loc" column="loc"></result>
</association>
</resultMap>
<!--=======================================================-->
<select id="selectOne" resultMap="selectEmp">
<!--方式一-->
-- select empno,ename,sal,deptno from emp where empno = #{empno}
<!--方式二-->
select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e left outer join dept d on e.deptno = d.deptno where e.empno = #{empno}
</select>
<select id="selectAll" resultMap="selectEmp">
<!--方式一-->
-- select empno,ename,sal,deptno from emp
<!--方式二-->
select e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc from emp e left outer join dept d on e.deptno = d.deptno
</select>
</mapper>
configuration.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/testmybatis?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--<mapper resource="mapper/EmpMapper.xml"></mapper>-->
<mapper resource="mapper/DeptMapper.xml"></mapper>
</mappers>
</configuration>
网友评论