Hibernate Criteria using FIND_IN_SET

Question

I have a below requirement in my project:
(1) Request object to an API which is off list type (of strings).
(2) We are using Hibernate Criteria to fetch the resultSet from the mysql database.
(3) One of the columns is having Pipe delimited values.
(4) We have to fetch the rows against each of the request object's values.

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

This query will not work as the starting of the value (from table) is not having "|" symbol..

If I modify the critieria API to generate the query in the following fashion,

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%';

This design also has a flaw. The only way to solve this by using FIND_IN_SET.

Mysql query:

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

How to create a find_in_set query using Hibernate Criteria? Using @Formula ?

You need another table where you can store multiple rows for each user, with one hobby per row. This is the way to store a many-to-many relationship in a relational database. Then you don't need to use FIND_IN_SET() or worry about which character separates hobbies. — Bill Karwin
Thank you Bill for the answer. Since having multiple tables is not an option, we are stuck with this. Not sure how to solve on this. — Srinivas Lakshman

Konstantin Triger Konstantin Triger · Accepted Answer · 2019-08-11T09:40:35

If your goal is building query dynamically and not using Criteria API, you may build it with FluentJPA, like in this:

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

Hibernate Criteria using FIND_IN_SET

2 Answers