Mybatis多表查询resultMap

Mybatis多表查询resultMap

• 配置resultMap时，在配置查询语句时候指定resultMap。
• column指定数据库中查询出的列，property指定类的属性名。
• id配置主键，result配置普通属性

使用collection指定集合属性，注意ofType是设置集合中类型

<?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.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="phoneNum" property="phoneNum"/>
        <collection property="orderList" ofType="order">
            <id column="oid" property="id"/>
            <result column="orderTime" property="orderTime"/>
            <result column="total" property="total"/>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        SELECT *, o.id oid FROM sys_user u,sys_order o WHERE u.id=o.uid
    </select>

    <resultMap id="userAndRoleMap" type="user">
        <id column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="email" property="email"/>
        <result column="phoneNum" property="phoneNum"/>
        <collection property="roleList" ofType="role">
            <id column="roleId" property="id"/>
            <result column="roleName" property="roleName"/>
            <result column="roleDesc" property="roleDesc"/>
        </collection>
    </resultMap>
    <select id="findUserAndRoleAll" resultMap="userAndRoleMap">
        SELECT * FROM sys_user u, sys_role r, sys_user_role ur WHERE u.id=ur.userId AND r.id=ur.roleId
    </select>
</mapper>

使用association配置集合中的属性，

<?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.mapper.OrderMapper">
    <!--    指定映射关系-->
    <resultMap id="orderMap" type="order">
        <!--
        方式一：
        主键使用id，其他使用result，
        column表示数据库字段，
        property表示指定类相应属性-->
        <id column="oid" property="id"/>
        <result column="orderTime" property="orderTime"/>
        <result column="total" property="total"/>

        <!--        <result column="uid" property="user.id"/>-->
        <!--        <result column="username" property="user.username"/>-->
        <!--        <result column="password" property="user.password"/>-->
        <!--        <result column="email" property="user.email"/>-->
        <!--        <result column="phoneNum" property="user.phoneNum"/>-->

        <!--
        方式二：
        association指定关系
        -->
        <association property="user" javaType="com.domain.User">
            <id column="uid" property="id"/>
            <id column="username" property="username"/>
            <id column="password" property="password"/>
            <id column="email" property="email"/>
            <id column="phoneNum" property="phoneNum"/>
        </association>
    </resultMap>


    <select id="findAllOrder" resultMap="orderMap">
        SELECT *, o.id oid FROM sys_order o,sys_user u WHERE u.id=o.uid
    </select>
</mapper>

User类：

package com.domain;

import java.util.List;

public class User {
    private long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;
    private List<Order> orderList;
    private List<Role> roleList;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public List<Order> getOrderList() {
        return orderList;
    }

    public void setOrderList(List<Order> orderList) {
        this.orderList = orderList;
    }

    public List<Role> getRoleList() {
        return roleList;
    }

    public void setRoleList(List<Role> roleList) {
        this.roleList = roleList;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +
                ", phoneNum='" + phoneNum + '\'' +
                ", orderList=" + orderList +
                ", roleList=" + roleList +
                '}';
    }
}