MyBatis 高级查询 resultMap

一对一查询

方法一 扩展实体类 (实体类继承)

public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
}

// OrderUser 实体类继承 Order 扩展OrderUser
public class OrderUser extends Order {
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private Date created;
    private Date updated;
}

sql xml 文件

<mapper namespace="Dao接口 路径">
    <select id="queryOrderUserByOrderNumber" resultType="实体类(OrderUser) 路径">
      select * from tb_order o left join tb_user u on o.user_id=u.id where o.order_number = #{number}
   </select>
</mapper>

方法二 在Order对象中添加User对象

public class User {
    private String id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private Integer sex;
    private Date birthday;
    private String created;
    private String updated;
}
// Order实体类 中放入User类 
public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
    private User user;
}

sql xml 文件
使用resultType不能完成自动映射，需要手动完成结果集映射resultMap：

<!--  
	type 返回结果集对应java中实体类型
	id resultMap的唯一标识
	autoMapping 默认完成映射 如果已开启驼峰匹配 可以解决驼峰匹配
-->
<resultMap id="resultMap唯一标识" type="实体类(Order) 路径" autoMapping="true">
	<!-- 
		指定主键
		column 数据库中主键
		property Java类中对应字段属性
	 -->
     <id column="id" property="id"/>
     <!--association:完成子对象的映射-->
     <!--property:子对象在父对象中的属性名-->
     <!--javaType:子对象的java类型-->
     <!--autoMapping:完成子对象的自动映射，若开启驼峰，则按驼峰匹配-->
     <association property="user" javaType="实体类(User) 路径" autoMapping="true">
         <id column="user_id" property="id"/>
     </association>
 </resultMap>

 <select id="唯一标识" resultMap="resultMap唯一标识">
   select * from tb_order o left join tb_user u on o.user_id=u.id where o.order_number = #{number}
</select>

一对多查询

public class OrderDetail {
    private Integer id;
    private Integer orderId;
    private Double totalPrice;
    private Integer status;
}
// 在Order 类中新增 detailList属性
public class Order {
    private Integer id;
    private Long userId;
    private String orderNumber;
    private Date created;
    private Date updated;
    private User user;
    private List<OrderDetail> detailList;
}

<resultMap id="resultMap唯一标识" type="实体类(Order) 路径" autoMapping="true">
    <id column="id" property="id"/>
    <association property="user" javaType="实体类(User) 路径" autoMapping="true">
        <id column="user_id" property="id"/>
    </association>
    <collection property="detailList" javaType="List" ofType="实体类(OrderDetail) 路径" autoMapping="true">
        <id column="id" property="id"/>
    </collection>
</resultMap>

 <select id="id唯一标识" resultMap="resultMap唯一标识">
   select * from tb_order o
   left join tb_user u on o.user_id=u.id
   left join tb_orderdetail od on o.id=od.order_id
   where o.order_number = #{number}
</select>

多对多查询

public class OrderDetail {
    private Integer id;
    private Integer orderId;
    private Double totalPrice;
    private Integer status;
    private Item item;
}

public class Item {
    private Integer id;
    private String itemName;
    private Float itemPrice;
    private String itemDetail;
}

<resultMap id="resultMap唯一标识" type="实体类(Order) 路径" autoMapping="true">
    <id column="id" property="id"/>
    <association property="user" javaType="实体类(User) 路径" autoMapping="true">
        <id column="user_id" property="id"/>
    </association>
    <collection property="detailList" javaType="List" ofType="实体类(OrderDetail) 路径" autoMapping="true">
        <id column="detail_id" property="id"/>
        <association property="item" javaType="实体类(Item) 路径" autoMapping="true">
            <id column="item_id" property="id"/>
        </association>
    </collection>
</resultMap>

 <select id="id唯一标识" resultMap="resultMap唯一标识">
   select * ,od.id as detail_id from tb_order o
   left join tb_user u on o.user_id=u.id
   left join tb_orderdetail od on o.id=od.order_id
   left join tb_item i on od.item_id=i.id
   where o.order_number = #{number}
</select>