实现查询思路:
- 嵌套查询方式: 通过执行另一条SQL语句映射语句来返回预期的复杂类型。
- 嵌套结果方式:使用嵌套结果映射来处理复杂的联合结果的子集。
一对多查询:
嵌套结果使用双标签\<collection >\</collection >
一对一查询:
嵌套结果使用双标签\<association>\</association>
就像下面这样:
//嵌套查询查询 使用标签<association>
<association property="card" column="card_id" javaType="Pojo.IdCard"
select="Mapper.UserMapper.FindById"/>
//嵌套结果查询 使用标签 <association></association>
<association property="card" column="card_id" javaType="Pojo.IdCard">
//查询到表二结果,并映射结果集
<id property="id" column="card_id" />
<result property="code" column="code"/>
</association>
一对一具体应用时的步骤:
1. 先查询表tb_person
<mapper namespace="Mapper.UserMapper">
<!-- 一对一查询 begin-->
<select id="FindPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
SELECT * from tb_person where id=#{id}
</select>
2.使用resultMap 映射查询到表tb_person 的结果
<resultMap id="IdCardWithPersonResult" type="Pojo.Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
3.使用<association/>标签将通过Mapper包中的接口类UserMapper定义的方法查询表tb_idcard,从而使得两张表的查询链接起来。
4.根据 <id property="id" column="id"/>查询的id,传入下一个查询语句中,id=card_id,并将查询到的表二结果放到List<IDcard> card中;
<association property="card" column="card_id" javaType="Pojo.IdCard"
select="Mapper.UserMapper.FindById"/>
</resultMap>
<select id="FindById" parameterType="Integer" resultType="Pojo.IdCard" >
SELECT * from tb_idcard where id=#{id}
</select>
具体实现:
<!-- 一对一查询 begin-->
<select id="FindPersonById" parameterType="Integer" resultMap="IdCardWithPersonResult">
SELECT * from tb_person where id=#{id}
</select>
<resultMap id="IdCardWithPersonResult" type="Pojo.Person">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<association property="card" column="card_id" javaType="Pojo.IdCard"
select="Mapper.UserMapper.FindById"/>
</resultMap>
<select id="FindById" parameterType="Integer" resultType="Pojo.IdCard" >
SELECT * from tb_idcard where id=#{id}
</select>
<!-- 一对一嵌套查询 end-->
<!-- 一对一嵌套结果查询 begin-->
<select id="FindPersonById2" parameterType="Integer"
resultMap="IdCardWithPersonResult2">
SELECT p.*,idcard.code
from tb_person p,tb_idcard idcard
where p.card_id=idcard.id
and p.id=#{id}
</select>
<resultMap id="IdCardWithPersonResult2" type="Pojo.Person" >
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<result property="sex" column="sex"/>
<association property="card" column="card_id" javaType="Pojo.IdCard">
<id property="id" column="card_id" />
<result property="code" column="code"/>
</association>
</resultMap>
<!-- 一对一嵌套结果查询 end-->
项目结构:
Mapper.UserMapper接口:
public interface UserMapper {
//这里时namespace的命名空间
//一对一查询
void FindPersonById();
void FindPersonById2();
//一对多查询
void FindUserWithDept();
// 嵌套查询
void FindEmpWithDept();
void FindEmpWithDeptByOne();
}
下面直接实现一对多查询 的两种方式
- 嵌套查询方式
Mapper.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="Mapper.UserMapper">
<!--一对多嵌套查询 begin-->
<select id="FindEmpWithDept" parameterType="Integer" resultMap="EmpWithDeptResult">
SELECT * from dept where deptno=#{deptno}
</select>
<resultMap id="EmpWithDeptResult" type="Pojo.Dept">
<id property="deptno" column="deptno"/>
<result property="dname" column="dname"/>
<result property="loc" column="loc"/>
<collection property="empList" column="deptno" ofType="Pojo.Emp"
select="Mapper.UserMapper.FindEmpWithDeptByOne"/>
</resultMap>
<select id="FindEmpWithDeptByOne" parameterType="Integer" resultType="Pojo.Emp">
SELECT * from emp where deptno=#{deptno}
</select>
<!-- 一对多 嵌套查询 end-->
<!-- 一对多 嵌套结果查询 begin-->
<!-- SELECT d.*,e.* 查询两各表的所有需要的字段-->
<!-- from dept d,emp e 来自dept表和emp表,d,e是表名的缩写,用于区分两个表的deptno字段-->
<!-- SELECT d.*,e.deptno,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm-->
<select id="FindUserWithDept" parameterType="Integer"
resultMap="UserWithOrdersResult">
SELECT d.*,e.*
from dept d,emp e
where d.deptno=e.deptno
and d.deptno=#{deptno}//传入参数
</select>
<resultMap id="UserWithOrdersResult" type="Dept" >
<!-- 先查询dept表,然后再拼接empList-->
<id property="deptno" column="deptno"/>
<result property="dname" column="dname"/>
<result property="loc" column="loc"/>
<collection property="empList" ofType="Emp" >
<!-- property="empList" 引入Dept对象指定实体类对象中的emplist泛型数组 -->
<!-- ofType="Emp" 指定实体类对象中的集合类属性 -->
<id property="empno" column="empno"/>
<result property="ename" column="ename"/>
<result property="job" column="job"/>
<result property="mgr" column="mgr"/>
<result property="hiredate" column="hiredate"/>
<result property="sal" column="sal"/>
<result property="comm" column="comm"/>
<result property="deptno" column="deptno"/>
</collection>
</resultMap>
<!-- 一对多 嵌套结果查询 end-->
</mapper>
Dept实体类;
package Pojo;
import java.util.List;
public class Dept {
private Integer 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 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;
}
@Override
public String toString() {
return "Dept{" +
"deptno=" + deptno +
", dname='" + dname + '\'' +
", loc='" + loc + '\'' +
", empList=" + empList +
'}';
}
}
Emp实体类:
package Pojo;
public class Emp {
private Integer empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private int sal;
private int comm;
private Integer deptno;
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 int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr=" + mgr +
", hiredate='" + hiredate + '\'' +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
测试:
import Pojo.Dept;
import Pojo.Person;
import Utils.Mybatis;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class AppTest {
@Test
public void findPersonByIdTest(){//一对一查询
SqlSession sqlSession = Mybatis.openSession();
List<Person> person1 = sqlSession.selectList("Mapper.UserMapper.FindById",1);
List<Person> person2 = sqlSession.selectList("Mapper.UserMapper.FindPersonById",1);
List<Person> person3 = sqlSession.selectList("Mapper.UserMapper.FindPersonById2",1);
System.out.println(person1.toString());
System.out.println(person2.toString());
System.out.println(person3.toString());
sqlSession.close();
}
@Test
public void FindUserWithDeptTest(){
// 一对多嵌套结果查询
SqlSession sqlSession = Mybatis.openSession();
List<Dept> deptList = sqlSession.selectList("Mapper.UserMapper.FindUserWithDept",20);
System.out.println(deptList);
sqlSession.close();
}
@Test
public void FindEmpWithDeptTest(){
// 一对多嵌套查询
SqlSession sqlSession = Mybatis.openSession();
List<Dept> deptList = sqlSession.selectList("Mapper.UserMapper.FindEmpWithDept",10);
System.out.println(deptList);
sqlSession.close();
}
}
注意:工具类和配置文件这里没有给出,需要自行配置。
查询结果
一对一
一对多查询: