快捷導(dǎo)航

Mybatis 多對(duì)一查詢的實(shí)現(xiàn)方法

更新時(shí)間：2022年02月23日 10:40:39 作者：小小代碼熊

這篇文章主要介紹了Mybatis 多對(duì)一查詢,本文通過(guò)場(chǎng)景分析示例代碼相結(jié)合給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

架構(gòu)

在這里插入圖片描述

這里從學(xué)生的角度來(lái)說(shuō)就是多對(duì)一的場(chǎng)景

那么在Java中是怎么樣的呢？

環(huán)境搭建

數(shù)據(jù)庫(kù)

CREATE TABLE `student`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(20) DEFAULT NULL 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES (1,"李老師");
CREATE TABLE `student` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(20) DEFAULT NULL ,
`tid` INT(4) NOT NULL,
KEY `fk_tid` (`tid`),
CONSTRAINT fk_tid FOREIGN KEY(`tid`) REFERENCES `teacher`(`id`)
INSERT INTO student VALUES
(1,"小紅",1),
(2,"小綠",1),
(3,"小燈",1),
(4,"小勿",1),
(5,"小闖",1);

在這里插入圖片描述

實(shí)體類

Student

package pojo;

import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;//這里因?yàn)槲覀冇幸粋€(gè)對(duì)應(yīng)的關(guān)系，學(xué)生要對(duì)應(yīng)到一個(gè)老師，所以要使用Teacher類型
}

Teacher

package pojo;

import lombok.Data;
@Data
public class Teacher {
    private int id;
    private String name;
}

接口

（因?yàn)檫@里是多對(duì)一，所以我就沒有用到TeacherMapper）
StudentMapper

package dao;

import pojo.Student;
import java.util.List;
public interface StudentMapper {
    List<Student> getStudents();
    List<Student> getStudents2();
}

TeacherMapper

package dao;
public interface TeacherMapper {
}

Mapper配置文件

TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.TeacherMapper">
</mapper>

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.StudentMapper">
<!--  第二種  -->
    <select id="getStudents2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid=t.id;
    </select>
    <resultMap id="StudentTeacher2" type="Student" >
        <result column="sid" property="id" />
        <result column="sname" property="name" />
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid" />
            <result property="name" column="tname" />
        </association>
    </resultMap>
<!--  第一種  -->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student;
    </select>
    <resultMap id="StudentTeacher" type="Student" >
        <result column="id" property="id" />
        <result column="name" property="name" />
        <association property="teacher" column="tid" javaType="Teacher" select="getTeachers" />
    </resultMap>
    <select id="getTeachers" resultType="Teacher">
        select * from teacher where id=#{id};
    </select>
</mapper>

這里說(shuō)明一下

association 是相對(duì)于對(duì)象的
collection是相對(duì)于集合的

然后這里有兩種方法，我們一種一種看

子查詢方法

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.StudentMapper">
<!--  第一種  -->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student;
    </select>
    <resultMap id="StudentTeacher" type="Student" >
        <result column="id" property="id" />
        <result column="name" property="name" />
        <association property="teacher" column="tid" javaType="Teacher" select="getTeachers" />
    </resultMap>

    <select id="getTeachers" resultType="Teacher">
        select * from teacher where id=#{id};
</mapper>

也就是這種方法，它其實(shí)是相當(dāng)于我們Sql里面的子查詢，也就是一層查詢里面嵌套一層查詢

按結(jié)果集查詢

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.StudentMapper">
<!--  第二種  -->
<select id="getStudents2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid=t.id;
    </select>
    <resultMap id="StudentTeacher2" type="Student" >
        <result column="sid" property="id" />
        <result column="sname" property="name" />
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid" />
            <result property="name" column="tname" />
        </association>
    </resultMap>
</mapper>

這種方法就是我們一次性把結(jié)果查詢出來(lái)然后對(duì)結(jié)果集做一下處理
這里特別說(shuō)明一下column這個(gè)屬性，這個(gè)可不是數(shù)據(jù)庫(kù)中你需要的字段名，而是你查詢出來(lái)的那張表的字段名，這里起了別名之后就不再是原來(lái)teacher數(shù)據(jù)庫(kù)表中的字段名了

到此這篇關(guān)于Mybatis 多對(duì)一查詢的文章就介紹到這了,更多相關(guān)Mybatis 多對(duì)一查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: