Mybatis 级联查询如何实现？

上一篇MyBatis极简入门

本篇主要简单介绍Mybatis级联查询的使用

  • 一对多
  • 多对多

常见的一对多模型，一个班有多个学生，一个学生只属于一个班。

我们首先在数据库中建立student,classes两张表
student表，其中cid是外键，关联classed的id属性
s
classes表
在这里插入图片描述

现在我们有如下需求，根据一个学生id，查询出学生的姓名班级和班级id。

我们新建两个实体类。student和classes,代码如下：

package com.ibuyi.mybatis.entity;

import lombok.Data;

@Data
public class Student {
    private long id;
    private String name;
    private Classes classes;

}


package com.ibuyi.mybatis.entity;

import lombok.Data;

import java.util.List;
@Data
public class Classes {
    private long id;
    private String name;
    private List<Student> students;
}

紧接着我们使用mapper代理的方式创建接口类，如下：

package com.ibuyi.mybatis.repository;

import com.ibuyi.mybatis.entity.Student;

public interface StudentDAO {
    //根据student ID查询学生姓名班级
    Student findStudentByID(long id);
}

创建了接口以后我们需要配置student.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.ibuyi.mybatis.repository.StudentDAO">
<!--namespace指的是接口类-->
       <select id="findStudentByID" parameterType="int" resultMap="student">
       <!--resultMap也是一种返回类型，是我们自定义的-->
       <!--查询语句，因为mybatis是根据查找结果的列名和我们的实体类进行映射的，我们student和clsses中id,name重复，所以在查询时用as改名。方便进行映射-->
           select s.id as id,s.`name`as name, c.id as cid,c.`name`as cname from student as s,classes as c where s.id=1 and s.cid=c.id
       </select>
    <resultMap id="student" type="com.ibuyi.mybatis.entity.Student">
    <!--需要指定对应的实体类即type-->
    
        <!--指定id属性对应查询语句查询出来的结果的id列-->
          <id property="id" column="id"></id>
        
          <!--指定name属性对应查询语句查询出来的结果的name列-->
        <result property="name" column="name"></result>
        
        <!--对象属性需要用association指定javatype,property指的是student类中的属性，javatype指明类型-->
        <association property="classes" javaType="com.ibuyi.mybatis.entity.Classes">
        
         <!--指定classes对象的id属性对应查询语句查询出来的结果的cid列-->
            <id property="id" column="cid"></id>
            
               <!--指定classes对象的name属性对应查询语句查询出来的结果的cname列-->
            <result property="name" column="cname"></result>
        </association>
    </resultMap>
</mapper>

执行测试代码：

package com.ibuyi.mybatis.test;

import com.ibuyi.mybatis.entity.Student;
import com.ibuyi.mybatis.repository.StudentDAO;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test {
    public static void main(String[] args) {
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        StudentDAO studentDAO=sqlSession.getMapper(StudentDAO.class);
        Student student=studentDAO.findStudentByID(1L);
        System.out.println(student);

    }
}

现在我们要根据Classes的id查询班级里面的所有学生怎么做呢？

首先还是要先定义接口，如下：

在这里插入代码片package com.ibuyi.mybatis.repository;

import com.ibuyi.mybatis.entity.Classes;

import java.util.List;

public interface ClassDAO {
    //根据cid查询班级全部学生
    List<Classes> findStudentByID(long id);
}

第二步：创建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="com.ibuyi.mybatis.repository.ClassDAO">
   <select id="findStudentByID" parameterType="long" resultMap="studentlist">
       select c.id as id,c.name as name,s.name as student,s.id as sid from student as s, classes as c where c.id=2 and s.cid=c.id   </select>
    <resultMap id="studentlist" type="com.ibuyi.mybatis.entity.Classes">
        <id property="id" column="id" ></id>
        <result property="name" column="name"></result>
        <!--这里不能使用association,因为是List<Student>,所以要用collection-->
        <collection property="students" ofType="com.ibuyi.mybatis.entity.Student">
            <id property="id" column="sid"></id>
            <result property="name" column="student"></result>
        </collection>
    </resultMap>
</mapper>

最后别忘了在config.xml中的配置mapper

<mappers>
        <mapper resource="com/ibuyi/mybatis/repository/student.xml"/>
        <mapper resource="com/ibuyi/mybatis/repository/class.xml"/>
</mappers>

看一下实验结果如下：
在这里插入图片描述

下面我们学习一下多对多关系
常见的多对多关系如顾客购买商品，一个顾客可以购买多个商品，一个商品可以被多个顾客购买。

