springboot中的group by查询显示 springboot关联查询

转载

mob6454cc6c1f4a 2024-02-19 17:44:25

文章标签 mybatis bc 关联查询 List 文章分类 架构后端开发

OK，这一篇博客继续讲解＜collection＞标签实现一对多关联查询。

其实，几乎是一模一样的做法！注意一对一的是 association，一对多的是 collection

在 <collection> 元素中通常使用以下属性。
property：指定映射到实体类的对象属性。
column：指定表中对应的字段（即查询返回的列名，用该字段去关联查询）。
ofType：指定映射到实体对象集合属性的类型。一对一的是 javaType
select：指定引入嵌套查询的子 SQL 语句，该属性用于关联映射中的嵌套查询。

为了大家查阅信息方便，这里直接给出核心代码（有2种方式），如果有时间可以继续往后面看！

<!-- 一对多关联查询方式1 -->
  <resultMap id="BaseResultMap_3" type="com.study.entity.StudentEntity">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="student_no" jdbcType="VARCHAR" property="studentNo" />
    <result column="student_name" jdbcType="VARCHAR" property="studentName" />
    <result column="introduce" jdbcType="VARCHAR" property="introduce" />

    <!-- 一对多关联查询 -->
    <collection
            property="courseList"
            column="student_no"
            ofType="com.study.entity.StudentCourseEntity"
            select="com.study.dao.StudentCourseMapper.getAllByNo"
    />

  </resultMap>

  <select id="getAllInfoByNo_3" parameterType="java.lang.String" resultMap="BaseResultMap_3">
    select * from t_student
    where student_no = #{studentNo,jdbcType=VARCHAR}
  </select>




  <!-- 一对多关联查询方式2 -->
  <resultMap id="BaseResultMap_4" type="com.study.entity.StudentEntity">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="student_no" jdbcType="VARCHAR" property="studentNo" />
    <result column="student_name" jdbcType="VARCHAR" property="studentName" />
    <result column="introduce" jdbcType="VARCHAR" property="introduce" />

    <!-- 一对多关联查询 -->
    <collection property="courseList" ofType="com.study.entity.StudentCourseEntity">
      <id column="rel_id" jdbcType="INTEGER" property="id" />
      <result column="rel_student_no" jdbcType="VARCHAR" property="studentNo" />
      <result column="course_name" jdbcType="VARCHAR" property="courseName" />
    </collection>

  </resultMap>

  <select id="getAllInfoByNo_4" parameterType="java.lang.String" resultMap="BaseResultMap_4">
    select s.*,c.id as rel_id,c.student_no as rel_student_no,c.course_name
    from t_student as s
    left join t_student_course as c
    on s.student_no = c.student_no
    where s.student_no = #{studentNo,jdbcType=VARCHAR}
  </select>

详细内容介绍

先创建一个简单的学生-课程表，并制造测试数据：

CREATE TABLE `t_student_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_no` varchar(50) DEFAULT NULL COMMENT '学号，对应 t_student 的 student_no 字段',
  `course_name` varchar(50) DEFAULT NULL COMMENT '课程名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后，创建学生-课程实体类：

package com.study.entity;

/**
 * @author biandan
 * @description 学生-课程实体类
 * @signature 让天下没有难写的代码
 * @create 2021-05-11 下午 10:44
 */
public class StudentCourseEntity {

    private Integer id;

    private String studentNo;//学号

    private String courseName;//课程名

    @Override
    public String toString() {
        return "StudentCourseEntity{" +
                "id=" + id +
                ", studentNo='" + studentNo + '\'' +
                ", courseName='" + courseName + '\'' +
                '}';
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getStudentNo() {
        return studentNo;
    }
    public void setStudentNo(String studentNo) {
        this.studentNo = studentNo;
    }
    public String getCourseName() {
        return courseName;
    }
    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }
}

创建 dao 层接口：

package com.study.dao;

