1

I've a database structure built with JPA. To give a little bit context - the user is shown a datatable and some (many...) filters with which he can filter the shown entries of the datatable. For example, every entry of the datatable corresponds to a certain factory (of e.g. 40 factories in total) and the user can filter for one or more factories.

To prevent overhead, the user can only filter by factories which occur in the dataset the datatable is built of, i.e. factories which would give an empty result after filtering (since there aren't any entries with this factory) aren't shown at all in the factory filter.

Furthermore, the datatable is paginated, i.e. just the first (second, third, etc.) 50 entries are given to the frontend but -- and that's the crucial point -- the filters should of course correspond to all entries (i.p. also the entries on the pages which are not shown). That means if factory B only occurs on page 2 and page 1 is loaded (i.e. there is no factory B under the shown entries resp. the entries the frontend receives), the factory filter should still list factory B.

My approach is the following: When constructing the result, using the same sql query, I get all values which one can filter by. So my constructed table looks so to say like this

factory | status       | ...
F1      |  done        | ...
F2      | in progress  | ...
F1      | in progress  | ...

containing the entries over all pages (of which just the first (second, third, ...) 50 entries are given to the frontend) projected to the properties which one can filter by.

Now I just get the needes values by e.g. SELECT DISTINCT(entry.factory) FROM ... in a typed query. Basically, at this point I would create a sql view of the constructed table and get then 1) all distinct factories, 2) all distinct statuses, etc. pp. But JPA doesn't allow a CREATE VIEW when creating a typed query so at the moment, for each column I construct the same table (the one above) and get the distinct values in the current column.

(just for the sake of completeness: for performance reasons, it is not possible to get all entities and then iterate e.g. using the stream API over the list of results mapping to the "filterable" attributes, i.e. filtering has to happen directly in the database using SQL (i guess?))

I have two questions:

  • Is this implementation reasonable?
  • If yes, how can I approach the idea of creating a view? Ideally, directly via JPA but unfortunately I didn't find a real way to approach this using JPA.

Edit: To break it down to the very basic problem:

QueryPart queryPart = new QueryPartDTO("<some JPA query>");
QueryFactory<MyEntity> queryFactory = new QueryFactory<>();
final TypedQuery<MyEntity> query = queryFactory.buildTypedQuery(queryPart, entityManager, MyEntity.class);
List<MyEntity> resultList = query.getResultList(); 
//  at this point, the query fires ↑
//  that's okay because I do need the List<Entity> in order to give it to the frontend
//  but instead of just getting a List<MyEntity>, 
//  I would like to have a sql view containing the result (resp. the whole result) as well
//  because moreover, I have to get e.g. all factories, all statuses, etc.
//  which occur in the given data - what could of course be done with
//  the already given List<MyEntity> using the Stream API but since the list 
//  is in general very large, this approach would be to inefficient.
//  Instead, I would like to do these computations using SQL - but for this,
//  I need the data given by the constructed query above in a SQL table.
//  Executing the query over and over again (and first, getting all distinct factories,
//  second getting all distinct statuses, etc.) is not possible for
//  performance reasons - the query is expensive. Hence, I need to
//  save the result the query gives - in a way, I can execute some more
//  queries on it: a view i guess.

Thanks in advance.