我们先建立三张表，一张顾客表（id，name）一张商品表（id，name）一张中间表c_goods(id,cid,gid)
其中cid，gid分别时顾客表和商品表的id对应。 随便插入几条数据。

1.现在我们想要某个人购买的全部商品，请看代码：

第一步，先定义两个实体对象，good和customer

package com.ibuyi.mybatis.entity;

import lombok.Data;

import java.util.List;

@Data
public class Customer {
    private long id;
    private String name;
    private List<Good> goods;
}


package com.ibuyi.mybatis.entity;

import lombok.Data;
import java.util.List;
@Data
public class Good {
    private long id;
    private String name;
    private List<Customer> customers;
}

紧接着，写查询某人购买商品的接口：

package com.ibuyi.mybatis.repository;

import com.ibuyi.mybatis.entity.Customer;
import com.ibuyi.mybatis.entity.Good;

import java.util.List;

public interface CustomerDAO {
    //根据客户id查询客户购买的商品
    Customer findGoodsByID(long id);
}

再接着，写mapper.xml文件，定义sql查询。

<?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.ibuyi.mybatis.repository.CustomerDAO">
<select id="findGoodsByID" parameterType="long" resultMap="customer">
  select c.id as cid ,c.name as cname,g.id as gid,g.name as gname from customers c,goods g ,c_goods cg where c.id=#{id} and c.id=cg.cid and g.id=cg.gid;
</select>
    <resultMap id="customer" type="com.ibuyi.mybatis.entity.Customer">
        <id property="id" column="cid"></id>
        <result property="name" column="cname"></result>
        <collection property="goods" ofType="com.ibuyi.mybatis.entity.Good">
            <id property="id" column="gid"></id>
            <result property="name" column="gname"></result>
        </collection>
    </resultMap>
</mapper>

最后注册到config.xml中

<mappers>
        <mapper resource="com/ibuyi/mybatis/repository/student.xml"/>
        <mapper resource="com/ibuyi/mybatis/repository/class.xml"/>
        <mapper resource="com/ibuyi/mybatis/repository/customer.xml"/>
        <mapper resource="com/ibuyi/mybatis/repository/good.xml"/>
    </mappers>

现在，我们来看一下测试代码：

package com.ibuyi.mybatis.test;
import com.ibuyi.mybatis.entity.Customer;
import com.ibuyi.mybatis.entity.Good;
import com.ibuyi.mybatis.repository.CustomerDAO;
import com.ibuyi.mybatis.repository.GoodDAO;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test {
    public static void main(String[] args) {
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        CustomerDAO customerDAO=sqlSession.getMapper(CustomerDAO.class);
        Customer customer=customerDAO.findGoodsByID(3L);
        System.out.println(customer);
 
    }
}

结果如下

在这里插入图片描述

2.根据商品id查询所有购买了该商品的人。

还是同样的操作步骤，这里把代码贴上：

package com.ibuyi.mybatis.repository;

import com.ibuyi.mybatis.entity.Good;

public interface GoodDAO {
    Good findGoodByID(long id);
}


<?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.ibuyi.mybatis.repository.GoodDAO">
    <select id="findGoodByID" parameterType="long" resultMap="good">
        select g.id as gid ,g.name as gname,c.id as cid,c.name as cname from customers c,goods g ,c_goods cg where g.id=#{id} and g.id=cg.gid and cg.cid=c.id;
    </select>
    <resultMap id="good" type="com.ibuyi.mybatis.entity.Good">
        <id property="id" column="gid"></id>
        <result property="name" column="gname"></result>
        <collection property="customers" ofType="com.ibuyi.mybatis.entity.Customer">
            <id property="id" column="cid"></id>
            <result property="name" column="cname"></result>
        </collection>
    </resultMap>
</mapper>


package com.ibuyi.mybatis.test;
import com.ibuyi.mybatis.entity.Customer;
import com.ibuyi.mybatis.entity.Good;
import com.ibuyi.mybatis.repository.CustomerDAO;
import com.ibuyi.mybatis.repository.GoodDAO;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

public class Test {
    public static void main(String[] args) {
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("config.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        GoodDAO goodDAO=sqlSession.getMapper(GoodDAO.class);
        Good good=goodDAO.findGoodByID(1);
        System.out.println(good);
        sqlsession.close();

    }
}

在这里插入图片描述

mybatis的级联查询的相关内容到此就结束了！

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hzcya1995/p/13309404.html