JPA – correct join in jpql

I have the following JPA entities:

@Entity
class UserClient{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;
}

@Entity
class UserAccess{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @ManyToOne(optional = false,cascade = { CascadeType.REFRESH })
    private UserClient user;

    @Temporal(TemporalType.TIMESTAMP)
    private Date accessTs;
}

Now I want to run a jpql query to get the list of users who last accessed the date Unfortunately, the following query will not return users who have never visited the system, that is, they exist in the userclient table, but there are no records in the useraccess table

SELECT ua.user,MAX(ua.accessTs) FROM UserAccess ua RIGHT JOIN ua.user

Did I miss anything? Do you use right join correctly?

I am using the latest hibernate JPA Version (4.0.0.cr1)

Solution

You should make the userclient table the owner of the relationship (which makes IMO more logical) Then you can use left join instead of right join

SELECT uc,MAX(ua.accessTs) FROM UserClient uc LEFT JOIN uc.userAccess ua

Here’s why left join on UserAccess works：