mybatis 第七天关联查询一对多一对一多对多多对一懒加载

Java 码农

已于 2024-01-26 15:59:02 修改

阅读量349

点赞数 6

文章标签： mybatis 数据库 oracle

于 2024-01-26 15:25:04 首次发布

本文链接：https://blog.csdn.net/weixin_58026642/article/details/135867346

版权

1: 关联查询

数据库表准备
CREATE TABLE `t_class` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `stu_address` varchar(100) DEFAULT NULL,
  `courseId` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `t_student_course_mapping` (
  `sid` bigint(20) NOT NULL,
  `cid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 这个暂时用不着
CREATE TABLE `t_user` (
  `id` varchar(100) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `user_address` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.1 一对一

使用经典的学生选课 假定需求为一个学生只能选择一门课
public class Student implements Serializable {
    private Long id;
    private String name;
    private String stuAddress;
    private Integer age;
    private Class aClass;
}
  
public class Class implements Serializable {
    private Integer id;
    private String name;
}  

直接嵌套查询
    <resultMap id="oneToOneMap" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="stuAddress" column="stu_address"/>
        <association property="aClass" javaType="org.wl.pojo.Class">
            <id property="id" column="cid"/>
            <result property="name" column="cName"/>
        </association>
    </resultMap>

    <select id="queryStudentCourseOneToOne" resultType="student" resultMap="oneToOneMap">
        select stu.id,stu.name,age,stu_address,classId,cl.id as cid, cl.name as cName
            from t_student stu , t_class cl
        where stu.classId = cl.id and  stu.id = #{stuId}
    </select>

子查询的方式: student 的calss 对象属性的id 对应studnet表的classId  将这个classId 列作为只查询的列参数        
    <resultMap id="oneToOneMapSubQuery" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="stuAddress" column="stu_address"/>
        <result property="aClass.id" column="classId"/>
        <association property="aClass" javaType="class" column="classId" select="queryClassById"/>
    </resultMap>

    <select id="queryStudentCourseOneToOne2" resultMap="oneToOneMapSubQuery">
        select * from t_student where id = #{stuId}
    </select>
    <select id="queryClassById" resultType="class">
      select * from t_class where id=#{classId}
    </select>
查看日志：
2024:01:26 14:24:08.253 [main] DEBUG o.w.m.S.queryStudentCourseOneToOne2 -- ==>  Preparing: select * from t_student where id = ?
2024:01:26 14:24:08.279 [main] DEBUG o.w.m.S.queryStudentCourseOneToOne2 -- ==> Parameters: 6(Integer)
2024:01:26 14:24:08.628 [main] DEBUG o.w.m.StudentMapper.queryClassById -- ====>  Preparing: select * from t_class where id=?
2024:01:26 14:24:08.629 [main] DEBUG o.w.m.StudentMapper.queryClassById -- ====> Parameters: 1(Integer)
2024:01:26 14:24:08.683 [main] DEBUG o.w.m.StudentMapper.queryClassById -- <====      Total: 1
2024:01:26 14:24:08.684 [main] DEBUG o.w.m.S.queryStudentCourseOneToOne2 -- <==      Total: 1
[Student{id=6, name='刘亦菲', stuAddress='gz', age=20, aClass=Class{id=1, name='音乐'}}]
2024:01:26 14:24:08.684 [main] DEBUG o.a.i.t.jdbc.JdbcTransaction -- Opening JDBC Connection
2024:01:26 14:24:10.088 [main] DEBUG o.a.i.d.pooled.PooledDataSource -- Created connection 23133760.
2024:01:26 14:24:10.089 [main] DEBUG o.a.i.t.jdbc.JdbcTransaction -- Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@160fe40]
2024:01:26 14:24:10.134 [main] DEBUG o.w.m.S.queryStudentCourseOneToOne2 -- ==>  Preparing: select * from t_student where id = ?
2024:01:26 14:24:10.135 [main] DEBUG o.w.m.S.queryStudentCourseOneToOne2 -- ==> Parameters: 2(Integer)
2024:01:26 14:24:10.186 [main] DEBUG o.w.m.StudentMapper.queryClassById -- ====>  Preparing: select * from t_class where id=?
2024:01:26 14:24:10.186 [main] DEBUG o.w.m.StudentMapper.queryClassById -- ====> Parameters: 2(Integer)
2024:01:26 14:24:10.231 [main] DEBUG o.w.m.StudentMapper.queryClassById -- <====      Total: 1
2024:01:26 14:24:10.232 [main] DEBUG o.w.m.S.queryStudentCourseOneToOne2 -- <==      Total: 1
[Student{id=2, name='lining', stuAddress='gz', age=20, aClass=Class{id=2, name='形体'}}]

1.2 一对多查询

修改Student 对象
public class Student implements Serializable {
    private Long id;
    private String name;
    private String stuAddress;
    private Integer age;
    private List<Class> classList;  //单个对象属性改为List
 ｝  
 
 修改表结构 : ALTER TABLE priceindex.t_class ADD stuId INT NULL;
 使用直接嵌套的方式查询 一对多
     <resultMap id="oneToMoreMap" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="stuAddress" column="stu_address"/>
        <collection property="classList" ofType="class" javaType="java.util.List">
            <id property="id" column="cid"/>
            <result property="name" column="cName"/>
            <result property="stuId" column="stuId"/>
        </collection>
    </resultMap>

    <select id="queryStudentCourseOneToMore" resultMap="oneToMoreMap">
        select stu.id,stu.name,age,stu_address,cl.id as cid, cl.name as cName,stuId
        from t_student stu , t_class cl
        where stu.id = cl.stuId and stu.id = #{stuId}
    </select>
 
  使用子查询的方式查询 一对多
      <resultMap id="oneToMoreMapSubQuery" type="student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="stuAddress" column="stu_address"/>
        <collection property="classList" ofType="class" javaType="java.util.List" column="id" select="queryClassByIdOneToMore"/>
    </resultMap>
    <select id="queryStudentCourseOneToMore2" resultMap="oneToMoreMapSubQuery">
        select * from t_student where id = #{stuId}
    </select>

    <select id="queryClassByIdOneToMore" resultType="class">
        select * from t_class where stuId=#{id}
    </select>
    
同理也可以去实验多对多和多对一的情况，只是代码和数据库的设计稍微有所不同
其中多对多的时候 需要创建一个中间表来保存 关联表之间的对应关系
    如：
 CREATE TABLE `t_student_course_mapping` (
  `sid` bigint(20) NOT NULL,
  `cid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
用这个表来关联起多对多的关系的表,这里不做更多介绍了

## 1.3 开启懒加载

lazyLoadingEnabled 在settings 中添加这个属性值为true

resultMap 中的关联集合的属性fetchType=“lazy”

这样设置之后查看测试结果
@Test
public void testQuery04(){
StudentService studentService = new StudentServiceImpl();
List studentList = studentService.queryStudentCourseOneToMore2(6);
for (int i = 0; i < studentList.size(); i++) {
Student student = studentList.get(i);
System.out.println(student.getName());
//关联的集合要在具体使用的时候才加载
// List classList = student.getClassList();
// for (int j = 0; j < classList.size(); j++) {
// System.out.println(classList.get(j));
// }
}
}
}
此时子查询并没有执行
2024:01:26 15:55:40.874 [main] DEBUG o.a.i.t.jdbc.JdbcTransaction – Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@422e18]
2024:01:26 15:55:40.920 [main] DEBUG o.w.m.S.queryStudentCourseOneToMore2 – ==> Preparing: select * from t_student where id = ?
2024:01:26 15:55:40.940 [main] DEBUG o.w.m.S.queryStudentCourseOneToMore2 – > Parameters: 6(Integer)
2024:01:26 15:55:41.052 [main] DEBUG o.w.m.S.queryStudentCourseOneToMore2 – < Total: 1

打开注释掉的代码可以看到在class集合循环使用的时候这个懒加载的子查询才被执行了一次
2024:01:26 15:56:48.912 [main] DEBUG o.w.m.S.queryStudentCourseOneToMore2 – ==> Preparing: select * from t_student where id = ?
2024:01:26 15:56:48.932 [main] DEBUG o.w.m.S.queryStudentCourseOneToMore2 – > Parameters: 6(Integer)
2024:01:26 15:56:49.036 [main] DEBUG o.w.m.S.queryStudentCourseOneToMore2 – < Total: 1
刘亦菲
2024:01:26 15:56:49.036 [main] DEBUG o.w.m.S.queryClassByIdOneToMore – ==> Preparing: select * from t_class where stuId=?
2024:01:26 15:56:49.037 [main] DEBUG o.w.m.S.queryClassByIdOneToMore – > Parameters: 6(Long)
2024:01:26 15:56:49.084 [main] DEBUG o.w.m.S.queryClassByIdOneToMore – < Total: 3
Class{id=1, name=‘音乐’, stuId=6}
Class{id=2, name=‘形体’, stuId=6}
Class{id=3, name=‘美术’, stuId=6}