占位符 单参数查询
this.entityManager.createNativeQuery("SELECT statistical_date as statisticalDate, DATE_FORMAT(statistical_date, '%Y') AS year , insured_job_code as insuredJobCode,SUM(policy_daily_total) AS totalPolicy,SUM(policy_daily_price) AS insuFee " + "FROM daily_policy_occupation " + "WHERE customer_id = ? and insu_type = ? and statistical_date >= ? and statistical_date <= ? " + "GROUP BY year,insuredJobCode order by year asc;") .setParameter(1, ao.getCustomerId()) .setParameter(2, ao.getInsuType()) .setParameter(3, ao.getStartDate()) .setParameter(4, ao.getEndDate()) .unwrap(NativeQuery.class) .setResultTransformer(Transformers.aliasToBean(YearlyOccupationInsuFeeVO.class)) .getResultList()
@Query("select new com.compass.dal.model.employer.DailyClaimClosedFeeModel(statisticalDate, sum(closedDailyCount), sum(closedDailyPrice) ,sum(dailyMedicineFee),sum(dailyDeathFee),sum(dailyDisabilityFee),sum(dailyDelayWorkFee),sum(dailyHospitalizationFee),sum(dailyOtherFee)) from DailyClaimFeeTypeDO where customerId = :customerId and statisticalDate >= :startDate and statisticalDate <= :endDate and insuType = :insuType group by statisticalDate order by statisticalDate asc")
List<DailyClaimClosedFeeModel> findByCustomerIdAndInsuTypeAndStatisticalDateBetweenOrderByStatisticalDateAsc(@Param("customerId") String customerId, @Param("insuType") String insuType, @Param("startDate") Date startDate, @Param("endDate") Date endDate);
占位符多参数 指定参数名查询
this.entityManager.createNativeQuery("SELECT statistical_date as statisticalDate, DATE_FORMAT(statistical_date, '%Y') AS year , insured_job_code as insuredJobCode,SUM(policy_daily_total) AS totalPolicy,SUM(policy_daily_price) AS insuFee " + "FROM daily_policy_occupation " + "WHERE customer_id = ? and insu_type = ? and statistical_date >= ? and statistical_date <= ? " + "GROUP BY year,insuredJobCode order by year asc;") .setParameter(1, ao.getCustomerId()) .setParameter(2, ao.getInsuType()) .setParameter(3, ao.getStartDate()) .setParameter(4, ao.getEndDate()) .unwrap(NativeQuery.class) .setResultTransformer(Transformers.aliasToBean(YearlyOccupationInsuFeeVO.class)) .getResultList()
@Query("select new com.compass.dal.model.employer.DailyClaimClosedFeeModel(statisticalDate, sum(closedDailyCount), sum(closedDailyPrice) ,sum(dailyMedicineFee),sum(dailyDeathFee),sum(dailyDisabilityFee),sum(dailyDelayWorkFee),sum(dailyHospitalizationFee),sum(dailyOtherFee)) from DailyClaimFeeTypeDO where customerId in (:customerIds) and statisticalDate >= :startDate and statisticalDate <= :endDate and insuType = :insuType group by statisticalDate order by statisticalDate asc")
List<DailyClaimClosedFeeModel> findByCustomerIdsAndInsuTypeAndStatisticalDateBetweenOrderByStatisticalDateAsc(@Param("customerIds") List<String> customerIds, @Param("insuType") String insuType, @Param("startDate") Date startDate, @Param("endDate") Date endDate);