I've a JPA entity class called "Product" which represents the following table in a relational database (PostgreSQL), called "product":
There is a constraint of type UNIQUE composed by the columns "ext_code_a" and "ext_code_b" that, in turn, has created an index of type BTREE.
Some registries of the table are:
Well, I want to build a JPQL query to get many registries filtering by the entity fields "extCodeA" (ext_code_a) and "extCodeB" (ext_code_b). The clause WHERE will contain many couples of values.
If I build the query dinamically as follows, this may become really huge when I filter by many couples:
Because of this, I have thought about concatenating the fields and taking advantage of the operator IN:
The parameter "?1" would be a Collection <String> of concatenated couples. However, this approach prevents the DB query planner to use the index and might become too slow when the table becomes larger.
In PostgreSQL I can do this simple SQL query to take advantage of the index:
I've seen that JPA translates a JPQL query to such form of SQL when it uses multifield primary keys created with the @Embeddable annotation:
The parameter "?1" would be a Collection <ProductPK>, being every ProductPK object composed by "extCodeA" and "extCodeB". However, in my specific case, both fields are not part of any primary key, but an unique key.
So my questions are:
Would you worry about the length of the first example or would use it as is?
Is there a way to achieve something similar to the PostgreSQL example in JPA or the Hibernate implementation?
May the @Embeddable objects be used anywhere, in an arbitrary way?
Thank you very much and apologizes for such a long message.
This morning I've had enough time to try using an @Embeddable object that contains the unique key. It has worked flawlessly . I've used a JPQL query like this one:
Where "productUniq" is an @Embedded field of type ProductUniq. ProductUniq is a new class which I've made to group the fields "extCodeA" and "extCodeB", and is defined as @Embeddable. The parameter ?1 is of type Collection <ProductUniq> .
This kind of design bloats my code, but I guess that I accepted such condition when I started using JPA anyway.
However, I'm still interested in your thoughs about the first example that I wrote. Would you worry for such long queries?
subject: JPQL: filtering by 2 fields, using many couples of values.