1

I need to specify the AS OF SYSTEM TIME follower_read_timestamp() clause cause statement from CockroachDB to my query using Criteria Builder. I need to use Criteria Builder since my queries are dynamically constructed and have a lot of different optional predicates based upon user input. I need to specify the clause cause statement because I am running into some transaction issues and have been advised to add it by support. I also just generally want to know how/if to add clauses like this since I've had to do it other times as well.hibernate

Here is the AS OF SYSTEM TIME CockroachDB clause cause statement docs https://www.cockroachlabs.com/docs/v22.2/as-of-system-time

Here is an example of what my Criteria Builder code looks like -- it's actually much more complicated but I simplified it for this question.

// Get criteria builder
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Create criteria query and root
CriteriaQuery<StorageEntity> criteriaQuery = criteriaBuilder.createQuery(StorageEntity.class);
Root<StorageEntity> root = criteriaQuery.from(StorageEntity.class);

List<Predicate> predicates = new ArrayList<>();

if (CollectionUtils.isNotEmpty(ids)) {
  predicates.add(criteriaBuilder.<List<UUID>> in(root.get("id")).value(ids));
}

/**
A lot of other dynamic predicates are added here which is why I'm using criteria builder
*/

if (CollectionUtils.isNotEmpty(otherIds)) {
  predicates.add(criteriaBuilder.<List<UUID>> in(root.get("otherIds")).value(otherIds));
}

// Get criteria query with filter predicates
criteriaQuery = criteriaQuery.where(predicates.toArray(new Predicate[] {}));

// Create query
TypedQuery<StorageEntity> query = entityManager.createQuery(criteriaQuery);

/**
Ideally here I would be able to add `AS OF SYSTEM TIME <timestamp>` clause cause statement
*/

// Execute query and get results
List<StorageEntity> storageEntities = query.getResultList();

p.s. I'm using jakarta.persistence:jakarta.persistence-api:2.2.3 pulled in as a transitive dependency by org.springframework.boot:spring-boot-starter-data-jpa:2.7.7

joshuatvernon
  • 1,530
  • 2
  • 23
  • 45
  • JPA does not have historical data querying functionality. Returning managed entities based on a specific time and having to track how to persist changes.. just wasn't done that I'm aware of. Any support for history data is going to be JPA provider specific - or you can issue a native SQL query. – Chris Jan 25 '23 at 21:27
  • Have you tried telling JPA that your table name is "actualname AS OF SYSTEM TIME follower_read_timestamp()" ? – histocrat Jan 26 '23 at 20:11

1 Answers1

2

I haven't tested this (came here from the cockroachdb tag), but judging from docs, JPA doesn't automatically quote identifiers containing spaces. Given that, since the clause goes at the end of the FROM clause in a table, you could probably just use a class annotated with

Table(name = "actual_table_name AS OF SYSTEM TIME follower_read_timestamp()")

Instead of the normal table name annotation whenever you want the clause. Since AS OF SYSTEM TIME only works in top-level queries, this should generate valid SQL as long as this is the last table name referenced in the FROM clause.

If that doesn't work in your actual query, you might be stuck building the query, extracting the raw SQL, adding in the clause through string manipulation, and then executing it natively.

histocrat
  • 2,291
  • 12
  • 21
  • here is a good resource of how you could do what @histocrat suggested: https://antoniogoncalves.org/2012/05/24/how-to-get-the-jpqlsql-string-from-a-criteriaquery-in-jpa/ – RCX Jan 26 '23 at 22:09