MyBatis(三)

最新推荐文章于 2022-11-03 21:42:45 发布

澎湖Java架构师

最新推荐文章于 2022-11-03 21:42:45 发布

阅读量850

点赞数

分类专栏：面试学习路线阿里巴巴文章标签： java java进阶后端

本文链接：https://blog.csdn.net/m0_54850604/article/details/124776761

版权

本文详细介绍了MyBatis中的复杂查询处理，包括多对一和一对多的映射配置，并提供了环境搭建及示例代码。此外，还深入讲解了动态SQL的使用，如if、choose、trim和foreach等标签的应用。最后，探讨了MyBatis的缓存机制，包括一级缓存和二级缓存的工作原理、配置以及可能遇到的问题和解决方案。

摘要由CSDN通过智能技术生成

MyBatis

文章目录

MyBatis

十.复杂查询环境搭建

1.多对一处理

例如:多个学生对应一个老师
对于学生这边而言:关联多个学生关联一个老师 (多对一)
对于老师这边而言:集合,一个老师有很多学时(一对多)

在这里插入图片描述
创建数据库:

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

测试环境搭建:
结构:
在这里插入图片描述

导入lombok
org.projectlombok lombok 1.18.24 provided
新建实体类teacher,student

import lombok.Data;

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

import lombok.Data;

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

以下为:按照查询嵌套处理

建立mapper接口

import com.mnm.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);
}

import com.mnm.pojo.Student;
import java.util.List;

public interface StudentMapper {
//查询所有学生的信息,以及对应的老师的信息
List getStudent();
}
建立mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?> <?xml version="1.0" encoding="UTF-8" ?> select * from student; select * from teacher where id = #{id};
在核心配置文件中绑定注册我们的mapper接口
<?xml version="1.0" encoding="UTF-8" ?>
测试

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

import java.util.List;

public class MyTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher();
System.out.println(teacher);
sqlSession.close();
}
```
@Test
public void getStudent(){
    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();
}
```
}

以下为按照结果嵌套处理:

<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,
           s.name sname,
           t.id tid,
           t.name tname
    from student s left join teacher as t on 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="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>

回顾Mysql多对一查询方式:

子查询
联表查询

2.一对多处理

比如:一个老师拥有多个学生
对于老师而言,就是一对多的关系

修改实体类:

import lombok.Data;

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


import lombok.Data;
import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    /*一个老师拥有多个学生*/
    private List<Student> students;
}

按照结果嵌套处理
按照查询嵌套处理

建立mapper接口

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

import java.util.List;

public interface TeacherMapper {

    /*获取所有老师*/
    List<Teacher> getTeacher();

    /*获取指定老师及其所有学生,按照结果嵌套处理*/
    Teacher getTeacherStudent(@Param("tid") int id);

    /*按照查询嵌套处理*/
    Teacher getTeacherStudent2(@Param("tid") int id);
}

创建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">
<!--core configuration file-->

<mapper namespace="com.mnm.dao.TeacherMapper">
    <select id="getTeacher" resultType="Teacher">
        select * from teacher;
    </select>

    <!--按结果嵌套查询-->
    <select