Mybatis中联表查询（一对多）处理方式总结（mybatis plus 联表查询）

25-03-06 10

这篇文章主要围绕Mybatis中联表查询和一对多处理方式总结展开，旨在为您提供一份详细的参考资料。我们将全面介绍Mybatis中联表查询的优缺点，解答一对多处理方式总结的相关问题，同时也会为您带来2.

这篇文章主要围绕Mybatis中联表查询和一对多处理方式总结展开，旨在为您提供一份详细的参考资料。我们将全面介绍Mybatis中联表查询的优缺点，解答一对多处理方式总结的相关问题，同时也会为您带来2.3 mybatis增删改查注解配置，多表配置（一对一，一对多，多对多）整合ssm、8_MyBatis实现多表查询-3_一对多关联查询、java day55【 Mybatis 连接池与事务深入、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多、 Mybatis 多表查询之多对多】、Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多的实用方法。

本文目录一览：

Mybatis中联表查询（一对多）处理方式总结（mybatis plus 联表查询）
2.3 mybatis增删改查注解配置，多表配置（一对一，一对多，多对多）整合ssm
8_MyBatis实现多表查询-3_一对多关联查询
java day55【 Mybatis 连接池与事务深入、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多、 Mybatis 多表查询之多对多】
Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多

Mybatis中联表查询（一对多）处理方式总结（mybatis plus 联表查询）

1、创建表格环境，MysqL中的数据表格如下：

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=utf8INSERT 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');

第1章 Mybatis 连接池与事务深入

1.1 Mybatis 的连接池技术

1.1.1 Mybatis 连接池的分类

1.1.2 Mybatis 中数据源的配置

1.1.3 Mybatis 中 DataSource 的存取

1.1.4 Mybatis 中连接的获取过程分析

1.2 Mybatis 的事务控制

1.2.1 JDBC 中事务的回顾

1.2.2 Mybatis 中事务提交方式

1.2.3 Mybatis 自动提交事务的设置

第2章 Mybatis 的动态 SQL 语句

2.1 动态 SQL 之标签

2.1.1 持久层 Dao 接口

2.1.2 持久层 Dao 映射配置

2.1.3 测试

2.2 动态 SQL 之标签

2.2.1 持久层 Dao 映射配置

2.3 动态标签之标签

2.3.1 需求

2.3.1.1 在 QueryVo 中加入一个 List 集合用于封装参数

2.3.2 持久层 Dao 接口

2.3.3 持久层 Dao 映射配置

2.3.3.1 编写测试方法

2.4 Mybatis 中简化编写的 SQL 片段

2.4.1 定义代码片段

2.4.2 引用代码片段

第3章 Mybatis 多表查询之一对多

3.1 一对一查询(多对一)

3.1.1 方式一

3.1.1.1 定义账户信息的实体类

3.1.1.2 编写 Sql 语句

3.1.1.3 定义 AccountUser 类

3.1.1.4 定义账户的持久层 Dao 接口

3.1.1.5 定义 AccountDao.xml 文件中的查询配置信息

3.1.1.6 创建 AccountTest 测试类

3.1.1.7 小结：

3.1.2 方式二

3.1.2.1 修改 Account 类

在 Account 类中加入 User 类的对象作为 Account 类的一个属性。

3.1.2.2 修改 AccountDao 接口中的方法

3.1.2.3 重新定义 AccountDao.xml 文件

3.1.2.4 在 AccountTest 类中加入测试方法

3.2 一对多查询

3.2.1 编写 SQL 语句

3.2.2 User 类加入 List

3.2.3 用户持久层 Dao 接口中加入查询方法

3.2.4 用户持久层 Dao 映射文件配置

3.2.5 测试方法

第4章 Mybatis 多表查询之多对多

4.1 实现 Role 到 User 多对多

4.1.1 用户与角色的关系模型

4.1.2 业务要求及实现 SQL

4.1.3 编写角色实体类

4.1.4 编写 Role 持久层接口

4.1.5 编写映射文件

4.1.6 编写测试类

4.2 实现 User 到 Role 的多对多

4.2.1 User 到 Role 的多对多

4.2.2 作业：实现 User 到 Role 的一对多查询

Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多

