Inject a list into SpringData to be used as a kind of virtual database table

user_6419338 0
Viewed 28

I have a database table on which a sorted query needs to be done. To do the sorting a join on another table is requiered. The problem is that this other table does not exist in the database because we read the required data on the services startup from a CSV file and keep it as an in-memory list.

Is it possible to somehow inject this list as a kind of virtual database into Spring Data? So that it could use this list to make the required join and sorting.

As far as I know, the only other options I have would be to create a real database table from this in-memory list or load the whole table and do the sorting in the service itself.

spring-data spring-data-jpa spring-boot hibernate
1 Answers

You can add a special order by expression through e.g. Spring Data Specification, but that is going to be very ugly. In HQL it looks like this:

case rootAlias.attribute when 'value1' then 1 when 'value2' then 2 ... else null end

which will return some integer value by which you can sort ascending or descending, based on the mapping you have.

Even if you have lots of values, I would rather recommend you don't do a join at all, and instead try to make this attribute of your main table sortable, so that you don't need this mapping. You can maybe create a trigger that maintains a column based on the mapping, which can be used for sorting directly. If you do all your changes through JPA/Hibernate, you could also use a @PreUpdate/@PrePersist listener to handle the maintenance of this column.

user_4124460
Related