处理JPA期间查询的正确方法是什么？

debugcn 发表于 Dev

埃弗顿·门多萨（EvertonMendonça）

我需要在下面生成预期的结果。基本上，这是根据特定时间段（每周，每月等）汇总值的查询。有一个日期过滤器，带有开始和结束，我们需要返回所有范围的值。如果不存在，则应返回0。

在下面的示例中，开始日期为'2015-08-02'和and日期为'2015-08-23'，期间为WEEKLY。请注意，对于第2周，我们没有值，但应返回零值。

那么，在这种情况下，使用JPA做到这一点的最佳方法是什么？我们考虑使用临时表并将结果与该表连接以获取整个范围的结果，但是我不知道使用JPA是否可行，因为我们需要创建表，先连接然后销毁临时表。

另一种选择是创建数据库视图并将其映射到实体。

在上述情况下，JPQL查询应类似于以下内容：

@Query("select status, sum(totalInvoice), week from Invoice where " +
        "left join TempTable as tt..." + <-- TEMP TABLE OR VIEW TO GET THE PERIODS
        "issuer.id = :issuerId and type = :type and (:recipientId is null or recipient.id = :recipientId) and " +
        "status in ('ISSUED', 'PAID') " +
        "group by status")

另一个选择是使用存储过程，但是用JPA似乎很难实现它们，而且我认为它们不是必需的。

预期结果：

{  
   "code":"xxx",
   "title":"This is the title of the first series"
   "type":"PERIODIC",
   "period":"WEEKLY", <-- PERIOD
   "from":"2015-08-02",
   "to":"2015-08-29",
   "labels": ["2015-08-02", "2015-08-09", "2015-08-16", "2015-08-23"],
   "tabelType": "TEXT",
   "series":[  
      {  
         "code":"xxx",
         "title":"This is the title of the first series"
         "values":[10, 0, 13, 18] <- in this example, we don't have values for label "2015-08-09"
      },
      {  
         "code":"xxx",
         "title":"This is the title of the second series"
         "values":[10, 0, 13, 18] <- in this example, we don't have values for label "2015-08-09"
      }
   ]
}

富足动物

@pozs在这里提供了答案。这只能通过本机查询（PostgreSQL）来完成。结果如下：

/**
 * Returns the counts, totals and averages of the states by their currency, period and status.
 */
@Query(value = "select i.currency, date(p), i.status, count(id), sum(coalesce(i.total, 0)), avg(coalesce(i.total, 0)) " +
    "from generate_series(date_trunc(:period, cast(:from as timestamp)), date_trunc(:period, cast(:to as timestamp)) + cast('1 ' || :period as interval), cast('1 ' || :period as interval)) p " +
    "inner join invoice i on i.due_date >= p and i.due_date < p + cast('1 ' || :period as interval) " +
    "where issuer_id = :issuerId and type = :type and (:recipientId = 0 or recipient_id = :recipientId) and type = :type " +
    "group by i.currency,  date(p), i.status " +
    "order by i.currency, date(p), i.status", nativeQuery = true)
List<Object[]> getIssuerStatementTotalsByCurrencyPeriodAndStatus(
    @Param("issuerId") long issuerId,
    @Param("recipientId") long recipientId,
    @Param("type") String statementType,
    @Param("from") String from,
    @Param("to") String to,
    @Param("period") String period);

请注意，这将返回对象数组的列表。另请注意，我无法将枚举和复杂参数传递给该方法。我不得不将这些值简化为字符串和基元。

通过以下课程，我已经将此结果转换为有意义的东西：

  /**
   * Contains the result of a single result in an aggregate query.
   */
  public class AggregateResult {

      private List<String> keys;
      private List<BigDecimal> values;

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2) {
          this(new Object[] { value1, value2 });
      }

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2, Object value3) {
          this(new Object[] { value1, value2, value3 });
      }

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2, Object value3, Object value4) {
          this(new Object[] { value1, value2, value3, value4 });
      }

      @SuppressWarnings("unused")
      public AggregateResult(Object value1, Object value2, Object value3, Object value4, Object value5) {
          this(new Object[] { value1, value2, value3, value4, value5 });
      }

      public AggregateResult(Object... vals) {
          values = new ArrayList<>();
          while (values.size() < vals.length && vals[vals.length - values.size() - 1] instanceof Number) {
              Number number = (Number) vals[vals.length - values.size() - 1];
              values.add(number instanceof BigDecimal ? (BigDecimal) number : new BigDecimal(number.toString()));
          }

          this.keys = Stream.of(ArrayUtils.subarray(vals, 0, vals.length - values.size())).map(Object::toString).collect(toList());
      }

      public List<String> getKeys() {
          return keys;
      }

      public List<BigDecimal> getValues() {
          return values;
      }

      /**
       * Returns the list of {@link AggregateResult}s for the raw result. The raw result is expected to
       * have been returned from a native JPA query.
       */
      public static List<AggregateResult> fromNativeResult(List<Object[]> raw) {
          return raw.stream().map(AggregateResult::new).collect(toList());
      }
  }

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。