项目搭建Springboot 1.5

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>

    <groupId>cn.cnki</groupId>
    <artifactId>ref</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>war</packaging>

    <name>ref</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.14.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </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>
        <!-- mysql连接 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!-- 阿里系的Druid依赖包 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.5</version>
        </dependency>
        <!--pageHelper分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.3.18.RELEASE</version>
        </dependency>
        <!--To display the auto-configuration report re-run your application with ''debug'' enabled. -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.0.7.Final</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

View Code

application.properties

server.port = 8080
########################################################
###mybatis-mysql-druid
########################################################
#sql配置文件的路径
mybatis.mapper-locations="classpath:mapper/*.xml"
#这个是mybatis全局配置文件路径
mybatis.config-location="classpath:mapper/config/mybatis-config.xml"
#实体类路径
mybatis.type-aliases-package="cn.cnki.ref.pojo"
# 驱动配置信息
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/springbootmybatis?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.jdbc.Driver
########################################################

View Code

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties>
        <property name="dialect" value="mysql" />
    </properties>
    <settings>
        <!-- 这个配置使全局的映射器启用或禁用缓存。系统默认值是true，设置只是为了展示出来 -->
        <setting name="cacheEnabled" value="true" />
        <!-- 全局启用或禁用延迟加载。当禁用时，所有关联对象都会即时加载。 系统默认值是true，设置只是为了展示出来 -->
        <setting name="lazyLoadingEnabled" value="true" />
        <!-- 允许或不允许多种结果集从一个单独的语句中返回（需要适合的驱动）。 系统默认值是true，设置只是为了展示出来 -->
        <setting name="multipleResultSetsEnabled" value="true" />
        <!--使用列标签代替列名。不同的驱动在这方便表现不同。参考驱动文档或充分测试两种方法来决定所使用的驱动。 系统默认值是true，设置只是为了展示出来 -->
        <setting name="useColumnLabel" value="true" />
        <!--允许 JDBC 支持生成的键。需要适合的驱动。如果设置为 true 则这个设置强制生成的键被使用，尽管一些驱动拒绝兼容但仍然有效（比如 
            Derby）。 系统默认值是false，设置只是为了展示出来 -->
        <setting name="useGeneratedKeys" value="false" />
        <!--配置默认的执行器。SIMPLE 执行器没有什么特别之处。REUSE 执行器重用预处理语句。BATCH 执行器重用语句和批量更新 系统默认值是SIMPLE，设置只是为了展示出来 -->
        <setting name="defaultExecutorType" value="SIMPLE" />
        <!--设置超时时间，它决定驱动等待一个数据库响应的时间。 系统默认值是null，设置只是为了展示出来 -->
        <setting name="defaultStatementTimeout" value="25000" />
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageHelper">
            <property name="dialect" value="mysql" />
            <property name="offsetAsPageNum" value="true" />
            <property name="rowBoundsWithCount" value="true" />
            <property name="pageSizeZero" value="true" />
            <property name="reasonable" value="true" />
        </plugin>
    </plugins>
</configuration>

View Code

RefApplication

package cn.cnki.ref;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;


@SpringBootApplication(exclude={DataSourceAutoConfiguration.class,HibernateJpaAutoConfiguration.class})
@MapperScan("cn.cnki.ref.mapper")
public class RefApplication {

    public static void main(String[] args) {
        SpringApplication.run(RefApplication.class, args);
    }

    //datasource注入
    @Bean(initMethod = "init", destroyMethod = "close")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DruidDataSource dataSource() {
        return new DruidDataSource();
    }

    //mybatis SQLSession注入
    @Bean
    public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource());
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:/mapper/*Mapper.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    //事务支持
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

}

View Code

一对一association

　　每个学生对应一张身份证，每张身份证上有身份证号cardId、开始日期、结束日期。并且建立与student表的外键关系。

cardid表、student表

/*Table structure for table `cardid` */

DROP TABLE IF EXISTS `cardid`;

CREATE TABLE `cardid` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cardId` varchar(18) NOT NULL,
  `sid` int(10) NOT NULL,
  `beginTime` datetime NOT NULL,
  `endTime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `cardid` */

