尚矽谷-mybatis-(一對一關聯)，矽谷-mybatis-

尚矽谷-mybatis-(一對一關聯)，矽谷-mybatis-

提出需求：

根據班級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);

Teacher實體類：

package com.atguigu.mybatis.bean;public class Teacher {private int id;private String name;public Teacher(int id, String name) {super();this.id = id;this.name = name;}public Teacher() {super();}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Teacher [id=" + id + ", name=" + name + "]";}}

Classes實體類：

package com.atguigu.mybatis.bean;public class Classes {private int id;private String name;private Teacher teacher;public Classes(int id, String name, Teacher teacher) {super();this.id = id;this.name = name;this.teacher = teacher;}public Classes() {super();}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}@Overridepublic String toString() {return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher+ "]";}}

classesMapper.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.atguigu.mybatis.bean.classesMapper"><!-- 根據班級id查詢班級資訊(帶老師的資訊)##1. 聯表查詢SELECT * FROM class c,teacher t WHERE c.teacher_id=t.t_id AND c.c_id=1;##2. 執行兩次查詢SELECT * FROM class WHERE c_id=1;  //teacher_id=1SELECT * FROM teacher WHERE t_id=1;//使用上面得到的teacher_id --><!-- 方式一：嵌套結果：使用嵌套結果映射來處理重複的聯合結果的子集         封裝聯表查詢的資料(去除重複的資料)select * from class c, teacher t where c.teacher_id=t.t_id and  c.c_id=1--><select id="getClass" parameterType="int" resultMap="getClassMap">SELECT * FROM class c,teacher t WHERE c.teacher_id=t.t_id AND c.c_id=#{id}</select><resultMap type="Classes" id="getClassMap"><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 * FROM class WHERE c_id=1;SELECT * FROM teacher WHERE t_id=1   //1 是上一個查詢得到的teacher_id的值--><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>

擷取SqlSessionFactory工廠的MybatisUtils代碼：

package com.atguigu.mybatis.utils;import java.io.InputStream;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;public class MybatisUtils {public static SqlSessionFactory getFactory() {String resource = "conf.xml";InputStream inputStream = MybatisUtils.class.getClassLoader().getResourceAsStream(resource);SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);return factory;}}

設定檔conf.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"/><!-- 為實體類定義別名,簡化sql映射xml檔案中的引用--><typeAliases><!-- 該包下的所有類以其簡單類名為實體類別名（如：User類的別名為User） --><package name="com.atguigu.mybatis.bean"/></typeAliases><!-- development : 開發模式work : 工作模式 --><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="${name}" /><property name="password" value="${password}" /></dataSource></environment></environments><mappers><mapper resource="com/atguigu/mybatis/bean/classesMapper.xml"/></mappers></configuration>

測試類別Test4代碼：

package com.atguigu.mybatis.test4;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import com.atguigu.mybatis.bean.Classes;import com.atguigu.mybatis.utils.MybatisUtils;/* * 測試: 一對一關聯表查詢 */public class Test4 {public static void main(String[] args) {SqlSessionFactory factory=MybatisUtils.getFactory();SqlSession session=factory.openSession();String statement="com.atguigu.mybatis.bean.classesMapper.getClass";statement="com.atguigu.mybatis.bean.classesMapper.getClass2";Classes c =session.selectOne(statement, 2);System.out.println(c);session.close();}}