Recently in writing Spring+springmvc+mybatis related demo, share the experience of multiple table query:
Table A structure:
C Table Structure
A entity class:
Package com.azj.entity;
Import java.util.List;
public class A {
private int aid;
Private String Aname;
Private list<c> CList;
Public list<c> getclist () {
return clist;
}
public void Setclist (list<c> clist) {
This.clist = CList;
}
public int Getaid () {
return aid;
}
public void Setaid (int aid) {
This.aid = aid;
}
Public String Getaname () {
return aname;
}
public void Setaname (String aname) {
This.aname = Aname;
}
@Override
Public String toString () {
Return "A [aid=" + Aid + ", aname=" + Aname + ", clist=" + CList + "]";
}
}
C Entity Class
Package com.azj.entity;
public class C {
private int id;
private String name;
public int getId () {
return ID;
}
public void setId (int id) {
This.id = ID;
}
Public String GetName () {
return name;
}
public void SetName (String name) {
THIS.name = name;
}
@Override
Public String toString () {
Return "C [id=" + ID + ", name=" + name + "]";
}
public C (int ID, String name) {
Super ();
This.id = ID;
THIS.name = name;
}
Public C () {
Super ();
}
}
SQL statement Mapping:
<! DOCTYPE Mapper
Public "-//mybatis.org//dtd Mapper 3.0//en"
"Http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!--multi-query-->
The first method of <!---->
<select id= "GetAll" resultmap= "Geta" parametertype= "Map" >
<!--select * from a AA left join b bb to aa.bid=bb.bid Right join C cc on AA.AID=CC.CID where 1=1-->
SELECT * from a AA left JOIN C cc on Aa.aid=cc.aid where 1=1
<choose>
<when test= "Aid!=null" >
and Aa.aid=${aid}
</when>
<when test= "Aname!=null" >
and Aa.aname=${aname}
</when>
</choose>
</select>
<resultmap type= "A" id= "Geta" >
<id column= "Aid" property= "aid"/>
<result column= "Aname" property= "Aname"/>
<collection property= "CList" javatype= "ArrayList" oftype= "C" >
<id column= "CID" property= "id"/>
<result column= "CNAME" property= "name"/>
</collection>
</resultMap>
The second method of <!---->
<select id= "GetAll" resultmap= "Geta" parametertype= "Map" >
SELECT * from a AA where 1=1
<choose>
<when test= "Aid!=null" >
and Aa.aid=${aid}
</when>
<when test= "Aname!=null" >
and Aa.aname=${aname}
</when>
</choose>
</select>
<resultmap type= "A" id= "Geta" >
<id column= "Aid" property= "aid"/>
<result column= "Aname" property= "Aname"/>
<collection property= "CList" column= "aid" select= "getc" javatype= "java.util.List" oftype= "C"/>
</resultMap>
<select id= "getc" resultmap= "Studentresultmap" parametertype= "Java.lang.Integer" >
SELECT *
From C
Where aid = #{aid}
</select>
<resultmap id= "Studentresultmap" type= "C" >
<id column= "CID" property= "id"/>
<result column= "CNAME" property= "name"/>
</resultMap>-->
</mapper>