Mybatis之一对多，多对一查询

最新推荐文章于 2024-07-31 13:56:59 发布

m0_72068475

最新推荐文章于 2024-07-31 13:56:59 发布

阅读量401

点赞数

文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/m0_72068475/article/details/126346832

版权

需求

现有一个班级，班级内有多个学生，1个老师，该场景为多对一，一对多

要求使用MyBatis查出学生对应的老师，及每个老师教的学生。

数据表准备

student：

CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`tid` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
)

teacher：

CREATE TABLE `teacher` (
`id` int(10) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
)

创建好表格后随便设立几个学生信息和老师信息，然后我们就可以开始创建我们的程序了。创建spring项目，然后接下来配置pom文件，主要是应用数据库相关依赖。

程序创建：

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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.tlkj</groupId>
    <artifactId>select</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>select</name>
    <description>select</description>

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

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
        <!--thymeleaf模板引擎配置-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!--Web依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--MyBatis配置-->
        <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-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.7.RELEASE</version>
                <configuration>
                    <mainClass>com.tlkj.select.SelectApplication</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

然后在resource文件夹下创建spring可以识别的application.yml文件。

application.yml

server:
  port: 8081
spring:
  datasource:
    name: test  #数据库名
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf8
    username: root  #用户名
    password: 123456  #密码
    driver-class-name: com.mysql.cj.jdbc.Driver  #数据库链接驱动

mybatis:
  mapper-locations[0]: classpath:mapper/*.xml  #配置映射文件
  type-aliases-package: com.example.select.bean #配置实体类

Bean文件

创建Bean文件，用于封装学生和老师的属性。封装之后记得同时生成他们的setter和getter。

学生的Bean：

public class StuBean {
    private Integer id;
    private String name;
    private Integer tid;
    private TeaBean teaBean;
}

老师的Bean：

public class TeaBean {
    private Integer id;
    private String name;
    private List<StuBean> studentList;
}

值得注意的事情是：学生Bean里面有一个老师的信息，即tid，也就是老师的id。然后，老师的Bean里面有一个List集合用于存储老师名下学生的信息。

然后就是创建mapper.xml文件，也就是重点。

一对多查询：（一个老师查询他的所有学生）

<select id="teac" parameterType="com.tlkj.select.bean.TeaBean" resultMap="TeacherStudent">
        select * from teacher where id = #{id}
    </select>
    <resultMap id="TeacherStudent" type="com.tlkj.select.bean.TeaBean">
        <result property="id" column="id"/>
        <collection property="studentList" javaType="ArrayList" ofType="com.tlkj.select.bean.StuBean" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getStudentByTeacherId" parameterType="com.tlkj.select.bean.StuBean" resultType="com.tlkj.select.bean.StuBean">
        select * from student where tid = #{id}
    </select>

多对一查询：（从学生查老师）

<select id="stu" parameterType="com.tlkj.select.bean.StuBean" resultMap="getStudentTeacher">
        select * from student where id = #{id}
    </select>
    <resultMap id="getStudentTeacher" type="com.tlkj.select.bean.StuBean">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="tid" column="tid"/>
        <association property="teaBean" column="tid"  javaType="com.tlkj.select.bean.TeaBean" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" parameterType="com.tlkj.select.bean.TeaBean" resultType="com.tlkj.select.bean.TeaBean">
        select * from teacher where id = #{tid}
    </select>

mapper.java

StuMapper,java

@Mapper
public interface StuMapper {
    StuBean stu(@Param("id") Integer id ,@Param("name")String name,@Param("tid")Integer tid);
}

TeaMapper.java

@Mapper
public interface TeaMapper {
    TeaBean teac(@Param("id") Integer id, @Param("name") String name);
}

Mapper里面值得注意的事情是要使用@Param注释每一个变量。

然后就是常规的service层。

service.java

首先是两个查询方式的接口：

public interface StuService {
    StuBean stud(Integer id ,String name,Integer tid);
}

public interface TeaService {
    TeaBean teach(Integer id,String name);
}

然后就是两个接口的实现类

@Service
public class StuServiceImpl implements StuService {
    @Autowired(required = false)
    private StuMapper stuMapper;
    @Override
    public StuBean stud(Integer id ,String name,Integer tid){
        return stuMapper.stu(id, name, tid);
    }
}

@Service
public class TeaServiceImpl implements TeaService {
    @Autowired(required = false)
    private TeaMapper teaMapper;
    @Override
    public TeaBean teach(Integer id, String name) {
        return teaMapper.teac(id,name);
    }
}

controller.java

这一层主要是处理一下前端过来的请求。以及部分业务处理。

@RestController
public class StuController {
    @Autowired
    StuService stuService;
    @RequestMapping(value = "/stuselect",method = RequestMethod.POST)
    public String studen(@RequestBody StuBean stuBean){
        StuBean stuBean1 = stuService.stud(stuBean.getId(),stuBean.getName(),stuBean.getTid());
        if (stuBean1==null){
            return "查询失败";
        }
        else{
            return "学生："+stuBean1.getName()+" 的老师是 "+ stuBean1.getTeaBean().getName();
        }
    }
}

@RestController
public class TeaController {
    @Autowired
    TeaService teaService;
    @RequestMapping(value = "/teaselect",method = RequestMethod.POST)
    public String teacher(@RequestBody TeaBean teaBean1){
        TeaBean teaBean = teaService.teach(teaBean1.getId(),teaBean1.getName());
        if (teaBean==null){
            return "查询失败";
        }
        else{
            String  str = teaBean.getName()+"的学生是： ";
            System.out.println(teaBean.getStudentList().size());
            for (int i =0;i <teaBean.getStudentList().size();i++) {
                String str1 = teaBean.getStudentList().get(i).getName();
                str = new StringBuffer().append(str).append(str1).append("  ").toString();
            }
            return str;
        }
    }
}

这个地方需要注意的事情是，我是通过前postman发送post请求过来的，需要用到@RequeBody注解。以及后面查询老师对应学生信息的时候，学生集合中读取学生名字，要调用get方法然后再调用getName方法。

启动类：

@MapperScan("com.tlkj.select.mapper.*")
@SpringBootApplication
public class SelectApplication {
    public static void main(String[] args) {
        SpringApplication.run(SelectApplication.class, args);
    }
}

启动类上方记得添加@MapperScan注释。