使用FIND_IN_SET的Hibernate标准

Question

在我的项目中，我有以下要求：

(1)请求对象到非列表类型(字符串)的API。

(2)我们使用Hibernate标准从mysql数据库中获取resultSet。

(3)其中一列具有管道分隔值。

(4)我们必须针对获取请求对象值的每个的行。

Table: user_info  
-----------------------------------------------------------
 user_id       user_name    .. .. .. ..   hobbies
-----------------------------------------------------------
 101           johnSmith    .. .. .. ..   Traveling|Painting|Gardening
 102           tomWatch     .. .. .. ..   Gardening|Reading|Fishing
 103           robertPatt   .. .. .. ..   Dancing|Gardening|Swimming|Blogging
 104           julieAdams   .. .. .. ..   Bicycling|Fishing|Socializing

 JSON Request object:
 {
   searchParams : {
       hobbies : [ 
         "Gardening",
         "Fishing",
         "Reading"
       ],
     ..
     ..
     ..
   }
  }


  Java Code 


  if(!CollectionUtils.isEmpty(searchParams.getHobbies())) {
        List<String> reqHobbies = searchParameters.getHobbies();
        Disjunction disjunction = Restrictions.disjunction();
        for(String hobby : reqHobbies) {
            Criterion criterion = Restrictions.like("hobbies", "|" + hobby + "|");
            disjunction.add(criterion);
        }
        criteria.add(disjunction);
    }

此查询将不起作用，因为值的开头(从表中)没有“AC.26”符号。

如果我以下列方式修改critieria以生成查询，

select * from user_info
where hobbies like '%Gardening|%' or hobbies like '%Fishing|%' or hobbies like '%Reading|%' 
 or hobbies like '%|Gardening|%' or hobbies like '%|Fishing|%' or hobbies like '%|Reading|%' 
 or hobbies like '%|Gardening%' or hobbies like '%|Fishing%' or hobbies like '%|Reading%';

这个设计也有一个缺陷。使用FIND_IN_SET解决这一问题的唯一方法。

Mysql查询：

select * from user_info 
where hobbies find_in_set('Gardening', replace(hobbies, '|', ',')) 
or hobbies find_in_set('Fishing', replace(hobbies, '|', ','))
or hobbies find_in_set('Reading', replace(hobbies, '|', ','));

如何使用Hibernate条件创建find_in_set查询？使用“公式”？

Answer 1

如果您的目标是构建动态查询而不是使用条件API，您可以使用FluentJPA构建它，如下所示：

public List<UserInfo> filterByHobbies(List<String> hobbies) {

    Function1<UserInfo, Boolean> dynamicFilter = buildOr(hobbies);

    FluentQuery query = FluentJPA.SQL((UserInfo user) -> {
        SELECT(user);
        FROM(user);

        WHERE(dynamicFilter.apply(hobbies);
    });

    return query.createQuery(getEntityManager(), UserInfo.class).getResultList();
}

private Function1<UserInfo, Boolean> buildOr(List<String> hobbies) {
    Function1<UserInfo, Boolean> criteria = Function1.FALSE();

    for (String hobby : hobbies)
        criteria = criteria.or(u -> FIND_IN_SET(parameter(hobby),
                                                u.getHobbies().replace('|', ',')) > 0);

    return criteria;
}

Answer 2

在某些情况下，这是一种选择：

xxxRepository.findAll((root, query, cb) -> {

   List<Predicate> predicateList = new ArrayList<>();
    if (StringUtils.isNotBlank(param)) {
        Expression<Integer> findInSetFun = cb.function("FIND_IN_SET", Integer.class,             
                            cb.literal(param), root.get("targetColName"));

        predicateList .add(cb.greaterThan(findInSetFun, 0));
    }
}, pageable);

Answer 3

Expression<Integer> function = criteriaBuilder.function("find_in_set", Integer.class,
                    criteriaBuilder.literal(warehouseList), root.get("sWarehouseIdList"));
            
Predicate sWarehouseIdList = criteriaBuilder.greaterThan(function, 0);
            predicateList.add(sWarehouseIdList);