Hibernate Union alternatives

Question

What alternatives do I have to implement a union query using hibernate  I know hibernate does not support union queries at the moment  right now the only way I see to make a union is to use a view table   The other option is to use plain jdbc  but this way I would loose all my example criteria queries goodies  as well as the hibernate mapping validation that hibernate performs against the tables columns

User · Accepted Answer

Use VIEW  The same classes can be mapped to different tables views using entity name  so you won t even have much of a duplication  Being there  done that  works OK   Plain JDBC has another hidden problem  it s unaware of Hibernate session cache  so if something got cached till the end of the transaction and not flushed from Hibernate session  JDBC query won t find it  Could be very puzzling sometimes

User · Answer

Perhaps I had a more straight-forward problem to solve  My  for instance  was in JPA with Hibernate as the JPA provider   I split the three selects  two in a second case  into multiple select and combined the collections returned myself  effectively replacing a  union all

User · Answer

I have a solution  for one critical scenario  for which I struggled a lot  with union in HQL    e g  Instead of not  working  -  select i   j from A a     select i   j from B union select i   j from C  d where a i   d i    OR  select i   j from A a  JOIN  select i   j from B union select i   j from C  d on a i   d i    YOU could do in Hibernate HQL -   Query q1  session createQuery select i   j from A a JOIN B b on a i   b i  List l1   q1 list     Query q2   session createQuery select i   j from A a JOIN C b on a i   b i  List l2   q2 list      then u can add both list -   l1 addAll l2

User · Answer

As Patrick said  appending the LISTs from each SELECT would be a good idea but remember that it acts like UNION ALL  To avoid this side effect  just control if the object is already added in final collection or not  If no  then add it  Something else that you should care about is that if you have any JOIN in each SELECT  the result would be a list of object array List lt Objetc   gt   so you have to iterate over it to only keep the object that you need   Hope it works

User · Answer

I have to agree with Vladimir   I too looked into using UNION in HQL and couldn t find a way around it   The odd thing was that I could find  in the Hibernate FAQ  that UNION is unsupported  bug reports pertaining to UNION marked  fixed   newsgroups of people saying that the statements would be truncated at UNION  and other newsgroups of people reporting it works fine    After a day of mucking with it  I ended up porting my HQL back to plain SQL  but doing it in a View in the database would be a good option  In my case  parts of the query were dynamically generated  so I had to build the SQL in the code instead

User · Answer

I have a solution  for one critical scenario  for which I struggled a lot  with union in HQL    e g  Instead of not  working  -  select i   j from A a     select i   j from B union select i   j from C  d where a i   d i    OR  select i   j from A a  JOIN  select i   j from B union select i   j from C  d on a i   d i    YOU could do in Hibernate HQL -   Query q1  session createQuery select i   j from A a JOIN B b on a i   b i  List l1   q1 list     Query q2   session createQuery select i   j from A a JOIN C b on a i   b i  List l2   q2 list      then u can add both list -   l1 addAll l2

User · Answer

I have to agree with Vladimir   I too looked into using UNION in HQL and couldn t find a way around it   The odd thing was that I could find  in the Hibernate FAQ  that UNION is unsupported  bug reports pertaining to UNION marked  fixed   newsgroups of people saying that the statements would be truncated at UNION  and other newsgroups of people reporting it works fine    After a day of mucking with it  I ended up porting my HQL back to plain SQL  but doing it in a View in the database would be a good option  In my case  parts of the query were dynamically generated  so I had to build the SQL in the code instead

User · Answer

I too have been through this pain - if the query is dynamically generated  e g  Hibernate Criteria  then I couldn t find a practical way to do it    The good news for me was that I was only investigating union to solve a performance problem when using an  or  in an Oracle database    The solution Patrick posted  combining the results programmatically using a set  while ugly  especially since I wanted to do results paging as well  was adequate for me

User · Answer

I too have been through this pain - if the query is dynamically generated  e g  Hibernate Criteria  then I couldn t find a practical way to do it    The good news for me was that I was only investigating union to solve a performance problem when using an  or  in an Oracle database    The solution Patrick posted  combining the results programmatically using a set  while ugly  especially since I wanted to do results paging as well  was adequate for me

User · Answer

A view is a better approach but since hql typically returns a List or Set    you can do list 1 addAll list 2    Totally sucks compared to a union but should work

User · Answer

Perhaps I had a more straight-forward problem to solve  My  for instance  was in JPA with Hibernate as the JPA provider   I split the three selects  two in a second case  into multiple select and combined the collections returned myself  effectively replacing a  union all

User · Answer

A view is a better approach but since hql typically returns a List or Set    you can do list 1 addAll list 2    Totally sucks compared to a union but should work

