Mybatis一對一關聯查詢

本次整合Spring和Mybatis 需要查詢多表關聯的資料，資料庫設計将表一對一關聯，是以本次隻對一對一關聯查詢進行了總結。

需求

通過主表中的外鍵關聯關系查詢副表的中的字段

SELECT
    a.roomid, b.typename, c.statename
    FROM T_ROOM_INFO a
    LEFT JOIN T_ROOM_TYPE b
    ON  a.type_id = b.type_id
    LEFT JOIN T_ROOM_STATE c
    ON a.stateid = c.stateid
           

資料庫設計

--主表 房間資訊表
create table t_room_info(
       rId number not null,
       stateId number not null,
       type_Id number not null,
       constraint pk_rId primary key(rId),
       constraint fk_stateId foreign key(stateId) references t_room_state(stateid),
       constraint fk_typeId foreign key(type_Id) references t_room_type(type_id)
);
           

--副表 房間狀态表
create table t_room_state(
       stateId number not null,
       stateName varchar2() not null,
       constraint pk_stateId primary key(stateId)
);
           

--副表 房間類型表
create table t_room_type(
       type_Id number not null,
       typeName varchar2() not null,
       constraint pk_typeId primary key(type_Id)
);
           

實體類

//房間資訊
public class TRoomInfo {
    private Short rid;
    private Short stateid;
    private Short typeId;

    //省略get()、set()方法
}
           

//房間狀态
public class TRoomState {
    private Short stateid;
    private String statename;

    //省略get()、set()方法
}    
           

//房間類型
public class TRoomType {
    private Short typeId;
    private String typename;

    //省略get()、set()方法
}    
           

實作方法

1.将多張關聯表中所要查詢的字段寫入一個新的實體類中

//新的實體類包含房間資訊、狀态、類型類中的屬性
public class TRoom {
    private TRoomInfo tRoomInfo;
    private TRoomState tRoomState;
    private TRoomType tRoomType;
    private Short rid;
    private String statename;
    private String typename;

    //在get()方法中判斷屬性是否空，否則查詢時可能會報："No getter() for rId on TRoom ***"
    public Short getRid() {
        short id = ;
        if (tRoomInfo == null) {
            id = rid;
        } else {
            id = tRoomInfo.getRid();
        }
        return id;
    }

    public void setRid(Short rid) {
        this.rid = rid;
    }

    //省略其他get()、set()方法
}    
           

2.在主表的實體類中新增副表中的屬性

//主表對應的實體類包含狀态、類型類中的屬性
public class TRoomInfo {
    private TRoomState tRoomState;
    private TRoomType tRoomType;
    private Short rid;
    private String statename;
    private String typename;

    //在get()方法中判斷屬性是否空，否則查詢時可能會報："No getter() for rId on TRoom ***"
    public Short getRid() {
        short id = ;
        if (tRoomInfo == null) {
            id = rid;
        } else {
            id = tRoomInfo.getRid();
        }
        return id;
    }

    public void setRid(Short rid) {
        this.rid = rid;
    }

    //省略其他get()、set()方法
}    
           

個人建議： 使用第一種方法，符合Java中單一職責原則，後續SQL查詢字段更改也可單獨在TRoom實體類中增删屬性，主表和副表中不必查詢的字段也可不必放入TRoom實體類中。

mapper檔案

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<!-- 映射檔案要與實體類中的屬性對應上 -->
<mapper namespace="com.evan.dao.TRoomDao" >
  <!-- 方法一對應的mapper -->
  <resultMap id="TypeAndState1" type="com.evan.domain.TRoom">
    <id property="rid" column="RID" jdbcType="DECIMAL" />
    <association property="tRoomInfo" javaType="com.evan.domain.TRoomInfo" resultMap="roomInfo">
      <!-- 可在下方輸入實體類中需要查詢的屬性，也可通過property屬性指定resultMap的id -->
      <!--<result column="ROOMID" property="roomid" jdbcType="DECIMAL" />-->
    </association>
    <association property="tRoomType" javaType="com.evan.domain.TRoomType" resultMap="roomType">
      <!--<result column="TYPENAME" property="typename" jdbcType="VARCHAR" />-->
    </association>
    <association property="tRoomState" javaType="com.evan.domain.TRoomState" resultMap="roomState">
      <!--<result column="STATENAME" property="statename" jdbcType="VARCHAR" />-->
    </association>
  </resultMap>
  <resultMap id="roomInfo" type="com.evan.domain.TRoomInfo">
    <result column="ROOMID" property="roomid" jdbcType="DECIMAL" />
  </resultMap>
  <resultMap id="roomType" type="com.evan.domain.TRoomType">
    <result column="TYPENAME" property="typename" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="roomState" type="com.evan.domain.TRoomState">
    <result column="STATENAME" property="statename" jdbcType="VARCHAR" />
  </resultMap>

  <!-- 方法二對應的mapper -->
  <resultMap id="TypeAndState2" type="com.evan.domain.TRoomInfo" >
    <id column="RID" property="rid" jdbcType="DECIMAL" />
    <association property="tRoomType" javaType="com.evan.domain.TRoomType" resultMap="roomType">
      <result column="TYPENAME" property="typename" jdbcType="VARCHAR" />
    </association>
    <association property="tRoomState" javaType="com.evan.domain.TRoomState" resultMap="roomState">
      <result column="STATENAME" property="statename" jdbcType="VARCHAR" />
    </association>
  </resultMap> 
           

DAO層和Service層不變。

測試

package com.evan.util;

import com.evan.domain.TRoom;
import com.evan.service.TRoomService;
import org.apache.log4j.Logger;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)     //表示繼承了SpringJUnit4ClassRunner類
@ContextConfiguration(locations = {"classpath:spring-mybatis.xml"})
public class TestUtil {

    private static Logger logger = Logger.getLogger(TestUtil.class);

    @Resource
    private TRoomService roomService = null;

    @Test
    public void testRoom() {
        List<TRoom> lists = roomService.getList();

        for (TRoom list : lists) {
            logger.info(list.getTypename());
        }
    }

}
           

參考資料：

[ MyBatis學習總結(五)——實作關聯表查詢 ]

[ Java的MyBatis架構中實作多表連接配接查詢和查詢結果分頁 ]