1

I have a somewhat complicated @Query in a JpaRepository.

I need to get the results of this query in two forms (but not at the same time!):

  • First, the client asks for a count of the number of results: SELECT COUNT(x.*) FROM my_table x ...
  • Then later (maybe), they want to see the actual data: SELECT x.* FROM my_table x ...

What follows (the ...) is identical for both queries. Is there any way to combine these so that I don't repeat myself?

  • I know I could just use the second method, and count the number of elements in the resulting List. However, this adds the overhead of actually fetching all those elements from the database.
  • I could put the ... in a String constant somewhere, but that kind of separates it from its context (I'd lose IntelliJ's syntax highlighting/error checking)
  • I can't convert it to a Criteria or Example query, because I need to use PostGIS's geography type. (And these are less readable anyway...)

Any other ideas?

Kricket
  • 4,049
  • 8
  • 33
  • 46
  • 1
    Using two separate queries already meets your requirements and seems like the only option here. – Tim Biegeleisen Oct 09 '19 at 14:41
  • It also violates DRY... – Kricket Oct 09 '19 at 15:45
  • If you aren't sure that you would even need to run the second query, then how can you justify running it ahead of schedule? – Tim Biegeleisen Oct 09 '19 at 15:46
  • I don't run it ahead of time. I run the first query and report the count. Then later, if I get the request, I run the second query and return the results. – Kricket Oct 09 '19 at 15:52
  • JPA really has nothing to do with this, it's just a database problem. If you _don't_ run the full query at first, then you have to run it a second time. Note here that time spent on the database isn't really the issue, it's the extra roundtrip you have to make to and from your application. – Tim Biegeleisen Oct 09 '19 at 15:55
  • As regards the conversion to a Criteria query, Hibernate [does support PostGis spatial types](https://stackoverflow.com/questions/52314732/use-postgis-geography-point-with-hibernate-spatial-5-in-spring-boot), and even [registers certain custom functions](https://github.com/hibernate/hibernate-orm/blob/master/hibernate-spatial/src/main/java/org/hibernate/spatial/dialect/postgis/PostgisFunctions.java), so maybe that's a possibility after all – crizzis Oct 09 '19 at 15:56
  • @crizzis Ah thanks for the link; I had searched but couldn't find anywhere that used Geography. I'll have to give it a try. – Kricket Oct 10 '19 at 05:59
  • @TimBiegeleisen I was hoping that JPA (or Spring, or Hibernate) might have some way of having a reusable SQL fragment, similar to a \@NamedQuery – Kricket Oct 10 '19 at 06:01

1 Answers1

0

If your worries is about some developer change the COUNT query and forgot to change the SELECT query too, you can create a repository integration test to guarantee the expected result between the two queries.

Another alternative is create a unit test to read the annotation content and verify if the final of these two queries are equal.

Dherik
  • 17,757
  • 11
  • 115
  • 164