Mapper.xml中查詢返回帶有List屬性的實體類結果問題

更新時間：2023年06月26日 16:09:00 作者：cxy1109

這篇文章主要介紹了Mapper.xml中查詢返回帶有List屬性的實體類結果問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

1.建立學生關系表

建了五張表，分別是school（學院）class（班級）student（班級）course（課程）stu_course（學生選課表），表結構如下

學院表

班級表

學生表

課程表

學生選課表

班級表通過school_id與學院表關聯(lián)，學生表通過class_id與班級表關聯(lián)，學生選課表關聯(lián)學生表和課程表。

2.建立springboot項目

在STS中新建springboot項目，application.properties配置如下

pom.xml文件如下

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cheng</groupId>
    <artifactId>testspringboot</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>testspringboot</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <scope>provided</scope>
        </dependency> -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.tomcat.embed</groupId>
            <artifactId>tomcat-embed-jasper</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <scope>provided</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
    <!--     <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency> -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatisplus-spring-boot-starter</artifactId>
            <version>1.0.5</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>2.3</version>
        </dependency>
        <!--MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.20</version>
        </dependency>
        <!-- lombok需要的jar包 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 </project>

項目建好后使用mybatis plus自動生成實體類service和controller以及Mapper，生成好的如下圖。

并創(chuàng)建要返回的學生詳細信息的實體類StudentDto，該實體類包含List類型的學生選課的課程信息

package com.example.chapter3.dto;
import java.io.Serializable;
import java.util.List;
import com.baomidou.mybatisplus.activerecord.Model;
import com.example.chapter3.model.Course;
public class StudentDto extends Model<StudentDto>{
    private static final long serialVersionUID = 1L;
    /**
     * 學生ID
     */
    private String studentId;
    /**
     * 學生姓名
     */
    private String studentName;
    /**
     * 學生年齡
     */
    private Integer studentAge;
    /**
     * 學生性別
     */
    private String studentSex;
    /**
     * 學校ID
     */
    private String schoolId;
    /**
     * 班級ID
     */
    private String classId;
    private String shcoolName;
    private String className;
    private List<Course> courses;
    @Override
    protected Serializable pkVal() {
        // TODO Auto-generated method stub
        return studentId;
    }
    public String getStudentId() {
        return studentId;
    }
    public void setStudentId(String studentId) {
        this.studentId = studentId;
    }
    public String getStudentName() {
        return studentName;
    }
    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }
    public Integer getStudentAge() {
        return studentAge;
    }
    public void setStudentAge(Integer studentAge) {
        this.studentAge = studentAge;
    }
    public String getStudentSex() {
        return studentSex;
    }
    public void setStudentSex(String studentSex) {
        this.studentSex = studentSex;
    }
    public String getSchoolId() {
        return schoolId;
    }
    public void setSchoolId(String schoolId) {
        this.schoolId = schoolId;
    }
    public String getClassId() {
        return classId;
    }
    public void setClassId(String classId) {
        this.classId = classId;
    }
    public String getShcoolName() {
        return shcoolName;
    }
    public void setShcoolName(String shcoolName) {
        this.shcoolName = shcoolName;
    }
    public String getClassName() {
        return className;
    }
    public void setClassName(String className) {
        this.className = className;
    }
    public List<Course> getCourses() {
        return courses;
    }
    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }
    public static long getSerialversionuid() {
        return serialVersionUID;
    }
    @Override
    public String toString() {
        return "StudentDto [studentId=" + studentId + ", studentName=" + studentName + ", studentAge=" + studentAge
                + ", studentSex=" + studentSex + ", schoolId=" + schoolId + ", classId=" + classId + ", shcoolName="
                + shcoolName + ", className=" + className + ", courses=" + courses + "]";
    }
}

3.編寫要查詢的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.example.chapter3.mapper.StudentMapper">
    <!-- 通用查詢映射結果 -->
    <resultMap id="BaseResultMap" type="com.example.chapter3.model.Student">
        <id column="student_id" property="studentId" />
        <result column="student_name" property="studentName" />
        <result column="student_age" property="studentAge" />
        <result column="student_sex" property="studentSex" />
        <result column="school_id" property="schoolId" />
        <result column="class_id" property="classId" />
    </resultMap>
    <!-- 通用查詢結果列 -->
    <sql id="Base_Column_List">
        student_id, student_name, student_age, student_sex, school_id, class_id
    </sql>
    <resultMap type="com.example.chapter3.dto.StudentDto" id="stuDtoResultMap">
        <id column="student_id" property="studentId" />
        <result column="student_name" property="studentName" />
        <result column="student_age" property="studentAge" />
        <result column="student_sex" property="studentSex" />
        <result column="school_id" property="schoolId" />
        <result column="class_id" property="classId" />
        <result column="shcool_name" property="shcoolName" />
        <result column="class_name" property="className" />
        <collection property="courses"    ofType="com.example.chapter3.model.Course"
            column="student_id" select="com.example.chapter3.mapper.CourseMapper.selectCourseByStuId">
         </collection>        
    </resultMap>
    <select id="selectStudentDetails" resultMap="stuDtoResultMap">
        SELECT sc.school_name, sc.school_id, sc.adress, cl.class_id, cl.class_name, stu.student_id, 
        stu.student_name, stu.student_age, stu.student_sex FROM school sc LEFT JOIN class cl ON sc.school_id = cl.school_id 
        LEFT JOIN student stu ON stu.class_id = cl.class_id WHERE stu.student_id = #{studentId}
    </select>
</mapper>

其對應的StudentMapper.class文件如下

該Mapper有一個查詢學生信息的方法對應studentMapper.xml里的selectStudentDetails。

通過聯(lián)合查詢返回stuDtoResultMap，這是個resulMap類型，type="com.example.chapter3.dto.StudentDto"是學生的詳細信息實體類，其中的

<collection property="courses"    ofType="com.example.chapter3.model.Course"
            column="student_id" select="com.example.chapter3.mapper.CourseMapper.selectCourseByStuId">
 </collection>

表示的是StudentDto的List集合，返回的是List<Course>,property是StudentDto類中的集合屬性名，也就是courses，ofType是集合中的類型，是Course， column是查詢傳遞的參數(shù)，select是要查詢的語句，同一個Mapper直接使用id，不同Mapper使用查詢方法的全類名，這里把student_id作為參數(shù)傳遞給Course表的查詢函數(shù)selectCourseByStuId。

CourseMapper如下。