mybatis的一对一、一对多、多对多查询学习

于 2021-06-19 16:55:28 发布
阅读量423 收藏
点赞数
文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zwy111zwy/article/details/118056180
版权

mybatis的一对一、一对多、多对多查询学习

对于mybatis的几种关联查询的总结记录，废话不多说，直接开始。

一对一

数据库表

student：学生表

student_card：学生卡

例子

studentMapper.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.strawberry.mapper.StudentMapper">
  
    <resultMap id="BaseResultMap" type="com.strawberry.entity.Student">
        <id column="student_id" property="studentId"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
    </resultMap>
    
    <!--  extends 继承上面的BaseResultMap   type:映射对象     -->
    <resultMap id="StudentAndCardMap" extends="BaseResultMap" type="com.strawberry.vo.StudentVo">
        <!--association:用于映射关联查询单个对象的信息
                property:实体类对应的属性名
                javaType:实体类对应的全类名
                resultMap:引用另外一个xml文件的resultMap  规则：namespace + id
              -->
        <association property="studentCard" javaType="com.strawberry.entity.StudentCard"
                     resultMap="com.strawberry.mapper.StudentCardMapper.BaseResultMap">
        </association>
    </resultMap>
      <!--
       嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
                                  封装联表查询的数据(去除重复的数据)
        select * from student s,student_card sc where s.student_id = sc.student_id and s.student_id = #{studentId}
    -->
    <select id="getStudentAndCard" resultMap="StudentAndCardMap">
        select * from student s,student_card sc where s.student_id = sc.student_id and s.student_id = #                   {studentId}
    </select>
  
  
    <resultMap id="StudentAndCardMap2" extends="BaseResultMap" type="com.strawberry.vo.StudentVo">
        <!--
            方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
                 select student_id from student WHERE student_id=1;//得到student_id
                 select * from student_card WHERE student_id=1   //1 是上一个查询得到的student_id的值
                 property:别名(属性名)    column：列名  
                 select:引用studentCardMapper.xml 里面的方法getByStudentId  规则：namespace + id -->
        <association property="studentCard" column="student_id"
                     select="com.strawberry.mapper.StudentCardMapper.getByStudentId"></association>
    </resultMap>
    
    <select id="getStudentAndCard2" resultMap="StudentAndCardMap2">
        select * from student where student_id = #{studentId}
    </select>
  
  
</mapper>

studentCardMapper.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.strawberry.mapper.StudentCardMapper">

    <resultMap id="BaseResultMap" type="com.strawberry.entity.StudentCard">
        <!--
            id:指定查询列表唯一标识，如果有多个唯一标识，则配置多个id
            column:数据库对应的列
            property:实体类对应的属性名
        -->
        <id column="card_id" property="cardId"></id>
        <result column="card_number" property="cardNumber"></result>
        <result column="student_id" property="studentId"></result>
    </resultMap>

    <select id="getByStudentId" resultMap="BaseResultMap">
        select * from student_card where student_id = #{studentId}
    </select>

</mapper>

public interface StudentMapper {
    StudentVo getStudentAndCard(String studentId);
    StudentVo getStudentAndCard2(String studentId);
}

public class Student {

    private String studentId;

    private String name;

    private Integer age;

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "studentId='" + studentId + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}


public class StudentCard {

    private String cardId;

    private String cardNumber;

    private String studentId;

    public String getCardId() {
        return cardId;
    }

    public void setCardId(String cardId) {
        this.cardId = cardId;
    }

    public String getCardNumber() {
        return cardNumber;
    }

    public void setCardNumber(String cardNumber) {
        this.cardNumber = cardNumber;
    }

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    @Override
    public String toString() {
        return "StudentCard{" +
                "cardId='" + cardId + '\'' +
                ", cardNumber='" + cardNumber + '\'' +
                ", studentId='" + studentId + '\'' +
                '}';
    }
}


public class StudentVo extends Student {

    private StudentCard studentCard;

    public StudentCard getStudentCard() {
        return studentCard;
    }

    public void setStudentCard(StudentCard studentCard) {
        this.studentCard = studentCard;
    }
}

上面介绍了2种一对一查询的方式，一个是通过2表关联查询的方式，另一个是用在association的select配置关联表的查询方法。

一对多

数据库

student：学生表（一）

electronic_equipment：电子产品表（多）

例子

