Mybatis快速上手3 关联表查询一：一对一表关联查询

其他章节及开发包：https://blog.csdn.net/XIMAX/article/details/106255196

环境 ：Eclipse+Tomcat9+JDK10+Mybatis-3.1.1、Navicat

test1~8分别对应八章，本章使用包test3、tool，文件config.xml、db.properties、log4j.properties

需求

1.一个教师只带一个班
2.一个班只有一个老师
3.根据班级id查询班级信息(带老师的信息)

代码

建库，建表

CREATE TABLE teacher(
 t_id INT PRIMARY KEY AUTO_INCREMENT, 
 t_name VARCHAR(20)
);
CREATE TABLE class(
 c_id INT PRIMARY KEY AUTO_INCREMENT, 
 c_name VARCHAR(20), 
 teacher_id INT
);

ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id); 
 
INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');
 
INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

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>
  <properties resource="db.properties"></properties>
  <typeAliases>
    <package name="com.rjxy.test3"/>
 <typeAliases>
 
 <typeAliases>
   <package name="com.rjxy.test2"/>
 <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 resource="com/rjxy/test3/userMapper.xml"/>
 <mappers>
</configuration>

userMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 
<mapper namespace="com.rjxy.test3.userMapper">
<!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集封装联表查询的数据(去除重复的数据) -->
<select id="getClass" parameterType="int" resultMap="ClassResultMap">
  select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
 </select>
 <resultMap type="Classes" id="ClassResultMap">
  <id property="id" column="c_id"/>
  <result property="name" column="c_name"/>
  <association property="teacher" javaType="Teacher">
   <id property="id" column="t_id"/>
   <result property="name" column="t_name"/>
  </association>
 </resultMap>
 
<!-- 方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型-->
<select id="getClass2" parameterType="int" resultMap="getClass2Map">
  select * from class where c_id=#{id}
 </select>
 <select id="getTeacher" parameterType="int" resultType="Teacher">
  select t_id id,t_name name from teacher where t_id=#{id}
 </select>
 <resultMap type="Classes" id="getClass2Map">
  <id property="id" column="c_id"/>
  <result property="name" column="c_name"/>
  <association property="teacher" column="teacher_id" select="getTeacher">
  </association>
 </resultMap> 
</mapper>

Classes.java

package com.rjxy.test3;
public class Classes {
 private int id;
 private String name;
 private Teacher teacher;
 //省略 get、set 及 toString 方法
 }

Teacher.java

package com.rjxy.test3;
public class Teacher {
 private int id;
 private String name;
 
 public Teacher() {
  super();
 }
 public Teacher(int id, String name) {
  super();
  this.id = id;
  this.name = name;
 }
 //省略 get、set 及 toString 方法
}

JTest3.java

package com.rjxy.test3;
import java.io.IOException;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import com.rjxy.tool.MybatisUtils;
public class JTest3 {
 public static void main(String[] args) throws IOException {
  SqlSessionFactory factory = MybatisUtils.getFactory();
  SqlSession session = factory.openSession();

  String statement="com.rjxy.test3.userMapper.getClass";

  Classes c = session.selectOne(statement,2);
  System.out.println(c);
  System.out.println("------------------");
 
  String statement2 = "com.rjxy.test3.userMapper.getClass2";
  Classes s = session.selectOne(statement2,2);
  System.out.println(s);
  session.close();
 }
}

其它

其它文件与该系列第一篇相同 链接： https://blog.csdn.net/XIMAX/article/details/106244701

结果

2020-05-20 23:31:45,094 [main] DEBUG [com.rjxy.test3.userMapper.getClass] - ooo Using Connection [com.mysql.cj.jdbc.ConnectionImpl@58ce9668]
2020-05-20 23:31:45,094 [main] DEBUG [com.rjxy.test3.userMapper.getClass] - ==>  Preparing: select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=? 
2020-05-20 23:31:45,195 [main] DEBUG [com.rjxy.test3.userMapper.getClass] - ==> Parameters: 2(Integer)
Classes [id=2, name=bj_b, teacher=Teacher [id=2, name=LS2]]
------------------
2020-05-20 23:31:45,488 [main] DEBUG [com.rjxy.test3.userMapper.getClass2] - ooo Using Connection [com.mysql.cj.jdbc.ConnectionImpl@58ce9668]
2020-05-20 23:31:45,488 [main] DEBUG [com.rjxy.test3.userMapper.getClass2] - ==>  Preparing: select * from class where c_id=? 
2020-05-20 23:31:45,488 [main] DEBUG [com.rjxy.test3.userMapper.getClass2] - ==> Parameters: 2(Integer)
2020-05-20 23:31:45,488 [main] DEBUG [com.rjxy.test3.userMapper.getTeacher] - ooo Using Connection [com.mysql.cj.jdbc.ConnectionImpl@58ce9668]
2020-05-20 23:31:45,488 [main] DEBUG [com.rjxy.test3.userMapper.getTeacher] - ==>  Preparing: select t_id id,t_name name from teacher where t_id=? 
2020-05-20 23:31:45,488 [main] DEBUG [com.rjxy.test3.userMapper.getTeacher] - ==> Parameters: 2(Integer)
Classes [id=2, name=bj_b, teacher=Teacher [id=2, name=LS2]]