lcsays

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

全栈技术 java实战随笔教程 发表于 2020-12-29 11:44:00 阅读59次


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>
    
    <select id="getShiftsScheduleList" resultMap="ShiftsScheduleResultMap">
        select * from shifts_schedule
    </select>
    
    <resultMap id="ShiftsScheduleWithPersonResultMap"
               type="com.blublublu.entities.ShiftsSchedule"
               extends="ShiftsScheduleResultMap">
        <association property="mainPerson" javaType="com.blublublu.entities.ShiftsPerson" autoMapping="true">
            <id column="main_id" property="id" />
            <result column="main_mail_id" property="mailId" />
            <result column="main_name" property="name" />
            <result column="main_phone" property="phone" />
        </association>

        <association property="vicePerson" javaType="com.blublublu.entities.ShiftsPerson" autoMapping="true">
            <id column="vice_id" property="id" />
            <result column="vice_mail_id" property="mailId" />
            <result column="vice_name" property="name" />
            <result column="vice_phone" property="phone" />
        </association>
    </resultMap>

    <select id="getShiftsScheduleWithPerson" resultMap="ShiftsScheduleWithPersonResultMap">
        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
    </select>
</mapper>

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