我在使用简单的 Spring Data 查询或 @Query 或 QueryDSL 在 Spring Data 中构建查询时遇到问题。
如何选择三列(研究、国家、登录)不同的行,并且查询结果将是用户对象类型的列表?
Table:
-------------------------------------
| User |
-------------------------------------
| Id | Study | Country | Site | Login |
-------------------------------------
| 1 | S1 | US | 11 | user1 |
| 2 | S1 | US | 22 | user1 |
| 3 | S1 | US | 33 | user1 |
| .. | .. | .. | .. | .. |
-------------------------------------
我需要一个仅基于的查询Study
将为每个返回唯一的用户Login
and Country
仅且不考虑Site
column.
方法签名如下:
List<User> findByStudyIgnoreCase(String study);
现在正在返回用户表中的所有行。因此,我在“学习”和“国家/地区”中存在有关用户分配的重复行,因为我在其他表中有 UI 演示,其中Site
不需要。
所以,我需要类似的东西:
select distinct Study, Country, Login from User
但返回对象必须是 User 对象,就像方法签名所说的那样(例如匹配结果的第一个)。
如何做呢?
-
以这种方式或类似的方式可能吗?怎样才能使其正确呢?
@Query("SELECT DISTINCT s.study, s.country, s.login FROM user s where s.study = ?1 ")
List<User> findByStudyIgnoreCase(String study);
可以使用 QueryDSL 吗?
- - 编辑 - -
我尝试像 TimoWestkämper 建议的那样通过 QueryDSL 编写查询,但遇到问题。
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;
}
调用上述查询后,出现异常:
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
为什么会发生这种情况?
---- 编辑 2 ----
My User
class:
@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;
}
可嵌入UserId
class:
@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;
}
生成的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;
}
}