springboot jpa 的使用 二

一、 自定义查询，返回结果映射到 自定义类

@Query("SELECT new com.vmi.replenishment.dto.OrderReminderDTO(arrivalTime,SUM(totalNum)) FROM SplitOrderPurchaseBody WHERE shipToCode in:shipToList GROUP BY arrivalTime ORDER BY arrivalTime")
    List<OrderReminderDTO> queryOrderReminderByShipTo(@Param(value="shipToList")List<String> shipToList);

 

要点：

1.  返回的类 实现了序列化  。然后需要提供构造方法

public class OrderReminderDTO implements Serializable {

    /** serialVersionUID*/
    private static final long serialVersionUID = 1L;

    /* 总数据 */
    //@Column(name = "TOTAL_NUM")
    private Long totalNum;
    
    /* 到货日期*/
    //@Column(name = "ARRIVAL_TIME")
    private String arrivalTime;

public OrderReminderDTO() {
}
public OrderReminderDTO(String arrivalTime,Long totalNum) {
this.arrivalTime = arrivalTime;
this.totalNum = totalNum;
}

 

2、sql 语句上用 new 提供全路径名

SELECT new com.vmi.replenishment.dto.OrderReminderDTO

 

3、属性写类的属性就可以，不用写 表字段名

from 也是类名 不是表名  

FROM SplitOrderPurchaseBody

arrivalTime

 

4、 参数赋值

: 加上参数

@Param(value="shipToList")List<String> shipToList

 WHERE shipToCode in:shipToList

 

 

5、 更新方法

 @Modifying
     @Transactional
     @Query("update SkuQuota set hubAllocation =:hubAllocation where skuNo =:skuNo and soldTo =:soldTo")
     public void distributeBySkuAndSoldTo(@Param(value="hubAllocation")String hubAllocation,
             @Param(value="skuNo")String skuNo,@Param(value="soldTo")String soldTo);

 

必须有三个注解，否则报错