In the previous three articles, we learned what is MyBatis, simply implement a MyBatis program, use MyBatis to achieve CRUD operation, involving some common configurations of MyBatis, simple use of ResultMap, log function implementation and introduction, paging implementation.

Below is the address of the previous article

(a)

(2)

(3)

In this article we’ll look at the use of annotations, many-to-one, and one-to-many implementations. Many-to-one and one-to-many are more difficult to understand and can seem tiring.

There are several paragraphs in the MyBatis introduction.

I think these paragraphs are an appropriate introduction to notes

Use simple annotations

Using annotations eliminates the need to write mapper.xml files

Since there is no mapper. XML mapping file, we can map it through an interface

<! Mapping with fully qualified name of mapper interface -->
<mappers>
    <mapper class="com.molu.mapper.UserMapper"/>
</mappers>
Copy the code

To solve the mapping problem, we write a method in the Mapper interface and use annotations

public interface UserMapper {
    // Use the annotation form of the select tag
    @Select("select * from user")
    List<User> getUsers(a);
}
Copy the code

As you can see, using annotations saves a lot of work. We just need to solve the mapping and write the SQL in the annotations correctly to test it.

But so far, our entity class attribute name is still not changed back, and is still inconsistent with the field name in the database

There is no way to configure ResultMap to map the two using annotations alone, so you can expect the result to be null password

Never mind, we will use the resultMap annotation later to string the two together

The test class

    @Test
    public void getUsersTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUsers();
        for(User user : users) { System.out.println(user); }}Copy the code

Inevitably, all the returned passwords are null.

To solve this problem, it is easy to write mapper. XML. This kind of mixed method is very common in MyBatis.

When writing mapper. XML, note that mapper. XML and the mapper interface must be in the same interface and have the same name. (because the mapping binding is through the fully qualified name of the Mapper interface) it looks like this:

Write the mapper.xml mapping file

In the Mapper label, only the resultMap label and specific configuration are written.

      
<! DOCTYPEmapper
        PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.molu.mapper.UserMapper">
<resultMap id="UserMap" type="user">
    <result column="pwd" property="password"/>
</resultMap>
</mapper>
Copy the code

Bind the resultMap ID to the @ResultMap annotation in the interface.

public interface UserMapper {
    // Bind resultMap ID
    @ResultMap("UserMap")
    @Select({"select * from user"})
    List<User> getUsers(a);
}
Copy the code

Writing this and running it should solve the problem.

You can also add an AS alias to your SQL statement as described earlier.

@Select({"select id,name,pwd as password from user"})
Copy the code

This is why I will use these paragraphs of MyBatis official documentation as an introduction at the beginning.

Using annotations to map simple statements makes your code look cleaner, but for slightly more complex statements, Java annotations are not only ineffective, they can also clutter up your already complex SQL statements. Therefore, if you need to do something complicated, it is best to map statements in XML.

How you choose to configure the mapping, and whether you think you should unify the form of mapping statement definitions, is entirely up to you and your team. In other words, never stick to one approach; you can easily migrate and switch between annotation-based and XML-based statement mapping.

In addition to mixing configuration files and annotations, we can also map them using pure annotations

The @Results annotation corresponds to the resultMap tag in mapper.xml

@result corresponds to the Result tag in mapper.xml

public interface UserMapper {
    @Results({ @Result(property = "password", column = "pwd") })
    @Select({"select * from user"})
    List<User> getUsers(a);
}
Copy the code

This is fine, but if there are too many fields and attributes to map, it can be a bit of a disaster…….

This is the end of the annotations, there is not much to say. If you want to learn more about annotations, you can move them to other blogs, such as

For one more

Build a many-to-one environment

Create teacher table (master table)

CREATE TABLE `teacher` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
Copy the code

Create student table (from table)

CREATE TABLE `student` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `tid` int(3) NOT NULL.PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Copy the code

Both use a physical foreign key association, that is, the tid in the student table is the ID in the teacher table

Throw a demand

Now we want to query the student information and return the name of the teacher corresponding to tid

If you do it in SQL, it’s naturally very simple

What if we want to use MyBatis?

Try to achieve

Write the Teacher entity class

