Use multiple columns as filters while fetching table records using JPA & Spring Boot

Question

I'm creating one Spring Boot Application that contains one User Entity.

I want to implement a functionality where an admin can search for users using different filters, and the filters can be one or more than one column from the User table.

Below is the User Entity class:

@Getter
@Setter
@Entity
@Table(name = "users")
public class UserEntity {

    @Id
    @Column(name = "user_id", nullable = false, unique = true, length = 10)
    private String userId;

    @Column(name = "user_first_name", nullable = false, length = 20)
    private String userFirstName;

    @Column(name = "user_middle_name", length = 20)
    private String userMiddleName;

    @Column(name = "user_last_name", nullable = false, length = 20)
    private String userLastName;

    @Column(name = "user_contact", nullable = false, unique = true, length = 10)
    private Long userContact;

    @Column(name = "user_email", nullable = false, unique = true, length = 50)
    private String userEmail;

    @Column(name = "user_password", nullable = false)
    private String userPassword;

    @Column(name = "user_enabled", nullable = false)
    private Boolean userEnabled;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "user_roles",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<RoleEntity> userRoles;

    @OneToMany(mappedBy = "tokenUser", fetch = FetchType.LAZY)
    private Set<TokenEntity> userTokens;

    @Temporal(TemporalType.DATE)
    @Column(name = "last_updated_on", nullable = false)
    private Date lastUpdatedOn;

    @Column(name = "last_updated_by", nullable = false)
    private String lastUpdatedBy;

    @Temporal(TemporalType.DATE)
    @Column(name = "created_on", nullable = false)
    private Date createdOn;

    @Column(name = "created_by", nullable = false)
    private String createdBy;
}

While searching for a user, only FirstName can be a filter or at some point, both FirstName and LastName can be a filter, or if required some other column values (ID, Email, and Contact) also can be used as filters along with them. The choice of filters will be totally the admin's choice at run-time.

Can someone please guide me on how I can implement this functionality using JPA and Spring Boot?

Below are my Controller, Service, and Repository classes respectively.

UserController

@RestController
@RequestMapping("/bma/api/admin/users")
public class UserController {

    @Autowired
    private UserService userService;

    private final int DEFAULT_PAGE_NUMBER = 1;
    private final int DEFAULT_PAGE_SIZE = 5;

    @GetMapping("/all")
    public ResponseEntity<?> getAllUsers(
            @RequestParam(name = "page_number", required = false) Integer pageNumber,
            @RequestParam(name = "page_size", required = false) Integer pageSize) {
        try {
            if (pageNumber == null) pageNumber = DEFAULT_PAGE_NUMBER;
            if (pageSize == null) pageSize = DEFAULT_PAGE_SIZE;
            List<UserResponseModel> responseModels = userService.getAllUserList(pageNumber-1, pageSize);
            GetAllDataResponseMetadata metadata = new GetAllDataResponseMetadata(HttpStatus.FOUND.value(),
                    pageNumber, pageSize);
            return ResponseEntity.status(HttpStatus.FOUND).body(new SuccessResponse<>(metadata, responseModels));
        }
        catch (NoRecordAvailableException exception) {
            CommonResponseMetadataWithMessage metadata =
                    new CommonResponseMetadataWithMessage(HttpStatus.NOT_FOUND.value(), exception.getMessage());
            return ResponseEntity.status(HttpStatus.NOT_FOUND).body(new ErrorResponse<>(metadata));
        }
    }
}

