如何使用JPQL、Spring Data Repositorys和Hibernate参数化TimescaleDB`tim

Question

我在PostgreSQL 13上使用带有TimescaleDB扩展的Spring Data JPA(下面是Hibernate，JPA 2.1(，并希望使用time_bucket函数。这取数据的作为INTERVAL的bucket_width和作为TIMESTAMP列的time。

我想把它放在Spring Data Repository中，并想使用JPQL@Query将数据提取到一个投影中，该投影表示返回时间桶的聚合计数、平均值等。我不想使用本机查询，因为我想与其他一些表连接，并自动填充它们的实体。

我将time_bucket函数注册到我正在扩展的PostgisPG95Dialect，如下所示：

public class CustomPostgresqlDialect extends PostgisPG95Dialect {
public CustomPostgresqlDialect() {
super();
this.registerFunction("time_bucket", new StandardSQLFunction("time_bucket", new OffsetDateTimeType()));
}
}

如果bucket_width是硬编码的，那么所有这些都可以正常工作。但是我希望bucket_width是查询方法的一个参数。

以下操作很好：

@Query("select sys as system, "
+ "function('time_bucket', '10 mins', vt.ts) as startTime, "
+ "count(vt) as total, avg(vt.speed) as avgSpeed "
+ "from Data vt "
+ "JOIN vt.system sys "
+ "where sys.sysId = :sysId and "
+ "function('time_bucket', '10 mins', vt.ts)  between :from and :to "
+ "group by system, startTime "
+ "order by startTime")
List<SummaryAggregate> getSummaryData(
@Param("sysId") String sysId,
@Param("from") OffsetDateTime from,
@Param("to") OffsetDateTime to);

但当我试图参数化间隔时，我无法使其发挥作用。我尝试将间隔作为字符串传递，因为这就是它在硬编码版本中的编写方式：

@Query("select sys as system, "
+ "function('time_bucket', :grouping, vt.ts) as startTime, "
+ "count(vt) as total, avg(vt.speed) as avgSpeed "
+ "from Data vt "
+ "JOIN vt.system sys "
+ "where sys.sysId = :sysId and "
+ "function('time_bucket', :grouping, vt.ts)  between :from and :to "
+ "group by system, startTime "
+ "order by startTime")
List<SummaryAggregate> getSummaryData(
@Param("sysId") String sysId,
@Param("from") OffsetDateTime from,
@Param("to") OffsetDateTime to,
@Param("grouping") String grouping);

其中CCD_ 11被传递类似于CCD_。

但为此，我得到了这个错误：

SQL Error: 0, SQLState: 42883
ERROR: function time_bucket(character varying, timestamp with time zone) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 61

然后我尝试将其更改为Duration，因为Hibernate将Duration转换为PostgreSQL Interval类型

@Query("select sys as system, "
+ "function('time_bucket', :grouping, vt.ts) as startTime, "
+ "count(vt) as total, avg(vt.speed) as avgSpeed "
+ "from Data vt "
+ "JOIN vt.system sys "
+ "where sys.sysId = :sysId and "
+ "function('time_bucket', :grouping, vt.ts)  between :from and :to "
+ "group by system, startTime "
+ "order by startTime")
List<SummaryAggregate> getSummaryData(
@Param("sysId") String sysId,
@Param("from") OffsetDateTime from,
@Param("to") OffsetDateTime to,
@Param("grouping") Duration grouping);

但我仍然得到了同样的错误，这一次它认为Duration是bigint而不是Interval。

SQL Error: 0, SQLState: 42883
ERROR: function time_bucket(bigint, timestamp with time zone) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 61

有没有一种方法可以使用JPQL对Interval进行参数化？

Answer 1

有一种方法，但您必须为此注册一个自定义函数，因为您不能强制转换为任意SQL类型。

public class CastInterval implements SQLFunction {
@Override
public boolean hasArguments() {
return true;
}
@Override
public boolean hasParenthesesIfNoArguments() {
return true;
}
@Override
public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
return firstArgumentType;
}
@Override
public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
return "cast(" + args.get(0) + " as interval)";
}
}

您必须在方言中注册该函数。

因此，如果方言按照指示进行扩展，则可以使用以下内容来完成：

this.registerFunction("castInterval", new CastInterval());

然后你可以这样使用它：function('time_bucket', castInterval(:grouping), vt.ts)