Shang Silicon Valley-mybatis-(one-to-one Association), Silicon Valley-mybatis-

Shang Silicon Valley-mybatis-(one-to-one Association), Silicon Valley-mybatis-

Demand:

Query Class information by class id (with Teacher Information)

Project Structure:

Create tables and data:

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 entity class:

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 object class:

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 ing file code:

<? Xml version = "1.0" encoding = "UTF-8"?> <! DOCTYPE mapper PUBLIC "-// mybatis.org//DTD Mapper 3.0 //" http://mybatis.org/dtd/mybatis-3-mapper.dtd "> <mapper namespace =" com. atguigu. mybatis. bean. classesMapper "> <! -- Query the class information based on the class id (with the Teacher's Information) #1. join Table query SELECT * FROM class c, teacher t WHERE c. teacher_id = t. t_id AND c. c_id = 1; #2. execute two queries: SELECT * FROM class WHERE c_id = 1; // teacher_id = 1 SELECT * FROM teacher WHERE t_id = 1; // use the preceding teacher_id --> <! -- Method 1: Nested results: Use nested result ing to process the subset of duplicate Union results to encapsulate the data queried in the join table (remove duplicate data) 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"/> <associat Ion property = "teacher" javaType = "Teacher"> <id property = "id" column = "t_id"/> <result property = "name" column = "t_name"/> </association> </resultMap> <! -- Method 2: Nested query: execute another SQL ing statement to return the expected complex type SELECT * FROM class WHERE c_id = 1; SELECT * FROM teacher WHERE t_id = 1 // 1 is the value of teacher_id obtained in the previous query --> <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>

Obtain the MybatisUtils code of the SqlSessionFactory Factory:

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;}}

Configuration File conf. xml code:

<? 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"/> <! -- Define aliases for object classes to simplify SQL ing references in xml files --> <typeAliases> <! -- All classes in this package are named as entity classes (for example, the alias of the User class is User) --> <package name = "com. atguigu. mybatis. bean "/> </typeAliases> <! -- Development: development Mode work: working Mode --> <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>

Test class Test4 code:

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;/** test: one-to-one join Table query */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 ();}}