mybatis对两表之间有两个外键的联合查询解决方案

  • A+
所属分类:java

mybatis对两表之间有两个外键的联合查询解决方案,相同的问题没有找到,找到有相似的,最后综合了之后得出的方法。关键主要在于mapping.xml。

数据库情况(蓝色相连的两张表)

mybatis对两表之间有两个外键的联合查询解决方案

mapping.xml

<resultMap id="ClassResultMap" type="com.five.police.model.Policeinfo" >
  <id column="policeid" property="policeid" jdbcType="CHAR" />
  <result column="name" property="name" jdbcType="VARCHAR" />
  <result column="idnum" property="idnum" jdbcType="CHAR" />
  <result column="age" property="age" jdbcType="INTEGER" />
  <result column="workage" property="workage" jdbcType="INTEGER" />
  <result column="phone" property="phone" jdbcType="CHAR" />
  <result column="address" property="address" jdbcType="VARCHAR" />
  <result column="record" property="record" jdbcType="VARCHAR" />
  <result column="photo" property="photo" jdbcType="VARCHAR" />
  <result column="role" property="role" jdbcType="CHAR" />
  <association property="ddsex" javaType="com.five.police.model.Dd">
    <id property="code" column="dd_sex.code"/>
    <result property="value" column="dd_sex.value"/>
  </association>
  <association property="ddrole" javaType="com.five.police.model.Dd">
    <id property="code" column="dd_role.code"/>
    <result property="value" column="dd_role.value"/>
  </association>
</resultMap>

<select id="selectallpolice" resultMap="ClassResultMap" parameterType="java.lang.String" >
  select p.policeid, p.name, p.idnum,
    p.sex, p.age, p.workage, p.phone,
    p.address, p.record, p.photo,
    p.role,dd_sex.code "dd_sex.code",dd_sex.value "dd_sex.value",
    dd_role.code "dd_role.code",dd_role.value "dd_role.value"
  from (select * from DD) AS dd_sex
  RIGHT JOIN policeinfo p ON dd_sex.code = p.sex
  LEFT JOIN DD dd_role ON dd_role.code = p.role
</select>

实体类里添加

private Dd ddsex;

private Dd ddrole;

public Dd getDdsex() {
    return ddsex;
}

public Dd getDdrole() {
    return ddrole;
}

public void setDdsex(Dd ddsex) {
    this.ddsex = ddsex;
}

public void setDdrole(Dd ddrole) {
    this.ddrole = ddrole;
}

mybatis对两表之间有两个外键的联合查询解决方案

前端本人是用的Layui,方法也贴出来吧,其他框架也是能用。

mybatis对两表之间有两个外键的联合查询解决方案

最终效果

mybatis对两表之间有两个外键的联合查询解决方案

 

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: