MyBatis multi-table query

With the progress of learning, the improvement of demand, we use in the actual development of the most or multi-table query, let us learn MyBatis multi-table query.

Database preparation

Class table

Student table

The project structure

The integration of Spring+MyBatis is used this time, and I am not very clear about the specific specifications, so I do not know whether the project structure is standardized, and the final project structure is mainly based on the actual requirements outline.

  • Dao layer
    • ClazzMapper, StudentMapper as MyBatis * mapper. XML interface
    • Tool is the interface for providing services at the Service layer, and ToolImpl is the implementation class of this interface
  • POJO
    • Entity classes for two tables, with additional attributes added based on business requirements.

I don’t know whether the structure of Tool is needed or not. I haven’t actually developed the project during my study period, so I think I need it. Provide a service to a Service (suppose it’s a Web project).

We need to use MyBatis to achieve multiple table query methods mainly have two kinds: business code implementation, SQL statement implementation of multiple table query general relations? Multi-table queries are divided into three relationships:

  • One to one
  • More than a pair of
  • Many to many

We are not talking about many-to-many here, because third-party tables are required to implement it. Let’s talk about the first two!

Business requirements:

  • Query the class information of all students (one to one)
  • Query information about students in each class (one-to-many)

1. Business code implementation

The business code is implemented in two ways: one is to use resultType, the other is to use resultMap, in fact, the two refer to the label attribute name of the operation statement in mapper.xml file.

1.1 Use resultType to achieve multi-table query of business code:

In fact, when we learn MyBatis, resultType is often used (baidu can be used if we forget). Its function is to indicate what data type is used in the query statement to receive the queried data.

What is the business code to implement MyBatis multi-table query? Business code is to use code to achieve the query, sometimes multi-table query does not necessarily need foreign key connection, two tables do not have diplomatic connection, but the demand may need to combine two tables, we can use code to control the relationship between tables when querying. How do you do that? Suppose we now want to implement our first business requirement (one to one), write two queries:

  <! Select * from t_class where cid = 1;
  <select id="findClass" resultType="clazz">
      select * from t_class where cid = #{parma1}
  </select>

  <! Select * from t_student;
  <select id="findAll" resultType="student">
      select * from t_student
  </select>
Copy the code

We could start with all the Student information query, because t_student croom t_class cid field name in a table, and then use a for loop to each Student’s croom as conditions, query croom corresponding class information, again into Student Clazz attribute, the final output.

@Override
    public List<Student> findAll(a) {
        List<Student> all = studentMapper.findAll();
        for (Student s:all){
            Clazz aClass = clazzMapper.findClass(s.getcRoom());
            s.setLi(aClass);
        }
        return all;
    }
Copy the code

In this way, we realized the multi-table query of the business code. The characteristic is that we did not use the join query, each query statement is a single table query, but we used the Java business code combined with the query statement to achieve the multi-table query. Output result:

