亲宝软件园·资讯

展开

Mybatis中多表关联时,怎么利用association优雅写resultMap来映射vo

三国梦回 人气:2

前言

有好一阵没碰mybatis了,这次的项目基于性能考虑,选了mybatis,写着写着,发现有下面的需求,比如两表联查,取其中各一部分字段,怎么更方便地用vo来接,这里犯了难;

我想的是,因为这个sql联查的vo,能不能直接使用两个表的po来接呢,比如下面这种:

Users、SeatInformation都是对应了数据库两张表的po
@Data
public class UserSeatUnionQueryVO {
    private Users users;

    private SeatInformation seatInformation;
}

折腾了2个小时,网上各种找不到类似需求,终于搞出来了,还是值得记录和分享。

一、两表关联,映射到如下类型vo(拷贝单表po属性,组合另一单表的po),怎么写

sql:


    <select id="selectOnlineUserBySeatState" resultMap="UserSeatUnionQueryVOResultMap">
        SELECT
            u.`user_id`,u.`account_status`,s.*
        FROM
            users u,
            seat_information s
        WHERE u.`user_id` = s.`user_id`
              AND u.`account_status` = 1
              AND u.`delete_status` = 1
              AND u.`center_id` = 0
              AND s.`token` IS NOT NULL
              AND s.`seat_state` = 1
              AND s.delete_status = 1
    </select>

假设我想映射的vo如下:

@Data
public class UserSeatUnionQueryVO {

    /**
     * 用户Id
     */
    @ApiModelProperty(value = "用户Id")
    @TableId(value = "user_id", type = IdType.ID_WORKER)
    private Long userId;


    /**
     * 账号状态,-1停用,1启用
     */
    @ApiModelProperty(value = "账号状态,-1停用,1启用")
    private Integer accountStatus;

    /**
     * 这里的SeatInformation是表seat_information对应的po
     */
    private SeatInformation seatInformation;
}

则mapper中应该这样写:

UsersMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.orghttps://img.qb5200.com/download-x/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ceiec.cad.mapper.UsersMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.ceiec.cad.model.Users">
        <id column="user_id" property="userId" />
        <result column="account_status" property="accountStatus" />
    </resultMap>


    <resultMap id="UserSeatUnionQueryVOResultMap" type="com.ceiec.cad.service.UserSeatUnionQueryVO">
        <id column="user_id" property="userId" />
        <result column="account_status" property="accountStatus" />
        <association property="seatInformation"  javaType="com.ceiec.cad.model.SeatInformation" resultMap="com.ceiec.cad.mapper.SeatInformationMapper.BaseResultMap">

        </association>

    </resultMap>


    <select id="selectOnlineUserBySeatState" resultMap="UserSeatUnionQueryVOResultMap">
        SELECT
            u.`user_id`,u.`account_status`,s.*
        FROM
            users u,
            seat_information s
        WHERE u.`user_id` = s.`user_id`
              AND u.`account_status` = 1
              AND u.`delete_status` = 1
              AND u.`center_id` = 0
              AND s.`token` IS NOT NULL
              AND s.`seat_state` = 1
              AND s.delete_status = 1
    </select>
</mapper>
SeatInformationMapper.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.orghttps://img.qb5200.com/download-x/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ceiec.cad.mapper.SeatInformationMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.ceiec.cad.model.SeatInformation">
        <id column="seat_information_id" property="seatInformationId" />
        //省略无关代码
    </resultMap>

</mapper>

二、两表关联,映射到如下类型vo(组合两表po),怎么写

上面的方案呢,假设A关联B,在vo里,相当于是把B组合进了A;我想的是,能不能新建一个vo,同时组合A和B呢,比如下面这样:

@Data
public class UserSeatUnionQueryVO {
    private Users users;

    private SeatInformation seatInformation;
}

sql:

和方案一一样。

UsersMapper.xml:


    <resultMap id="UserSeatUnionQueryVOResultMap" type="com.ceiec.cad.service.UserSeatUnionQueryVO">
        <association property="users"  javaType="com.ceiec.cad.model.Users" resultMap="com.ceiec.cad.mapper.UsersMapper.BaseResultMap">

        </association>
        <association property="seatInformation"  javaType="com.ceiec.cad.model.SeatInformation" resultMap="com.ceiec.cad.mapper.SeatInformationMapper.BaseResultMap">

        </association>

    </resultMap>

注意这里的 association元素中的resultMap字段,引用了其他Mapper文件的BaseResultMap

格式是:其他Mapper文件的namespace,加上resultMap的名字。

这里附上SeatInformationMapper.xml的内容:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.orghttps://img.qb5200.com/download-x/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ceiec.cad.mapper.SeatInformationMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.ceiec.cad.model.SeatInformation">
        <id column="seat_information_id" property="seatInformationId" />
        //省略无关
    </resultMap>

</mapper>

下面附上完整的usersMapper.xml的内容:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.orghttps://img.qb5200.com/download-x/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ceiec.cad.mapper.UsersMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.ceiec.cad.model.Users">
        <id column="user_id" property="userId" />
        <result column="account_status" property="accountStatus" />
    </resultMap>


    <resultMap id="UserSeatUnionQueryVOResultMap" type="com.ceiec.cad.service.UserSeatUnionQueryVO">
        <association property="users"  javaType="com.ceiec.cad.model.Users" resultMap="com.ceiec.cad.mapper.UsersMapper.BaseResultMap">

        </association>
        <association property="seatInformation"  javaType="com.ceiec.cad.model.SeatInformation" resultMap="com.ceiec.cad.mapper.SeatInformationMapper.BaseResultMap">

        </association>

    </resultMap>


    <select id="selectOnlineUserBySeatState" resultMap="UserSeatUnionQueryVOResultMap">
        SELECT
            u.*,s.*
        FROM
            users u,
            seat_information s
        WHERE u.`user_id` = s.`user_id`
              AND u.`account_status` = 1
              AND u.`delete_status` = 1
              AND u.`center_id` = 0
              AND s.`token` IS NOT NULL
              AND s.`seat_state` = 1
              AND s.delete_status = 1
    </select>
</mapper>

展示下效果:

可以看到,最后这种,这么写没问题。

三、官网文档

我在官网看了半天,后来才找到类似的例子:

https://mybatis.org/mybatis-3/sqlmap-xml.html

因为文档很长,上面这个图,大家可以通过打开上面的网址后,搜索 Multiple ResultSets for Association到达该位置。

加载全部内容

相关教程
猜你喜欢
用户评论