public class Teacher {
    private int id;
    private String name;

    public Teacher(a) {}public Teacher(int id, String name) { this.id = id;  this.name = name; }public int getId(a) { returnid; }public void setId(int id) { this.id = id; }public String getName(a) { returnname; }public void setName(String name) { this.name = name; }@Override
    public String toString(a) {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                '} '; }}Copy the code

Write the Student entity class

public class Student {
    private int id;
    private String name;
    private int tid;
    private String teacherName;

    public Student(int id, String name, int tid, String teacherName) {
        this.id = id;
        this.name = name;
        this.tid = tid;
        this.teacherName = teacherName;
    }
    
//======== omits the generated getter setter method ========
    
    @Override
    public String toString(a) {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                ", tid=" + tid +
                ", teacherName=" + teacherName +
                '} '; }}Copy the code

Write methods in the Mapper interface

public interface StudentMapper {
    List<Student> getStudent(a);
}
Copy the code

Write the mapper.xml file

<mapper namespace="com.molu.mapper.StudentMapper">
    <select id="getStudent" resultType="Student">
        select s.id,s.name,s.tid,t.name as teacherName
        from student s
        inner join teacher t
        where s.tid=t.id
    </select>
</mapper>
Copy the code

Writing test classes

public class MapperTest {
    @Test
    public void getStudentTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for(Student student : studentList) { System.out.println(student); } sqlSession.close(); }}Copy the code

Test results:

Very unexpectedly can achieve……. , was also intended to extend resultMap support for complex queries by failing to implement it.

Now that I’ve written it here, I’d like to expand it……

Handle as query nesting

In addition to more direct write a table query SQL

We can also write two SQL queries and nest the results of one as the results of the other.

• First we modify the original entity class

public class Student {
    private int id;
    private String name;
    // private int tid;
    // The tid attribute is no longer required
    private Teacher teacher;
    TeacherName teacherName teacherName teacherName teacherName teacherName teacherName teacherName
Copy the code

• Write two query SQL
• Map the query result of getTeacher to the teacher attribute in the Student entity class
• After mapping, the getTeacher query results are nested in the getStudent query results

<mapper namespace="com.molu.mapper.StudentMapper">
    <select id="getStudent" resultType="Student">
        select * from student
    </select>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher
    </select>
</mapper>
Copy the code

• Connect the two using resultMap

    <select id="getStudent" resultMap="ST_Map">
        select * from student
    </select>

    <resultMap id="ST_Map" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
Copy the code

This should be a little confusing, so don't look down.

• Test classes (mediocre test classes)

    @Test
    public void getStudentTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
Copy the code

• Test results:

The test results were very satisfactory, which satisfied our previous requirements, and did not use the concatenated table query method

association

The above series of operations, you should be able to understand. At best, the association label is confusing

• First, we use the resultMap tag to map attributes in the entity class to fields in the database.

• Id and name naturally do not need us to map them

• The only thing that needs to be mapped is our teacher property

• But this property it’s not a simple field, it’s an object

• In resultMap, simple attributes are mapped using result or ID

• Complex properties (such as objects and collections) are mapped using association and Collection

How to use association

<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
Copy the code

• So if we’re going to do the mapping, thenpropertyandcolumnNature is essential
• If it’s just a simple mapping, then we’ll use itresultCan do it. But the teacher property is an object
• associationProvided in the labeljavaType, so that we can specify which attributes we want to mapObject type
• associationTags also provideselect, which allows us to specify a SELECT tag, which is the one below

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

• At this point, our teacher property has been mapped to the TID field, and the object type has been specified, and the nested query results have been specified
• In other words, our tid will be taken aswhere id = #{id}Is executed first
• The returned query results are nested into the teacher property in getStudent
• When we execute the getStudent method to query, teacher will be nested as the result of the getTeacher query.

This operation is very similar to the subquery in our SQL. It is a little difficult to understand, but it should be better understood by implementing it.

Process as result nesting

In addition to nesting two query SQL with each other as described above, association can also nest the results

First, we will make some changes to the SQL in the body of the SELECT tag.

