I have the scenario as One Primary Entity and another Secondary Entity.The Primary entity fetches records from Secondary Entity using OneToMany relationship.But I need only the latest 3 records records from the secondary entity using created_timestamp.How can I achieve this scenario using Spring Boot/Spring Data JPA.The database used in this scenario is Postgres. Please suggest.
Asked
Active
Viewed 205 times
0
-
2First of all, provide your code and mention database, latest 3 records by what? – Nitin Dhomse Jan 15 '18 at 06:28
-
This might help https://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-hql – Vasily Liaskovsky Jan 15 '18 at 06:31
-
Also check here https://stackoverflow.com/questions/9314078/setmaxresults-for-spring-data-jpa-annotation#answer-9315989. And yes, you should search the site before asking a question. – Vasily Liaskovsky Jan 15 '18 at 06:37
-
The problem is I am looking at retrieving from the secondary table not the primary table. – Ajay Kalkoti Jan 15 '18 at 06:46
1 Answers
0
There are various options how you can achieve this. Which one is right for you depends among other things on WHY you want to do this.
If you just want the last three elements because you want to work with those, but you don't care if the others get loaded or not you can simple create another property which hands out a filtered collection. Just make sure that property doesn't get mapped by JPA.
I guess more like is the case that you actually don't want to load the other entities because it might be a huge number of those.
The problem is that JPQL doesn't support a LIMIT clause but only has a setMaxResults method on the query object. This means you can apply it to subselects or similar. This again means you have to use SQL to do the limiting.
With this you again have multiple options:
Back to the roots: Just write a SQL statement that gives you exactly the results you need. Execute it with a
JdbcTemplateand provide a suitable (most likely handcrafted)RowMapperorResultSetExtractor. Of course, the resulting entities will be detached and it would be a bad idea to try to reattach them to a JPA session.Use views: Create a view for the Secondary Entity which limits the rows to three per Primary Entity. Map your entity classes to that view. Note that the entities will be attached to the JPA session but you must not modify them, because writes to view as this one will most likely not work at all and if they do it won't work with JPA as desired, because the database would effectively change the collection content and JPA would need to reselect all the data to get a consistent state.
Either way, you are bound to write the query in SQL. For this, you'll need the LIMIT clause and possibly apply it to an inline view in a join.
Jens Schauder
- 77,657
- 34
- 181
- 348