MyBatis中collection标签的使用

tianliang_230918

已于 2023-09-24 22:48:45 修改

阅读量915

点赞数

CC 4.0 BY-SA版权

文章标签： mybatis java

于 2023-09-24 15:48:24 首次发布

本文链接：https://blog.youkuaiyun.com/tianliang_230918/article/details/133236157

一、需求如下：查找每个学生有哪些课程（cllection单条件查询）。

（一）表结构及数据如下。

CREATE TABLE `student` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(10) NOT NULL COMMENT '学生姓名',
`report_time` DATE NOT NULL COMMENT '报到日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息';

INSERT INTO student (name, report_time) VALUES ('孙悟空', '2023-07-15');
INSERT INTO student (name, report_time) VALUES ('伽罗', '2023-07-16');
INSERT INTO student (name, report_time) VALUES ('孙尚香', '2023-07-17');

CREATE TABLE course (
   id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
   course_name varchar(10) NOT NULL COMMENT '课程名',
   PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT '课程表';

INSERT INTO course (course_name) VALUES ('语文');
INSERT INTO course (course_name) VALUES ('数学');

CREATE TABLE s_info (
   id int(11) unsigned not null AUTO_INCREMENT COMMENT '主键',
   userinfo_id int(11) not null COMMENT '学生id',
   course_id int(11) not null COMMENT '课程id',
   select_course_time DATE not null COMMENT '选课日期',
   type tinyint(4) default 1 COMMENT '学习等级',
   PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='学生课程';

INSERT INTO s_info (userinfo_id, course_id, select_course_time, type) VALUES (1, 1, '2023-07-15', 1);
INSERT INTO s_info (userinfo_id, course_id, select_course_time, type) VALUES (2, 1, '2023-07-16', 1);
INSERT INTO s_info (userinfo_id, course_id, select_course_time, type) VALUES (3, 2, '2023-07-18', 2);
INSERT INTO s_info (userinfo_id, course_id, select_course_time, type) VALUES (1, 2, '2023-07-15', 2);
INSERT INTO s_info (userinfo_id, course_id, select_course_time, type) VALUES (2, 2, '2023-07-17', 1);

（二）创建对应的实体、控制层、服务层、实现层、dao层以及xml文件。

1、创建两个实体类。

（1）StudentInfoVO.java

package com.fl.vo;

import java.util.List;

public class StudentVO {

/**
* 主键
*/
private Integer id;

/**
* 学生姓名
*/
private String name;

/**
* 报到时间
*/
private String reportTime;

/**
* 学生信息
*/
private List<StudentInfoVO> studentInfos;

public Integer getId() {
return id;
}

public void setId(Integer id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public String getReportTime() {
return reportTime;
}

public void setReportTime(String reportTime) {
this.reportTime = reportTime;
}

public List<StudentInfoVO> getStudentInfos() {
return studentInfos;
}

public void setStudentInfos(List<StudentInfoVO> studentInfos) {
this.studentInfos = studentInfos;
}
}

（2）StudentVO.java

package com.fl.vo;

public class StudentInfoVO {

/**
* 主键
*/
private Integer sInfoId;

/**
* 课程名
*/
private String courseName;

/**
* 实习地址
*/
private String selectCourseTime;

public Integer getsInfoId() {
return sInfoId;
}

public void setsInfoId(Integer sInfoId) {
this.sInfoId = sInfoId;
}

public String getCourseName() {
return courseName;
}

public void setCourseName(String courseName) {
this.courseName = courseName;
}

public String getSelectCourseTime() {
return selectCourseTime;
}

public void setSelectCourseTime(String selectCourseTime) {
this.selectCourseTime = selectCourseTime;
}
}

2、创建 StudentDao。

package com.fl.dao;

import com.fl.vo.StudentVO;
import java.util.List;

public interface StudentDao {
List<StudentVO> getUserInfo();
}

3、创建 StudentService.java。

package com.fl.service;

import com.fl.common.Pagination;
import java.util.Map;

public interface StudentService {
Map<String, Object> getUserInfo(Pagination page);
}

4、创建 StudentServiceImpl.java。

package com.fl.service.impl;

import com.fl.common.Pagination;
import com.fl.dao.StudentDao;
import com.fl.service.StudentService;
import com.fl.vo.StudentVO;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
public class StudentServiceImpl implements StudentService {

@Autowired
private StudentDao studentDao;

@Override
public Map<String, Object> getUserInfo(Pagination page) {
HashMap<String, Object> map = new HashMap<>();
PageHelper.startPage(Optional.ofNullable(page.getPageNum()).orElse(1),
Optional.ofNullable(page.getPageSize()).orElse(15));
List<StudentVO> list = studentDao.getUserInfo();
PageInfo<StudentVO> pageInfo = new PageInfo<>(list);
map.put("list", list);
map.put("total", pageInfo.getTotal());
return map;
}
}

5、创建 StudentController.java。

package com.fl.controller;

import com.fl.common.Pagination;
import com.fl.common.ResultModel;
import com.fl.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class StudentController {

@Autowired
private StudentService studentService;

@GetMapping("/userinfo")
public ResultModel getUserInfo(Pagination page) {
return new ResultModel(studentService.getUserInfo(page));
}
}

6、创建 StudentDao.xml。

有两种方式实现此需求。

（1）方法一(推荐)，使用collection-select。

<?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.fl.dao.StudentDao">

<resultMap id="studentMap" type="com.fl.vo.StudentVO">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<collection property="studentInfos" column="id" select="com.fl.dao.StudentDao.getCourseInfo"/>
</resultMap>

<select id="getUserInfo" resultMap="studentMap">
select
s.id,
s.name
from student s
</select>

<select id="getCourseInfo" resultType="com.fl.vo.StudentInfoVO">
select
si.id as sInfoId,
c.course_name as courseName
from s_info si
left join course c ON c.id = si.course_id
where si.userinfo_id = #{id}
</select>
</mapper>

结果如下：

（2）方法二(如果使用了PageHelper分页，则不推荐使用此方法。因为返回的总数不对，它是先执行的分页统计，然后才进行分组)。

<?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.fl.dao.StudentDao">

<select id="getUserInfo" resultMap="studentMap">
select
s.id,
s.name,
si.id as sInfoId,
c.course_name as courseName
from student s
left join s_info si on si.userinfo_id = s.id
left join course c ON c.id = si.course_id
</select>
</mapper>

结果如下：

二、需求如下：查找学生报到的当天，报考的课程。(collection多条件查询)。

（一）实体、控制层、服务层、实现层、dao层同上，

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.fl.dao.StudentDao">

<select id="getUserInfo" resultMap="studentMap">
select
s.id,
s.name,
s.report_time
from student s
</select>

<select id="getCourseInfo" resultType="com.fl.vo.StudentInfoVO">
select
si.id as sInfoId,
c.course_name as courseName,
si.select_course_time as selectCourseTime
from s_info si
left join course c ON c.id = si.course_id
where si.userinfo_id = #{s_id}
and si.select_course_time = #{s_time}
</select>

</mapper>

备注：collection 标签的 column，可通过map的形式传递参数。

三、需求如下：查找学生报到的当天，报考的课程，并且学生等级为1。(collection多条件查询)。

1、修改 StudentServiceImpl.java

package com.fl.service.impl;

import java.util.*;

@Service
public class StudentServiceImpl implements StudentService {

@Autowired
private StudentDao studentDao;

@Override
public Map<String, Object> getUserInfo(Pagination page) {
HashMap<String, Object> map = new HashMap<>();
PageHelper.startPage(Optional.ofNullable(page.getPageNum()).orElse(1),
Optional.ofNullable(page.getPageSize()).orElse(15));
List<StudentVO> list = studentDao.getUserInfo(2);
PageInfo<StudentVO> pageInfo = new PageInfo<>(list);
map.put("list", list);
map.put("total", pageInfo.getTotal());
return map;
}
}

2、修改 StudentDao.java

package com.fl.dao;

import com.fl.vo.StudentVO;
import java.util.List;

public interface StudentDao {
List<StudentVO> getUserInfo(int type);
}

3、修改 StudentDao.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.fl.dao.StudentDao">

<select id="getUserInfo" resultMap="studentMap">
select
s.id,
s.name,
s.report_time,
#{type} as type
from student s
</select>

</mapper>