    <select id="getStudent" resultMap="ST_Map">
        SELECT s.id sid,s.name sname,t.id teacherId,t.name tname
        FROM student s,teacher t
        WHERE s.tid=t.id
    </select>
Copy the code

Since the previous approach is no longer used, we also need to make some changes to the resultMap tag

    <resultMap id="ST_Map" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
            <result property="id" column="teacherId"/>
        </association>
    </resultMap>
Copy the code

As you can see, all the query fields in THE SQL are mapped in the resultMap tag

However, we still process the complex teacher attribute through association

But this time we don’t specify a SELECT tag for it to nest. Instead, in its tag body, it maps the attributes of the Teacher object to the Teacher table field in SQL.

After you have all mapped, execute the getStudent method.

And once again, we get what we want. So those are the two ways association is used

More than a pair of

Set up a one-to-many environment

Student entity class

public class Student {
    private int id;
    private String name;
    private int tid;
    // Omit the parameter-free constructs and getter, setter, and toString methods below
Copy the code

The Teacher entity class

public class Teacher {
    private int id;
    private String name;
    private List<Teacher> teachers;
    / / with
Copy the code

Throw a demand

We want to query the data of students corresponding to TID while querying the teacher.

This is also easy to do using SQL statements

Same, can we achieve through MyBatis?

Try to achieve

Write a method to query the teacher in mapper interface

public interface TeacherMapper {
    List<Teacher> getTeacher(a);
}
Copy the code

Synchronize to mapper.xml

<mapper namespace="com.molu.mapper.TeacherMapper">
    <select id="getTeacher" resultType="Teacher">
        select t.id,t.name,s.tid,s.id,s.name
        from teacher t,student s
        where t.id=s.tid
    </select>
</mapper>
Copy the code

The test class

    @Test
    public void getTeacherTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacher();
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }
Copy the code

The test results

Obviously, there is no way to query the student’s information, nor will we write all of the student’s fields into the teacher’s entity class in the way we did in the first place.

Process as result nesting

In many-to-one, we've also used this approach. In fact, the implementation is roughly similar in one-to-many

First we modify mapper.xml (basically the same as above)

<mapper namespace="com.molu.mapper.TeacherMapper">
    <select id="getTeacher" resultMap="TS_Map">
        select t.id tid,t.name tname,s.id sid,s.name sname,s.tid stid
        from teacher t,student s
        where t.id=s.tid
    </select>
    <resultMap id="TS_Map" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="stid"/>
        </collection>
    </resultMap>
</mapper>
Copy the code

As mentioned above, we use association and Collection to map complex attributes (such as objects and collections)

Now the attribute of students in our Teacher is a complex attribute, which is a set.

Objects use the Association tag, so it’s conceivable that we would use the Collection tag for collections

Java objects are mapped using JavaType, while collections are mapped using ofType

You modify these and they’re almost the same as many-to-one.

The test class

    @Test
    public void getTeacherTest(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacher();
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }
Copy the code

Test results:

With a many-to-one basis, achieving one-to-many is not too difficult.

Next, let's look at the more painful nesting by query, which is subquery-like

Handle as query nesting

Start by modifying the mapper.xml file

    <select id="getTeacher" resultMap="TS_Map">
        select t.id,t.name,s.id sid,s.name sname
        from teacher t,student s
        where t.id=s.tid
    </select>

    <resultMap id="TS_Map" type="Teacher">
        <collection property="students" column="sid" javaType="ArrayList" ofType="Student" select="getStudent"/>
    </resultMap>
    
    <select id="getStudent" resultType="Student">
        select * from student where id=#{sid}
    </select>
</mapper>
Copy the code

Since there are no fields in the teacher table that can be mapped to the Student object we can only use SQL for a join table query

In the collection tag we can use JavaType and ofType to specify object types and types in generics

This is going to be a little messy, but you should still be able to see it if you stroke it

With my level can only barely write this code, if you can optimize the comment section for a simple point

Test class invariant

The test results

It also fulfills our above requirements

That here MyBatis simple annotation use, many to one, one to many also come to an end. MyBatis article will be updated one after another. Thank you for checking it out. (‾◡◝)

---

Relax your eyes

The original address

Painters home page

---