This week's book giveaway is in the Java in General forum.
We're giving away four copies of Think Java: How to Think Like a Computer Scientist and have Allen B. Downey & Chris Mayfield on-line!
See this thread for details.
Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Avor Nadal
Ranch Hand
Posts: 148
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Posts: 148
    Java Netbeans IDE Postgres Database
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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?
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic