需要查找特定角色的所有用户JPA Criteria API [英] Need to find all Users for a specific role JPA Criteria API

问题描述

我正在实现一个具有以下实体的用户管理系统：

I am implementing a user management system which has the following entities :

public class UserEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    Long id;

    @Column(unique = true, name = "EMAIL")
    private String email;
    @Column(name = "NAME")
    private String name;
    @Column(name = "PASSWORD")
    private String password;
    @Column(name = "MOBILE")
    private String mobile;
    @Column(name = "OWNER_ID")
    private String ownerId;
    @Column(name = "TRAINER_ID")
    private String trainerId;
    @Column(name = "ADDED_ON")
    private Timestamp addedOn;
    @Column(name = "MODIFIED_ON")
    private Timestamp modifiedOn;
    @Column(name = "ADDED_BY")
    private String addedBy;
    @Column(name = "MODIFIED_BY")
    private String modifiedBy;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "USER_ROLES", joinColumns = @JoinColumn(name = "USER_ID", referencedColumnName = "ID"),
            inverseJoinColumns =
            @JoinColumn(name =
                    "ROLE_ID", referencedColumnName = "ROLE_ID"))
    List<RoleEntity> roles;

    @OneToOne(
            mappedBy = "user",
            cascade = CascadeType.ALL,
            orphanRemoval = true,
            fetch = FetchType.LAZY
    )
    private UserStatisticsEntity userStatisticsEntity;

这里是RoleClass：

here is the RoleClass :

public class RoleEntity implements GrantedAuthority {

    @Id
    @Column(name="ROLE_ID")
    private String roleId;

    @Column(name="ROLE_NAME")
    private String roleName;

    @ManyToMany(mappedBy = "roles")
    private List<UserEntity> users;

    @Override
    public String getAuthority() {
        return this.roleId;
    }
}

我想获取属于特定用户的所有用户角色，还可以添加动态的where子句，包括名称，手机，电子邮件以及分页等。

I would like to fetch all users belonging to a particular role and also be able to add dynamic where clauses on name, mobile, email along with paging etc.

我当前的代码如下所示，以动态方式获取用户的选定字段where子句和分页：

My current code looks like this to fetch selected fields of Users with dynamic where clauses and pagination :

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        CriteriaQuery<TrainerDTO> criteriaQuery = criteriaBuilder.createQuery(TrainerDTO.class);
        Root<UserEntity> main = criteriaQuery.from(UserEntity.class);
        criteriaQuery.multiselect(main.get("id"), main.get("name"), main.get("email"), main.get("ownerId"), main.get(
                "mobile"),
                main.get("addedBy"), main.get("modifiedBy"), main.get("addedOn"), main.get("modifiedOn"))
                     .orderBy(criteriaBuilder.desc(main.get("addedOn")))
                     .distinct(true);
        List<Predicate> predicates = new ArrayList<>();
        if (StringUtils.isNotBlank(queryParams.get("mobile"))) {
        predicates.add(criteriaBuilder.and(criteriaBuilder.equal(main.get("mobile"), queryParams.get("mobile"))));
    }
    if (StringUtils.isNotBlank(queryParams.get("name"))) {
        predicates.add(criteriaBuilder.and(criteriaBuilder.like(main.get("name"),
                "%" + queryParams.get("name") + "%")));
    }
    if (StringUtils.isNotBlank(queryParams.get("email"))) {
        predicates.add(criteriaBuilder.and(criteriaBuilder.equal(main.get("email"), queryParams.get("email"))));
    }
        criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
        log.info("TrainerDAO::getAllTrainersPaginatedForOwner Query created...");

        TypedQuery<TrainerDTO> query = entityManager.createQuery(criteriaQuery);
        query.setFirstResult(pageNumber - 1);
        query.setMaxResults(pageSize);
        return query.getResultList();

我在这里遇到两个问题：

I am having two issues here :

1. 如何获取具有特定角色的所有用户？假设我需要查找所有具有ROLE_ID = ROLE_ADMIN角色的用户。


2. 在我的分页实现中，下一页重复最后一项。假设User1是页面1的最后一项，他也将是页面2的第一项。

请提出进一步的建议。所有帮助将不胜感激。

Please suggest on how to proceed further. All help would be appreciated.

推荐答案

这是我的方式：

问题1：

您需要到达RoleEntity来检查role_id是否等于 ROLE_ADMIN，因此您需要先从RoleEntity获取角色并在那里获取所有信息。

You need to reach RoleEntity to check if the role_id is equal to "ROLE_ADMIN", so you need to fetch roles from RoleEntity first and get all the information there.

创建主对象后：

Fetch<UserEntity, RoleEntity> fetchedRoles = main.fetch("roles", JoinType.LEFT);

1. 您将把条件附加到谓词列表；

1. You will append your condition to your predicates list;

predicates.add(criteriaBuilder.equal( fetchedRoles.get( "roleId" ), "ROLE_ADMIN"));

问题2：

在这种情况下，我将尽我所能帮助您解决问题。

I will try to share what I would do in this case to help you solve the issue.

假设您在此处创建查询，此方法使用可分页对象，您想返回Page

Let's say you create the query here, in this method with pageable object, you want to return Page

private Page<Books> getUsersWithAdminRole(String... parameters, Pageable pageable){
  //...

  List<UserEntity> result = entityManager.createQuery(criteria).setFirstResult((int) pageable.getOffset()).setMaxResults(pageable.getPageSize()).getResultList();

  CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
  Root<UserEntity> userCount = countQuery.from(UserEntity.class);
  countQuery.select(criteriaBuilder.count(userCount)).where(criteriaBuilder.and(predicates.toArray(newPredicate[predicates.size()])));
  Long count = entityManager.createQuery(countQuery).getSingleResult();
  Page<UserEntity> userPage = new PageImpl<>(result, pageable, count);
  return userPage;
}

我希望这会有所帮助

这篇关于需要查找特定角色的所有用户JPA Criteria API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！