“This is the 15th day of my participation in the First Challenge 2022. For details: First Challenge 2022.”

The premise is introduced

In Mybatis how to do many-to-one, one-to-many (one-to-one) multi-table query? This chapter shows you how to solve very smoothly!

Basic usage

One to one

association

Association is usually used to map one-to-one relationships. For example, there is a class user that corresponds to the following entity class:

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Student {
    private int id;
    private String name;
    /** ** Students should associate with a teacher */
    private Teacher teacher;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Teacher {
    private int id;
    private String name;
}
Copy the code
Dao layer performs Mapper query operations
public interface TeacherMapper {
    Teacher getTeacher(@Param("tid") int id);
    Teacher getTeacher2(@Param("tid") int id);
}
Copy the code
The Dao layer performs the mapper.xml file
 <resultMap id="StudentTeacher" type="com.sunreal.pojo.Student">
        <result column="id" property="id"></result>
        <result column="name" property="name"></result>
        <association property="teacher" column="id"  javaType="com.sunreal.pojo.Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getStudent" resultMap="StudentTeacher">
        select *
        from student
    </select>
    <select id="getTeacher" resultType="com.sunreal.pojo.Teacher">
        select *
        from teacher
        where id = #{id}
    </select>
    <resultMap id="StudentTeacher2" type="com.sunreal.pojo.Student">
        <result column="sid" property="id"></result>
        <result column="sname" property="name"></result>
        <association property="teacher" javaType="com.sunreal.pojo.Teacher">
            <result property="name" column="tname"></result>
        </association>
    </resultMap>
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, t.name tname
        from student s,
             teacher t
        where s.tid = t.id
    </select>
Copy the code
  • Assocication: You can specify federated JavaBean objects
    • Select: specify related query result sqlid
    • Property =”role “: Specifies which property is the federated object
    • JavaType: Specifies the type of this property object
    • Column =”{javabean familiar = database fields,Javabean properties = database fields}”
    <association property="role" javaType="com.queen.mybatis.bean.Role">
	<id column="role_id" property="id"/>
	<result column="roleName" property="roleName"/>
</association>
Copy the code

Select: specifies namespace. SelectId.

collection
@Alias("Student")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Student {
    private int id;
    private String name;
    private int tid;
}
@Alias("Teacher")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Teacher {
    private int id;
    private String name;
    /** * A teacher contains more than one student */
    private List<Student> studentList;
}
Copy the code
Dao layer performs Mapper query operations
public interface TeacherMapper {
    Teacher getTeacher(@Param("tid") int id);
    Teacher getTeacher2(@Param("tid") int id);
}
Copy the code
The Dao layer performs the mapper.xml file
<resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"></result>
        <result property="name" column="tname"></result>
        <collection property="studentList" ofType="Student">
            <result property="id" column="sid"></result>
            <result property="name" column="sname"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid, s.name sname, t.name name, t.id tid
        from student s,
             teacher t
        where s.tid = t.id
          and t.id = #{tid}
    </select>

    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="studentList" javaType="ArrayList" ofType="Student"
					select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select *
        from teacher
        where id = #{tid}
    </select>
    <select id="getStudentByTeacherId" resultType="Student">
        select *
        from student
        where tid = #{tid}
    </select>
Copy the code

Note: Try not to have the same name field between each table, including the primary key ID, otherwise it may cause data confusion error;

  • Both JavaType and ofType are used to specify object types
    • Property =” Specifies the name of the property information field inside the object (type List)”
    • JavaType is the type used to specify attributes in poJOs
    • OfType specifies the type of POJOs mapped to the list collection properties.
    • Column =”{javabean familiar = database fields,Javabean properties = database fields}”
    • Select: specify related query result sqlid

“Special uncle” use section

One-to-one mapping

Entity column class Tb_blog/TbBlog
    private long blogId;
    private String blogTitle;
    private String blogContent;
    private Date createTime;
    private  String blogType;
    private String sId;

    private Tb_author author;
    List<TbAuthor> tbAuthorList;
Copy the code
Entity class TbAuthor
    private long id;
    private String username;
    private String password;
    private String email;
    private String address;
    private String phone;
    private TbBlog tbBlog;
    private List<TbBlog> tbBlogList;
Copy the code

ResultMap label configuration

 <resultMap id="blogMap" type="Tb_blog"  >
            <id column="blogId" property="blogId"/>
            <result column="blogTitle" property="blogTitle"/>
            <result column="blogContent"  property="blogContent"/>
            <result column="blogType"  property="blogType"/>
            <result column="createTime"  property="createTime"/>
     	    <result column="sId"	property="sId"/>
            <result column="id"	property="author.id"/> <! Select * from table 2;
     	   	<result column="username"	property="author.username"/>
     	    <result column="password"	property="author.password"/>
    	    <result column="email"	property="author.email"/>
</resultMap>
  <select id="selectBlogAndAuthor" resultMap="blogMap">
        select * from tb_blog g inner join tb_author r
        on g.blogId = r.id 
  </select>
Copy the code

Adding an alias to SQL can also be injected automatically, just like a field property field.

Association Label Configuration

<resultMap id="blogMap" type="Tb_blog"  >
            <id column="blogId" property="blogId"/>
            <result column="blogTitle" property="blogTitle"/>
            <result column="blogContent"  property="blogContent"/>
            <result column="blogType"  property="blogType"/>
            <result column="createTime"  property="createTime"/>
            <! The association property attribute is the attribute name of the second table in the entity class.
            <association property="tb_author" javaType="TbAuthor"><! --javaType attributes are returned entity-class objects -->
                <id column="id"  property="id"/>
                <result column="username" property="username"/>
                <result column="password" property="password"/>
                <result column="email" property="email"/>
                <result column="address" property="address"/>
            </association>
</resultMap>
    <select id="selectBlogAndAuthor" resultMap="blogMap">
        select * from tb_blog g inner join tb_author r on g.blogId = r.id 
    </select>
Copy the code

Collection Tag Configuration

Mapper interface definition

AuthorMapper.interface
/ /! The second SQL statement of a nested query writes a condition to associate the first table with the id and the value sId of the association column attribute in the mapping file
List<TbAuthor> selectAuthorandBlogAssociation(int id); 
Copy the code
BlogMapper.interface
List<TbBlog> selectBlogAndAuthorAssociation(a);
Copy the code
AuthorMapper.xml
<select id="selectAuthorandBlogAssociation" resultType="com.xqh.pojo.TbAuthor">
        select * from tb_author where id=#{id}
</select>
 <resultMap id="mapCollection" type="TbAuthor">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="email" column="email"/>
        <result property="phone" column="phone"/>
        <result property="address" column="address"/>
        <collection property="tbBlogList" column="id"
            select="com.xqh.mapper.BlogMapper.selectBlogAndAuthor"
                    fetchType="lazy">
        </collection>
    </resultMap>
    <select id="selectAuthor_BlogList" resultMap="mapCollection">
        select * from tb_author
    </select>
Copy the code
BlogMapper.xml
    <select id="selectBlogAndAuthor" resultType="com.xqh.pojo.TbBlog">
        select * from tb_blog where sId = #{id}
    </select>
Copy the code
conclusion
One-to-one mapping for multi-table queries

Association tag

Column = Foreign key field of the second table associated with the current entity class SELECT = “second query statement” (parameter limits must be written for the second SQL statement Otherwise you get all the values.)

Multiple tables query one-to-many

The collection TAB

JavaType = Return the nested entity class with two more attributes column= second table associated with the current entity class Select * from (select * from); select * from (select * from); Multi-table query for one – to – multiple collection label

Column = Foreign key field of the second table associated with the current entity class select= “second query statement” (you must write parameter limits for the second SQL statement OfType = collection ofType = collection ofType = collection ofType = collection ofType = collection ofType = collection