ATW
  • 233
  • 2
  • 14
  • Why don't you just use [queries](https://docs.oracle.com/javaee/6/tutorial/doc/bnbrg.html) with the entity manager? If you really have to use views, here are related stackoverflow questions - [question1](https://stackoverflow.com/questions/644577/does-jpa-support-mapping-to-sql-views), [question2](https://stackoverflow.com/questions/16686501/jpa-and-table-views-can-it-be-done), and a [guide](https://vladmihalcea.com/map-jpa-entity-to-view-or-sql-query-with-hibernate/). – Chaosfire Jan 17 '22 at 14:48
  • @Chaosfire if I use queries with the entity manager, I cannot hold a constructed SQL table (as SQL table to execute more SQL queries on it, not as java list) as well, can I? What I wanna do is a (rather expensive) construction of a table T and then getting all distinct values from column 1, all distinct values from column 2, etc. What I want to prevent is costructing T multiple times so I want to somewhat "cache" T. Is this possible using em queries? – ATW Jan 19 '22 at 10:31
  • And by caching I mean caching T as SQL table after constructing, not just holding the query. – ATW Jan 19 '22 at 10:51
  • And regarding the guide - then I have to "translate" the JPA-ish query to plain SQL, don't I? Isn't there a way with JPA directly? (and btw, thank you very much for your help so far!) – ATW Jan 19 '22 at 12:53
  • I have not heard of a jpa-ish way to map entity to a view.Couldn't find anything about that either. Actually i am not sure a view will cache the result in the way you want, but it's been a while since i used them, so take that with a grain of salt. Take a look at this [question](https://stackoverflow.com/questions/16687736/is-there-a-way-to-cache-a-view-so-that-queries-against-it-are-quick). They are talking about something called materialised view, which might be what you need. The first time i am hearing about it, i guess a learned something new today. – Chaosfire Jan 19 '22 at 17:46
  • Maybe I'm missing something, but why don't you just write a JPQL query (or native SQL query if you must) and then use the [setFirstResult](https://docs.oracle.com/javaee/6/api/javax/persistence/TypedQuery.html#setFirstResult(int)) and [setMaxResults](https://docs.oracle.com/javaee/6/api/javax/persistence/TypedQuery.html#setMaxResults(int)) methods? – SeverityOne Jan 20 '22 at 22:59
  • What do you mean @SeverityOne? how do setFirstResult and setMaxResults help me with my "table caching"-issue? – ATW Jan 24 '22 at 11:24
  • What I'm wondering is why you're trying to do all sorts of complicated caching when you can tell JPA which rows you want. In other words, are you trying to implement something that already exists? – SeverityOne Jan 25 '22 at 11:00
  • Sorry, but unfortunately I can’t quite follow you. I need all distinct values from column 1, all distinct values from column 2, etc. AND a chunk (say entry 101-150) of the whole generated table and I want to prevent constructing the table (what is done via several expensive joins) multiple times – so the approach I have in mind is to cache the result table after the said expensive joins and getting then the respective distinct values by SELECT DISTINCT and the chunk by LIMIT/OFFSET on the cached table. How can the selection of rows in JPA help me with this? Or am I missing something? – ATW Jan 25 '22 at 11:11

1 Answers1

2

JPA doesn't support view creation indeed. It doesn't support any DDL queries.

Dynamic view creation is a fishy idea. Normally you design your database scheme only once. Moreover, you'll need separate views for different users which starts to sound like a nightmare.

What you can do is create a table with user filtering results, keyed with the user ID. Whenever the user applies his filters, you can DELETE the old result rows for this user and INSERT the new ones. Then you do the SELECT DISTINCT queries over this smaller dataset.

JPA won't help you with this too much since it doesn't support the INSERT SELECT SQL statement which you'll like to use here.

EDIT. I changed this answer to keep useful comments below. The previous version was completely different and not useful anyway.

Alexey Veleshko
  • 792
  • 1
  • 18
  • The tables are separated, that's not the point. I'm joining them together in order to select just the entries I want to have (e.g. the data entries which match the current filter configuration the user made) - and the data I am getting at this point after joining the respecting tables together is the data i need multiple times but which I do not want to construct multiple times. The question is how I can save the table I get after joining in a way I can filter with SQL afterwards again -- without creating the result again. – ATW Jan 24 '22 at 13:01
  • How often does your data change? – Alexey Veleshko Jan 24 '22 at 13:50
  • 1
    Rather often. The whole software is so to say a multi-user management software portraying business processes and status workflows in the data, i.e. one has to expect unpredicable data changes in multiple places. That is to say, I have to construct the result table at least once — ideally exactly once. – ATW Jan 24 '22 at 14:01
  • 1
    I updated my answer, but somehow I don't have high hopes on it. It might involve moving too much data around. – Alexey Veleshko Jan 24 '22 at 14:59
  • 1
    Your updated answer sounds like an useful approach -- may be appropriate despite the costs of carrying much data around. I will think about it. Thanks! – ATW Jan 24 '22 at 19:22