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(25) not null,
       constraint pk_stateId primary key(stateId)
);

--副表 房间类型表
create table t_room_type(
       type_Id number not null,
       typeName varchar2(25) 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 = 0;
        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 = 0;
        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框架中实现多表连接查询和查询结果分页 ]