JPA search String, Long and Boolean

Question

I have a Spring-Boot app. There's an entity:

@Entity
@Table(name = "user")
public class User {
    private Long id;
    private String name;
    private Long schoolId;
    private Boolean isActive;
    // getters and setters
}

I have a repository:

@Repository
public interface UserRepositoryPageable extends PagingAndSortingRepository<User, Long> {
}

I need to make a request to search by schoolId and filter all fields by some string.

Something like this:

@Query("SELECT u FROM User u " +
        "WHERE u.schoolId = :schoolId AND (" +
        "u.id like %:searchVal% OR " +
        "u.name like %:searchVal% OR " +
        "u.isActive like %:searchVal%)")
Page<User> getUserBySchoolIdWithFilter(@Param("schoolId") Long schoolId,
                                       Pageable pageable,
                                       @Param("searchVal") String searchVal);

But I receive an exception because I try to apply like to Long and Boolean.

For example, if I try to filter by "testSearchValue", I receive this exception:

java.lang.IllegalArgumentException: Parameter value [%testSearchValue%] did not match expected type [java.lang.Long (n/a)

Unfortunately, CAST and CONVERT haven't worked for me.

So is there any workaround?

SOME DETAILS

I send a GET request to this API:

@RequestMapping(path = "users/{schoolId}/search", method = GET)
public ResponseEntity<Page<User>> searchUserBySchoolWithFilter(
                    @PathVariable(value = "schoolId") Long schoolId, Pageable pageable,
                    @RequestParam(value = "searchVal", required = false) String searchVal) {
    return new ResponseEntity<>(userService
                .getUserBySchoolIdWithFilter(schoolId, pageable, searchVal), HttpStatus.OK);
    }

Then in UserService:

public Page<User> getUserBySchoolIdWithFilter(Long schoolId, Pageable pageable, String searchVal) {
    return userRepositoryPageable.getUserBySchoolIdWithFilter(schoolId, pageable, searchVal);
}

So:

As far as I see it, the basic point of the question is to represent Long and Boolean as String.
Maybe it's better to use nativeQuery? If so, then could you give me a hint about how to use CAST() or CONVERT() with LIKE clause?

Answer 1

Have you considered the usage of Specifications?

Using specifications, you can dynamically generate the WHERE part of a spring data query. In order to use specifications with your spring data JPA queries, you will have to extend the org.springframework.data.jpa.repository.JpaSpecificationExecutor interface. So your user repository could look like this:

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

Your search method could look like this

public List<User> getAllFilterByString(String text) {

    if(StringUtils.isEmpty(text))
        return userRepository.findAll();

    Specification<User> specification =
            (root, query, cb) -> {
                List<Predicate> predicates = new ArrayList<>();
                predicates.add(cb.like(cb.lower(root.get("name")), "%"+text.toLowerCase()+"%"));

                //check if the text value can be casted to long.
                //if it is possible, then add the check to the query
                try {
                    long longValue = Long.valueOf(text);
                    predicates.add(cb.equal(root.get("id"), longValue));
                }
                catch (NumberFormatException e) {
                    //do nothing, the text is not long
                }

                //check if the text can be casted to boolean
                //if it is possible, then add the check to the query

                Boolean value = "true".equalsIgnoreCase(text) ? Boolean.TRUE :
                        "false".equalsIgnoreCase(text) ? Boolean.FALSE : null;

                if(value != null) {
                    predicates.add(cb.equal(root.get("isActive"), value));
                }

                return cb.or(predicates.toArray(new Predicate[] {}));
            };

    return userRepository.findAll(specification);

}

First we start by adding the name LIKE %text% part of the where expression.

Next, we check if the value of the text variable can be cast to long. If it can, then we get the long value out of the string and add it to the where query.

Last we check if the text variable can be cast to boolean. If it can, then we add that check to the query as well.

For example, if the value of the text variable is test1 the where part will be

WHERE name LIKE '%test1%;

If the value of the text variable is true then the where part will be

WHERE name LIKE '%true%' OR is_active = true;

Finally, if the value of the text variable is 12 then the where part will be

WHERE name LIKE '%12%' OR id = 12;

Note: I added cb.lower(root.get("name")) and text.toLowerCase() to the part when we search by name in order to make the search case insensitive.