SpringBoot JPA 调用SQLServer储存过程

版本信息:

SpringBoot v2.1.6

JPA  2.2.4

SQLServer 2012

业务需求:按时间段连续统计每日销售额,如出现无销售情况,填充日期销售额为0

思路:创建储存过程,JPA调用,Service层使用工具类转换查询到的结果集

1、创建储存过程

储存过程查询结果如图:

2、JpaRepository 中调用:

public interface 类名 extends JpaRepository<实体类名, Integer> {


    @Query(value = "exec 储存过程名 @startTime=?1,@endTime=?2,@shopId=?3",
            nativeQuery = true)
    List<Object[]> getTest(String sd,String ed,int sid);
}

JPA使用原生SQLnativeQuery = true

3、Service中使用工具类将结果集转换为对象

  public void test() {
        System.out.println(EntityUtils.castEntity(Dao类名.getTest("2020-2-1","2020-2-10",1), CountShopDaySalesByDateAndShopIdDTO.class,
                new CountShopDaySalesByDateAndShopIdDTO()));
    }

 

输出信息:

Hibernate: exec 储存过程名 @startTime=?,@endTime=?,@shopId=?
[CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-01', money=580.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-02', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-03', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-04', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-05', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-06', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-07', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-08', money=355.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-09', money=0.0}, 
CountShopDaySalesByDateAndShopIdDTO{dayTime='2020-02-10', money=0.0}]

 

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页