1、业务场景
两张表:user、t_order
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(30) DEFAULT NULL,
`sex` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
CREATE TABLE `t_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` bigint(20) NOT NULL,
`order_money` decimal(20,3) NOT NULL COMMENT '金额',
`order_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
需要查询用户的信息以及最近2笔订单:
public class UserOrdersDTO {
private Long id;
private String userName;
private List<Order> orderList;
}
2、实现方式
1)代码流程式:首先查用户信息、然后查该用户最近2笔订单
public UserOrdersDTO qryUserOrders(Long userId){
UserOrdersDTO userOrdersDTO = new UserOrdersDTO();
//步骤一:查询用户信息
User user = userMapper.selectByPrimaryKey(userId);
if(user == null){
return userOrdersDTO;
}
userOrdersDTO.setId(userId);
userOrdersDTO.setUserName(user.getName());
//步骤二:查询用户最近2个订单
PageHelper.startPage(1, 2);
Condition condition = new Condition(Order.class);
condition.createCriteria().andEqualTo("userId",userId);
condition.setOrderByClause("order_time desc");
List<Order> orderList = orderMapper.selectByCondition(condition);
userOrdersDTO.setOrderList(orderList);
return userOrdersDTO;
}
针对单表操作,使用公共Mapper方法即可完成,无需再写DAO代码。
2)注解式:
public interface UserMapper extends Mapper<User> {
@Select("select id ,name from user where id = #{userId}")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "name",property = "userName"),
@Result(column = "id",property = "orderList",
many = @Many(select = "com.ss.quickStart.dao.OrderMapper.qryOrdersByUserIdLastTwo"))
})
UserOrdersDTO qryUserOrders2(@Param("userId") Long userId);
}
public interface OrderMapper extends Mapper<Order> {
@Select("SELECT id,user_id,order_money,order_time FROM t_order WHERE user_id = #{userId} ORDER BY order_time DESC limit 2")
@ResultMap("BaseResultMap")
List<Order> qryOrdersByUserIdLastTwo(@Param("userId") String userId);
}
3)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="com.ss.quickStart.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.ss.quickStart.domain.User">
<!--
WARNING - @mbg.generated
-->
<id column="id" jdbcType="BIGINT" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="sex" jdbcType="INTEGER" property="sex" />
</resultMap>
<resultMap id="userOrdersMap" type="com.ss.quickStart.domain.dto.UserOrdersDTO">
<id column="id" property="id" />
<result column="userName" property="userName" />
<collection property="orderList" column="id" resultMap="orderMap" />
</resultMap>
<resultMap id="orderMap" type="com.ss.quickStart.domain.Order">
<result column="orderId" property="id" />
<result column="userId" property="userId" />
<result column="orderMoney" property="orderMoney" />
<result column="orderTime" property="orderTime" />
</resultMap>
<select id="qryUserOrders3" resultMap="userOrdersMap">
select
t.id,
name userName,
t1.id orderId,
t1.user_id userId,
t1.order_money orderMoney,
t1.order_time orderTime
from user t left join t_order t1 on t.id=t1.user_id
where t.id=#{userId}
order by order_time desc
limit 2
</select>
</mapper>