insert  into `cardid`(`id`,`cardId`,`sid`,`beginTime`,`endTime`) values (1,''412728199513161601'',1,''2008-07-19 11:19:27'',''2018-07-19 11:19:30''),(2,''412728199513161602'',2,''2008-04-01 11:20:13'',''2018-04-01 11:20:13''),(3,''412728199513161603'',3,''2008-07-19 11:21:06'',''2018-07-19 11:21:16'');

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`) values (1,''张三''),(2,''李四''),(3,''王五'');

View Code

Cardid实体

package cn.cnki.ref.pojo;

import java.util.Date;

public class Cardid {
    private Integer id;

    private String cardid;

    private Integer sid;

    private Date begintime;

    private Date endtime;

    private Student student;

    public Student getStudent() {
        return student;
    }
    public void setStudent(Student student) {
        this.student = student;
    }

    public Integer getId() {
        return id;
    }

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

    public String getCardid() {
        return cardid;
    }

    public void setCardid(String cardid) {
        this.cardid = cardid == null ? null : cardid.trim();
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public Date getBegintime() {
        return begintime;
    }

    public void setBegintime(Date begintime) {
        this.begintime = begintime;
    }

    public Date getEndtime() {
        return endtime;
    }

    public void setEndtime(Date endtime) {
        this.endtime = endtime;
    }
}

View Code

Student实体

package cn.cnki.ref.pojo;

public class Student {
    private Integer id;

    private String name;

    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 == null ? null : name.trim();
    }
}

View Code

CardidMapper

package cn.cnki.ref.mapper;

import cn.cnki.ref.pojo.Cardid;
import java.util.List;

public interface CardidMapper {
    /**
     * 获取包括学生信息的身份证列表
     * @return
     */
    public List<Cardid>  getListIdOfStudent();
}

View Code

CardidMapper.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="cn.cnki.ref.mapper.CardidMapper" >

  <!--  学生表与身份证表的一对一映射-->
  <resultMap type="cn.cnki.ref.pojo.Cardid" id="IDCardOfStudentMap">
    <id property="id" column="id"  jdbcType="INTEGER"/>
    <result property="cardid" column="cardid"  jdbcType="VARCHAR" />
    <result property="begintime" column="beginTime" jdbcType="TIMESTAMP"/>
    <result property="endtime" column="endTime" jdbcType="TIMESTAMP"/>
    <association property="student" javaType="cn.cnki.ref.pojo.Student">
      <id property="id" column="id" jdbcType="INTEGER"/>
      <result property="name" column="name" jdbcType="VARCHAR"/>
    </association>
  </resultMap>

  <!-- 查出带有学生信息的身份证列表 -->
  <select id="getListIdOfStudent" resultMap="IDCardOfStudentMap">
    SELECT * FROM cardid a,student b WHERE a.sid=b.id;
  </select>


</mapper>

View Code

CardidController

package cn.cnki.ref.controller;

import cn.cnki.ref.mapper.CardidMapper;
import cn.cnki.ref.pojo.Cardid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/Cardid")
public class CardidController {

    @Autowired
    private CardidMapper CardidMapper;
    @RequestMapping(value = "/GetListIdOfStudent", method = RequestMethod.GET)
    public List<Cardid> index() {
        return CardidMapper.getListIdOfStudent();
    }

}

View Code

测试

http://localhost:8080/Cardid/GetListIdOfStudent

一对多Collection

　　一个人有好多本书，每本书的主人只有一个人。当我们查询某个人拥有的所有书籍时，就涉及到了一对多的映射关系。

book表

/*Table structure for table `book` */

DROP TABLE IF EXISTS `book`;

CREATE TABLE `book` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `sid` int(10) NOT NULL,
  `price` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

/*Data for the table `book` */

insert  into `book`(`id`,`name`,`sid`,`price`) values (1,''《黑客与画家》'',1,''45''),(2,''《北方的空地》'',1,''40'');

View Code

Book实体

package cn.cnki.ref.pojo;

public class Book {
    private Integer id;

    private String name;

    private Integer sid;

    private Long price;

    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 == null ? null : name.trim();
    }

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public Long getPrice() {
        return price;
    }

    public void setPrice(Long price) {
        this.price = price;
    }
}

View Code

Student实体

package cn.cnki.ref.pojo;

import java.util.List;

public class Student {
    private Integer id;

    private String name;

    private List<Book> books;

    public Student() {

    }


    public List<Book> getBooks() {
        return books;
    }


    public void setBooks(List<Book> books) {
        this.books = books;
    }


    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 == null ? null : name.trim();
    }
}

View Code

StudentMapper

package cn.cnki.ref.mapper;

import cn.cnki.ref.pojo.Student;

import java.util.List;

public interface StudentMapper {

    /**
     * 根据id查询
     * @param id
     * @return
     */
    public Student getById(Integer id);

    /**
     * 查询全部
     * @return
     */
    public List<Student> list();

    /**
     * 插入
     * @param student
     */
    public int insert(Student student);
    /**
     * 主键回填的插入
     * @param student
     * @return
     */
    public int insertToId(Student student);

    /**
     * 根据student的id修改
     * @param student
     */
    public void update(Student student);

    /**
     * 根据id删除
     * @param id
     */
    public void delete(Integer id);

    /**
     * 根据id查询所有的书
     * @param id
     */
    public Student selectBookById(Integer id);

}

View Code

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="cn.cnki.ref.mapper.StudentMapper">

    <!-- 根据id查询 -->
    <select id="getById" parameterType="int" resultType="cn.cnki.ref.pojo.Student">
    select * from student where id=#{id}
    </select>
    <!-- 查询所有 -->
    <select id="list" parameterType="int" resultType="cn.cnki.ref.pojo.Student">
    select * from student
    </select>

    <!-- 插入一个学生 -->
    <insert id="insert" parameterType="cn.cnki.ref.pojo.Student">
    insert into student(name) values(#{name})
    </insert>
    <!-- 主键回填的插入 -->
    <insert id="insertToId" parameterType="cn.cnki.ref.pojo.Student" useGeneratedKeys="true" keyProperty="id">
    insert into student(name) values(#{name})
    </insert>

    <!-- 根据id修改学生信息 -->
    <update id="update" parameterType="cn.cnki.ref.pojo.Student">
    update student set name=#{name} where id=#{id}
    </update>

    <!-- 根据id删除学生 -->
    <delete id="delete" parameterType="int">
    delete  from student where id=#{id}
    </delete>

    <resultMap type="cn.cnki.ref.pojo.Student" id="bookMap">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <collection property="books" ofType="cn.cnki.ref.pojo.Book" javaType="ArrayList" >
            <id property="id" column="bid" jdbcType="INTEGER"/>
            <result property="name" column="bname" jdbcType="VARCHAR" />
            <result property="price" column="price" jdbcType="DECIMAL"/>
        </collection>
    </resultMap>
    <!--根据id查询所有的书  -->
    <select id="selectBookById" parameterType="int" resultMap="bookMap">
      select a.*,b.id bid,b.name bname,b.price from student a,book b where a.id=b.sid and a.id=#{id};
    </select>
</mapper>

View Code

StudentController

package cn.cnki.ref.controller;

import cn.cnki.ref.mapper.StudentMapper;
import cn.cnki.ref.pojo.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
public class StudentController {
    @Autowired
    private StudentMapper studentMapper;

    /**
     * 根据id查询学生
     * @param id
     * @return
     */
    @GetMapping("/student/{id}")
    public Student getById(@PathVariable("id") Integer id) {

        Student student = studentMapper.getById(id);
        return student;

    }

    /**
     * 查询全部
     * @return
     */
    @GetMapping("/students")
    public List<Student> list(){
        List<Student> students = studentMapper.list();
        return students;
    }

    /**
     * 插入
     * @param student
     */
    @PostMapping("/student")
    public void insert( Student student) {
        studentMapper.insert(student);
    }

    /**
     * 修改
     * @param student
     */
    @PutMapping("/student/{id}")
    public void update(Student student,@PathVariable("id")Integer id) {
        studentMapper.update(student);
    }

    /**
     * 根据id删除
     * @param id
     */
    @DeleteMapping("/student/{id}")
    public void delete(@PathVariable("id") Integer id) {
        studentMapper.delete(id);
    }

    /**
     * 根据id查询所有的书
     */
    @GetMapping("/student/book/{id}")
    public Student getBooks(@PathVariable("id") Integer id) {
        Student student = studentMapper.selectBookById(id);
        return student;
    }

}

View Code

测试

http://localhost:8080//student/book/1

多对多——查询某个学生所选的全部课程代码实现

　　之前我们探讨了一对一、一对多的映射关系，今天我们来讨论多对多的映射关系。

　　多对多，其实可以拆成多个一对多来理解。

　　比如：

　　学生-------课程----------学生选课的关系：

　　（1）查询某个学生所选的全部课程；

　　（2）查询选修某个课程的全部学生；

course表、student_course表

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`id`,`name`) values (1,''语文''),(2,''数学''),(3,''英语'');

/*Table structure for table `student_course` */

DROP TABLE IF EXISTS `student_course`;

CREATE TABLE `student_course` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sid` int(10) NOT NULL,
  `cid` int(10) NOT NULL,
  `score` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `student_course` */

