我是jpa newbie,想拥有一个单个表的JPA本机查询(下图),我想在我的 @entity基于 @entity的类中获取该表,称为TestRequest。它具有用dayName()获取的列"请求时间",然后使用netatediff()函数获取。
SELECT TestRequest.Id AS Id
, TestRequest.RequestTime AS RequestTime
, DAYNAME(TestRequest.RequestTime) AS RequestDay
, TestRequest.StatusMessage AS StatusMessage
, DATEDIFF(CURDATE(), TestRequest.RequestTime) AS HowLongAgo
FROM TestRequest
LEFT JOIN TestRun
ON TestRequest.TestRunId = TestRun.Id
WHERE Requestor = '[NAME]'
ORDER BY Id DESC
是否有任何方法将列(第二次获取为Howlongago)设置为未映射到TestRequest类中的表列的属性?是否有任何字段级注释?
您需要使用基于接口的投影:
您将需要创建一个界面,以定义投影中每个字段的getters,例如:
public interface RequestJoinRunProjection {
int getId();
LocalDate getRequestTime();
String getMessage();
String getRequestDay();
Long getHowLongAgo();
}
然后,您在存储库上定义了要运行的本机查询的方法:
public interface TestRequestRepository extends CrudRepository<TestRequest, Long> {
// Any other custom method for TestRequest entity
@Query(value = "SELECT trq.Id AS id " +
" , trq.RequestTime AS requestTime " +
" , DAYNAME(trq.RequestTime) AS requestDay " +
" , trq.StatusMessage AS statusMessage " +
" , DATEDIFF(YEAR, CURDATE(), trq.RequestTime) AS howLongAgo " +
"FROM TestRequest trq " +
" LEFT JOIN TestRun tr " +
" ON trq.TestRunId = tr.Id " +
"WHERE Requestor = ?1 ORDER BY Id DESC"), nativeQuery = true)
List<RequestJoinRunProjection> findTestSumary(String name);
}
通知查询必须是本地的,因为您使用的是数据库函数,列名也必须匹配投影接口的设置器(以下BEAN规则),因此请使用AS
来更改查询中的名称。
我强烈建议您在注入@Query
注释之前对H2进行测试。DATEDIFF
需要3个参数。