mybatis 多表关联mapper文件写法操作
林奇lc 人气:3两张表SystemParam(系统参数表) 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标签实现多表查询(多个对象)
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中添加查询全部
<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是当属性为集合类型时使用的标签
2 多表联合
<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 多表关联mapper文件写法操作就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持。
加载全部内容