MyBatis级联查询

最新推荐文章于 2024-10-31 15:56:11 发布

OYBox

最新推荐文章于 2024-10-31 15:56:11 发布

阅读量88

点赞数

分类专栏： Java技术栈文章标签： java java-ee mybatis

本文链接：https://blog.csdn.net/qq_37570991/article/details/119722141

版权

Java技术栈专栏收录该内容

23 篇文章 0 订阅

订阅专栏

本文详细介绍了使用MyBatis进行一对多、多对一以及多对多的级联查询操作。通过示例展示了如何在Student、Classes、Customer和Goods实体类中配置关联，并在对应的Mapper XML文件中编写SQL查询，实现数据的级联获取。

摘要由CSDN通过智能技术生成

MyBatis级联查询

⭐一对多
Student

package com.oyrf.frist;

import lombok.Data;

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


}

Classes

package com.oyrf.frist;

import lombok.Data;

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

StudentRepository.java

package com.oyrf.repository;

import com.oyrf.frist.Student;

public interface StudentRepository {
    Student findById(long id);

}

StudentRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.oyrf.repository.StudentRepository">
    <resultMap id="studentMap" type="com.oyrf.frist.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <association property="classes" javaType="com.oyrf.frist.Classes">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </association>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="studentMap">
        SELECT s.id,s.name,c.id AS cid,c.name AS cname FROM student s,classes c WHERE s.id = #{id} AND s.cid = c.id
    </select>
</mapper>

⭐多对一
ClassesRepository.java

package com.oyrf.repository;

import com.oyrf.frist.Classes;

public interface ClassessRepository {
    Classes findById(long id);
}

ClassesRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.oyrf.repository.ClassessRepository">
    <resultMap id="classesMap" type="com.oyrf.frist.Classes">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <collection property="students" ofType="com.oyrf.frist.Student">
            <id column="sid" property="id"></id>
            <id column="sname" property="name"></id>
        </collection>
    </resultMap>
    <select id="findById" parameterType="long" resultMap="classesMap">
        select s.id as sid,s.name as sname,c.id,c.name from student s,classes c where c.id=#{id} and c.id=s.cid
    </select>
</mapper>

⭐多对多

通过顾客的名字看顾客买了什么商品
Customer.java

package com.oyrf.frist;

import lombok.Data;

import java.util.List;

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

CustomerRepository.java

package com.oyrf.repository;

import com.oyrf.frist.Customer;

public interface CustomerRepository {
    Customer findByName(String name);
}

CustomerRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.oyrf.repository.CustomerRepository">
    <resultMap id="customerMap" type="com.oyrf.frist.Customer">
        <result column="cname" property="name"></result>
        <collection property="goods" ofType="com.oyrf.frist.Goods">
            <result column="gname" property="name"></result>
        </collection>
    </resultMap>
    <select id="findByName" parameterType="java.lang.String" resultMap="customerMap">
        SELECT c.`name` AS cname,g.name AS gname FROM customer c,goods g,customer_goods cg WHERE c.`name`=#{name} AND c.`id`=cg.`cid` AND g.`id`=cg.`gid`
    </select>

</mapper>

同过商品看哪些顾客买了该商品
Goods.java

package com.oyrf.frist;

import lombok.Data;

import java.util.List;

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

GoodsRepository.java

package com.oyrf.repository;

import com.oyrf.frist.Goods;

public interface GoodsRepository {
    Goods findByName(String name);
}

GoodsRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.oyrf.repository.CustomerRepository">
    <resultMap id="customerMap" type="com.oyrf.frist.Customer">
        <result column="cname" property="name"></result>
        <collection property="goods" ofType="com.oyrf.frist.Goods">
            <result column="gname" property="name"></result>
        </collection>
    </resultMap>
    <select id="findByName" parameterType="java.lang.String" resultMap="customerMap">
        SELECT c.name AS cname,g.name AS gname FROM customer c,goods g,customer_goods cg WHERE c.name=#{name} AND c.id=cg.cid AND g.id=cg.gid
    </select>

</mapper>