how to write inner join a selected table query in hibernate.ejb-CodePudding

I have a query working fine:

StringBuilder query = new StringBuilder(
        "select o.deviceName, o.deviceOs, o.loginOn, e.username, e.name, e.idNo from LoginHistory o, User e ");
    query.append(" where o.userId = e.userId");
Query q = getEm().createQuery(query.toString());

This createQuery() will go to createQuery() in class org.hibernate.ejb.AbstractEntityManagerImpl.

I want to edit the query to get the last login for each user. The following SQL query can run in a db2 command successfully:

select m1.*, m2.*
from tibs.LoginHistory m1 inner join (
    select userId, max(loginOn) as loginOn from tibs.LoginHistory group by userId
    ) m2
on m1.userId = m2.userId and m1.loginOn = m2.loginOn;

But when I try to apply this in my code above, it will hit QuerySyntaxException: unexpected token: at ( after inner join.

Code is something like:

StringBuilder query = new StringBuilder(
        "select o.deviceName, o.deviceOs, o.loginOn, e.username, e.name, e.cif, e.idNo from LoginHistory o, ECUser e ");
    query.append("inner join (select o2.userId, o2.max(loginOn) as loginOn from LoginHistory group by userId) o2 ");
    query.append("on o.userId = o2.userId and o.loginOn = o2.loginOn");
    query.append(" where o.userId = e.userId");

Is this workable in this way? If yes, what syntax should I use?

Or does Hibernate not accept this, and I need to do it in another way?

Add on** Even I change my query to this, still the same:

StringBuilder query = new StringBuilder(
        "select o.deviceName, o.deviceOs, o.loginOn, e.username, e.name, e.cif, e.idNo from LoginHistory o, ECUser e ");
    query.append("inner join (select o2.userId, o2.max(loginOn) as loginOn from LoginHistory o2 group by userId) ");
    query.append("on o.userId = o2.userId and o.loginOn = o2.loginOn");
    query.append(" where o.userId = e.userId");

CodePudding user response：

I think the inner query should be as follows (note position of o2):

(select o2.userId, o2.max(loginOn) as loginOn from LoginHistory o2 group by userId)

Please name variables appropriately to facilitate comprehension.
It would be helpful if o2 was renamed to lh, since it represents LoginHistory entity.

(select lh.userId, lh.max(loginOn) as loginOn from LoginHistory lh group by userId)

CodePudding user response：

You can use the following query:

select o.deviceName, o.deviceOs, o.loginOn, e.username, e.name, e.idNo
from LoginHistory o, User e 
where o.id = (select max(lh.id) from LoginHistory lh where lh.userId = e.userId)