CODEWITHSUNDEEP
spring-boothibernatejpaspring-data-jpaspecifications
Manoj Kumar
Manoj Kumar

Reputation: 89

Spring data jpa specification and pageable in @manytomany using join table repository

I have a use case to filter and paginate the record with @manytomany relation using a separate join table. Below are the relation and entities

   public class User {
        private Long userId;
        private String userName
        @OneToMany(mappedBy = "user")
        private List<UserRole> userRole;
    }

public class Role {
    private Long roleId;
    private String roleName
    @OneToMany(mappedBy = "role")
    private List<UserRole> userRole;
}

public class UserRole{
    private Long id;
    private Integer active
    @ManyToOne   
    @MapsId("userId")
    private User user;
    @ManyToOne   
    @MapsId("roleId")
    private Role role;
}

    @Repository
    public interface UserRoleRepository extends 
                       JpaRepository<UserRole, String>, 
                       JpaSpecificationExecutor<UserRole> {
    }

     public class UserRoleSpecification implements Specification<UserRole> 
     {
    
        private SearchCriteria criteria;
        
        public RuleEntitySpecification(SearchCriteria criteria ) {
            this.criteria = criteria;
        }
    
        @Override
        public Predicate toPredicate(Root<UserRole> root, 
                               CriteriaQuery<?> query,
                               CriteriaBuilder criteriaBuilder) {
            
          if(criteria.getOperation().equalsIgnoreCase("eq")) {
             if(root.get(criteria.getKey()).getJavaType() == String.class) 
             {
               return criteriaBuilder.like(root.get(criteria.getKey()), 
                                      "%" + criteria.getValue() + "%");
             } else {
               return criteriaBuilder.equal(root.get(criteria.getKey()), 
                                            criteria.getValue());
             }
           }
           return null;
        }
    
    }

    public class SearchCriteria implements Serializable {
    
        private String key;
        private String operation;
        private Object value;
    }

    UserRoleSpecificationBuilder specBuilder = new UserRoleSpecificationBuilder();
    
    specBuilder.with("active", "eq" , 1); // giving us proper result
    
    Specification<UserRole> spec = specBuilder.build();
    
    Pageable paging = PageRequest.of(0, 5, Sort.by("user.userId"));
    
    Page<UserRole> pagedResult = userRoleRepository.findAll(spec,paging);

But when we try to filter based on Rule/User table properties like userName/roleName specBuilder.with("user.userName", "eq" , "xyz");, I am getting following exception:

    org.springframework.dao.InvalidDataAccessApiUsageException: 
    Unable to locate Attribute  with the the given name 
    [user.userName] on this ManagedType

Kindly suggest if there is any way to achieve the filter using UserRole Join Table repository and specification

Pagination is also required hence using repository of Type UserRole JoinTable.

Upvotes: 1

Views: 1577

Answers (1)

Kavithakaran Kanapathippillai
Kavithakaran Kanapathippillai

Reputation: 8213

    @Override
    public Predicate toPredicate(Root<UserRole> root,
                                 CriteriaQuery<?> query,
                                 CriteriaBuilder criteriaBuilder) {
      if (criteria.getOperation().equalsIgnoreCase("eq")) {
         String key = criteria.getKey();
         Path path;
         if (key.contains(".")) {
            String attributeName1 = key.split("\\.")[0];
            String attributeName2 = key.split("\\.")[1];
            path = root.get(attributeName1).get(attributeName2);
         } else {
            path = root.get(key);
         }
         if (path.getJavaType() == String.class) {
           return criteriaBuilder.like(path, "%" + criteria.getValue() + "%");
         } else {
           return criteriaBuilder.equal(root.get(key), criteria.getValue());
         }
      }
      return null;
    }

Upvotes: 1

Related Questions