Mybatis学习——一对一关联表查询

1.SQL语句建表

 1 CREATE TABLE teacher(
 2 t_id INT PRIMARY KEY AUTO_INCREMENT, t_name VARCHAR(20)
 3 );
 4 CREATE TABLE class(
 5 c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(20),
 6 teacher_id INT
 7 );
 8 ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);
 9 INSERT INTO teacher(t_name) VALUES('LS1'); INSERT INTO teacher(t_name) VALUES('LS2');
10 INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1); INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

sql

2.实体类

 1 package com.zhengbin.entity;
 2 
 3 public class Teacher {
 4     private int id;
 5     private String name;
 6     public int getId() {
 7         return id;
 8     }
 9     public void setId(int id) {
10         this.id = id;
11     }
12     public String getName() {
13         return name;
14     }
15     public void setName(String name) {
16         this.name = name;
17     }
18     @Override
19     public String toString() {
20         return "Teacher [id=" + id + ", name=" + name + "]";
21     }
22     public Teacher(int id, String name) {
23         super();
24         this.id = id;
25         this.name = name;
26     }
27     public Teacher() {
28         super();
29         // TODO Auto-generated constructor stub
30     }
31 }

Teacher.java

 1 package com.zhengbin.entity;
 2 
 3 public class Classes {
 4     private int id;
 5     private String name;
 6     private Teacher teacher;
 7     @Override
 8     public String toString() {
 9         return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher + "]";
10     }
11     public Classes() {
12         super();
13     }
14     public int getId() {
15         return id;
16     }
17     public void setId(int id) {
18         this.id = id;
19     }
20     public String getName() {
21         return name;
22     }
23     public void setName(String name) {
24         this.name = name;
25     }
26     public Teacher getTeacher() {
27         return teacher;
28     }
29     public void setTeacher(Teacher teacher) {
30         this.teacher = teacher;
31     }
32 }

Classes.java

3.实体类映射文件

　　两种方式：

　　　　1. 联表查询

　　　　　　关键是返回值的设置

　　　　2. 执行两次查询

　　　　　　关键是返回值的设置，和第二次查询所需参数的传递

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <!-- 每个映射文件的namespace应该是唯一的 -->
 4 <mapper namespace="com.zhengbin.entity.classMapper">
 5     <!-- parameterType 参数表示需要参数的类型 -->
 6     <!-- resultType 参数表示返回结果的类型，该可以写为实体包的全路径，或者在conf.xml配置文件中，声明实体的别名 -->
 7     
 8     <!-- 
 9         方式一：
10             嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据)
11      -->
12     <select id="getClass" parameterType="int" resultMap="getClassMap">
13         select * from class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id};
14     </select>
15     <resultMap type="Classes" id="getClassMap">
16         <id property="id" column="c_id"/>
17         <result property="name" column="c_name"/>
18         <association property="teacher" javaType="Teacher">
19             <id property="id" column="t_id"/>
20             <result property="name" column="t_name"/>
21         </association>
22     </resultMap>
23     <!-- 
24         方式二：
25             嵌套查询：通过执行另外一个 SQL 映射语句来返回预期的复杂类型 
26                     SELECT * FROM class WHERE c_id=1;
27                     SELECT * FROM teacher WHERE t_id=1 //1 是上一个查询得到的 teacher_id 的值
28      -->
29      <select id="getClass2" parameterType="int" resultMap="getClass2Map">
30          select * from class where c_id=#{id}
31      </select>
32      <select id="getTeacher" parameterType="int" resultType="Teacher">
33          select t_id id,t_name name from teacher where t_id=#{id}
34      </select>
35      <resultMap type="Classes" id="getClass2Map">
36          <id property="id" column="c_id"/>
37          <result property="name" column="c_name"/>
38          <association property="teacher" select="getTeacher" column="teacher_id"/>
39      </resultMap>
40 </mapper>

classMapper.xml

4.测试类

 1 package com.zhengbin.test;
 2 
 3 import org.apache.ibatis.session.SqlSession;
 4 import org.apache.ibatis.session.SqlSessionFactory;
 5 
 6 import com.zhengbin.entity.Classes;
 7 import com.zhengbin.util.MyBatisUtils;
 8 
 9 public class Test3 {
10     @org.junit.Test
11     public void getTeacher(){
12         SqlSessionFactory sessionFactory = MyBatisUtils.getFactory();
13         // 参数为TRUE，相当于session.commit();
14         SqlSession session = sessionFactory.openSession(true);
15         // 读取映射文件
16         String statement = "com.zhengbin.entity.classMapper" + ".getClass";
17 //        String statement = "com.zhengbin.entity.classMapper" + ".getClass2";
18         Classes c = session.selectOne(statement,1);
19         System.out.println(c);
20         session.close();
21     }
22 }

Test3.java

5.几个关键的属性

association : 用于一对一的关联查询
property : 对象属性的名称
javaType : 对象属性的类型
column : 所对应的外键字段名称
select : 使用另一个查询封装的结果