MyBatis复杂映射

一对一查询

模型：用户表和订单表的关系，一个用户有多个订单，一个订单只属于一个用户。

一对一查询的需求：查询一个订单，同时查询出该订单所属的用户。

sql语句以及查询结果如下：

select * from orders o, user u where o.uid = u.id;

id	order_no	amount	uid	id	username	password
1	ord_1	100	1	1	jack	123456
2	ord_2	200	2	2	tom	123456
3	ord_3	300	3	3	lili	123456

创建实体

以下实体忽略setter&getter

public class User {
    private int id;
    private String username;
    private String password;
}

public class Order {
    private int id;
    private String orderNo;
    private Double amount;
    /**
     * 代表当前订单从属于哪一个客户
     */
    private User user;
}

定义OrderMapper

public interface OrderMapper {
    List<Order> findAll();
}

编写映射XML

<resultMap id="orderMap" type="org.example.hello.mybatis.entity.Order">
    <result property="id" column="id"/>
    <result property="orderNo" column="order_no"/>
    <result property="amount" column="amount"/>
    <association property="user" javaType="org.example.hello.mybatis.entity.User">
        <result column="uid" property="id"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
    </association>
</resultMap>

<select id="findAll" resultMap="orderMap">
    select * from orders o, user u where o.uid = u.id
</select>

或者 resultMap的写法为：

<resultMap id="orderMap" type="org.example.hello.mybatis.entity.Order">
    <result column="order_no" property="orderNo"/>
    <result column="uid" property="user.id"/>
    <result column="username" property="user.username"/>
    <result column="password" property="user.password"/>
</resultMap>

测试用例

// 略加载核心配置等步骤
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> all = mapper.findAll();
for(Order order : all){
  System.out.println(order);
}
/**结果**/
Order{id=1, orderNo='ord_1', amount=100.0, user=User{id=1, username='jack', password='123456'}}
Order{id=2, orderNo='ord_2', amount=200.0, user=User{id=2, username='tom', password='123456'}}
Order{id=3, orderNo='ord_3', amount=300.0, user=User{id=3, username='lili', password='123456'}}

一对多查询

模型：同上述一对一查询的用户表和订单表的关系。

一对多查询需求：查询一个用户，与此同时查询出该用户具有的订单。

对应的sql语句&查询的结果：

select * from user u left join orders o on u.id = o.uid;

id	username	password	id	order_no	amount	uid
1	jack	123456	1	ord_1	100	1
2	tom	123456	2	ord_2	200	2
3	lili	123456	4	ord_4	400	3
3	lili	123456	3	ord_3	300	3

修改User实体

public class User {
    private int id;
    private String username;
    private String password;
    /**
     * 代表当前用户具备哪些订单
     */
    private List<Order> orderList;
    // ......
}

创建UserMapper接口

public interface UserMapper {
    List<User> findAll();
}

编写映射XML

<mapper namespace="org.example.hello.mybatis.mapper.UserMapper">

    <resultMap id="userMap" type="org.example.hello.mybatis.entity.User">
        <result column="uid" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <collection property="orderList" ofType="org.example.hello.mybatis.entity.Order">
            <result column="o.id" property="id"></result>
            <result column="order_no" property="orderNo"></result>
            <result column="amount" property="amount"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select * from user u left join orders o on u.id = o.uid;
    </select>
  
</mapper>

测试用例

// 略加载核心配置等步骤
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> all = mapper.findAll();
for(User usr : all){
    System.out.println(usr);
}
/** 结果 **/
User{id=1, username='jack', password='123456', orderList='[Order{id=0, orderNo='ord_1', amount=100.0, user=null}]'}
User{id=2, username='tom', password='123456', orderList='[Order{id=0, orderNo='ord_2', amount=200.0, user=null}]'}
User{id=3, username='lili', password='123456', orderList='[Order{id=0, orderNo='ord_4', amount=400.0, user=null}, Order{id=0, orderNo='ord_3', amount=300.0, user=null}]'}

多对多查询

模型：用户表和⻆色表的关系，一个用户有多个⻆色，一个⻆色被多个用户使用。

多对多查询的需求：查询用户同时查询出该用户的所有⻆色。

对应的sql语句&查询的结果：

select u.*, r.id rid, r.role_name
from user u
left join user_role ur on u.id = ur.user_id
inner join role r on ur.role_id = r.id;

id	username	password	rid	role_name
1	jack	123456	1	CEO
1	jack	123456	3	CTO
2	tom	123456	2	CFO
3	lili	123456	3	CTO

创建Role实体，修改User实体

public class User {
    private int id;
    private String username;
    private String password;

    /**
     * 代表当前用户具备哪些订单
     */
    private List<Order> orderList;

    /**
     * 代表当前用户具备哪些角色
     */
    private List<Role> roleList;
}

public class Role {
    private int id;
    private String roleName;
}

添加UserMapper接口方法

List<User> findAllWithRole();

配置UserMapper.xml

<resultMap id="userMapWithRole" type="org.example.hello.mybatis.entity.User">
    <result column="uid" property="id"></result>
    <result column="username" property="username"></result>
    <result column="password" property="password"></result>
    <collection property="roleList" ofType="org.example.hello.mybatis.entity.Role">
        <result column="rid" property="id"></result>
        <result column="role_name" property="roleName"></result>
    </collection>
</resultMap>

<select id="findAllWithRole" resultMap="userMapWithRole">
    select u.*, r.id rid, r.role_name
    from user u
    left join user_role ur on u.id = ur.user_id
    inner join role r on ur.role_id = r.id
</select>

测试用例

// 略加载核心配置等步骤
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> all = mapper.findAllWithRole();
for(User usr : all){
  System.out.println(usr);
}
/** 测试结果 **/
User{id=0, username='jack', password='123456', orderList='null', roleList='[Role{id=1, roleName='CEO'}, Role{id=3, roleName='CTO'}]'}
User{id=0, username='tom', password='123456', orderList='null', roleList='[Role{id=2, roleName='CFO'}]'}
User{id=0, username='lili', password='123456', orderList='null', roleList='[Role{id=3, roleName='CTO'}]'}