UserService

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private RoleRepository roleRepository;

    @Autowired
    private PasswordEncoder passwordEncoder;

    public UserResponseModel addNewUser(UserRequestModel userRequestModel) {
        if (userRepository.existsUserEntityByUserContact(userRequestModel.getUserContact())) {
            throw new InformationAlreadyExistsException("Contact Number Already Exists");
        }
        else if (userRepository.existsUserEntityByUserEmail(userRequestModel.getUserEmail())) {
            throw new InformationAlreadyExistsException("Email Already Exists");
        }
        else {
            UserDetailsValidationUtility.isEmailValid(userRequestModel.getUserEmail());
            UserDetailsValidationUtility.isContactNumberValid(userRequestModel.getUserContact());
            UserDetailsValidationUtility.isNameValid(userRequestModel.getUserFirstName(), false);
            UserDetailsValidationUtility.isNameValid(userRequestModel.getUserLastName(), false);
            UserDetailsValidationUtility.isNameValid(userRequestModel.getUserMiddleName(), true);
            UserDetailsValidationUtility.isPasswordValid(userRequestModel.getUserPassword());

            UserEntity newUserEntity = getUserEntityFromModel(userRequestModel);
            return getUserResponseModelFromEntity(userRepository.save(newUserEntity));
        }
    }

    public List<UserResponseModel> getAllUserList(int pageNumber, int PageSize) {
        Pageable pageWithSpecificNumberOfRecord = PageRequest.of(pageNumber, PageSize);
        Page<UserEntity> userEntities = userRepository.findAll(pageWithSpecificNumberOfRecord);
        if (userEntities.isEmpty()) {
            throw new NoRecordAvailableException("No User Record Available");
        }
        else {
            List<UserResponseModel> userResponseModels = new ArrayList<>();
            userEntities.forEach(userEntity ->
                    userResponseModels.add(getUserResponseModelFromEntity(userEntity)));
            return userResponseModels;
        }
    }

    private UserResponseModel getUserResponseModelFromEntity(UserEntity existingUserEntity) {
        UserResponseModel responseModel = new UserResponseModel();
        responseModel.setUserId(existingUserEntity.getUserId());
        responseModel.setUserFullName(buildFullNameOfUser(existingUserEntity));
        responseModel.setUserContact(existingUserEntity.getUserContact());
        responseModel.setUserEmail(existingUserEntity.getUserEmail());
        return responseModel;
    }

    private UserEntity getUserEntityFromModel(UserRequestModel userRequestModel) {
        Date currentDateTime = BankManagementUtility.getCurrentDateTime();
        String userId = generateUserId();

        UserEntity newUserEntity = new UserEntity();
        newUserEntity.setUserId(userId);
        newUserEntity.setUserFirstName(userRequestModel.getUserFirstName().trim());
        if (userRequestModel.getUserMiddleName() == null)
            newUserEntity.setUserMiddleName("");
        else newUserEntity.setUserMiddleName(userRequestModel.getUserMiddleName().trim());
        newUserEntity.setUserLastName(userRequestModel.getUserLastName().trim());
        newUserEntity.setUserContact(userRequestModel.getUserContact());
        newUserEntity.setUserEmail(userRequestModel.getUserEmail().trim());
        newUserEntity.setUserPassword(passwordEncoder.encode(userRequestModel.getUserPassword().trim()));
        newUserEntity.setUserEnabled(false);
        newUserEntity.setLastUpdatedOn(currentDateTime);
        newUserEntity.setLastUpdatedBy(userId);
        newUserEntity.setCreatedOn(currentDateTime);
        newUserEntity.setCreatedBy(userId);

        newUserEntity.setUserTokens(new HashSet<>());
        newUserEntity.setUserRoles(new HashSet<>());
        newUserEntity.getUserRoles().add(roleRepository.findRoleEntityByRoleName("USER"));

        return newUserEntity;
    }

    private String generateUserId() {
        StringBuilder userId = new StringBuilder("USER");
        userId.append(BankManagementUtility.generateNumberIdNumberByDigit(6));
        if (userRepository.existsUserEntityByUserId(userId.toString())) {
            return generateUserId();
        }
        else return userId.toString();
    }

    private String buildFullNameOfUser(UserEntity userEntity) {
        StringBuilder fullName = new StringBuilder(userEntity.getUserFirstName());
        if (!userEntity.getUserMiddleName().isEmpty()) {
            fullName.append(" ");
            fullName.append(userEntity.getUserMiddleName());
        }
        fullName.append(" ");
        fullName.append(userEntity.getUserLastName());
        return fullName.toString();
    }
}

UserRepository

@Repository
public interface UserRepository extends JpaRepository<UserEntity, String> {

    UserEntity findUserEntityByUserEmail(String email);
    UserEntity findUserEntityByUserId(String id);

    Boolean existsUserEntityByUserId(String id);
    Boolean existsUserEntityByUserEmail(String email);
    Boolean existsUserEntityByUserContact(Long contact);
}

Also here is the GitHub repository link if someone wants to take a look at the whole project:

https://github.com/rajeshsinha1997/Centralised_Banking_System_Application

Apart from the above-mentioned query, if someone has any other advice/suggestion regarding the code or project, that will be appriciated.

genchev · Accepted Answer · 2021-09-19 11:56:11Z

0

I think the best way for you is to make another endpoint, which receives a JSON object with search criteria. For example:

{
    "userFilter": {
        "userId": "1",
        "userFirstName": "John",
        "userLastName": "Doe",
        "userEmail": "[email protected]"
    }
}

In the endpoint, replace all "null" attributes with a "*". This way you can generate a query, where all data will be returned for a specific attribute, if it is not part of the filter parameters.

@PostMapping("/filter")
public ResponseEntity<?> filterUsers(@RequestBody UserFilter userFilter) {
    if(userFilter.getUserId() == null) {
        userFilter.setUserId("*");
    }
    // ... do this for all parameters of userFilter ...
    return ResponseEntity.body(userService.filterUsers(userFilter.getId(), userFilter.getUserFirstName(), userfilter.getUserLastName(), userFilter.getUserEmail()));
}

Then, in the repository, I would do something like this:

@Query("SELECT u FROM User u WHERE u.id like CONCAT('%', :id, '%') and u.first_name like CONCAT('%', :firstName, '%') and u.last_name like CONCAT('%', :lastName, '%') and u.email like CONCAT('%', :email, '%')")
List<User> filterUsers(@Param("id") Integer id, @Param("firstName") String firstName, @Param("lastName") String lastName, @Param("email") String userEmail);

For reference on JPQL: https://www.baeldung.com/spring-data-jpa-query

answered Sep 19, 2021 at 11:56

genchev

1001 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rajesh Patel Over a year ago

Suppose, I am not passing email then how we can manage that thing in our repository. Is there any way to make repository method as dynamic based on json filter object passed ?

Collectives™ on Stack Overflow

Use multiple columns as filters while fetching table records using JPA & Spring Boot

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related