wood burning stoves 2.0*
The moose likes Object Relational Mapping and the fly likes JPA - abtsract class - WHERE clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA - abtsract class - WHERE clause" Watch "JPA - abtsract class - WHERE clause" New topic
Author

JPA - abtsract class - WHERE clause

Gyorgy Pugymer
Greenhorn

Joined: May 22, 2011
Posts: 9
Hi!

I have an abstract class which is extended by some subclass. I persist this structure with hibernate. The Inheritance type is set to TABLE_PER_CLASS so every child class has it's own table.

I have a @NamedQuery on the abstract class and it has Where clause. I checked the generated SQL and it is not the best. At first it queries the whole table of every child classes then it uses UNION to merge them into one. Finally the where clause filters the merged table. So this SQL SELECT queries the whole TABLES with lot of records and it makes the application very slow. I think it would be more optimized to use the where part on the query of the child tables and not on the merged table.

How can I do that?

I have tried to find any example or tutorial about it, but I couldn't find!

Thanks


Dieter Quickfend
Bartender

Joined: Aug 06, 2010
Posts: 543
    
    4


from www.w3schools.org SQL tutorial:


Oracle Certified Professional: Java SE 6 Programmer && Oracle Certified Expert: (JEE 6 Web Component Developer && JEE 6 EJB Developer)
Gyorgy Pugymer
Greenhorn

Joined: May 22, 2011
Posts: 9
Unfortunately I can't use SQL directly. So this is not an sql problem. This problem is about JPA or Hibernate

The annotations on the anchestor class:

@Entity
@MappedSuperclass
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@NamedQueries({
@NamedQuery(name = ......, query = "FROM ClassName where ......),
.......

public abstract class.......
.....
Dieter Quickfend
Bartender

Joined: Aug 06, 2010
Posts: 543
    
    4

Kind of a design flaw to use TABLE_PER_CLASS and then do polymorphic queries, no? If possible, return to SINGLE_TABLE... If not, SQL is your only option.

See here: http://stackoverflow.com/questions/14095964/hibernate-fetching-polymorphic-property-in-table-per-class-inheritance-mode
Gyorgy Pugymer
Greenhorn

Joined: May 22, 2011
Posts: 9
I can't change the annotation TABLE_PER_CLASS because many user use this application. The tables contains a few hundred thousand record. If I change that annotation, the hibernate will refactor the table structure in the database. Unfortunately I can't allow myself to lost every data in the database. Although I think it could solve my problem.
If I use SQL, I will have to change the query on the anchestor class every time when I create a new child class.
That's why I try to find an other way
Dieter Quickfend
Bartender

Joined: Aug 06, 2010
Posts: 543
    
    4

Is it feasible to not do the polymorphic query, but do separate targeted queries and join them in your code?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA - abtsract class - WHERE clause