10、MyBatis多对一处理

最新推荐文章于 2022-09-11 10:02:14 发布

工一木子

最新推荐文章于 2022-09-11 10:02:14 发布

阅读量433

点赞数

分类专栏： MyBatis 文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/Prince140678/article/details/121449482

版权

MyBatis 专栏收录该内容

14 篇文章 1 订阅

订阅专栏

10、多对一处理

多对一：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-voPZt8D0-1637457695772)(C:\Users\Administrator\Desktop\学习笔记\mybatis\MyBatis学习.assets\image-20211120220024122.png)]$

多个学生对应一个老师
对于学生这边而言，关联… 多个学生关联一个老师【多对一】
对于老师而言，集合…一个老师，有很多学生【一对多】

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Xb100x6-1637457695777)(C:\Users\Administrator\Desktop\学习笔记\mybatis\MyBatis学习.assets\image-20211120221633998.png)]$

SQL

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`),
    constraint `fktid` foreign key (`tid`) references `teacher`(`id`)
)engine=innodb default charset=utf8;

insert 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.导入lombok

2.新建实体类，Teacher，Student

3.建立Mapper接口

4.建立Mapper.xml文件

5.在核心配置文件中，绑定注册我们的Mapper接口或者文件【方式很多，随心选】

6.测试查询是否能够成功

按照查询嵌套处理

<!--
    思路：
        1.查询所有的学生信息
        2.根据查询出来的学生的tid，寻找对应的老师 子查询
    -->
<select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂的属性，我们需要单独处理，对象：association 集合：collection-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

按照结果嵌套处理

<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

回顾mysql多对一查询方式：

子查询
联表查询

代码show

代码结构图：

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vl30zlpO-1637458211148)(C:\Users\Administrator\Desktop\学习笔记\mybatis\MyBatis学习.assets\image-20211121092657211.png)]$

pojo

package com.gongyi.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联一个老师
    private Teacher teacher;
}

package com.gongyi.pojo;

import lombok.Data;

@Data
public class Teacher {
    private int id;
    private String name;
}

dao

package com.gongyi.dao;


import com.gongyi.pojo.Student;

import java.util.List;

public interface StudentMapper {
    //查询所有的学生信息，以及对应的老师的信息
    List<Student> getStudent();
    List<Student> getStudent2();
}

package com.gongyi.dao;


import com.gongyi.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

utils

package com.gongyi.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;


//sqlSessionFactory -->sqlSession
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用mybatis第一步：获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = null;
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession(true);
    }
}

resources

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8
username=root
password=123456

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核心配置文件-->
<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties"/>

    <settings>
        <!--标准的日志工厂实现-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>

    </settings>
    <!--可以给实体类起别名-->
    <typeAliases>
        <package name="com.gongyi.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <!--绑定接口-->
    <mappers>
        <mapper class="com.gongyi.dao.TeacherMapper"/>
        <mapper class="com.gongyi.dao.StudentMapper"/>
    </mappers>

</configuration>

实体类对应的mapper.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gongyi.dao.StudentMapper">

    <!--按照结果嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname
        from student s,teacher t
        where s.tid = t.id;
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <!--
    思路：
        1.查询所有的学生信息
        2.根据查询出来的学生的tid，寻找对应的老师 子查询
    -->
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理，对象：association 集合：collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <!-- 里面的#{id}可以是任意变量，会智能匹配，不过为了增加可读性，一般写id-->
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{tid}
    </select>

</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gongyi.dao.TeacherMapper">

</mapper>

测试类：

import com.gongyi.dao.StudentMapper;
import com.gongyi.dao.TeacherMapper;
import com.gongyi.pojo.Student;
import com.gongyi.pojo.Teacher;
import com.gongyi.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {
    public static void main(String[] args) {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

    @Test
    public void testStudent() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

    @Test
    public void testStudent2() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

彩蛋

1.测试功能方法

1）main方法

2）单元测试类

2.在sqlyog中查看架构设计器

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0hxRN7DA-1637457695781)(C:\Users\Administrator\Desktop\学习笔记\mybatis\MyBatis学习.assets\image-20211120221051356.png)]$

把表拖拽到这个区域：【sqlyog企业版才可以使用这个功能】

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pnAdaITt-1637457695783)(C:\Users\Administrator\Desktop\学习笔记\mybatis\MyBatis学习.assets\image-20211120221559425.png)]$

3.mybatis实体类对应的mapper的mapper配置文件从哪里来？

1）复制mybatis核心配置文件，重命名

2）删除configuration标签内的内容

3）把docType那一行的的configuration变为mapper，config也变为mapper

4）把configuration标签改为mapper标签

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y1Snrz8b-1637457695785)(C:\Users\Administrator\Desktop\学习笔记\mybatis\MyBatis学习.assets\image-20211120222629979.png)]$