java中mapper返回数据Result mapper返回list

转载

mob64ca1403c772 2024-07-28 08:43:15

文章标签 带有List属性的实体类查询 spring List 实体类 文章分类 Java 后端开发

刚入职公司，参与到一个项目中，由于是刚毕业的菜鸟，只能分配写一些查询的接口代码。其中有一个就是需要返回一个实体类的信息，该实体类带有List属性，返回的结果类似于下图。

java中mapper返回数据Result mapper返回list_spring

我的解决办法是将查询分成两步，第一步是查询到对应的表的实体类结果，第二部查询到一个List集合然后将这个集合通过set方法复制给实体类。后来看了同事的代码豁然开朗，这里用学生的信息模拟一下。

1.建立学生关系表

建了五张表，分别是school（学院）class（班级）student（班级）course（课程）stu_course（学生选课表），表结构如下

学院表

java中mapper返回数据Result mapper返回list_spring_02

班级表

java中mapper返回数据Result mapper返回list_spring_03

学生表

java中mapper返回数据Result mapper返回list_实体类_04

课程表

java中mapper返回数据Result mapper返回list_带有List属性的实体类查询_05

学生选课表

java中mapper返回数据Result mapper返回list_带有List属性的实体类查询_06

班级表通过school_id与学院表关联，学生表通过class_id与班级表关联，学生选课表关联学生表和课程表。

2.建立springboot项目

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

java中mapper返回数据Result mapper返回list_实体类_07

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，生成好的如下图。

java中mapper返回数据Result mapper返回list_spring_08

并创建要返回的学生详细信息的实体类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文件如下

java中mapper返回数据Result mapper返回list_带有List属性的实体类查询_09

该Mapper有一个查询学生信息的方法对应studentMapper.xml里的selectStudentDetails。通过联合查询返回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是查询传递的参数，select是要查询的语句，同一个Mapper直接使用id，不同Mapper使用查询方法的全类名，这里把student_id作为参数传递给Course表的查询函数selectCourseByStuId。CourseMapper如下。

java中mapper返回数据Result mapper返回list_List_10