mybatis 多表關(guān)聯(lián)mapper文件寫法操作

更新時間：2020年12月01日 08:44:41 作者：林奇lc

這篇文章主要介紹了mybatis 多表關(guān)聯(lián)mapper文件寫法操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

兩張表SystemParam（系統(tǒng)參數(shù)表） Suit （主題）

SystemParam 與 Suit 是多對一

Suit 的higerSuit字段是Suit 的父及主題id 是多對一，需要自連接查詢，因為重名所以父表sql字段加別名

mapper方法

Systemparam selectJoinSuit(String strparamcode);

Po類

public class Systemparam {
 //ManyToOne "主題"
 private Suit suitobj;
 private String strparamcode;
 private String strenable;
 private String strparamname;
 //suit表主鍵
 private String suit;
 private String strparamvalue;
｝ 

public class Suit {
 //ManyToOne
 private Suit suit;
 //主鍵
 private String strsuitcode;
 private String strsuitname;
 //父級id
 private String higersuit;
｝

resultMap的寫法

<resultMap id="BaseResultMap" type="net.transino.model.Systemparam" >
 <id column="strParamCode" property="strparamcode" jdbcType="VARCHAR" />
 <result column="strEnable" property="strenable" jdbcType="VARCHAR" />
 <result column="strParamName" property="strparamname" jdbcType="VARCHAR" />
 <result column="suit" property="suit" jdbcType="VARCHAR" />
</resultMap>

resultMap 使用extends 繼承上級map

<resultMap id="ResultMapWithBLOBs" type="net.transino.model.Systemparam" extends="BaseResultMap" >
 <result column="strParamValue" property="strparamvalue" jdbcType="LONGVARCHAR" />
</resultMap>
<resultMap id="JoinsuitMap" type="net.transino.model.Systemparam" extends="ResultMapWithBLOBs" >
 <association property="suitobj" javaType="Suit">
 <id column="strSuitCode" property="strsuitcode" jdbcType="VARCHAR" />
 <result column="strSuitName" property="strsuitname" jdbcType="VARCHAR" />
 <result column="higerSuit" property="higersuit" jdbcType="VARCHAR" />
 <association property="suit" javaType="Suit">
 <id column="pstrSuitCode" property="strsuitcode" jdbcType="VARCHAR" />
 <result column="pstrSuitName" property="strsuitname" jdbcType="VARCHAR" />
 <result column="phigerSuit" property="higersuit" jdbcType="VARCHAR" />
 </association>
 </association>
</resultMap>

select寫法

<select id="selectJoinSuit" resultMap="JoinsuitMap" parameterType="java.lang.String">
 select
 systempara0_.*,
 suit1_.*,
 suit2_.strSuitCode pstrSuitCode,
 suit2_.strSuitName pstrSuitName,
 suit2_.higerSuit phigerSuit
 from SystemParam systempara0_
 LEFT OUTER JOIN
 Suit suit1_
 ON systempara0_.suit=suit1_.strSuitCode
 LEFT OUTER JOIN
 Suit suit2_
 ON suit1_.higerSuit=suit2_.strSuitCode
 WHERE
 systempara0_.strParamCode=#{strparamcode,jdbcType=VARCHAR}
</select>

補充知識：Mybatis中resultMap標(biāo)簽實現(xiàn)多表查詢（多個對象）

1 n+1

1 在teacher中添加List student，

public class Teacher {
 private int id;
 private String name;
 private List<Student> list;

2 在studentMapper.xml中添加通過tid查詢

<select id="selByTid" resultType="Student" parameterType="int">
 select * from student where tid=#{0}
</select>

3 在TeacherMapper.xml中添加查詢?nèi)?/p>

<resultMap type="Teacher" id="mymap1"> 
 <id column="id" property="id"/>
 <result column="name" property="name"/>
 <collection property="list" ofType="Student" select="com.bjsxt.mapper.StudentMapper.selByTid" column="id"></collection>
</resultMap> 
<select id="selAll" resultMap="mymap1">
 select * from teacher 
</select>

其中collection是當(dāng)屬性為集合類型時使用的標(biāo)簽

2 多表聯(lián)合

<resultMap type="Teacher" id="stumap1">
 <id column="tid" property="id"/>
 <result column="tname" property="name"/>
 <collection property="list" ofType="Student">
 <id column="sid" property="id"/>
 <result column="sname" property="name"/>
 <result column="age" property="age"/>
 <result column="tid" property="tid"/>
 <association property="teacher" select="com.bjsxt.mapper.TeacherMapper.selById" column="tid"></association>
 </collection> 
 </resultMap>
 
 <select id="selAll1" resultMap="stumap1">
 select t.id tid,t.name tname,s.id sid,s.name sname,age,tid from teacher t left join student s on t.id=s.tid 
 </select>

以上這篇mybatis 多表關(guān)聯(lián)mapper文件寫法操作就是小編分享給大家的全部內(nèi)容了，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: