Mybatis联合查询之一对一(xml版)

Mybatis联合查询之一对一(xml版)

背景:

一张订单表,里边有一个用户记录,但只是记录一个用户的Id,创建订单实体类的时候,里边添加了一个用户的属性,是User类型;要求查询一条订单信息时为User属性赋值,此时的user属性不是Integer类型的user_id,而实User类的对象,解决这个问题,要用到一对一的联合查询

1. 两张表

• user表

  id	user_name	user_address	user_age
  1	李阳	兰德中心	18
  2	张三	动物园	20

• 订单表

  id	order_no	goods	user_id
  1	10001	电视机	1
  2	10002	显卡	2
  3	10003	显示器	1
  4	10004	品牌电脑	2

2. dao接口

public interface OrderMapper {
    Order findById (Long id);
}

3. orderMapper.xml

<?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="cn.liyang.springredis.mapper.OrderMapper">
    <resultMap id="orderMap" type="cn.liyang.springredis.pojo.Order">
        <id property="id" column="id"/>
        <result property="orderNo" column="order_no"/>
        <result property="goods" column="goods"/>
        <result property="userId" column="user_id"/>
        <!--告诉这个resultMap 表中不存在的属性user
            这个属性怎么查 :    association                联合其他表去查
            去哪里查       :    select="getUserById"        具体调用那个查询语句
            查的依据       :    column="user_id"            查询的参数是用户的id
        -->
        <association property="user" column="user_id" select="getUserById" ></association>
    </resultMap>
    <select id="findById" resultMap="orderMap" parameterType="java.lang.Long">
        select * from company.order where id = #{id}
    </select>
    <select id="getUserById" parameterType="java.lang.Long" resultType="cn.liyang.springredis.pojo.User">
        select * from company.user where id = #{id}
    </select>
</mapper>

Mybatis联合查询之一对一(注解版)

1. User.java

public class User {

  private long id;
  private String userName;
  private String userAddress;
  private long userAge;
  private Order order
}

2. Order.java

public class Order {
  private long id;
  private long orderNo;
  private String goods;
  private long userId;
  private User user;
}

3. UserMapper.java接口

public interface UserMapper {
    @Select( "select * from user where id=#{id}" )
    User findById (Long id);
}

4. OrderMapper.java接口

public interface OrderMapper {
    @Select( "select * from company.order where id=#{id}" )
    @Results({
            @Result(property = "user" , column = "user_id",one = @One(select = "cn.liyang.springredis.mapper.UserMapper.findById"))
    })
    Order findById (Long id);
}

测试类

    @Autowired
    OrderMapper orderMapper;
    @Test
    public void testOrder(){
        Order order = orderMapper.findById( 1L );
        System.out.println(order);
    }

总结:

• 无论是xml配置,还是注解都有以下三个关键参数传递
• 1. 交代那个属性是表中不存在的,需要联表查询的:user
  2. 用那个查询语句或者那个接口的那个方法:getUserByid/findUserById
  3. 查询的依据参数是哪一个:colunm=“user_id”
  4. 注解: @Result(property = “user” , column = “user_id”,one = @One(select = “cn.liyang.springredis.mapper.UserMapper.findById”))
  5. xml配置: association > property=“user” column=“user_id” select=“getUserById” ></association