MyBatis中多對一和一對多資料的處理-有解無憂

多對一的處理

多對一的理解：

多個學生對應一個老師
如果對于學生這邊，就是一個多對一的現象，即從學生這邊關聯一個老師！

1、資料庫設計

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`, `name`) VALUES (1, '吳老師'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小紅', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小張', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

2、搭建測驗環境

【Lombok的使用】

1. IDEA安裝Lombok插件

2. 引入Maven依賴

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.10</version>
</dependency>

3. 在代碼中增加注解

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

@Data
public class Student {
    private int id;
    private String name;
    //多個學生可以是同一個老師，即多對一
    private Teacher teacher;
}

4. 撰寫物體類對應的Mapper介面【兩個】

無論有沒有需求，都應該寫上，以備后來之需！

public interface StudentMapper {
}

public interface TeacherMapper {
}

5. 撰寫Mapper介面對應的 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.hang.mapper.StudentMapper">
</mapper>

<?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.hang.mapper.TeacherMapper">
</mapper>

3、按查詢嵌套處理

1. 給StudentMapper介面增加方法

//獲取所有學生及對應老師的資訊
public List<Student> getStudents();

2. 撰寫對應的Mapper檔案

<?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.hang.mapper.StudentMapper">

    <select id="getStudents" resultMap="studentTeacher">
        select * from mybatis.student;
    </select>

    <resultMap id="studentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--association關聯屬性 property屬性名 javaType屬性型別 column在多的一方的表中的列名-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from mybatis.teacher;
    </select>

</mapper>

3. 撰寫完畢去Mybatis組態檔中，注冊Mapper！

4. 測驗

    @Test
    public void getStudents(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students= mapper.getStudents();
        for (Student student : students) {
            System.out.println(
            "學生名:"+ student.getName()
            +"\t老師:"+student.getTeacher().getName());
        }
        sqlSession.close();
    }

4 、按結果嵌套處理

除了上面這種方式，還有其他思路嗎？

我們還可以按照結果進行嵌套處理；

1. 介面方法撰寫

public List<Student> getStudents2();

2. 撰寫對應的mapper檔案

　　<select id="getStudents2" resultMap="studentTeacher2">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from mybatis.student s, mybatis.teacher t
        where t.id = s.tid
    </select>

    <resultMap id="studentTeacher2" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

3. 去mybatis-config檔案中注入【此處應該處理過了】

4. 測驗

    @Test
    public void getStudents2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students= mapper.getStudents2();
        for (Student student : students) {
            System.out.println(
            "學生名:"+ student.getName()
            +"\t老師:"+student.getTeacher().getName());
        }
        sqlSession.close();
    }

5、小結

按照查詢進行嵌套處理就像SQL中的子查詢
按照結果進行嵌套處理就像SQL中的聯表查詢

一對多的處理

一對多的理解：

一個老師擁有多個學生
如果對于老師這邊，就是一個一對多的現象，即從一個老師下面擁有一群學生（集合）！

1、物體類撰寫

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

@Data
public class Teacher {
    private int id;
    private String name;
    //一個老師多個學生
    private List<Student> students;
}

和之前一樣，搭建測驗的環境！

2、按結果嵌套處理

1. TeacherMapper介面撰寫方法

//獲取指定老師，及老師下的所有學生
public Teacher getTeacher(int id);

2. 撰寫介面對應的Mapper組態檔

    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from mybatis.teacher t, mybatis.student s
        where t.id = s.tid and t.id = #{tid};
    </select>

    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

3. 將Mapper檔案注冊到MyBatis-config檔案中

4. 測驗

@Test
public void testGetTeacher(){
    SqlSession session = MybatisUtils.getSession();
    TeacherMapper mapper = session.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.getTeacher(1);
    System.out.println(teacher.getName());
    System.out.println(teacher.getStudents());
}

3、按查詢嵌套處理

1. TeacherMapper介面撰寫方法

public Teacher getTeacher2(int id);

2. 撰寫介面對應的Mapper組態檔

    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id = #{id}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <!--column是一對多的外鍵 , 寫的是一的主鍵的列名-->
        <collection property="students" javaType="ArrayList" ofType="Student" column="id" select="getStudentByTeacherId"/>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid = #{id}
    </select>

3. 將Mapper檔案注冊到MyBatis-config檔案中

4. 測驗

@Test
public void testGetTeacher2(){
    SqlSession session = MybatisUtils.getSession();
    TeacherMapper mapper = session.getMapper(TeacherMapper.class);
    Teacher teacher = mapper.getTeacher2(1);
    System.out.println(teacher.getName());
    System.out.println(teacher.getStudents());
}

4、小結

關聯-association
集合-collection
所以association是用于一對一和多對一，而collection是用于一對多的關系
JavaType和ofType都是用來指定物件型別的

- JavaType是用來指定pojo中屬性的型別
- ofType指定的是映射到list集合屬性中pojo的型別，

注意說明：

保證SQL的可讀性，盡量通俗易懂
根據實際要求，盡量撰寫性能更高的SQL陳述句
注意屬性名和欄位不一致的問題
注意一對多和多對一中：欄位和屬性對應的問題
盡量使用Log4j，通過日志來查看自己的錯誤

本文來自博客園，作者：腹白，轉載請注明原文鏈接：https://www.cnblogs.com/wyh518/

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/541066.html

標籤：其他

上一篇：每日演算法之洗掉鏈表中重復的結點

下一篇：用篩法求之N內的素數。（Java）