MyBatis 多对一关联和一对多关联
【摘要】
关联.多对一关联查询
package org.mybatis.example.dao; import java.util.Date;//雇员类public class Emp { private Integer empno; private String ename; private String job; private Integ...
关联.多对一关联查询
-
package org.mybatis.example.dao;
-
-
import java.util.Date;
-
//雇员类
-
public class Emp {
-
private Integer empno;
-
private String ename;
-
private String job;
-
private Integer mgr;
-
private Date hiredate;
-
private Integer sal;
-
private Integer comm;
-
private Dept dept;
-
public Emp() {
-
// TODO Auto-generated constructor stub
-
}
-
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 String getJob() {
-
return job;
-
}
-
public void setJob(String job) {
-
this.job = job;
-
}
-
public Integer getMgr() {
-
return mgr;
-
}
-
public void setMgr(Integer mgr) {
-
this.mgr = mgr;
-
}
-
public Date getHiredate() {
-
return hiredate;
-
}
-
public void setHiredate(Date hiredate) {
-
this.hiredate = hiredate;
-
}
-
public Integer getSal() {
-
return sal;
-
}
-
public void setSal(Integer sal) {
-
this.sal = sal;
-
}
-
public Integer getComm() {
-
return comm;
-
}
-
public void setComm(Integer comm) {
-
this.comm = comm;
-
}
-
public Dept getDept() {
-
return dept;
-
}
-
public void setDept(Dept dept) {
-
this.dept = dept;
-
}
-
-
}
//EmpMapper.java接口
-
package org.mybatis.example.dao;
-
import java.util.List;
-
-
public interface EmpMapper {
-
public List<Emp>selectManytoOne();
-
}
关联关系，体现的是两个类之间的一种强依赖关系。比如在员工类中，有一个属性是部门类的对象；先看第一种 嵌套查询：
通过执行另外一个SQL映射语句来返回语气的复杂类型。
//整体mybatis配置文件
-
<?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>
-
<properties resource="db.properties"/>
-
<environments default="development">
-
<environment id="development">
-
<transactionManager type="JDBC" />
-
<dataSource type="POOLED">
-
<property name="driver" value="${driver}"/>
-
<property name="url" value="${url}"/>
-
<property name="username" value="${username}"/>
-
<property name="password" value="${password}"/>
-
</dataSource>
-
</environment>
-
</environments>
-
<mappers>
-
<mapper resource="org/mybatis/example/dao/DeptMapper.xml"/>
-
<mapper resource="org/mybatis/example/dao/EmpMapper.xml"/>
-
</mappers>
-
</configuration>
//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="org.mybatis.example.dao.EmpMapper">
-
<resultMap type="org.mybatis.example.dao.Emp" id="getEmpresultMap">
-
<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="dept" column="deptno"
-
javaType="org.mybatis.example.dao.Dept">
-
<id column="deptno" property="deptno"/>
-
<result column="dname" property="dname"/>
-
<result column="loc" property="loc"/>
-
</association>
-
</resultMap>
-
<select id="selectManytoOne" resultMap="getEmpresultMap">
-
select
-
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
-
e.deptno,d.dname,d.loc
-
from emp e left join dept d on e.deptno=d.deptno
-
</select>
-
</mapper>
//测试类
-
import java.util.List;
-
-
import org.apache.ibatis.session.SqlSession;
-
import org.mybatis.example.dao.Emp;
-
import org.mybatis.example.dao.EmpMapper;
-
import org.mybatis.example.dao.SqlSessionFactoryUtil;
-
-
-
public class Test21 {
-
public static void main(String[] args) {
-
SqlSession session=SqlSessionFactoryUtil.getSqlSession();
-
EmpMapper empmapper=session.getMapper(EmpMapper.class);
-
List<Emp>empList=empmapper.selectManytoOne();
-
for(Emp emp:empList){
-
System.out.println(emp.getEname()+"的部门是："+emp.getDept().getDname());
-
}
-
}
-
}
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="org.mybatis.example.dao.EmpMapper">
-
<resultMap id="empResult" type="org.mybatis.example.dao.Emp">
-
<association property="dept" column="deptno"
-
javaType="org.mybatis.example.dao.Dept" select="selectDept"/>
-
</resultMap>
-
<select id="selectEmp" parameterType="int" resultMap="empResult">
-
select * from emp where empno=#{id}
-
</select>
-
<select id="selectDept" parameterType="int"
-
resultType="org.mybatis.example.dao.Dept">
-
select * from dept where deptno=#{id}
-
</select>
-
<select id="selectManytoOne" resultMap="getEmpresultMap">
-
select
-
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
-
e.deptno,d.dname,d.loc
-
from emp e left join dept d on e.deptno=d.deptno
-
</select>
-
</mapper>
EmpMapper.java接口
增加方法：public List<Emp>selectEmp(int id);
测试类
-
public static void main(String[] args) {
-
SqlSession session=SqlSessionFactoryUtil.getSqlSession();
-
EmpMapper empmapper=session.getMapper(EmpMapper.class);
-
List<Emp>empList=empmapper.selectEmp(2);
-
System.out.println(empList.size());
-
for(Emp emp:empList){
-
System.out.println(emp.getEname()+"的部门是："+emp.getDept().getDname());
-
}
-
}
但是这种方法，在查询的时候只能孤立的查询某个员工id比较复杂，可以使用如下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="org.mybatis.example.dao.EmpMapper">
-
<resultMap type="org.mybatis.example.dao.Emp" id="getEmpresultMap">
-
<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="dept" column="deptno"
-
javaType="org.mybatis.example.dao.Dept" resultMap="deptresultmap">
-
</association>
-
</resultMap>
<resultMap type="org.mybatis.example.dao.Dept" id="deptresultmap"> <resultMap type="org.mybatis.example.dao.Dept" id="deptresultmap">
<id column="deptno" property="deptno"/> <result column="dname" property="dname"/> <result column="loc" property="loc"/>
-
</result>
-
<select id="selectManytoOne" resultMap="getEmpresultMap">
-
select
-
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
-
e.deptno,d.dname,d.loc
-
from emp e left join dept d on e.deptno=d.deptno
-
</select>
-
</mapper>
</result>
<select id="selectManytoOne" resultMap="getEmpresultMap">
select
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
e.deptno,d.dname,d.loc
from emp e left join dept d on e.deptno=d.deptno
</select>
</mapper>
//一对多关联集合查询
重写下Dept类
增加多的一方的集合属性，private List<Emp>emps;并且添加相应的getter/setter方法;
重新配置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="org.mybatis.example.dao.DeptMapper">
-
<resultMap type="org.mybatis.example.dao.Dept" id="deptresultMap">
-
<id column="deptno" property="deptno"/>
-
<result column="dname" property="dname"/>
-
<result column="loc" property="loc"/>
-
<collection property="emps" ofType="org.mybatis.example.dao.Emp"
-
resultMap="empresultmap">
-
</collection>
-
</resultMap>
-
<resultMap type="org.mybatis.example.dao.Emp" id="empresultmap">
-
<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"/>
-
</resultMap>
-
<select id="selectOnetoMany" resultMap="deptresultMap">
-
select
-
e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,
-
e.deptno,d.dname,d.loc
-
from emp e left join dept d on e.deptno=d.deptno
-
</select>
-
</mapper>
测试类代码
-
public static void main(String[] args) {
-
SqlSession session=SqlSessionFactoryUtil.getSqlSession();
-
DeptMapper deptmapper=session.getMapper(DeptMapper.class);
-
List<Dept>deptList=deptmapper.selectOnetoMany();
-
for(Dept dept:deptList){
-
System.out.println("部门名称："+dept.getDname());
-
System.out.println("所属员工的个数："+dept.getEmps().size());
-
}
-
}
常见错误：java.lang.IllegalArgumentException: argument type mismatch,在Emp类中设置Dept 类型的dept属性，可能设置成了int类型，导致引用的时候，无法和Dept类关联起来!!!
无效的列类型: 1111 错误,可能是传递参数的时候，出现了问题，比如说嵌套查询empno=#{id},但是在接口中，却没有定义该查询语句所对应的参数，则必然会出问题哦！！！对于使用映射文件来操作数据库 ，操作系统的架构方式来说，参数 条件等强关联，大小写，格式等要严格遵守规范！！！！
JAVA就业套餐课：https://edu.csdn.net/combo/detail/1230
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/54947149
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）