MyBatis--Correlation query

Business requirements, the result set that needs to be queried is as follows

Structural analysis

1. Check out all comments, ie data[] inside is a list

2. Find out all the sub-comments under each comment ID (that is, Userobjectcmmtid) in the list, which is a one-to-many relationship.

The implementation method is as follows

1. The interface layer files are as follows

2. Implement the layer file as follows

3. Return to the first tier bean as follows

Returns the second-tier bean

The 4.xml file is as follows (first layer)

   <resultmap type= "Com.zhiji.caren.VO.CmmtRel"  id= "Cmmtlistmap" >         <result column= "user_person_cmmt_id"  property= " Userobjectcmmtid "            jdbctype=" VARCHAR "  />        <result column= "ROOT_USER_ID"  property = "Rootuserid"  jdbctype= "INTEGER"  />        <result  column= "root_user_img"  property= "rootuserimg"  jdbctype= "VARCHAR"  />         <result column= "Root_user_nickname"  property= "RootUserNickName"             jdbctype= "VARCHAR"  />         <result column= "Root_cmmt_content"  property= " Rootcmmtcontent "             jdbctype= "VARCHAR"  />         <result column= "Root_add_time"  property= "Rootaddtime"  jdbctype= "VARCHAR"  />         <collection property= "CmmtData"  javaType= " ArrayList "            column=" {rootCmmtId =  user_person_cmmt_id " select=" Getsubcmmtinfo " />    </resultMap>         <select id= "Selectcmmtlist"  resultMap= " Cmmtlistmap ">        SELECT         tupctui. User_person_cmmt_id,        tupctui. Root_user_id,        tupctui. Root_user_img,        tupctui. root_user_nickname,        tupctui. Root_cmmt_content,        tupctui. root_add_time        from          (SELECT&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. USER_PERSON_CMMT_ID,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. User_id as root_user_id,        tui. user_img as        root_user_img,         tui. NICKNAME&NBSP;AS&NBSP;ROOT_USER_NICKNAME,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. cmmt_content as        root_cmmt_content,     &NBSP;&NBSP;&NBSP;&NBSP;TUPC. add_time as root_add_time        from         t_user_person_cmmt tupc,t_user_info TUI&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;WHERE&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. User_id = tui. User_id        and        tupc . root_cmmt_id is null        and         PERSON_ID = #{objectId,jdbcType =         integer}        <if test= "lastTime !=  Null ">            AND     &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. add_time &lt;= #{lasttime}        </if>  &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;ORDER&NBSP;BY&NBSP;ROOT_ADD_TIME&NBSP;DESC)          tupctui        limit #{pageindex}    </select> 

The second level of the

XML file is as follows

    <resultmap type= "Com.zhiji.caren.VO.CmmtRelChild"  id= "Subcmmtmap" >         <result column= "super_cmmt_id"  property= " Supercmmtid " jdbctype=" VARCHAR " />        <result  column= "super_user_id"  property= "Superuserid"  jdbctype= "INTEGER"  />         <result column= "Super_nickname"  property= "SuperNickName"              jdbctype= "VARCHAR"  />         <result column= "child_user_id"  property= "ChildUserId"   Jdbctype= "INTEGER"  />        <result column= "CHILD_ User_nickname " property=" Childusernickname "             jdbctype= "VARCHAR" &NBSP;/&GT;&NBsp;       <result column= "Child_cmmt_content"  property= " Childcmmtcontent "            jdbctype=" VARCHAR "  />        <result column= "USER_PERSON_CMMT_ID"   Property= "Childcmmtid"             jdbctype= " VARCHAR " />    </resultMap>         <select id= "Getsubcmmtinfo"  resultmap= "Subcmmtmap" >        &NBSP;SELECT&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. ROOT_CMMT_ID,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. SUPER_CMMT_ID,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. Super_user_id,        tuii. NICKNAME&NBSP;AS&NBSP;SUPER_NICKNAME,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. USEr_id as child_user_id,        tui. NICKNAME&NBSP;AS&NBSP;CHILD_USER_NICKNAME,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. CMMT_CONTENT&NBSP;AS&NBSP;CHILD_CMMT_CONTENT,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. USER_PERSON_CMMT_ID,&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;TUPC. add_time as child_add_time        from         t_user_person_cmmt tupc        left  JOIN        t_user_info tui on     &NBSP;&NBSP;&NBSP;&NBSP;TUPC. User_id = tui. user_id        left join         t_user_info tuii        on     &NBSP;&NBSP;&NBSP;&NBSP;TUPC. Super_user_id = tuii.user_id        where         TUPC. root_cmmt_id is not null        and         ROOT_CMMT_ID = #{rootCmmtId}         ORDER BY CHILD_ADD_TIME    </select>

Summary: As in the example used in the MyBatis of the collection, namely

<collection property= "Cmmtdata" javatype= "ArrayList" column= "{Rootcmmtid = user_person_cmmt_id" select= "GetS Ubcmmtinfo "/>

Now learn to write an essay on this point

Refer to the article as follows link, really good, good things to take out everyone to share

Http://www.cnblogs.com/xdp-gacl/p/4264440.html

First, one to the related query

Suppose a table (class table, T_class)

c_id C_name teacher_id

Class 11 X

Class 22 X

Suppose B table (teacher table, T_teacher)

t_id T_name

1 sheets of three

2 John Doe

Business Requirements: Query class information According to class ID (check out the class's teacher information, assuming class and teacher one-on-one relationship)

Method One: Nested results--Union Table query, one query results

<!--  Use Resultmap to map the one by one correspondence between entity classes and fields  --><resultmap type= "Me.gacl.domain.Classes"  id= "Classresultmap" >     <id property= "id"  column= "c_id"/>      <result property= "name"  column= "C_name"/>      <association property= "Teacher"  javatype= "Me.gacl.domain.Teacher" >              <id property= "id"  column= "t_id"/>             <result property= "Name"   Column= "T_name"/>     </association>    </resultmap >        <select id= "GetClass"  parameterType= "int"  resultmap= "Classresultmap" >                  select * from class c, teacher t where c.teacher_id=t.t_id and  C.c_id=#{id}     </select>

Note: In the actual process, I may not use association that paragraph, i define the attribute in the me.gacl.domain.classes, the class attribute and the teacher attribute is defined together directly, so resultmap can be changed to write as follows

<resultmap type= "Me.gacl.domain.ClassesAndTeacher" id= "Classresultmap" > <id property= "cid" column= "c_id"/&     Gt <result property= "name" column= "C_name"/> <result property= "tid" column= "t_id"/> <result property= " Name "column=" T_name "/></resultmap>

Finally, consider it carefully, or feel better apart, because the class bean and the teacher bean are two basic beans, and I need to redefine a hybrid bean based on this business requirement, which is a waste of a bean. Logically, if each business needs to correlate multiple tables, as I do, I need to create a new bean, in the first way, you only need to correlate the underlying bean.

method Two: Nested query--Multiple queries to produce results

<select id= "GetClass2" parametertype= "int" resultmap= "Classresultmap" > select * from class where C_ID=#{ID}&L T;/select><resultmap type= "me.gacl.domain.Classes" id= "Classresultmap" > <id property= "id" column= "c_id" /> <result property= "name" column= "C_name"/> <association property= "teacher" column= "teacher_id" Selec t= "Getteacher"/> </resultMap> <select id= "Getteacher" parametertype= "int" resulttype= " Me.gacl.domain.Teacher "> select t_id ID, t_name name from Teacher WHERE T_id=#{id} </select>

Note: It is clear that this is a two-time query, first use #{id} query class information, and then use #{id} query teacher information.

Summarize:

MyBatis uses association tags to resolve one-to-one correlation queries, association tags are available with the following properties:

Property: The name of the object attribute

Javatype: Type of object property

Column: The foreign key field name that corresponds to

Select: Use another query to encapsulate the results

Two or one-to-many correlation queries

New requirement: Check out class information (including teachers, students) based on class ID. The class and the teacher one-to-one relationship, the class and the student relationship.

Now we need to add the student table to the database table above

Suppose C table (student table, t_student)

s_id S_name S_classid

1 Xiao Wu X

2 Xiao Li X

1. The structure returned is as follows

public class classinfo{         //class id        private int id;         //class name         private  String name;        /**       There is a teacher_id field in the   * class table, so a teacher attribute is defined in the classes class,         *  is used to maintain one-to-one relationships between teacher and class, and this teacher attribute lets you know which teacher is responsible for this class          **/        private Teacher teacher;         //uses a List<student> collection property to represent class-owned students          private list<student> students;} 

method one: nested results--Union Table query, one query results

<resultmap type= "me.gacl.domain.Classes"  id= "Classresultmap" >      <id property= "id"  column= "c_id"/>     <result property= "name"  column= "C_name"/>     <association property= "Teacher"  column= " teacher_id " javatype=" Me.gacl.domain.Teacher ">          <id property= "id"  column= "t_id"/>           <result property= "name"  column= "T_name"/>     </association>       <!-- oftype Specifies the type of object in the students collection  -->      <collection property= "Students"  oftype= "Me.gacl.domain.Student" >           <id property= "id"  column= "s_id"/>           <result property= "name"  column= "S_name"/>     </ Collection></resultmap><select id= "GetClass"  parametertype= "int"  resultMap= " Classresultmap ">        select * from class c,  teacher t,student s where c.teacher_id=t.t_id and c.C_id=s.class_id  And  c.c_id=#{id}</select>

<select id= "GetClass"  parametertype= "int"  resultmap= "Classresultmap" >         select * from class where c_id=#{id}</select>         <resultmap type= "Me.gacl.domain.Classes"  id= " Classresultmap ">    <id property=" id " column=" c_id "/>     <result property= "name"  column= "C_name"/>    <association  property= "Teacher"  column= "teacher_id"  javatype= "Me.gacl.domain.Teacher"  select= "Getteacher" >    </association>    <collection property= "Students " oftype=" me.gacl.domain.Student " column=" c_id " select=" Getstudent ">     </collection></resultmap><select id= "Getteacher"  parametertype= "int"  resultType= "Me.gacl.domain.Teacher" &Gt;        select t_id id, t_name name from  teacher where t_id=#{id}   </select><select id= "GetStudent"   parametertype= "int"  resulttype= "me.gacl.domain.Student" >         select s_id id, s_name name from student where class_id=#{id} </select>

Note:

SELECT * from class where C_id=#{id}//#{id} is the value that the implementation layer passes over

Select T_id,t_name from teacher where T_id=#{id}//#{id} is the last query obtained teacher_id

Select S_id,s_name from student where S_classid=#{id}//#{id} is a query to the C_ID

Summary: Using the collection tag in MyBatis to resolve a one-to-many association query, the OfType property specifies the object type of the element in the collection.

MyBatis--Correlation query