MyBatis - many to one & one to many

Posted by mcguinkc on Sun, 26 Sep 2021 02:00:29 +0200

SQL processing

In practical application, we need not only simple single table query, but also simultaneous query information of other tables by using MyBatis. To solve this problem, a more advanced result mapping is required.

preparation in advance

SQL preparation

We need a student table and a teacher table. The teacher's primary key is id and serves as the student's foreign key. The SQL statement is as follows:

CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, 'Miss Zhang');

CREATE TABLE `stu` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8


INSERT INTO `stu` (`id`, `name`, `tid`) VALUES ('1', 'Xiao Ming', '1');
INSERT INTO `stu` (`id`, `name`, `tid`) VALUES ('2', 'Xiao Hong', '1');
INSERT INTO `stu` (`id`, `name`, `tid`) VALUES ('3', 'Xiao Zhang', '1');
INSERT INTO `stu` (`id`, `name`, `tid`) VALUES ('4', 'petty thief', '1');
INSERT INTO `stu` (`id`, `name`, `tid`) VALUES ('5', 'Xiao Wang', '1');

Environmental preparation

Step 1: import Lombok

For unfamiliar words, refer to: Lombok

Step2: POJO

The entity classes are students (Stu) and teachers (Teacher).

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Stu {
    private int id;
    private String name;

    //Teachers
    private Teacher teacher;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

Step 3: mapper interface

Write interfaces corresponding to students (StuMapper) and teachers (TeacherMapper) respectively.

public interface StuMapper {
    //Students don't write first
}

public interface TeacherMapper {
    //In the teacher interface, write a query statement through annotation and @ Param
    @Select("select * from teacher where id = #{tid}")
    Teacher GetTeacher(@Param("tid") int id);
}

Step4: XML mapper

⚠️: Create the XML mapper file in the resource directory, and the corresponding interface path is required.

It contains students (StuMapper.xml) and teachers (TeacherMapper.xml) respectively.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD com.Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.Mapper.StuMapper">
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD com.Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.Mapper.TeacherMapper">
</mapper>

Step 5: binding mapper

Bind the built mapper to the core configuration file (mybatis config. XML).

Choose one of the following three ways:

<mappers>
    <mapper resource="com/Mapper/TeacherMapper.xml"/>
</mappers>

<mappers>
  <mapper class="com.Mapper.TeacherMapper"/>
</mappers>

<mappers>
  <package name="com.Mapper"/>
</mappers>

Step 6: Test Procedure

Test the teacher's query method to see if there are errors. If there are results, the preparation is completed.

@org.junit.Test
public void GetTeacher(){
    SqlSession sqlSession = MyBatisUtils.GetSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherMapper.GetTeacher(1);
    System.out.println(teacher);
    sqlSession.close();
}

Many to one processing

There are two processing methods:

Nested processing by query
Nested processing according to results

Nested processing by query

Query requirements: obtain the information of all students and corresponding teachers.

Implementation idea:

Obtain all student information;
The id of the teacher is obtained according to the student information, and then the id is provided to obtain the teacher information.

Question Thinking: how to realize the association query of two tables?

Make a result set mapping: Map Stu to StudentTeacher;
Realize the association of complex types through association.

Step 1: add method

Add a method to get students in the student interface.

// Query all student information and corresponding teacher information
List<Stu> getStudent();

Step 2: Stu SQL statement

<!--id Query method for students-->
<!--resultMapy-->
<select id="GetStu" resultMap="StudentTeacher">
    select * from stu
</select>

Step 3: result set mapping

<!--Stu Map to StudentTeacher-->
<resultMap id="StudentTeacher" type="Stu">
    <!--association Association properties
    property Stu Property name in
    column Column names in tables with more than one side
    javaType Attribute type
    select Methods of corresponding checking teachers-->
    <association property="teacher" column="tid" javaType="teacher" select="getTeacher"></association>
</resultMap>

javaType: the fully qualified name of a Java class or a type alias (for built-in type aliases, refer to the table above). If you map to a JavaBean, MyBatis can usually infer types. However, if you are mapping to a HashMap, you should explicitly specify a javaType to ensure that the behavior is consistent with expectations.

Step4: Teacher SQL statement

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

Step 5: Test

@org.junit.Test
public void TestStu() {
    SqlSession sqlSession = MyBatisUtils.GetSqlSession();
    StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
    List<Stu> stuList = stuMapper.GetStu();
    for (Stu s : stuList) {
        System.out.println(s);
    }
    sqlSession.close();
}

😉 Illustration:

Nested processing according to results

There is a simple and convenient way to handle nested queries.

Step 1: add method

// Query all student information and corresponding teacher information
List<Stu> getStudent2();

Step 2: Stu SQL statement

<select id="GetStu2" resultMap="StudentTeacher2">
    select s.id sid, s.name sname , t.name tname
    from stu s,teacher t
    where s.tid = t.id
</select>

Step 3: result set mapping

<resultMap id="StudentTeacher2" type="Stu">
    <id property="id" column="sid"></id>
    <result property="name" column="sname"></result>
    <association property="teacher" column="tid" javaType="teacher">
        <result property="name" column="tname"></result>
    </association>
</resultMap>

Step 4: Test

@org.junit.Test
public void TestStu2() {
    SqlSession sqlSession = MyBatisUtils.GetSqlSession();
    StuMapper stuMapper = sqlSession.getMapper(StuMapper.class);
    List<Stu> stuList = stuMapper.GetStu2();
    for (Stu s : stuList) {
        System.out.println(s);
    }
    sqlSession.close();
}

One to many processing

There are two processing methods:

Nested processing by query
Nested processing according to results

Add student attributes to the Teacher entity class. Since it is a one to many query, the student part of the returned value needs to be received by a collection, so the attribute type is defined as a collection.

private List<Stu> stus;

Nested processing by query

Similar to many to one queries.

Step 1: add method

// Query all teacher information and corresponding student information
Teacher GetTeaStu();

Step 2: Teacher SQL statement

<select id="GetTeaStu" parameterType="Teacher" resultMap="TeacherStudent">
    select * from teacher
</select>

Step 3: result set mapping

<resultMap id="TeacherStudent" type="Teacher">
    <collection property="stus" column="id" javaType="ArrayList" ofType="Stu" select="GetStudent"></collection>
</resultMap>

⚠️: One to many rooms are collection s, and many to one rooms are association s

Step4: Stu SQL statement

<select id="GetStudent" resultType="Stu">
    select * from stu where tid = #{id}
</select>

Step 5: Test

@org.junit.Test
public void TestTeaStu() {
    SqlSession sqlSession = MyBatisUtils.GetSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher  teacher = teacherMapper.GetTeaStu();
    System.out.println(teacher);
    sqlSession.close();
}

Nested processing according to results

Results there are two implementation methods for nested one to many association query, one needs to pass parameters and the other does not need to pass parameters.

Parametric method

Step 1: add method

Teacher GetTeaStu2();

Step 2: Stu SQL statement

<select id="GetTeaStu2" resultMap="TeacherStudent2">
    select t.name tname, t.id tid, s.id sid, s.name sname
    from stu s,teacher t
    where s.tid = t.id
</select>

Step 3: result set mapping

<resultMap id="TeacherStudent2" type="Teacher">
    <result property="name" column="tname" ></result>
    <collection property="stus" ofType="Stu" column="tid">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="tid"></result>
    </collection>
</resultMap>

Step 4: Test

@org.junit.Test
public void TestTeaStu2() {
    SqlSession sqlSession = MyBatisUtils.GetSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher  teacher = teacherMapper.GetTeaStu2();
    System.out.println(teacher);
    sqlSession.close();
}

Parametric method

If you use the method with parameters, you don't need to add the tid attribute to the Teacher's POJO.

Different from the nonparametric method:

There are parameters in the method of the interface
No tid attribute is required
SQL statement needs to get tid

Step 1: add method

Teacher GetTeaStu3(int id);

Step 2: Stu SQL statement

<select id="GetTeaStu3" resultMap="TeacherStudent3">
    select t.name tname, t.id tid, s.id sid, s.name sname
    from stu s,teacher t
    where s.tid = t.id and tid = #{id}
</select>

Step 3: result set mapping

resultMap id="TeacherStudent3" type="Teacher">
    <result property="name" column="tname"></result>
    <collection property="stus" ofType="Stu">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="tid"></result>
    </collection>
</resultMap>

Step 4: Test

@org.junit.Test
public void TestTeaStu3(){
    SqlSession sqlSession = MyBatisUtils.GetSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherMapper.GetTeaStu3(1);
    System.out.println(teacher);
    sqlSession.close();
}

Write at the end

association/collection

Use association when one-to-one or many to one

Use collection when one to many

property/column

Property: the property name in the POJO
column: attribute name in the database

JavaType/ofType

Both are used to specify the object type.

JavaType: used to specify the type of the property in the POJO
ofType: Specifies the type mapped to POJO s in the List collection property (applicable to collection)

Notes:

Ensure the readability of SQL statements and try to be easy to understand
According to the actual needs, try to write SQL statements with higher performance
Note that the attributes in the table are inconsistent with those in the POJO
Pay attention to the corresponding problems of one to many and many to one attributes
Try to use Log4j to view your own errors through the log

❤️ END ❤️

Write at the end

association/collection

Use association when one-to-one or many to one

Use collection when one to many

property/column

Property: the property name in the POJO
column: attribute name in the database

JavaType/ofType

Both are used to specify the object type.

JavaType: used to specify the type of the property in the POJO
ofType: Specifies the type mapped to POJO s in the List collection property (applicable to collection)

Notes:

Ensure the readability of SQL statements and try to be easy to understand
According to the actual needs, try to write SQL statements with higher performance
Note that the attributes in the table are inconsistent with those in the POJO
Pay attention to the corresponding problems of one to many and many to one attributes
Try to use Log4j to view your own errors through the log

❤️ END ❤️

Topics: Java Database Maven Mybatis SQL

Programmer Think

MyBatis - many to one & one to many

SQL processing

preparation in advance

SQL preparation

Environmental preparation

Step 1: import Lombok

Step2: POJO

Step 3: mapper interface

Step4: XML mapper

Step 5: binding mapper

Step 6: Test Procedure

Many to one processing

Nested processing by query

Step 1: add method

Step 2: Stu SQL statement

Step 3: result set mapping

Step4: Teacher SQL statement

Step 5: Test

Nested processing according to results

Step 1: add method

Step 2: Stu SQL statement

Step 3: result set mapping

Step 4: Test

One to many processing

Nested processing by query

Step 1: add method

Step 2: Teacher SQL statement

Step 3: result set mapping

Step4: Stu SQL statement

Step 5: Test

Nested processing according to results

Parametric method

Step 1: add method

Step 2: Stu SQL statement

Step 3: result set mapping

Step 4: Test

Parametric method

Step 1: add method

Step 2: Stu SQL statement

Step 3: result set mapping

Step 4: Test

Write at the end

Write at the end

Hot Topics