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

}