insert  into `student_course`(`id`,`sid`,`cid`,`score`) values (1,1,1,91),(2,1,2,92),(3,1,3,93),(4,2,1,81),(5,2,2,82),(6,2,3,83),(7,3,1,71),(8,3,2,72),(9,3,3,73);

View Code

Course实体

package cn.cnki.ref.pojo;

public class Course {
    private Integer id;

    private String name;

    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 == null ? null : name.trim();
    }
}

View Code

StudentCourse实体

package cn.cnki.ref.pojo;

import java.util.List;

public class StudentCourse {
    private Integer id;

    private Integer sid;

    private Integer cid;

    private Integer score;

    private List<Student> students;//一门课有多名学生

    private List<Course> courses;//一名学生有多门课

    public Integer getId() {
        return id;
    }

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

    public Integer getSid() {
        return sid;
    }

    public void setSid(Integer sid) {
        this.sid = sid;
    }

    public Integer getCid() {
        return cid;
    }

    public void setCid(Integer cid) {
        this.cid = cid;
    }

    public Integer getScore() {
        return score;
    }

    public void setScore(Integer score) {
        this.score = score;
    }

    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
    public List<Course> getCourses() {
        return courses;
    }
    public void setCourses(List<Course> courses) {
        this.courses = courses;
    }
}