import com.study.entity.InfoEntity;
import com.study.entity.StudentCourseEntity;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper //如果不在 dao 层增加 @Mapper 注解，就在启动类增加扫描 dao 层的包
public interface StudentCourseMapper {


    /**
     * 根据学号查询
     * @param studentNo
     * @return
     */
    List<StudentCourseEntity> getAllByNo(String studentNo);

}

在 resources 目录下创建 StudentCourseMapper.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.study.dao.StudentCourseMapper">

  <resultMap id="BaseResultMap" type="com.study.entity.StudentCourseEntity">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="student_no" jdbcType="VARCHAR" property="studentNo" />
    <result column="course_name" jdbcType="VARCHAR" property="courseName" />
  </resultMap>

  <!-- 根据学号查询信息 -->
  <select id="getAllByNo" parameterType="java.lang.String" resultMap="BaseResultMap">
    select * from t_student_course
    where student_no = #{studentNo,jdbcType=VARCHAR}
  </select>

</mapper>

然后，在学生实体类增加课程的集合类：

package com.study.entity;

import java.util.List;

public class StudentEntity {
    //主键ID
    private Integer id;

    //学号
    private String studentNo;

    //姓名
    private String studentName;

    //自我介绍
    private String introduce;

    //学生信息实体类
    private InfoEntity infoEntity;

    //学生-课程集合类
    private List<StudentCourseEntity> courseList;

    @Override
    public String toString() {
        return "StudentEntity{" +
                "id=" + id +
                ", studentNo='" + studentNo + '\'' +
                ", studentName='" + studentName + '\'' +
                ", introduce='" + introduce + '\'' +
                ", infoEntity=" + infoEntity +
                ", courseList=" + courseList +
                '}';
    }

    public List<StudentCourseEntity> getCourseList() {
        return courseList;
    }
    public void setCourseList(List<StudentCourseEntity> courseList) {
        this.courseList = courseList;
    }
    public InfoEntity getInfoEntity() {
        return infoEntity;
    }
    public void setInfoEntity(InfoEntity infoEntity) {
        this.infoEntity = infoEntity;
    }
    public Integer getId() {return id;}
    public void setId(Integer id) {
        this.id = id;
    }
    public String getStudentNo() {
        return studentNo;
    }
    public void setStudentNo(String studentNo) {
        this.studentNo = studentNo;
    }
    public String getStudentName() {
        return studentName;
    }
    public void setStudentName(String studentName) {
        this.studentName = studentName;
    }
    public String getIntroduce() {
        return introduce;
    }
    public void setIntroduce(String introduce) {
        this.introduce = introduce;
    }
}

然后，修改 StudentEntityMapper.xml （核心代码）

<!-- 一对多关联查询方式1 -->
  <resultMap id="BaseResultMap_3" type="com.study.entity.StudentEntity">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="student_no" jdbcType="VARCHAR" property="studentNo" />
    <result column="student_name" jdbcType="VARCHAR" property="studentName" />
    <result column="introduce" jdbcType="VARCHAR" property="introduce" />

    <!-- 一对多关联查询 -->
    <collection
            property="courseList"
            column="student_no"
            ofType="com.study.entity.StudentCourseEntity"
            select="com.study.dao.StudentCourseMapper.getAllByNo"
    />

  </resultMap>

  <select id="getAllInfoByNo_3" parameterType="java.lang.String" resultMap="BaseResultMap_3">
    select * from t_student
    where student_no = #{studentNo,jdbcType=VARCHAR}
  </select>




  <!-- 一对多关联查询方式2 -->
  <resultMap id="BaseResultMap_4" type="com.study.entity.StudentEntity">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="student_no" jdbcType="VARCHAR" property="studentNo" />
    <result column="student_name" jdbcType="VARCHAR" property="studentName" />
    <result column="introduce" jdbcType="VARCHAR" property="introduce" />

    <!-- 一对多关联查询 -->
    <collection property="courseList" ofType="com.study.entity.StudentCourseEntity">
      <id column="rel_id" jdbcType="INTEGER" property="id" />
      <result column="rel_student_no" jdbcType="VARCHAR" property="studentNo" />
      <result column="course_name" jdbcType="VARCHAR" property="courseName" />
    </collection>

  </resultMap>

  <select id="getAllInfoByNo_4" parameterType="java.lang.String" resultMap="BaseResultMap_4">
    select s.*,c.id as rel_id,c.student_no as rel_student_no,c.course_name
    from t_student as s
    left join t_student_course as c
    on s.student_no = c.student_no
    where s.student_no = #{studentNo,jdbcType=VARCHAR}
  </select>

