jpa 自定义查询

jpql

参考：https://stackoverflow.com/questions/52591535/spring-jpa-no-converter-found-capable-of-converting-from-type

@Component
public interface CrowdReportDAO extends CrudRepository<CrowdReportDO, String> {
    @Query(value = "select new com.tianrang.cbd.amc.mock.api.vo.CrowdReportVO(r.channel, r.crowd, sum(r.crowdSize), r.activityName, min(r.activityBeginTime), max(r.activityEndTime), sum(r.uv), c.name)  from crowd_report r join crowd c on r.crowd = c.id where channel= '1' group by r.crowd, r.activityName order by min(r.activityBeginTime) desc")
    List<CrowdReportVO> getAggregateReport();
}
@Data
@AllArgsConstructor
public class CrowdReportVO {
    String channel;
    String crowd;
    String crowdSize;
    String activityName;
    String activityBeginTime;
    String activityEndTime;
    Long uv;
    String crowdName;
}

@RestController
@RequestMapping(path = UriName.CROWD_REPORT)
public class CrowdReportController extends BaseController {
    @Resource
    CrowdReportDAO crowdReportDAO;
    @GetMapping
    public List<CrowdReportVO> retrieveList(@Valid CrowdReportGetDTO dto) {
        return crowdReportDAO.getAggregateReport();
    }
}

native query

@Component
public interface CrowdReportDAO extends CrudRepository<CrowdReportDO, String> {
    @Query(value = "select channel, crowd, c.name as crowd_name, activity_name, min(activity_begin_time) as activity_begin_time, max(activity_end_time) as activity_end_time, sum(crowd_size) as crowd_size, sum(uv) as uv from crowd_report r join crowd c on r.crowd = c.id where channel= '1' group by crowd, activity_name order by activity_begin_time desc;", nativeQuery = true)
    List<CrowdReportVO> getAggregateReport();
}
// 参考https://stackoverflow.com/questions/49500309/spring-jpa-native-query-with-projection-gives-converternotfoundexception
public interface CrowdReportVO {
    String getChannel();
    String getCrowd();
    // 不会自动将下划线格式转为驼峰，需要手动添加注解 https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#projections.interfaces.open
    @Value("#{target.crowd_size}")
    String getCrowdSize();
    @Value("#{target.activity_name}")
    String getActivityName();
    @Value("#{target.activity_begin_time}")
    String getActivityBeginTime();
    @Value("#{target.activity_end_time}")
    String getActivityEndTime();
    Long getUv();
    @Value("#{target.crowd_name}")
    String getCrowdName();
}

@RestController
@RequestMapping(path = UriName.CROWD_REPORT)
public class CrowdReportController extends BaseController {
    @Resource
    CrowdReportDAO crowdReportDAO;
    @GetMapping
    public List<CrowdReportVO> retrieveList(@Valid CrowdReportGetDTO dto) {
        return crowdReportDAO.getAggregateReport();
    }
}