mybatis单框架实现多对多连接

mybatis单框架实现多对多连接

1 建立stu3(学生表)、class3(班级表)、temp01(维护表)

1.1 stu3对应的sql语句

DROP TABLE IF EXISTS `stu3`;
CREATE TABLE `stu3`  (
  `sid` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `sname` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of stu3
-- ----------------------------
INSERT INTO `stu3` VALUES ('s001', '张三');
INSERT INTO `stu3` VALUES ('s002', '李四');
1
2
3
4
5
6
7
8
9
10
11
12

1.2 class3对应的sql语句

DROP TABLE IF EXISTS `class3`;
CREATE TABLE `class3`  (
  `cid` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `cname` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class3
-- ----------------------------
INSERT INTO `class3` VALUES ('c001', 'java课程');
INSERT INTO `class3` VALUES ('c002', 'html课程');
INSERT INTO `class3` VALUES ('c003', 'mysql课程');
1
2
3
4
5
6
7
8
9
10
11
12
13

1.3 temp01对应的sql语句

DROP TABLE IF EXISTS `temp01`;
CREATE TABLE `temp01`  (
  `tid` int NOT NULL AUTO_INCREMENT,
  `sid` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `cid` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`tid`) USING BTREE,
  INDEX `sid`(`sid`) USING BTREE,
  INDEX `cid`(`cid`) USING BTREE,
  CONSTRAINT `temp01_ibfk_1` FOREIGN KEY (`sid`) REFERENCES `stu3` (`sid`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `temp01_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `class3` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of temp01
-- ----------------------------
INSERT INTO `temp01` VALUES (1, 's001', 'c001');
INSERT INTO `temp01` VALUES (2, 's001', 'c002');
INSERT INTO `temp01` VALUES (3, 's002', 'c001');
INSERT INTO `temp01` VALUES (4, 's002', 'c003');
INSERT INTO `temp01` VALUES (5, 's001', 'c003');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

2 创建实体类

2.1 创建stu3类

package entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Stu3 {
    private String sid;
    private String sname;

    public Stu3(String sid, String sname) {
        this.sid = sid;
        this.sname = sname;
    }

    private List<Class3> class3s;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.2 创建class3类

package entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data
public class Class3 {
    private String cid;
    private String cname;
    private List<Stu3> stu3s;

    public Class3(String cid, String cname) {
        this.cid = cid;
        this.cname = cname;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3 findById方法及其xml中对应的标签内容

3.1 findByld(方法) 根据学生id找到他所对应的所有课程信息

 Stu3 findById(String id);
1

3.2 xml中对应的标签内容

<select id="findById" resultMap="student_Class">
     select * from stu3 WHERE sid=#{id}
select>
<resultMap id="student_Class" type="stu3">
    <id property="sid" column="sid">id>
    <collection property="class3s" column="sid" select="getClass">

    collection>
resultMap>

<select id="getClass" resultType="Class3">
   SELECT * FROM (select cid FROM temp01 WHERE sid=#{sid}) a
    INNER JOIN class3  c3 ON c3.cid=a.cid
select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4 findByCid方法及其xml中对应的标签内容

4.1 findByCid(方法) 根据学生id找到他所对应的所有课程信息

 Class3 findByCid(String id);
1

4.2 xml中对应的标签内容

    <select id="findByCid" resultMap="class_stu">
        select * from class3 WHERE cid=#{id}
    select>
    <resultMap id="class_stu" type="Class3">
        <id property="cid" column="cid">id>
        <collection property="stu3s" column="cid" select="getStu">collection>
    resultMap>
    <select id="getStu" resultType="Stu3">
        SELECT * FROM (SELECT sid FROM temp01 WHERE cid=#{cid}) t INNER JOIN
         stu3 s ON t.sid=s.sid
    select>
1
2
3
4
5
6
7
8
9
10
11
12

5 完整接口和xml文件版本

5.1 StudentToClass接口

package mapper;

import entity.Class3;
import entity.Stu3;

public interface StudentToClass {
    Stu3 findById(String id);
    Class3 findByCid(String id);
}
1
2
3
4
5
6
7
8
9

5.2 StudentToClass.xml文件

DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.StudentToClass">
    <select id="findById" resultMap="student_Class">
         select * from stu3 WHERE sid=#{id}
    select>
    <resultMap id="student_Class" type="stu3">
        <id property="sid" column="sid">id>
        <collection property="class3s" column="sid" select="getClass">

        collection>
    resultMap>
    
    <select id="getClass" resultType="Class3">
       SELECT * FROM (select cid FROM temp01 WHERE sid=#{sid}) a
        INNER JOIN class3  c3 ON c3.cid=a.cid
    select>
    
    <select id="findByCid" resultMap="class_stu">
        select * from class3 WHERE cid=#{id}
    select>
    <resultMap id="class_stu" type="Class3">
        <id property="cid" column="cid">id>
        <collection property="stu3s" column="cid" select="getStu">collection>
    resultMap>
    <select id="getStu" resultType="Stu3">
        SELECT * FROM (SELECT sid FROM temp01 WHERE cid=#{cid}) t INNER JOIN
         stu3 s ON t.sid=s.sid
    select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

6 测试及运行截图

6.1 findById(一个学生选了多少节课)

6.1.1 测试代码

@Test
public void t3(){
    SqlSessionFactory sf = SqlSessionUtil.getSF();
    SqlSession sqlSession = sf.openSession();
    StudentToClass mapper = sqlSession.getMapper(StudentToClass.class);
    Stu3 byId = mapper.findById("s002");
    System.out.println(byId);
    sqlSession.commit();
}
1
2
3
4
5
6
7
8
9

6.1.2 测试代码运行截图

6.2 findByCid(一个课程下面有多少名学生)

6.2.1 测试代码

@Test
public void t4(){
    SqlSessionFactory sf = SqlSessionUtil.getSF();
    SqlSession sqlSession = sf.openSession();
    StudentToClass mapper = sqlSession.getMapper(StudentToClass.class);
    Class3 c001 = mapper.findByCid("c001");
    System.out.println(c001);
    sqlSession.commit();
}
1
2
3
4
5
6
7
8
9

6.2.2 测试代码运行截图

7 总结

7.1 对于用到了维护表的多表联查,在设计实体类时,需要什么就写什么,需要一个对象就写对象,需要集合就写集合,如在本题中的学生表,需要课程表的信息,就可以在学生表里面加上课程表集合这个字段了
7.2 若多表联查返回的数据是某一表中的数据,那resultType的属性就为该该表的实体类名
7.3 resultMap中的type类型是有关联的select子句中查询到的字段所在表的实体类名
1
2
3