JPA搜索字符串，长整数和布尔值 [英] JPA search String, Long and Boolean

问题描述

我有一个Spring-Boot应用程序.有一个实体:

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
}

我有一个存储库:

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

我需要请求通过schoolId搜索并用某些字符串过滤所有字段.

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

类似这样的东西:

@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);

但是我收到一个例外，因为我尝试将like应用于Long和Boolean.

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

例如，如果我尝试按"testSearchValue"进行过滤，则会收到此异常:

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

java.lang.IllegalArgumentException:参数值[％testSearchValue％]与预期的类型[java.lang.Long(n/a)

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

很遗憾，CAST和CONVERT不适用于我.

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

那么有什么解决方法吗?

So is there any workaround?

一些细节

我向该API发送了GET请求:

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);
    }

然后在UserService中:

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

所以:

据我所见，问题的基本要点是将Long和Boolean表示为String.
也许最好使用nativeQuery?如果是这样，那么您能给我一个关于如何在LIKE子句中使用CAST()或CONVERT()的提示吗?

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?

推荐答案

您是否考虑过使用规范，您可以动态生成spring数据查询的WHERE部分. 为了在Spring数据JPA查询中使用规范，您将必须扩展org.springframework.data.jpa.repository.JpaSpecificationExecutor接口.因此，您的用户存储库可能如下所示:

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);

}

首先，我们从添加where表达式的name LIKE %text%部分开始.

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

接下来，我们检查text变量的值是否可以转换为long.如果可以，那么我们从字符串中获取long值，然后将其添加到where查询中.

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.

最后，我们检查text变量是否可以转换为布尔值.如果可以的话，那么我们也会将该检查添加到查询中.

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.

例如，如果text变量的值为 test1 ，则该部分将位于

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

WHERE name LIKE '%test1%;

如果text变量的值为 true ，则where部分将为

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

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

最后，如果text变量的值为 12 ，则where部分将为

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

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

注意: 当我们按名称搜索时，我在零件中添加了cb.lower(root.get("name"))和text.toLowerCase()以便使搜索不区分大小写.

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.

这篇关于JPA搜索字符串，长整数和布尔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！