title: How to select distinct results in Spring Data
body: <p>I have a problem to build query in Spring Data using simple Spring Data query or @Query or QueryDSL.</p>
  
  <p>How to select rows that will be distinct for three columns (Study, Country, Login) and as a result of query will be the list of objects type of User?</p>
  
  <p>Table:</p>
  
  <p><code>-------------------------------------</code>
  <code>
  | User                               |
  </code>
  <code>-------------------------------------
  </code>
  <code>| Id | Study | Country | Site | Login |
   -------------------------------------</code>
  <code>
  | 1  | S1    | US      | 11   | user1 |
  </code>
  <code>| 2  | S1    | US      | 22   | user1 |</code>
  <code>
  | 3  | S1    | US      | 33   | user1 |</code>
  <code>
  | .. | ..    | ..      | ..   | ..    |
  </code>
  <code>-------------------------------------</code></p>
  
  <p>I need a query which based only on <code>Study</code> will return unique Users for each <code>Login</code> and <code>Country</code> only and without taking into account the <code>Site</code> column.</p>
  
  <p>Method signature is like below:</p>
  
  <pre><code>List&lt;User&gt; findByStudyIgnoreCase(String study);
  </code></pre>
  
  <p>and right now is returning all rows from table Users. So I have duplicated rows about user assignments in Study and Country because I have UI presentation in other table where <code>Site</code> is not needed.</p>
  
  <p>So, I need something like:</p>
  
  <pre><code>select distinct Study, Country, Login from User
  </code></pre>
  
  <p>but returning object must be the User object just like the method signature says (for example first of matching result). </p>
  
  <p>How can it be done?</p>
  
  <ol>
  <li><p>Is it possible in this Way or similar way? How to make it correct?</p>
  
  <p><code>@Query("SELECT DISTINCT s.study, s.country, s.login FROM user s where s.study = ?1 ")
  List&lt;User&gt; findByStudyIgnoreCase(String study);</code></p></li>
  <li><p>Is it possible using QueryDSL?</p></li>
  </ol>
  
  <p>---- EDIT ----</p>
  
  <p>I tried to write query via QueryDSL like TimoWestkämper suggested but I have a problem. </p>
  
  <pre><code>    public List&lt;User&gt; findByStudyIgnoreCase(String study) {
          QUser $ = QUser.user;
          BooleanExpression studyExists = $.study.equalsIgnoreCase(study);
  
          List&lt;Users&gt; usersList = from($)
              .where(studyExists)
              .distinct()
              .list(Projections.bean(User.class, $.study, $.country, $.id.login));
  
          return usersList;
      }
  </code></pre>
  
  <p>After call above query the exception occurs:</p>
  
  <pre><code>org.springframework.dao.InvalidDataAccessApiUsageException: The bean of type: com.domain.app.model.User has no property called: study; nested exception is java.lang.IllegalArgumentException: The bean of type: com.domain.app.model.User has no property called: study
  </code></pre>
  
  <p>Why it happens?</p>
  
  <p>---- EDIT 2 ----</p>
  
  <p>My <code>User</code> class:</p>
  
  <pre><code>@Entity
  @Table(name="USER")
  @Immutable
  @Builder
  @NoArgsConstructor
  @AllArgsConstructor
  @Getter @EqualsAndHashCode @ToString
  @FieldDefaults(level=AccessLevel.PRIVATE)
  public class User {
  
      @EmbeddedId
      UserId id;
  
      @Column(name="CONTACT_UNIQUE_ID")
      String contactUniqueId;
  
      String country;
  
      @Column(name="COUNTRY_CODE")
      String countryCode;
  
      @Column(name="STUDY")
      String study;
  
      String firstname;
  
      String lastname;
  
      String email;
  
      String role;
  }
  </code></pre>
  
  <p>Embeddable <code>UserId</code> class:</p>
  
  <pre><code>@Embeddable
  @Getter @EqualsAndHashCode @ToString
  @NoArgsConstructor
  @AllArgsConstructor
  @FieldDefaults(level=AccessLevel.PRIVATE)
  public class UserId implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      String site;
      String login;
  
  }
  </code></pre>
  
  <p>Generated QUser class:</p>
  
  <pre><code>@Generated("com.mysema.query.codegen.EntitySerializer")
  public class QUser extends EntityPathBase&lt;User&gt; {
  
      private static final long serialVersionUID = 1646288729;
  
      private static final PathInits INITS = PathInits.DIRECT;
  
      public static final QUser user = new User("user");
  
      public final StringPath contactUniqueId = createString("contactUniqueId");
  
      public final StringPath country = createString("country");
  
      public final StringPath countryCode = createString("countryCode");
  
      public final StringPath study = createString("study");
  
      public final StringPath email = createString("email");
  
      public final StringPath firstname = createString("firstname");
  
      public final QUser id;
  
      public final StringPath lastname = createString("lastname");
  
      public final StringPath role = createString("role");
  
      public QUser(String variable) {
          this(User.class, forVariable(variable), INITS);
      }
  
      @SuppressWarnings("all")
      public QUser(Path&lt;? extends User&gt; path) {
          this((Class)path.getType(), path.getMetadata(), path.getMetadata().isRoot() ? INITS : PathInits.DEFAULT);
      }
  
      public QUser(PathMetadata&lt;?&gt; metadata) {
          this(metadata, metadata.isRoot() ? INITS : PathInits.DEFAULT);
      }
  
      public QUser(PathMetadata&lt;?&gt; metadata, PathInits inits) {
          this(User.class, metadata, inits);
      }
  
      public QUser(Class&lt;? extends User&gt; type, PathMetadata&lt;?&gt; metadata, PathInits inits) {
          super(type, metadata, inits);
          this.id = inits.isInitialized("id") ? new QUser(forProperty("id")) : null;
      }
  
  }
  </code></pre>