我有一个简单的数据模型,包含三个元素:Principal(用户),以及他的设置和个人资料数据。UserSetting和UserProfile都与Principal相关联,但Principal与它们都没有关联(单向)。
UserProfile ----> Principal <---- UserSettings
以下是实体列表(仅显示与问题相关的字段):
@Entity
public class Principal {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
private boolean enabled;
}
@Entity
public class UserProfile {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(fetch = FetchType.LAZY)
private Principal principal;
}
@Entity
public class UserSettings{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(fetch = FetchType.LAZY)
private Principal principal;
private boolean isHidden;
}
以下是一个可用的JPA查询,它连接表并提取所有正确的数据:
@Query( "SELECT DISTINCT(userProfile) " +
"FROM UserProfile userProfile " +
"LEFT JOIN FETCH userProfile.principal AS principal " +
"WHERE principal.enabled = :enabled " +
"AND principal.id IN ( " +
"SELECT userSettings.principal.id " +
"FROM UserSettings userSettings " +
"WHERE userSettings.isHidden = :hidden)")
有没有一种方法可以创建JPA规范来实现以下功能(请记住Principal没有对UserSettings或UserProfile的引用):
a)过滤掉未启用的Principal的用户资料
WHERE principal.enabled = :enabled
b) 过滤掉通过用户设置选择隐藏的用户资料
AND principal.id IN (
SELECT userSettings.principal.id
FROM UserSettings userSettings
WHERE userSettings.isHidden = :hidden
)