dao 层接口：

List<StudentEntity> getAllInfoByNo_3(String studentNo);


List<StudentEntity> getAllInfoByNo_4(String studentNo);

调用类：

String studentNo = "12310";
        List<StudentEntity> list_3 = studentEntityMapper.getAllInfoByNo_3(studentNo);
        for(StudentEntity entity : list_3){
            System.out.println(entity.toString());
        }

        System.out.println("********************************");

        List<StudentEntity> list_4 = studentEntityMapper.getAllInfoByNo_4(studentNo);
        for(StudentEntity entity : list_4){
            System.out.println(entity.toString());
        }

测试结果：

JDBC Connection [com.mysql.jdbc.JDBC4Connection@7cce96dd] will not be managed by Spring
==>  Preparing: select * from t_student where student_no = ? 
==> Parameters: 12310(String)
<==    Columns: id, student_no, student_name, introduce
<==        Row: 1, 12310, 张三, 你好啊
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1740a047]
JDBC Connection [com.mysql.jdbc.JDBC4Connection@7cce96dd] will not be managed by Spring
==>  Preparing: select * from t_student_course where student_no = ? 
==> Parameters: 12310(String)
<==    Columns: id, student_no, course_name
<==        Row: 1, 12310, 语文
<==        Row: 2, 12310, 数学
<==        Row: 3, 12310, English
<==      Total: 3
StudentEntity{id=1, studentNo='12310', studentName='张三', introduce='你好啊', infoEntity=null, courseList=[StudentCourseEntity{id=1, studentNo='12310', courseName='语文'}, StudentCourseEntity{id=2, studentNo='12310', courseName='数学'}, StudentCourseEntity{id=3, studentNo='12310', courseName='English'}]}

*******************************************************

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1e8643c] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.jdbc.JDBC4Connection@7cce96dd] will not be managed by Spring
==>  Preparing: select s.*,c.id as rel_id,c.student_no as rel_student_no,c.course_name from t_student as s left join t_student_course as c on s.student_no = c.student_no where s.student_no = ? 
==> Parameters: 12310(String)
<==    Columns: id, student_no, student_name, introduce, rel_id, rel_student_no, course_name
<==        Row: 1, 12310, 张三, 你好啊, 1, 12310, 语文
<==        Row: 1, 12310, 张三, 你好啊, 2, 12310, 数学
<==        Row: 1, 12310, 张三, 你好啊, 3, 12310, English
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1e8643c]
StudentEntity{id=1, studentNo='12310', studentName='张三', introduce='你好啊', infoEntity=null, courseList=[StudentCourseEntity{id=1, studentNo='12310', courseName='语文'}, StudentCourseEntity{id=2, studentNo='12310', courseName='数学'}, StudentCourseEntity{id=3, studentNo='12310', courseName='English'}]}

OK，搞定一对多的关联查询。

那么，多对多的关联查询怎么办呢？比如一个学生可以选多门课程，一个课程可以被多个学生选择。

事实上，我们不需要考虑多对多如何实现，只需要考虑一对多就行。增加一个关联关系表，然后在学生实体类增加课程实体的集合、课程实体类增加学生实体的集合就行了。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。