mybatis各种小知识点二：嵌套查询之一对一，多对多

CREATE TABLE t_passengers (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
sex varchar(1) DEFAULT NULL,
birthday date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
CREATE TABLE t_passports (
id int NOT NULL AUTO_INCREMENT,
nationality varchar(50) DEFAULT NULL,
expire date DEFAULT NULL,
passenger_id int DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY passenger_id (passenger_id),
CONSTRAINT t_passports_ibfk_1 FOREIGN KEY (passenger_id) REFERENCES t_passengers (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

定义接口

//通过旅客的id，查询旅客的信息及护照信息，关联查询 级联查询
Passenger queryPassengerById(Integer id);

定义sql语句

<resultMap id="passenger_passport" type="Passenger">
    <id column="id" property="id"></id>
    <result column="name" property="name"></result>
    <result column="sex" property="sex"></result>
    <result column="birthday" property="birthday"></result>
    <!--描述passid nationnality expire passenger_id 和 Passport映射-->
    <association property="passport" javaType="Passport">
        <id column="id" property="id"></id>
        <result column="nationality" property="nationality"></result>
        <result column="expire" property="expire"></result>
    </association>
</resultMap>
<select id="queryPassengerById" resultMap="passenger_passport">
    select t_passengers.id,t_passengers.name,t_passengers.sex,t_passengers.birthday,
           t_passports.id,t_passports.nationality,t_passports.expire,t_passports.passenger_id
    from t_passengers join t_passports on t_passengers.id = t_passports.passenger_id
    where passenger_id=#{arg0}
</select>

测试

/*一对一映射  passenger_passport*/
System.out.println("一对一映射 passenger_passport----------------------------");
PassengerDao mapper = sqlSession.getMapper(PassengerDao.class);
Passenger passenger = mapper.queryPassengerById(1);
System.out.println(passenger);
System.out.println(passenger.getPassport());
System.out.println("一对一映射 passport_passenger----------------------------");
PassportDao mapper1 = sqlSession.getMapper(PassportDao.class);
Passport passport = mapper1.queryPassportById(1);
System.out.println(passport);
System.out.println(passport.getPassenger());
sqlSession.close();

结果

DEBUG 2021-09-21 16:00:00,752 com.qf.dao.PassportDao.queryPassportById: ==>  Preparing: select t_passports.id,t_passports.nationality,t_passports.expire, t_passengers.id,t_passengers.name,t_passengers.sex,t_passengers.birthday from t_passports join t_passengers on t_passports.passenger_id = t_passengers.id where t_passports.id = ? 
DEBUG 2021-09-21 16:00:00,753 com.qf.dao.PassportDao.queryPassportById: ==> Parameters: 1(Integer)
DEBUG 2021-09-21 16:00:00,755 com.qf.dao.PassportDao.queryPassportById: <==      Total: 1
Passport{id=1, nationality='China', expire=Mon Aug 12 00:00:00 CST 2013, passenger=Passenger{id=1, name='shine_01', sex=true, birthday=Tue Jun 05 00:00:00 CST 2012, passport=null}}
Passenger{id=1, name='shine_01', sex=true, birthday=Tue Jun 05 00:00:00 CST 2012, passport=null}

多对多

CREATE TABLE t_stu_sub (
student_id int NOT NULL,
subject_id int NOT NULL,
PRIMARY KEY (student_id,subject_id),
KEY subject_id (subject_id),
CONSTRAINT t_stu_sub_ibfk_1 FOREIGN KEY (student_id) REFERENCES t_students (id),
CONSTRAINT t_stu_sub_ibfk_2 FOREIGN KEY (subject_id) REFERENCES t_subjects (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE t_students (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
sex varchar(1) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE t_subjects (
id int NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
grade int DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8;

定义接口

Subjects querySubjectById(Integer integer);

定义sql语句

<resultMap id="Subjects_students2" type="Subjects">
   <id column="tSubjectsId" property="id"></id>
   <result column="name" property="name"></result>
   <result column="grade" property="grade"></result>
   <collection property="students2" ofType="Students2">
       <id column="id" property="id"></id>
       <result column="name" property="name"></result>
       <result column="sex" property="sex"></result>
   </collection>
</resultMap>
<select id="querySubjectById" resultMap="Subjects_students2">
   select t_subjects.id as tSubjectsId,t_subjects.name,t_subjects.grade,
          t_students.id,t_students.name,t_students.sex
   from t_subjects join t_stu_sub on t_subjects.id = t_stu_sub.subject_id
                   join t_students on t_students.id = t_stu_sub.student_id
   where t_subjects.id = #{param1};
</select>

测试

SubjectsDao subjectsDao = sqlSession.getMapper(SubjectsDao.class);
Subjects subjects = subjectsDao.querySubjectById(1002);
System.out.println(subjects);
List<Students2> students2 = subjects.getStudents2();
System.out.println(students2);
for(Students2 students21:students2){
    System.out.println(students21);
}
sqlSession.close();

结果

DEBUG 2021-09-21 16:07:42,263 com.qf.dao.SubjectsDao.querySubjectById: ==>  Preparing: select t_subjects.id as tSubjectsId,t_subjects.name,t_subjects.grade, t_students.id,t_students.name,t_students.sex from t_subjects join t_stu_sub on t_subjects.id = t_stu_sub.subject_id join t_students on t_students.id = t_stu_sub.student_id where t_subjects.id = ?; 
DEBUG 2021-09-21 16:07:42,292 com.qf.dao.SubjectsDao.querySubjectById: ==> Parameters: 1002(Integer)
DEBUG 2021-09-21 16:07:42,317 com.qf.dao.SubjectsDao.querySubjectById: <==      Total: 2
Sbujects{id=1002, name='javaScript', grade='2', students2=[Students2{id=1, name='javaScript', sex='m', subjects=null}, Students2{id=2, name='javaScript', sex='f', subjects=null}]}
[Students2{id=1, name='javaScript', sex='m', subjects=null}, Students2{id=2, name='javaScript', sex='f', subjects=null}]
Students2{id=1, name='javaScript', sex='m', subjects=null}
Students2{id=2, name='javaScript', sex='f', subjects=null}