View Code

在Studnent实体中添加StudentCourse列表

package cn.cnki.ref.pojo;

import java.util.List;

public class Student {
    private Integer id;

    private String name;

    private List<Book> books;//一名学生有多本书

    private List<StudentCourse> studentCourses;

    public Student() {

    }

    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 == null ? null : name.trim();
    }

    public List<Book> getBooks() {
        return books;
    }

    public List<StudentCourse> getStudentCourses() {
        return studentCourses;
    }

    public void setStudentCourses(List<StudentCourse> studentCourses) {
        this.studentCourses = studentCourses;
    }

    public void setBooks(List<Book> books) {
        this.books = books;
    }
}

View Code

StudentCourseMapper

package cn.cnki.ref.mapper;

import cn.cnki.ref.pojo.Student;

public interface  StudentCourseMapper {
    /**
     * 根据学生id查询该学生选修的所有课程
     * @param id
     * @return
     */
    public Student selectCourseById(Integer id);
}

View Code

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="cn.cnki.ref.mapper.StudentCourseMapper">

    <resultMap type="cn.cnki.ref.pojo.Student" id="studentMap">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
    </resultMap>

    <resultMap type="cn.cnki.ref.pojo.Student" id="courseMap" extends="studentMap">
        <collection property="studentCourses" ofType="cn.cnki.ref.pojo.StudentCourse" javaType="ArrayList">
            <result property="score" column="score" jdbcType="INTEGER"/>
            <collection property="courses" ofType="cn.cnki.ref.pojo.Course" javaType="ArrayList">
                <id property="id" column="cid" jdbcType="INTEGER"/>
                <result property="name" column="cname" jdbcType="VARCHAR"/>
            </collection>
        </collection>
    </resultMap>

    <!-- 根据学生id查询该学生选修的所有课程 -->
    <select id="selectCourseById" parameterType="int" resultMap="courseMap" >
        select a.*,b.score,c.id cid,c.name cname from student a,student_course b,course c where a.id=b.sid and b.cid=c.id and a.id=#{id}
    </select>