User · Answer

I too have been through this pain - if the query is dynamically generated  e g  Hibernate Criteria  then I couldn t find a practical way to do it    The good news for me was that I was only investigating union to solve a performance problem when using an  or  in an Oracle database    The solution Patrick posted  combining the results programmatically using a set  while ugly  especially since I wanted to do results paging as well  was adequate for me

User · Answer

Here is a special case  but might inspire you to create your own work around    The goal here is to count the total number of records from two different tables where records meet a particular criteria   I believe this technique will work for any case where you need to aggregate data from across multiple tables sources      I have some special intermediate classes setup  so the code which calls the named query is short and sweet  but you can use whatever method you normally use in conjunction with named queries to execute your query    QueryParms parms new QueryParms    parms put  PROCDATE  PROCDATE    Long pixelAll     SourceCount Fetch row  PIXEL ALL  parms logger   getCOUNT      As you can see here  the named query begins to look an aweful lot like a union statement    Entity  NamedQueries            NamedQuery              name    PIXEL ALL               query                               SELECT new SourceCount                                select count a  from PIXEL LOG CURR1 a                                 where to char a TIMESTAMP   YYYYMMDD      PROCDATE                                                                select count b  from PIXEL LOG CURR2 b                                where to char b TIMESTAMP   YYYYMMDD      PROCDATE                                                           from Dual1                                     public class SourceCount        Id     private Long   COUNT       public SourceCount Long COUNT1  Long COUNT2            this COUNT   COUNT1 COUNT2             public Long getCOUNT             return COUNT             public void setCOUNT Long COUNT            this COUNT   COUNT            Part of the magic here is to create a dummy table and insert one record into it   In my case  I named it dual1 because my database is Oracle  but I don t think it matters what you call the dummy table    Entity  Table name  DUAL1   public class Dual1        Id     Long ID      Don t forget to insert your dummy record   SQL gt  insert into dual1 values  1

User · Answer

I have to agree with Vladimir   I too looked into using UNION in HQL and couldn t find a way around it   The odd thing was that I could find  in the Hibernate FAQ  that UNION is unsupported  bug reports pertaining to UNION marked  fixed   newsgroups of people saying that the statements would be truncated at UNION  and other newsgroups of people reporting it works fine    After a day of mucking with it  I ended up porting my HQL back to plain SQL  but doing it in a View in the database would be a good option  In my case  parts of the query were dynamically generated  so I had to build the SQL in the code instead

User · Answer

You could use id in  select id from      or id in  select id from       e g  instead of non-working  from Person p where p name  Joe  union from Person p join p children c where c name  Joe    you could do  from Person p    where p id in  select p1 id from Person p1 where p1 name  Joe        or p id in  select p2 id from Person p2 join p2 children c where c name  Joe      At least using MySQL  you will run into performance problems with it later  though  It s sometimes easier to do a poor man s join on two queries instead      use set for uniqueness Set lt Person gt  people   new HashSet lt Person gt   List lt Person gt   query1 list     people addAll  List lt Person gt   query2 list     return new ArrayList lt Person gt  people     It s often better to do two simple queries than one complex one   EDIT   to give an example  here is the EXPLAIN output of the resulting MySQL query from the subselect solution   mysql gt  explain    select p   from PERSON p      where p id in  select p1 id from PERSON p1 where p1 name    Joe          or p id in  select p2 id from PERSON p2          join CHILDREN c on p2 id   c parent where c name  Joe    G                             1  row                                        id  1   select type  PRIMARY         table  a          type  ALL possible keys  NULL           key  NULL       key len  NULL           ref  NULL          rows  247554         Extra  Using where                             2  row                                        id  3   select type  DEPENDENT SUBQUERY         table  NULL          type  NULL possible keys  NULL           key  NULL       key len  NULL           ref  NULL          rows  NULL         Extra  Impossible WHERE noticed after reading const tables                             3  row                                        id  2   select type  DEPENDENT SUBQUERY         table  a1          type  unique subquery possible keys  PRIMARY name sortname           key  PRIMARY       key len  4           ref  func          rows  1         Extra  Using where 3 rows in set  0 00 sec    Most importantly  1  row doesn t use any index and considers 200k  rows  Bad  Execution of this query took 0 7s wheres both subqueries are in the milliseconds

User · Answer

I too have been through this pain - if the query is dynamically generated  e g  Hibernate Criteria  then I couldn t find a practical way to do it    The good news for me was that I was only investigating union to solve a performance problem when using an  or  in an Oracle database    The solution Patrick posted  combining the results programmatically using a set  while ugly  especially since I wanted to do results paging as well  was adequate for me

User · Answer