- ==> Preparing: select * from t_student - ==> Parameters: - <== Total: 7 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 4(Integer) - <== Total: 1 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 4(Integer) - <== Total: 1 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 5(Integer) - <== Total: 1 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 1(Integer) - <== Total: 1 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 2(Integer) - <== Total: 1 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 3(Integer) - <== Total: 1 - ==> Preparing: select * from t_class where cid = ? - ==> Parameters: 3(Integer) - <== Total: 1 Student{sid=2, sName='zhangsan', age=21, cRoom=4, li=Clazz{cid=4, cName='JAVA005', room='r504', Student{sid=4, sName='lisi', age=23, cRoom=4, cName='JAVA005', room='r504', Student{sid=5, cName='PYTHON001', room='r505', cName='PYTHON001', cName='PYTHON001', cName='PYTHON001', cName='PYTHON001', room='r505', CName =1, cName='JAVA002', room='r501', cName= 1, cName='JAVA002', room='r501', Li =null}} Student{sid= 1, sName='zhaoliu', age=45, cRoom=2, cName='JAVA003', room='r502', Li =null}} Student{sid= 4, sName='zhaoliu', age=18, cRoom=3, cName='JAVA004', room='r503', Li =null}} Student{sid=9, sName='tome', sex=' male ', age=33, cRoom=3, li=Clazz{cid=3, cName='JAVA004', room='r503', li=null}Copy the code

We can see that the log output has a total of 7+1 (8) queries. In this way, we implement the relationship between two tables by using Java code, so we execute 8 SQL statements

1.2 Using resultMap to achieve multi-table query:

<! SQL > alter table query with resultMap
<select id="findAll" resultMap="rm1">
    select * from t_student
</select>

<! Namespace: com.lyl.dao.clazzmapper;
<select id="findClass" resultType="clazz">
    select * from t_class where cid = #{parma1}
</select>

<! -- student: type of data received -->
<resultMap id="rm1" type="student">
    <! -- id = primary key of this table -->
    <id column="sid" property="id"/>
    <! -- Result tag: The column attribute represents the field name, and the property attribute represents the attribute name of the entity class. MyBatis will automatically assign values for us, provided that the attribute name of our entity class must correspond to the field name of the table one by one, otherwise we must manually specify the assignment value -->
    <result column="sName" property="sName"/>
    <result column="sex" property="sex"/>
    <result column="age" property="age"/>
    <result column="cRoom" property="cRoom"/>
    <! Association tag: When the attribute type is a single object, we need to use this tag to pass data to the entity class. Select attribute: SQL statement executed, mapper. XML namespace with id column attribute: MyBatis will pass the column name or tag alias of the table to the SELECT statement where we pass the croom value of T_student. Select * from T_class where cid = #{parma1} Croom = select * from t_class where cid = #{parma1} JavaType property: Specifies the data type (clazz) of the result set returned after the SQL statement of select is executed.
    <association select="com.lyl.dao.ClazzMapper.findClass" column="cRoom" javaType="clazz" property="li"/>
</resultMap>
Copy the code

Business Code:

@Override
  public List<Student> findAll(a) {
      return studentMapper.findAll();
  }
Copy the code

View the log output:

- ==> Preparing: select * from t_student - ==> Parameters: - ====> Preparing: select * from t_class where cid = ? - ====> Parameters: 4(Integer) - <==== Total: 1 - ====> Preparing: select * from t_class where cid = ? - ====> Parameters: 5(Integer) - <==== Total: 1 - ====> Preparing: select * from t_class where cid = ? - ====> Parameters: 1(Integer) - <==== Total: 1 - ====> Preparing: select * from t_class where cid = ? - ====> Parameters: 2(Integer) - <==== Total: 1 - ====> Preparing: select * from t_class where cid = ? - ====> Parameters: 3(Integer) - <==== Total: 1 - <== Total: 7 Student{sid=2, sName='zhangsan', age=21, cRoom=4, li=Clazz{cid=4, cName='JAVA005', room='r504', Student{sid=4, sName='lisi', age=23, cRoom=4, cName='JAVA005', room='r504', Student{sid=5, cName='PYTHON001', room='r505', cName='PYTHON001', cName='PYTHON001', cName='PYTHON001', cName='PYTHON001', room='r505', CName =1, cName='JAVA002', room='r501', cName= 1, cName='JAVA002', room='r501', Li =null}} Student{sid= 1, sName='zhaoliu', age=45, cRoom=2, cName='JAVA003', room='r502', Li =null}} Student{sid= 4, sName='zhaoliu', age=18, cRoom=3, cName='JAVA004', room='r503', Li =null}} Student{sid=9, sName='tome', sex=' male ', age=33, cRoom=3, li=Clazz{cid=3, cName='JAVA004', room='r503', li=null}Copy the code

The result is that 7+1 (8) query records were executed. We did not use the above business code to achieve multi-table query, but with the help of MyBatis label – resultMap to achieve multi-table query. The attributes of the resultMap tag are described in the mapper. XML file.

Combining two ways of appeal summary:

  • The results of the two query methods are the same, and they both query 7+1 statements, 7 of which execute the conditional query of the class, and 1 of which execute all the query of the student. For short: “N+1” multi-table query, first query all the information of a table, according to the information of this table query information of the other table.
  • Both are business code to achieve multi-table query, in general is the usual join query statement into a number of single-table query, used to achieve, although the business code is a bit more, but relative to the use of a SQL to perform multi-table query to some extent to reduce the difficulty, such as: Use a SQL to achieve multiple table queries, if the demand is very complex, this time we can disassemble part of the single table queries together, as long as appropriate and reasonable, not only can reduce the difficulty, but also can facilitate maintenance.
  • Distinguish the difference between resultMap and resultType:
    • ResultType: Simply define the type of the returned result value
    • In general, resultMap is also one of the very important cores of MyBatis, because the official description is this: resultMap element is the most important and powerful element in MyBatis. It frees you from 90% of the JDBC ResultSets data extraction code, and in some cases allows you to do things that ARE not supported by JDBC

The above two methods implement multi-table queries using business assembly (multiple single-table queries implement multi-table queries)

2.SQL statement implementation

Since it is a multi-table query, then there must be a SQL statement query to achieve multi-table query. Directly on the code:


<select id="findStu1" resultMap="rm3">
    select * from t_student s join t_class c on s.croom = c.cid
</select>

<resultMap id="rm3" type="student">
    <! Assign values from database field names to attributes of entity classes -->
    <id column="sid" property="id"/>
    <result column="sName" property="sName"/>
    <result column="sex" property="sex"/>
    <result column="age" property="age"/>
    <result column="cRoom" property="cRoom"/>
    <! Association assignment: Property: Property name of the object in the entity class javaType: Now association no longer has the select tag, but the SQL statement findStu1 has found all the results, we just need to make a custom assignment, manually remove the required data from the resultMap attribute assigned to the entity class -->


    <! Clazz = clazz; clazz = clazz;
    <association property="li" javaType="clazz">
        <id column="cid" property="cid"/>
        <result column="cName" property="cName"/>
        <result column="room" property="room"/>
    </association>
</resultMap>

Copy the code

Let’s look at the result of our call:

- ==> Preparing: select * from t_student s join t_class c on s.croom = c.cid - ==> Parameters: - <== Total: 7 Student{sid=2, sName='zhangsan', age=21, cRoom=4, li=Clazz{cid=4, cName='JAVA005', room='r504', Student{sid=4, sName='lisi', age=23, cRoom=4, cName='JAVA005', room='r504', Student{sid=5, cName='PYTHON001', room='r505', cName='PYTHON001', cName='PYTHON001', cName='PYTHON001', cName='PYTHON001', room='r505', CName =1, cName='JAVA002', room='r501', cName= 1, cName='JAVA002', room='r501', Li =null}} Student{sid= 1, sName='zhaoliu', age=45, cRoom=2, cName='JAVA003', room='r502', Li =null}} Student{sid= 4, sName='zhaoliu', age=18, cRoom=3, cName='JAVA004', room='r503', Li =null}} Student{sid=9, sName='tome', sex=' male ', age=33, cRoom=3, li=Clazz{cid=3, cName='JAVA004', room='r503', li=null}Copy the code

You can see that the result is the same, but only one SQL statement is executed. This is to use SQL statements to achieve multiple table queries, is characterized by the use of a join query. ResultMap is still used, you can see how important resultMap is!

Conclusion:

  1. The appeal describes three different ways to implement multi-table queries, most of which use the second and third, or even in combination.
  2. MyBatis can automatically map to the entity class if the attribute name of the entity class is the same as the final field name of the query result. If the alias is set during the query, the assignment must be manually specified with the Result tag.
  3. In the third chapter, the result tag cannot be omitted. You must use the result tag to indicate what value you want to get from resultMap. If omitted or not written, MyBatis will not automatically help us to assemble, so we must specify it. Note: If two tables have the same field name, we must use the alias in the SQL statement to distinguish them, otherwise MyBatis will assign the first field name data.
  4. Since the single object assignment field name isassociationAnd what about a collection object in one-to-many? What do we do? At this point we need to change the label —collection, and replace the javaType attribute with the ofType attribute name. The ofType attribute can be thought of as a generic type of the collection.
  5. If we use a resultMap, we need to change the resultType in the SELECT to a resultMap, and the two cannot coexist.

More we should combine part of the official blog reference to help us learn to understand!