Java 如何在 Spring Data 中选择不同的结果

How to select distinct results in Spring Data

提问by Roman

I have a problem to build query in Spring Data using simple Spring Data query or @Query or QueryDSL.

我在使用简单的 Spring Data 查询或 @Query 或 QueryDSL 在 Spring Data 中构建查询时遇到问题。

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?

如何选择三列（Study、Country、Login）不同的行，并且查询的结果将是用户类型的对象列表？

Table:

桌子：

-------------------------------------| User | ------------------------------------- | Id | Study | Country | Site | Login | -------------------------------------| 1 | S1 | US | 11 | user1 | | 2 | S1 | US | 22 | user1 || 3 | S1 | US | 33 | user1 || .. | .. | .. | .. | .. | -------------------------------------

-------------------------------------| User | ------------------------------------- | Id | Study | Country | Site | Login | -------------------------------------| 1 | S1 | US | 11 | user1 | | 2 | S1 | US | 22 | user1 || 3 | S1 | US | 33 | user1 || .. | .. | .. | .. | .. | -------------------------------------

I need a query which based only on Studywill return unique Users for each Loginand Countryonly and without taking into account the Sitecolumn.

我需要它仅基于查询Study将返回各自独特的用户Login和Country只，没有考虑到Site列。

Method signature is like below:

方法签名如下：

List<User> findByStudyIgnoreCase(String study);

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 Siteis not needed.

现在正在返回表用户中的所有行。因此，我在 Study 和 Country 中复制了有关用户分配的行，因为我在其他Site不需要的表中有 UI 演示。

So, I need something like:

所以，我需要类似的东西：

select distinct Study, Country, Login from User

but returning object must be the User object just like the method signature says (for example first of matching result).

但是返回的对象必须是 User 对象，就像方法签名所说的一样（例如匹配结果的第一个）。

How can it be done?

怎么做到呢？

1. Is it possible in this Way or similar way? How to make it correct?

   @Query("SELECT DISTINCT s.study, s.country, s.login FROM user s where s.study = ?1 ") List<User> findByStudyIgnoreCase(String study);

2. Is it possible using QueryDSL?

1. 以这种方式或类似方式可能吗？如何使它正确？

   @Query("SELECT DISTINCT s.study, s.country, s.login FROM user s where s.study = ?1 ") List<User> findByStudyIgnoreCase(String study);

2. 是否可以使用 QueryDSL？

---- EDIT ----

- - 编辑 - -

I tried to write query via QueryDSL like TimoWestk?mper suggested but I have a problem.

我尝试通过 QueryDSL 编写查询，如 TimoWestk?mper 建议，但我遇到了问题。

    public List<User> findByStudyIgnoreCase(String study) {
        QUser $ = QUser.user;
        BooleanExpression studyExists = $.study.equalsIgnoreCase(study);

        List<Users> usersList = from($)
            .where(studyExists)
            .distinct()
            .list(Projections.bean(User.class, $.study, $.country, $.id.login));

        return usersList;
    }

After call above query the exception occurs:

调用上述查询后，发生异常：

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

Why it happens?

为什么会发生？

---- EDIT 2 ----

---- 编辑 2 ----

My Userclass:

我的User班级：

@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;
}

Embeddable UserIdclass:

可嵌入UserId类：

@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;

}

Generated QUser class:

生成的 QUser 类：

@Generated("com.mysema.query.codegen.EntitySerializer")
public class QUser extends EntityPathBase<User> {

    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<? extends User> path) {
        this((Class)path.getType(), path.getMetadata(), path.getMetadata().isRoot() ? INITS : PathInits.DEFAULT);
    }

    public QUser(PathMetadata<?> metadata) {
        this(metadata, metadata.isRoot() ? INITS : PathInits.DEFAULT);
    }

    public QUser(PathMetadata<?> metadata, PathInits inits) {
        this(User.class, metadata, inits);
    }

    public QUser(Class<? extends User> type, PathMetadata<?> metadata, PathInits inits) {
        super(type, metadata, inits);
        this.id = inits.isInitialized("id") ? new QUser(forProperty("id")) : null;
    }

}

回答by Timo Westk?mper

I can answer the Querydsl part. It works via

我可以回答 Querydsl 部分。它通过

List<User> users = query.from(user)
  .where(user.study.eq(arg))
  .distinct()
  .list(Projections.fields(User.class, user.study, user.country, user.login));

You will get User instances with populated study, country and login fields out. The User instances are not managed JPA entities, but populated beans.

您将获得包含研究、国家和登录字段的用户实例。User 实例不是托管的 JPA 实体，而是填充的 bean。

Alternatively you can query for Tuple instances like this

或者，您可以像这样查询 Tuple 实例

List<Tuple> tuples = query.from(user)
  .where(user.study.eq(arg))
  .distinct()
  .list(user.study, user.country, user.login);

But as you are using Spring Data you might want to return Users instead.

但是当您使用 Spring Data 时，您可能希望改为返回用户。

回答by Roman

I resolved the problem with call general query for all rows and then narrowing results in other method. It is not prefer solution for me but I want to show you my need. Maybe then you will find better solution with only query. Is it even possible to include this in only one query?

我解决了对所有行调用通用查询然后用其他方法缩小结果的问题。这对我来说不是首选解决方案，但我想向您展示我的需求。也许那时你会找到更好的解决方案，只需查询。甚至可以仅将其包含在一个查询中吗？

public List<User> findDistinctUsersByStudyNumIgnoreCase(String study) {
    QUser $ = QUser.user;
    BooleanExpression studyExists = $.study.equalsIgnoreCase(study);

    List<User> usersList = from($)
        .where(study)
        .listDistinct($);

    // narrowing results (this what I do not know how to use in QueryDsl query)
    usersList = removeDuplicates(usersList); 

    return usersList;
}

/**
 * <p>Remove duplicated user assignments for presentation User table purpose only</p>
 * <p><b>NOTE:</b> Duplicated assignment is when more than one entry in USER 
 * has the same <i>study</i>, <i>country</i>, <i>login</i> and differ with <i>site</i> only. 
 * For presentation User Assignments table purpose the <i>site</i> context is not needed, so we need
 * only info about user existence in <i>study</i> and <i>country</i> context only.</p>
 * @param usersList user assignments list from USER with study, country and site context (with duplicates)
 * @return distinct user assignments list narrowed to study and country context only and without site context (without duplicates)
 */
private List<User> removeDuplicates(List<User> usersList) {
    List<User> result = new ArrayList<User>();

    MultiKeyMap studyCountryLoginMap = new MultiKeyMap();
    for (User u : usersList) {
        if (!studyCountryLoginMap.containsKey(u.getStudy(), u.getCountry(), u.getId().getLogin())) {
            studyCountryLoginMap.put(u.getStudy(), u.getCountry(), u.getId().getLogin(), u);
        }
    }

    result.addAll(studyCountryLoginMap.values());
    return result;
}