my dog learned polymorphism*
The moose likes Object Relational Mapping and the fly likes JPQL: filtering by 2 fields, using many couples of values. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPQL: filtering by 2 fields, using many couples of values." Watch "JPQL: filtering by 2 fields, using many couples of values." New topic
Author

JPQL: filtering by 2 fields, using many couples of values.

Avor Nadal
Ranch Hand

Joined: Sep 15, 2010
Posts: 105

Hello:

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.
    Avor Nadal
    Ranch Hand

    Joined: Sep 15, 2010
    Posts: 105

    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?
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: JPQL: filtering by 2 fields, using many couples of values.