SpringData Jpa使用原始sql返回自定义对象

网上大量的copy文 真让人难受…

最近学习了jpa，多表条件查询时+分页，使用了Query这个对象，翻阅了大量资料终于找到了方法

核心方法：

@PersistenceContext
EntityManager entityManager;

//这里主要处理返回的自定义对象和字段类型 一定要和数据类型保持一致 比如bigInt=Long,varChar=String...
Query query = entityManager.createNativeQuery(querySql).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(自定义对象.class))
      //需要查询处理的字段和类型
     //这里的name必须和自定义对象的name一致！！！比如数据库中是order_no必须转为orderNo
    .addScalar("id",StandardBasicTypes.LONG)
      .addScalar("companyName",StandardBasicTypes.STRING)
    .addScalar("orderNo",StandardBasicTypes.STRING)
      ;

//参数赋值
for (Map.Entry<String, Object> entry : params.entrySet()) {
   String key = entry.getKey();
   query.setParameter(key, entry.getValue());
   //分页参数 过滤 limit :offset, :limit
   if (!key.equals("offset") && !key.equals("limit")) {
      countQuery.setParameter(key, entry.getValue());
   }
}

完整的代码:

public List orderList(JSONObject body, Result result) {
      StringBuffer tableSql = new StringBuffer("SELECT s.id,s.order_no as orderNo,y.company_name as companyName ");
      StringBuffer countSql = new StringBuffer("SELECT count(*) ");
      StringBuffer selectSql = new StringBuffer();
      StringBuffer whereSql = new StringBuffer();
      StringBuffer orderBySql = new StringBuffer();
      //分页
      Map<String, Object> params = new QueryUtil(body);
      StringBuffer limitSql = new StringBuffer(" limit :offset, :limit");
      selectSql.append(" FROM biz_orders s INNER JOIN biz_company y ON s.company_id = y.id INNER JOIN biz_user r ON s.user_id = r.id INNER JOIN biz_company_person pn ON y.id = pn.company_id AND pn.person_role = 1 ");
      selectSql.append(" WHERE s.order_status > 0 ");
      //下单日期 - 开始
      if (body.containsKey("payOrderTimeStart") && body.get("payOrderTimeStart") != null) {
         Date payOrderTimeStart = body.getDate("payOrderTimeStart");
         whereSql.append(" AND s.pay_order_time >= :payOrderTimeStart ");

         params.put("payOrderTimeStart", payOrderTimeStart);

      }
      //下单日期-结束
      if (body.containsKey("payOrderTimeEnd") && body.get("payOrderTimeEnd") != null) {
         Date payOrderTimeStart = body.getDate("payOrderTimeEnd");
         whereSql.append(" AND s.pay_order_time <= :payOrderTimeEnd ");
         params.put("payOrderTimeEnd", payOrderTimeStart);
      }
      //订单号
      if (body.containsKey("orderNo") && body.get("orderNo") != null) {
         String orderNo = body.getString("orderNo");
         whereSql.append(" AND s.order_no = :orderNo ");
         params.put("orderNo", orderNo);
      }
      //用户手机号
      if (body.containsKey("phoneNumber") && body.get("phoneNumber") != null) {
         String phoneNumber = body.getString("phoneNumber");
         whereSql.append(" AND r.phone_number = :phoneNumber ");
         params.put("phoneNumber", phoneNumber);
      }
      //首选公司名称-模糊
      if (body.containsKey("companyName") && body.get("companyName") != null) {
         String companyName = body.getString("companyName");
         whereSql.append(" AND y.company_name LIKE :companyName");
         params.put("companyName", "%" + companyName + "%");
      }

      //公司类型
      if (body.containsKey("companyType") && body.get("companyType") != null) {
         Integer companyType = body.getInteger("companyType");
         whereSql.append(" AND y.company_type = :companyType ");
         params.put("companyType", companyType);
      }
      //是否代理记账
      if (body.containsKey("bookkeeping") && body.get("bookkeeping") != null) {
         boolean bookkeeping = body.getBooleanValue("bookkeeping");
         if (bookkeeping) {
            whereSql.append(" AND s.bookkeeping_amount > 0 ");
         } else {
            whereSql.append(" AND s.bookkeeping_amount = 0 ");
         }
      }
      //收货方式
      if (body.containsKey("receivingType") && body.get("receivingType") != null) {
         Integer receivingType = body.getInteger("receivingType");
         whereSql.append(" AND s.receiving_type = :receivingType ");

         params.put("receivingType", receivingType);
      }
      //订单状态
      if (body.containsKey("orderStatus") && body.get("orderStatus") != null) {
         Integer orderStatus = body.getInteger("orderStatus");
         whereSql.append(" AND s.order_status = :orderStatus ");

         params.put("orderStatus", orderStatus);
      }

      //排序
      orderBySql.append(" ORDER BY s.create_time DESC ");

      //查询语句
      String querySql = tableSql.append(selectSql).append(whereSql).append(orderBySql).append(limitSql).toString();
    Query query = entityManager.createNativeQuery(querySql).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(BizOrders.class))
          //需要查询处理的字段和类型
          .addScalar("id",StandardBasicTypes.LONG)
          .addScalar("companyName",StandardBasicTypes.STRING)
         ;

      Query query = entityManager.createNativeQuery(querySql).unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(BizOrders.class))
            //需要查询处理的字段和类型
            .addScalar("id",StandardBasicTypes.LONG)
            .addScalar("companyName",StandardBasicTypes.STRING)
            ;

      //count
      Query countQuery = entityManager.createNativeQuery(countSql.append(selectSql).append(whereSql).toString());
      //参数赋值
      for (Map.Entry<String, Object> entry : params.entrySet()) {
         String key = entry.getKey();
         query.setParameter(key, entry.getValue());
         //分页参数 过滤 limit :offset, :limit
         if (!key.equals("offset") && !key.equals("limit")) {
            countQuery.setParameter(key, entry.getValue());
         }
      }
      //list
      List list = query.getResultList();
      //count
      long total = Long.valueOf(countQuery.getSingleResult().toString());
      entityManager.close();

      result.setData(list);
      result.setCount(total);

      return list;
   }

文章永久链接：https://tech.souyunku.com/19766