如何在java中使用querydsl从jsonb列中搜索指定对象

Question

在我的postgresql中，我有一个表，其中有一个jsonb类型的列。这是那个表的java类:

@Table(schema = "public", name = "vw_user_site_role_permission")
@Data
@Entity
@TypeDef(
name = "json",
typeClass = JsonType.class
)
public class ViewUserSiteRolePermission extends AuditableEntity{
@Column(name = "user_site_id")
private Long userSiteId;
@Column(name = "user_role_id")
private Long userRoleId;
@Column(name = "user_id")
private Long userId;
@Column(name = "first_name")
private String firstName;
@Column(name = "last_name")
private String lastName;
@Column(name = "site_id")
private Long siteId;
@Column(name = "site_name")
private String siteName;
@Column(name = "code")
private String code;
@Column(name = "role_id")
private Long roleId;
@Column(name = "role_name")
private String roleName;
@Column(name = "permission_group_id")
private Long permissionGroupId;
@Column(name = "title")
private String title;
@Type(type = "json")
@Column(name = "permission", columnDefinition = "jsonb")
private JsonNode permission;
}

现在一切工作良好的其他对象，但我必须实现搜索字段，这是存在于jsonb对象。

我在我的存储库中使用querydsl，

@Repository
public interface ViewUserSiteRolePermissionRepository extends 
JpaRepository<ViewUserSiteRolePermission, Long>, 
QuerydslPredicateExecutor<ViewUserSiteRolePermission> {
}

这是我在其他对象上实现过滤和搜索的方法:

@Override
public Page<ViewUserSiteRolePermission> getALLViewUserSiteRolePermission(List<Long> userIds, 
List<Long> userRoleIds, List<Long> siteIds, Long roleId, String search,String permissions, 
Pageable pageable) {
BooleanBuilder filter = new BooleanBuilder();
if (!ObjectUtils.isEmpty(userIds)) {
filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.userId.in(userIds));
}
if (!ObjectUtils.isEmpty(userRoleIds)) {         
filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.userRoleId.in(userRoleIds));
}
if (!ObjectUtils.isEmpty(siteIds)) {
filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.siteId.in(siteIds));
}
if (!ObjectUtils.isEmpty(roleId)) {
filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.roleId.eq(roleId));
}
if (StringUtils.isNotBlank(search)) {
BooleanBuilder booleanBuilder = new BooleanBuilder();
Arrays.asList(search.split(" ")).forEach(nm ->
booleanBuilder.or(QViewUserSiteRolePermission.viewUserSiteRolePermission.siteName.containsIgnoreCase(nm))
);
filter.and(booleanBuilder);
}
return viewUserSiteRolePermissionRepository.findAll(filter, pageable);
}

现在如何使用这种方法在jsonb对象上应用过滤器?

Answer 1

不幸的是，querydsl不能访问jsonb对象中的字段，只能访问整个对象。但还是有办法的。您可以在存储库中编写本机查询并以这种方式访问它。让我们假设您的权限对象中的字段名为'permissionTitle'。

回购:

import org.springframework.data.jpa.repository.Query;
@Repository public interface ViewUserSiteRolePermissionRepository extends  JpaRepository<ViewUserSiteRolePermission, Long>, QuerydslPredicateExecutor<ViewUserSiteRolePermission> {
@Query(
value =
"SELECT * "
+ "FROM view_user_site_role_permission "
+ "WHERE CAST(permission ->> 'permissionTitle' AS VARCHAR) = ?1",
nativeQuery = true)
List<ViewUserSiteRolePermission> findByPermissionTitle(final String permissionTitle);
}

现在在你的getALLViewUserSiteRolePermission函数中你可以这样做:

if (StringUtils.isNotBlank(permissionTitle)) {
final List<ViewUserSiteRolePermission> found = this.viewUserSiteRolePermissionRepository.findByPermissionTitle(permissionTitle);
filter.and(QViewUserSiteRolePermission.viewUserSiteRolePermission.in(found);
}