1. Association mapping (multi-table query)
There are three relationships between multiple tables in the database, as shown in the figure.
Many-to-many: Programmers<------>project user--------->Role one-to-many: class------>student School------>class handsome guy----->multiple girlfriends Local tycoon---multiple luxury cars one-on-one: student----->degree certificate people------>DNA citizen----ID card house--Ownership certificates
As can be seen from the figure, the three entity relationships designed by the system are: many-to-many, one-to-many and one-to-one relationships. Note: One-to-many relationship can be seen as two types: one-to-many and many-to-one.
Real life entities and relationships between entities: one-to-many many-to-many one-to-one
Relationships: It goes both ways! !
2. The role of association mapping
When modeling databases in real projects, we must follow the requirements of the database design paradigm, split the real business models, and encapsulate them in different data tables. There is a one-to-many or one-to-many relationship between tables. It is a many-to-many correspondence. Furthermore, the main body of our addition, deletion, modification, and query operations on the database has changed from a single table to a multi-table. So how does Mybatis implement the mapping of this multi-table relationship?
Query result set ResultMap
resultMap element is MyBatis the most important and powerful element in the it's what keeps you away from 90%need to retrieve data from the result set JDBC code, and in some cases allows you to do JDBC Unsupported things. Some friends will ask, in the previous example, we did not use the result set, and we can correctly map the data in the data table to Java in the properties of the object? Yes. that's exactly resultMap The original intention of element design is that simple statements do not require explicit result mapping, while many complex statements do need to describe their relationships.
In the resultMap element, the following direct child elements are allowed:
id - role and result The same, and it can be identified that other object instances can be distinguished by this field value. It can be understood as the primary key in the data table, which can locate the only record in the data table result - Inject fields from the datatable into Java in object properties association - Association, in simple terms, is "have a" relationship, such as "user" has an "account" has a collection - A collection, as the name suggests, is a "many" relationship, such as a "customer" with many "orders" has many
I will explain the usage and attributes of each element in combination below.
3. One-to-one association (understanding)
2.1. Requirements
Query class information based on class id (with teacher's information)
2.2. Create a data table
Create a teacher table and a class table. Here we assume that a teacher is only responsible for teaching one class, so the relationship between the teacher and the class is a one-to-one relationship.
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);
2.3. Define entity classes
1. The Teacher class, the Teacher class is the entity class corresponding to the teacher table.
public class Teacher { // Define the attributes 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 + "]"; } }
2. Classes class, Classes class is the entity class corresponding to the class table
public class Classes { // Define the attributes 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 define a teacher attribute in the Classes class, * It is used to maintain the one-to-one relationship between teacher and class. Through this teacher attribute, you can know which teacher is responsible for this 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 + "]"; } }
2.4. Define the sql mapping file classMapper.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.mybatis.mapper.ClassMapper"> <!-- select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1 --> <select id="getClass1" parameterType="int" resultMap="ClassResultMap1"> select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id} </select> <!-- use resultMap Map the one-to-one correspondence between entity classes and fields --> <resultMap type="Classes" id="ClassResultMap1"> <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> </mapper>
2.5 Writing unit test classes
@Test public void test1(){ Classes c1 = mapper.getClass1(1); System.out.println(c1); } @Test public void test2(){ Classes c1 = mapper.getClass2(1); System.out.println(c1); }
2.6 MyBatis one-to-one association query summary
The association (there is one) tag is used in MyBatis to solve one-to-one association queries. The attributes available in the association tag are as follows:
property:the name of the object property javaType:type of object property column:The corresponding foreign key field name select:Results wrapped with another query
4. One-to-many association (emphasis)
2.1. Requirements
Query corresponding class information according to classId, including students
2.2. Define the entity class
1. Student class
/** * student entity */ public class Student { private long sId; private String sName; private long sAge; private String sEmail; private long classId; //Prepare an additional class object private Classes classes; //Embody a student in a class public Classes getClasses() { return classes; } public void setClasses(Classes classes) { this.classes = classes; } public long getSId() { return sId; } public void setSId(long sId) { this.sId = sId; } public String getSName() { return sName; } public void setSName(String sName) { this.sName = sName; } public long getSAge() { return sAge; } public void setSAge(long sAge) { this.sAge = sAge; } public String getSEmail() { return sEmail; } public void setSEmail(String sEmail) { this.sEmail = sEmail; } public long getClassId() { return classId; } public void setClassId(long classId) { this.classId = classId; } @Override public String toString() { return "Student{" + "sId=" + sId + ", sName='" + sName + '\'' + ", sAge=" + sAge + ", sEmail='" + sEmail + '\'' + ", classId=" + classId + ", classes=" + classes + '}'; } }
2. Modify the Classes class, add a List students property, and use a List collection property to represent the students owned by the class, as follows:
package com.bruceliu.bean; import java.util.List; /** * A party of class entity class 1 */ public class Classes { private long cId; private String cName; //Indicates meaning There are multiple students under 1 class private List<Student> students; //student gathering public long getCId() { return cId; } public void setCId(long cId) { this.cId = cId; } public String getCName() { return cName; } public void setCName(String cName) { this.cName = cName; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Classes{" + "cId=" + cId + ", cName='" + cName + '\'' + ", students=" + students + '}'; } }
2.3. Modify the sql mapping file classMapper.xml
Add the following SQL mapping information
<?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.bruceliu.mapper.ClassesMapper"> <!--Configure 1-to-many result set mapping--> <resultMap id="classMap" type="Classes"> <!--primary key--> <id property="cId" column="C_ID"/> <result property="cName" column="c_name"/> <!--Configure a containment relationship "has many" relationship --> <collection property="students" ofType="Student"> <id property="sId" column="s_id"/> <result property="sName" column="s_name"/> <result property="sAge" column="s_age"/> <result property="sEmail" column="s_email"/> <result property="classId" column="class_id"/> </collection> </resultMap> <select id="getById" resultMap="classMap"> SELECT C.*,S.* FROM classes C INNER JOIN student S on C.c_id=S.class_id where C.c_id=#{classId} </select> </mapper>
2.4. Writing unit test classes
package com.bruceliu.test; import com.bruceliu.bean.Classes; import com.bruceliu.mapper.ClassesMapper; import com.bruceliu.utils.MyBatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; /** * @author bruceliu * @create 2019-07-09 10:06 * @description Test 1-to-many */ public class TestOne2Many { SqlSession session=null; ClassesMapper classesMapper=null; @Before public void init(){ session = MyBatisUtils.getSession(); classesMapper = session.getMapper(ClassesMapper.class); } @Test public void test1(){ Classes c = classesMapper.getById(1L); System.out.println(c); } @After public void destory(){ session.close(); } }
2.5. MyBatis one-to-many association query summary
The collection tag is used in MyBatis to solve one-to-many association queries, and the ofType attribute specifies the object type of the elements in the collection.
5. Many-to-many association (emphasis)
3.1 Requirements
3.2. Prepare SQL Statements
3.3 User entity class
package com.bruceliu.bean; import java.util.List; public class User { private long uId; private String uName; private String uSex; private long uAge; private List<Role> roles; //Multiple roles under one user public long getUId() { return uId; } public void setUId(long uId) { this.uId = uId; } public String getUName() { return uName; } public void setUName(String uName) { this.uName = uName; } public String getUSex() { return uSex; } public void setUSex(String uSex) { this.uSex = uSex; } public long getUAge() { return uAge; } public void setUAge(long uAge) { this.uAge = uAge; } public List<Role> getRoles() { return roles; } public void setRoles(List<Role> roles) { this.roles = roles; } @Override public String toString() { return "User{" + "uId=" + uId + ", uName='" + uName + '\'' + ", uSex='" + uSex + '\'' + ", uAge=" + uAge + ", roles=" + roles + '}'; } }
3.4 Role entity class
package com.bruceliu.bean; public class Role { private long rId; private String rName; public long getRId() { return rId; } public void setRId(long rId) { this.rId = rId; } public String getRName() { return rName; } public void setRName(String rName) { this.rName = rName; } @Override public String toString() { return "Role{" + "rId=" + rId + ", rName='" + rName + '\'' + '}'; } }
3.5 UserMapper
package com.bruceliu.mapper; import com.bruceliu.bean.User; /** * @author bruceliu * @create 2019-07-09 11:10 * @description */ public interface UserMapper { //1. Query the user according to the ID (at the same time, query the role collection information in association) public User getUserByid(long uid); }
3.6 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.bruceliu.mapper.UserMapper"> <resultMap id="userMap" type="User"> <id property="uId" column="u_id"/> <result property="uAge" column="u_age"/> <result property="uName" column="u_name"/> <result property="uSex" column="u_sex"/> <!--One user with multiple roles--> <collection property="roles" ofType="Role"> <id property="rId" column="r_id"/> <result property="rName" column="r_name"/> </collection> </resultMap> <select id="getUserByid" resultMap="userMap"> SELECT * FROM `user` U INNER JOIN role_user RU ON U.u_id=RU.uu__id INNER JOIN role R ON RU.rr_id=R.r_id where U.u_id=#{uid} </select> </mapper>
3.7 Testing
package com.bruceliu.test; import com.bruceliu.bean.Student; import com.bruceliu.bean.User; import com.bruceliu.mapper.StudentMapper; import com.bruceliu.mapper.UserMapper; import com.bruceliu.utils.MyBatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.After; import org.junit.Before; import org.junit.Test; /** * @author bruceliu * @create 2019-07-09 11:20 * @description */ public class TesMany2Many { SqlSession session=null; UserMapper userMapper=null; @Before public void init(){ session = MyBatisUtils.getSession(); userMapper = session.getMapper(UserMapper.class); } /** * The test queries the set of roles to which the person belongs */ @Test public void test1(){ User user = userMapper.getUserByid(1); System.out.println(user); } @After public void destory(){ session.close(); } }