mybatis 使用疑难杂症：多表关联、字段重复

mybatis通过mapperLocations: classpath:mapper/*.xml所自定的xml文件能轻松的实现数据库表和完全对应的entity做关联的dao接口，比如下面的例子：

<?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.blublublu.dao.MyDao">
   <select id="myInterface" resultType="com.blublublu.entities.MyStruct">
      SELECT * FROM my_table
   </select>
</mapper>

注意，为了避免java驼峰风格和数据库下划线风格的命名之间的自动关联，一般我们要开启驼峰开关：

mybatis:
  configuration:
    map-underscore-to-camel-case: true # 驼峰开关

但是一旦一个返回对象需要关联多个库表的时候，就比较麻烦了，所以我们以实现一个值班列表为例，做一些实战演练。

首先设计我们的数据结构，定义如下entity：

ShiftsPerson.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ShiftsPerson {
    private Long id;
    private String mailId; // 邮箱id
    private String name; // 姓名
    private String phone; // 电话
}

=======================================================

ShiftsSchedule.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ShiftsSchedule {
    private Long id;
    private Date beginDate;
    private ShiftsPerson mainPerson; // 主值班人
    private ShiftsPerson vicePerson; // 副值班人
}

然后依次定义我们的dao、service、controller，如下：

ShiftsDao.java

@Mapper
public interface ShiftsDao {
    List<ShiftsSchedule> getShiftsScheduleWithPerson();
}

=======================================================

ShiftsService.java

public interface ShiftsService {
    List<ShiftsSchedule> getShiftsScheduleWithPerson();
}

=======================================================

ShiftsServiceImpl.java

@Service
@Slf4j
public class ShiftsServiceImpl implements ShiftsService {
    @Resource
    private ShiftsDao shiftsDao;
    @Override
    public List<ShiftsSchedule> getShiftsScheduleWithPerson() {
        return shiftsDao.getShiftsScheduleWithPerson();
    }
}

=======================================================

ShiftsController.java

@RestController
@Slf4j
@RequestMapping("/api/shifts")
public class ShiftsController {
    @Resource
    private ShiftsService shiftsService;
    @GetMapping(value = "list")
    public BaseModel<List<ShiftsSchedule>> getShiftsList() {
        return BaseModel.getInstance("list", shiftsService.getShiftsScheduleWithPerson());
    }
}

然后就到最重要的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.blublublu.dao.ShiftsDao">
    <resultMap id="ShiftsScheduleResultMap" type="com.blublublu.entities.ShiftsSchedule">
        <id column="id" property="id" />
        <result column="begin_date" property="beginDate" />
    </resultMap>
&lt;select id="getShiftsScheduleList" resultMap="ShiftsScheduleResultMap"&gt;
    select * from shifts_schedule
&lt;/select&gt;

&lt;resultMap id="ShiftsScheduleWithPersonResultMap"
           type="com.blublublu.entities.ShiftsSchedule"
           extends="ShiftsScheduleResultMap"&gt;
    &lt;association property="mainPerson" javaType="com.blublublu.entities.ShiftsPerson" autoMapping="true"&gt;
        &lt;id column="main_id" property="id" /&gt;
        &lt;result column="main_mail_id" property="mailId" /&gt;
        &lt;result column="main_name" property="name" /&gt;
        &lt;result column="main_phone" property="phone" /&gt;
    &lt;/association&gt;
    &lt;association property="vicePerson" javaType="com.blublublu.entities.ShiftsPerson" autoMapping="true"&gt;
        &lt;id column="vice_id" property="id" /&gt;
        &lt;result column="vice_mail_id" property="mailId" /&gt;
        &lt;result column="vice_name" property="name" /&gt;
        &lt;result column="vice_phone" property="phone" /&gt;
    &lt;/association&gt;
&lt;/resultMap&gt;
&lt;select id="getShiftsScheduleWithPerson" resultMap="ShiftsScheduleWithPersonResultMap"&gt;
    SELECT s.*,
        p1.id AS main_id,
        p1.name AS main_name,
        p1.mail_id AS main_mail_id,
        p1.phone AS main_phone,
        p2.id AS vice_id,
        p2.name AS vice_name,
        p2.mail_id AS vice_mail_id,
        p2.phone AS vice_phone
    FROM shifts_schedule AS s
    LEFT JOIN shifts_person AS p1 ON s.main_person_id=p1.id
    LEFT JOIN shifts_person AS p2 ON s.vice_person_id=p2.id
&lt;/select&gt;

</mapper>

这里有一些重要的细节需要说明一下，首先extends="ShiftsScheduleResultMap"可以用来继承，相同的字段就不用重复写了，然后就是这里的association是用来实现关联表逻辑的，最后就是因为这里同时join了两次person表，所以返回字段相同情况下是有问题的，那么就用AS重命名，然后修改association里的result里的column来匹配。