CODEWITHSUNDEEP
javaoracle11gjpa-2.0distinct
nimrod
nimrod

Reputation: 5732

Having issues with distinct in JPA

Using JPA, I want to select all Log objects for a specific actiontype. From the log object I want to get the user (log.getUser()), but the users appear several times in the result list. I tried it with distinct, but it did not work, I guess because I was not able to define, what exactly has to be distinct. Here is my JPA query:

SELECT DISTINCT log 
  FROM Log AS log JOIN log.action AS action 
              JOIN log.user AS user 
 WHERE action.actionType = :actionType

If I say SELECT DISTINCT user, then I don't have the whole log object in the end.

Any help or hint would be appreciated.

Edit: Part of my Log Class:

public class Log {

    private int logId;
    private Calendar logDate;
    private User user;
    private Action action;
    private String description;
    ....
}

Upvotes: 1

Views: 1539

Answers (1)

Francisco Spaeth
Francisco Spaeth

Reputation: 23903

Two Queries Solution

Since you want Log objects and distinct User objects you could do two queries, first one to retrieve the Log objects and second one to retrieve distict User objects.

// first one to select Log objects
String logQuery = "SELECT l FROM Log l WHERE l.actionType = :actionType";
...
List<Log> logs = logJpaQuery.getResultList();

// second one to select distinct users from this objects
String usersQuery = "SELECT distinct l.user FROM Log l where l.logId in (:logIds)";
...
userJpaQuery.setParameter("logIds", logs);
List<User> users = userJpaQuery.getResultList();

With this approach you have the distinct users for the select Logs objects.

Upvotes: 2

Related Questions