1.1. Requirements
Query class information based on class id (with teacher's information)
1.2. Creating tables and data
Create a table of teachers and classes. Let's assume a teacher teaches only one class. The relationship between teachers and classes is one-to-one.
1.3 sql statement
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('teacher1');
INSERT INTO teacher(t_name) VALUES('teacher2');
INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);
1.4
package me.gacl.domain;
/**
* @author gacl
* Define the entity class corresponding to the class table
*/
public class Classes {
//Define the properties of the entity class, corresponding to the fields in the class table
private int id; //id===>c_id
private String name; //name===>c_name
/**
* class There is a teacher_id field in the table, so a teacher property is defined in the Classes class.
* Used to maintain a one-to-one relationship between a teacher and a class, this teacher property tells you which teacher is responsible for the class
*/
private Teacher teacher;
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;
}
@Override
public String toString() {
return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher+ "]";
}
}package me.gacl.domain;
/**
* @author gacl
* Define the entity class corresponding to the teacher table
*/
public class Teacher {
//Define the properties of the entity class, corresponding to the fields in the teacher table
private int id; //id===>t_id
private String name; //name===>t_name
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;
}
@Override
public String toString() {
return "Teacher [id=" + id + ", name=" + name + "]";
}
}1.5 xml class
<?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">
<!--Specify a unique namespace for this mapper. The namespace value is customarily set to package name + sql mapping file name, which ensures that the namespace value is unique
For example, namespace="me.gacl.mapping.classMapper" is me.gacl.mapping (package name)+classMapper(classMapper.xml file removes suffix)
-->
<mapper namespace="mapping.classMapper"> <!--Here is the first half of the location of the xml file-->
<!--
Query class information based on class id (with teacher's information)
##1. Joint 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 teacher_id obtained above
-->
<!--
Mode 1: Nested results: Use nested result mapping to handle a subset of duplicate union results
Encapsulate data for join table queries (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="ClassResultMap">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select>
<!--Map one-to-one correspondence between entity classes and fields using resu lt Map-->
<resultMap type="me.gacl.domain.Classes" id="ClassResultMap">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" javaType="me.gacl.domain.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
</association>
</resultMap>
<!--
Mode 2: Nested query: Return the expected complex type by executing another SQL mapping statement
SELECT * FROM class WHERE c_id=1;
SELECT * FROM teacher WHERE t_id=1//1 is the value of teacher_id from the previous query
-->
<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
select * from class where c_id=#{id}
</select>
<!--Map one-to-one correspondence between entity classes and fields using resu lt Map-->
<resultMap type="me.gacl.domain.Classes" id="ClassResultMap2">
<id property="id" column="c_id"/>
<result property="name" column="c_name"/>
<association property="teacher" column="teacher_id" select="getTeacher"/>
</resultMap>
<select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher">
SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
</select>
</mapper>1.6 Test
package mybatisfirst;
import me.gacl.domain.Classes;
import me.gacl.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class Test3 {
/* <mybatis Test Class for One-to-One Associated Queries */
/*
* Reference article https://www.cnblogs.com/xdp-gacl/p/4264440.html
*/
@Test
public void testGetClass(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
/**
* Map the identity string of sql,
* me.gacl.mapping.classMapper Is the value of the namespace attribute of the mapper tag in the classMapper.xml file.
* getClass Is the value of the id attribute of the select tag, through which you can find the SQL to execute
*/
String statement = "mapping.classMapper.getClass";//<Here is the first half of the xml, then the method getClass inside, not the class name)
//Perform query operation, automatically encapsulate query result as Classes object to return
Classes clazz = sqlSession.selectOne(statement,1);//Query a record with id 1 in the class table
//SqlSession needs to be closed after executing SQL using SqlSession
sqlSession.close();
System.out.println(clazz);//Print result: Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1]]
}
@Test
public void testGetClass2(){
SqlSession sqlSession = MyBatisUtil.getSqlSession();
/**
* Map the identity string of sql,
* me.gacl.mapping.classMapper Is the value of the namespace attribute of the mapper tag in the classMapper.xml file.
* getClass2 Is the value of the id attribute of the select tag, through which you can find the SQL to execute
*/
String statement = "mapping.classMapper.getClass2";//Mapping sql's identity string
//Perform query operation, automatically encapsulate query result as Classes object to return
Classes clazz = sqlSession.selectOne(statement,1);//Query a record with id 1 in the class table
//SqlSession needs to be closed after executing SQL using SqlSession
sqlSession.close();
System.out.println(clazz);//Print result: Classes [id=1, name=class_a, teacher=Teacher [id=1, name=teacher1]]
}
}Summary:
MyBatis One-to-One Association Query Summary
MyBatis uses the association tag to solve one-to-one association queries. The properties available for the association tag are as follows:
- Property:Name of object property
- javaType: Type of object property
- column:The corresponding foreign key field name
- select:Use another query to encapsulate the results