You could use id in  select id from      or id in  select id from       e g  instead of non-working  from Person p where p name  Joe  union from Person p join p children c where c name  Joe    you could do  from Person p    where p id in  select p1 id from Person p1 where p1 name  Joe        or p id in  select p2 id from Person p2 join p2 children c where c name  Joe      At least using MySQL  you will run into performance problems with it later  though  It s sometimes easier to do a poor man s join on two queries instead      use set for uniqueness Set lt Person gt  people   new HashSet lt Person gt   List lt Person gt   query1 list     people addAll  List lt Person gt   query2 list     return new ArrayList lt Person gt  people     It s often better to do two simple queries than one complex one   EDIT   to give an example  here is the EXPLAIN output of the resulting MySQL query from the subselect solution   mysql gt  explain    select p   from PERSON p      where p id in  select p1 id from PERSON p1 where p1 name    Joe          or p id in  select p2 id from PERSON p2          join CHILDREN c on p2 id   c parent where c name  Joe    G                             1  row                                        id  1   select type  PRIMARY         table  a          type  ALL possible keys  NULL           key  NULL       key len  NULL           ref  NULL          rows  247554         Extra  Using where                             2  row                                        id  3   select type  DEPENDENT SUBQUERY         table  NULL          type  NULL possible keys  NULL           key  NULL       key len  NULL           ref  NULL          rows  NULL         Extra  Impossible WHERE noticed after reading const tables                             3  row                                        id  2   select type  DEPENDENT SUBQUERY         table  a1          type  unique subquery possible keys  PRIMARY name sortname           key  PRIMARY       key len  4           ref  func          rows  1         Extra  Using where 3 rows in set  0 00 sec    Most importantly  1  row doesn t use any index and considers 200k  rows  Bad  Execution of this query took 0 7s wheres both subqueries are in the milliseconds

User · Answer

As Patrick said  appending the LISTs from each SELECT would be a good idea but remember that it acts like UNION ALL  To avoid this side effect  just control if the object is already added in final collection or not  If no  then add it  Something else that you should care about is that if you have any JOIN in each SELECT  the result would be a list of object array List lt Objetc   gt   so you have to iterate over it to only keep the object that you need   Hope it works

User · Answer

A view is a better approach but since hql typically returns a List or Set    you can do list 1 addAll list 2    Totally sucks compared to a union but should work

User · Answer

I have to agree with Vladimir   I too looked into using UNION in HQL and couldn t find a way around it   The odd thing was that I could find  in the Hibernate FAQ  that UNION is unsupported  bug reports pertaining to UNION marked  fixed   newsgroups of people saying that the statements would be truncated at UNION  and other newsgroups of people reporting it works fine    After a day of mucking with it  I ended up porting my HQL back to plain SQL  but doing it in a View in the database would be a good option  In my case  parts of the query were dynamically generated  so I had to build the SQL in the code instead

User · Answer

Here is a special case  but might inspire you to create your own work around    The goal here is to count the total number of records from two different tables where records meet a particular criteria   I believe this technique will work for any case where you need to aggregate data from across multiple tables sources      I have some special intermediate classes setup  so the code which calls the named query is short and sweet  but you can use whatever method you normally use in conjunction with named queries to execute your query    QueryParms parms new QueryParms    parms put  PROCDATE  PROCDATE    Long pixelAll     SourceCount Fetch row  PIXEL ALL  parms logger   getCOUNT      As you can see here  the named query begins to look an aweful lot like a union statement    Entity  NamedQueries            NamedQuery              name    PIXEL ALL               query                               SELECT new SourceCount                                select count a  from PIXEL LOG CURR1 a                                 where to char a TIMESTAMP   YYYYMMDD      PROCDATE                                                                select count b  from PIXEL LOG CURR2 b                                where to char b TIMESTAMP   YYYYMMDD      PROCDATE                                                           from Dual1                                     public class SourceCount        Id     private Long   COUNT       public SourceCount Long COUNT1  Long COUNT2            this COUNT   COUNT1 COUNT2             public Long getCOUNT             return COUNT             public void setCOUNT Long COUNT            this COUNT   COUNT            Part of the magic here is to create a dummy table and insert one record into it   In my case  I named it dual1 because my database is Oracle  but I don t think it matters what you call the dummy table    Entity  Table name  DUAL1   public class Dual1        Id     Long ID      Don t forget to insert your dummy record   SQL gt  insert into dual1 values  1

User · Answer

A view is a better approach but since hql typically returns a List or Set    you can do list 1 addAll list 2    Totally sucks compared to a union but should work

[java] Hibernate Union alternatives

Examples related to java

Examples related to sql

Examples related to hibernate

Examples related to union