studentMapper.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.strawberry.mapper.StudentMapper">

    <resultMap id="BaseResultMap" type="com.strawberry.entity.Student">
        <id column="student_id" property="studentId"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
    </resultMap>

    <resultMap id="studentAndElectronicEquipmentMap" extends="BaseResultMap" type="com.strawberry.vo.StudentVo">
    <!-- property：属性名 即 StudentVo里面的electronicEquipmentList  ofType：即 electronicEquipmentList 里面的对象  -->
        <collection property="electronicEquipmentList" ofType="com.strawberry.entity.ElectronicEquipment"></collection>
    </resultMap>

    <select id="getStudentAndElectronicEquipment" resultMap="studentAndElectronicEquipmentMap">
        select * from student s,electronic_equipment e where s.student_id = e.student_id and s.student_id = #{studentId}
    </select>

</mapper>

ElectronicEquipmentMapper.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.strawberry.mapper.ElectronicEquipmentMapper">

    <resultMap id="BaseResultMap" type="com.strawberry.entity.ElectronicEquipment">
        <id property="electronicId" column="electronic_id"></id>
        <result property="electronicName" column="electronic_name"></result>
        <result property="studentId" column="student_id"></result>
    </resultMap>

</mapper>

package com.strawberry.mapper;

import com.strawberry.entity.Student;
import com.strawberry.vo.StudentVo;

public interface StudentMapper {

    StudentVo getStudentAndElectronicEquipment(String studentId);

}

public class ElectronicEquipment {

    private Integer electronicId;

    private String electronicName;

    private String studentId;

    public Integer getElectronicId() {
        return electronicId;
    }

    public void setElectronicId(Integer electronicId) {
        this.electronicId = electronicId;
    }

    public String getElectronicName() {
        return electronicName;
    }

    public void setElectronicName(String electronicName) {
        this.electronicName = electronicName;
    }

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }
}


public class StudentVo extends Student {

    private List<ElectronicEquipment> electronicEquipmentList;

    public List<ElectronicEquipment> getElectronicEquipmentList() {
        return electronicEquipmentList;
    }

    public void setElectronicEquipmentList(List<ElectronicEquipment> electronicEquipmentList) {
        this.electronicEquipmentList = electronicEquipmentList;
    }
}

多对多

数据库

student：学生表

course：课程表

student_course：学生课程表

例子

studentMapper.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.strawberry.mapper.StudentMapper">

    <resultMap id="BaseResultMap" type="com.strawberry.entity.Student">
        <id column="student_id" property="studentId"></id>
        <result column="name" property="name"></result>
        <result column="age" property="age"></result>
    </resultMap>
    <!--resultMap="com.strawberry.mapper.CourseMapper.BaseResultMap" 引用另一个xml文件的ResultMap
        规则：namespace + id -->
    <resultMap id="studentAndCourseMap" extends="BaseResultMap" type="com.strawberry.vo.StudentVo">
        <collection property="courseList" ofType="com.strawberry.entity.Course"
                    resultMap="com.strawberry.mapper.CourseMapper.BaseResultMap"></collection>
    </resultMap>

    <select id="getStudentAndCourse" resultMap="studentAndCourseMap">
        select * from student s,student_course sc,course c where s.student_id = sc.student_id
        and sc.course_id = c.course_id and s.student_id = #{studentId}
    </select>

</mapper>

courseMapper.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.strawberry.mapper.CourseMapper">

    <resultMap id="BaseResultMap" type="com.strawberry.entity.Course">
        <!--
            id:指定查询列表唯一标识，如果有多个唯一标识，则配置多个id
            column:数据库对应的列
            property:实体类对应的属性名
        -->
        <id column="course_id" property="courseId"></id>
        <result column="course_name" property="courseName"></result>
    </resultMap>
</mapper>

public interface StudentMapper {
  
    StudentVo getStudentAndCourse(String studentId);
}

public class Student {

    private String studentId;

    private String name;

    private Integer age;

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student{" +
                "studentId='" + studentId + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

public class Course {

    private String courseId;
    private String courseName;

    public String getCourseId() {
        return courseId;
    }

    public void setCourseId(String courseId) {
        this.courseId = courseId;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }
}

public class StudentVo extends Student {

    private List<Course> courseList;

    public List<Course> getCourseList() {
        return courseList;
    }

    public void setCourseList(List<Course> courseList) {
        this.courseList = courseList;
    }
}

多对多查询其实和一对多差不多，只不过多关联了一个表。

可运行的项目地址：https://gitee.com/forget-cares-and-worries/learning-project 下的mybatisLearn

小胖-1
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值