Search code examples
javaspringhibernatehql

HQL merge two typedqueries

I have written two hibernate queries:

  1. TypedQuery q = em.createQuery("SELECT user.id FROM TableOne AS user WHERE ...", Long.class);
  2. TypedQuery q = em.createQuery("SELECT link.user_id FROM TableTwo AS link WHERE ...", Long.class);

Now, how do I merge these two queries? My return type has to be TypedQuery

Solution

  • Completely copying answer from https://stackoverflow.com/a/3940445/929701:

    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 the later though. It's sometimes easier to do a poor man's join on two queries instead:

    // use set for uniqueness
Set<Person> people = new HashSet<Person>((List<Person>) query1.list());
people.addAll((List<Person>) query2.list());
return new ArrayList<Person>(people);

    It's often better to do two simple queries than one complex one.