</mapper>

View Code

StudentCourseController

package cn.cnki.ref.controller;

import cn.cnki.ref.mapper.StudentCourseMapper;
import cn.cnki.ref.pojo.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class StudentCourseController {
    @Autowired
    private StudentCourseMapper studentCourseMapper;

    /************/

    /**
     * 根据学生id查询该学生选修的所有课程
     * @param id
     * @return
     */
    @GetMapping("/student/course/{id}")
    public Student selectCourseById(@PathVariable("id") Integer id) {
        Student student = studentCourseMapper.selectCourseById(id);
        return student;
    }
}

View Code

测试

http://localhost:8080/student/course/1

多对多——查询选修某个课程的全部学生代码实现

Course实体中添加StudentCourse列表

package cn.cnki.ref.pojo;

import java.util.List;

public class Course {
    private Integer id;

    private String name;

    private List<StudentCourse> studentCourses;//

    public List<StudentCourse> getStudentCourses() {
        return studentCourses;
    }
    public void setStudentCourses(List<StudentCourse> studentCourses) {
        this.studentCourses = studentCourses;
    }

    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 == null ? null : name.trim();
    }
}

View Code

CourseMapper

package cn.cnki.ref.mapper;

import cn.cnki.ref.pojo.Course;

public interface CourseMapper {
    /**
     * 根据课程id查询选修此课程的全部学生
     * @param id
     * @return
     */
    public Course selectStudentById(Integer id);

}

View Code

CourseMapper.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="cn.cnki.ref.mapper.CourseMapper">
    <resultMap type="cn.cnki.ref.pojo.Course" id="courseMap">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>
    <resultMap type="cn.cnki.ref.pojo.Course" id="studentMap" extends="courseMap">
        <collection property="studentCourses" ofType="cn.cnki.ref.pojo.StudentCourse">
            <result property="score" column="score"/>
            <collection property="students" ofType="cn.cnki.ref.pojo.Student">
                <id property="id" column="sid" jdbcType="INTEGER"/>
                <result property="name" column="sname" jdbcType="VARCHAR"/>
            </collection>
        </collection>
    </resultMap>
    <!-- 根据课程id查询选修此课程的全部学生 -->
    <select id="selectStudentById" parameterType="int" resultMap="studentMap">
     select a.*,b.score,c.id sid,c.name sname from student c,student_course b,course a where a.id=b.cid and b.sid=c.id and a.id=#{id}
    </select>
</mapper>

View Code

CourseController

package cn.cnki.ref.controller;

import cn.cnki.ref.mapper.CourseMapper;
import cn.cnki.ref.pojo.Course;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;


@RestController
public class CourseController {
    @Autowired
    private CourseMapper courseMapper;

    /**
     * 根据课程id查询选修此课程的全部学生
     * @param id
     * @return
     */
    @GetMapping("/course/student/{id}")
    public Course selectStudentById(@PathVariable("id")Integer id) {

        Course course = courseMapper.selectStudentById(id);
        return course;

    }

}

View Code

测试

http://localhost:8080//course/student/1

资料

源码下载

http://www.cnblogs.com/TimerHotel/tag/mybatis/

https://www.cnblogs.com/ashleyboy/p/9271597.html

我们今天的关于Mybatis中联表查询和一对多处理方式总结的分享已经告一段落，感谢您的关注，如果您想了解更多关于2.3 mybatis增删改查注解配置，多表配置（一对一，一对多，多对多）整合ssm、8_MyBatis实现多表查询-3_一对多关联查询、java day55【 Mybatis 连接池与事务深入、 Mybatis 的动态 SQL 语句、 Mybatis 多表查询之一对多、 Mybatis 多表查询之多对多】、Java SSM框架之MyBatis3（四）MyBatis之一对一、一对多、多对多的相关信息，请在本站查询。

本文标签：