JPA使用纯SQL语句查询
SQL语句
String sql = "select count(1),\n" +
"sum(case finish_time when 0 then 1 else 0 end),\n" +
"sum(case finish_time when 0 then 0 else 1 end),\n" +
"merchant_id,\n" +
"store_id,\n" +
"COUNT(DISTINCT DATE(create_time))\n" +
"from recipe_cook_task\n" +
"group by merchant_id,store_id";
方法
/**
* use native query
*/
public List<RecordMerchantBackVO> nativeQuery() throws Exception {
String sql = "select count(1),\n" +
"sum(case finish_time when 0 then 1 else 0 end),\n" +
"sum(case finish_time when 0 then 0 else 1 end),\n" +
"merchant_id,\n" +
"store_id,\n" +
"COUNT(DISTINCT DATE(create_time))\n" +
"from recipe_cook_task\n" +
"group by merchant_id,store_id";
Query query = entityManager.createNativeQuery(sql);
List<Object[]> ret = query.getResultList();
// 转对象时有些问题 原生 /**query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(RecordMerchantBackVO.class));
* 转化成自定义时报错
**/
// 加上下面这行可以直接转自定义对象
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
return query.getResultList();
}
controller
@GetMapping("erp/record/merchant/back")
public BaseRet<List<RecordMerchantBackVO>> back4MerchantList() throws Exception{
return BaseRet.createSuccessRet(cuisineRecordService.nativeQuery());
}
接口响应
{
"code": 0,
"msg": "请求成功!",
"data": [
{
"store_id": "283491778840092672",
"days": 1,
"total": 4,
"merchant_id": "",
"success": 2,
"failure": 2
},
{
"store_id": "304108006227476480",
"days": 3,
"total": 17,
"merchant_id": "",
"success": 16,
"failure": 1
},
{
"store_id": "306209606672760832",
"days": 1,
"total": 5,
"merchant_id": "306209606672760832",
"success": 2,
"failure": 3
},
{
"store_id": "392849008856612864",
"days": 1,
"total": 1,
"merchant_id": "lingtest06",
"success": 0,
"failure": 1
},
{
"store_id": "283491778840092672",
"days": 3,
"total": 38,
"merchant_id": "mmm",
"success": 36,
"failure": 2
},
{
"store_id": "304108006227476480",
"days": 20,
"total": 813,
"merchant_id": "mmm",
"success": 564,
"failure": 249
},
{
"store_id": "306209606672760832",
"days": 1,
"total": 5,
"merchant_id": "mmm",
"success": 4,
"failure": 1
},
{
"store_id": "393998821044756480",
"days": 1,
"total": 1,
"merchant_id": "mmm",
"success": 1,
"failure": 0
},
{
"store_id": "408183319466156032",
"days": 3,
"total": 11,
"merchant_id": "mmm",
"success": 11,
"failure": 0
}
],
"page": null
}
MySQL查询结果:
自定义的类
@Data
public class RecordMerchantBackVO {
private BigInteger total;
private long success;
private long failure;
private String merchant_id;
private String store_id;
private long days;
}
实验结果