order-by-using-search-string - JPA

Question

hey i'm looking for way to search for text pattern (in jpa) and to sort the result, first all the result starting with this string and then all other result. i have found Mysql order by using search string to get the answer for mysql

most of the answers use union(which does not exist in jpa) and force to query the db or open a view for that. (ordering from code is not that good solution since we use paging to get part of the result as the result size can be really big)

One solution i like from the link above is : select * from employee where name like '%ani%' order by locate('ani', name) asc, name asc source

This seems to me very clear but i'm not sure how to convert it to jpa. seems like Order object is not able to get locate output

any ideas will be welcome

Thanks!

Alon

EDIT: thanks for reply. i'm tring to achive the same with jpa critiera

Iterator<Order> sortingIter = page.getSort().iterator();
ArrayList<javax.persistence.criteria.Order> order = new    ArrayList<javax.persistence.criteria.Order>();
String fieldName;
while (sortingIter.hasNext()) {
        Order sort = sortingIter.next();
        fieldName = sort.getProperty();
        order.add(sort.getDirection() == Sort.Direction.ASC ? cb
        .asc(keyword.get(fieldName)) : cb.desc(keyword
                    .get(fieldName)));
}       

while the above works well. i cannot add the following line to the code. seems like Order object doesnt like them

Expression<String> fieldValue = keyword.get(fieldName);
order.add(cb.locate(fieldValue,key));

EDIT 2: tried order.add(new javax.persistence.criteria.Order() {

        @Override
        public javax.persistence.criteria.Order reverse() {
            // TODO Auto-generated method stub
            return null;
        }

        @Override
        public boolean isAscending() {
            // TODO Auto-generated method stub
            return true;
        }

        @Override
        public Expression<?> getExpression() {
            // TODO Auto-generated method stub

            return cb.locate(fieldValue,key);
        }
    });

jpa doesn't complain but the query does not get the right order

EDIT 3: Found my mistake!

The key value i was passing above already contained "%" and both sides... so locate did not work properly.

now i'm getting some weird behavior on some special chars:if - for example - i have the word Ghurabā the query like %ba% will find it. but, it seems that locate(Ghurabā,ba) will return 0 - meaning as pattern was not found in string any idea how to overcome this issue?

seems like this is not only jpa but also mysql behavior.

SELECT * 
FROM  `keywords` 
WHERE name LIKE  '%ba%'
ORDER BY LOCATE(  'ba', name ) , name
LIMIT 0 , 30

will return the next result

Ghurabā'
Khuṭabā'
qabā\
Ribāṭ
ba'urchi (cook)
Baghdad
...

note that it does work for "regular english characters" but there is a mismatch between the like and the locate function

Using Collcation: utf8_general_ci (got the same result with utf_unicode_ci)

Answer 1

This does not makes any complain.

String jpql = "select e from Employee e where e.name like '%ani%' order by locate('ani', e.name) asc, e.name asc";
TypedQuery<Employee> query2 = em.createQuery(jpql ,Employee.class);

And this is the translation that hibernate does.

Hibernate: select employee0_.id as id1_2_, employee0_.address_id as address5_2_, employee0_.DEPT_ID as DEPT6_2_, employee0_.manager_id as manager7_2_, employee0_.name as name2_2_, employee0_.salary as salary3_2_, employee0_.startDate as startDat4_2_ from Employee employee0_ where employee0_.name like '%ani%' order by locate('ani', employee0_.name) asc, employee0_.name asc

Using some data as the link you mention.

Employee 10: name: anil, salary: 59000, 
Employee 1: name: anirudha, salary: 55000, 
Employee 5: name: rani, 
Employee 7: name: Stephanie, salary: 54000, 

{anil,anirudha,rani, ...}

Same problem using CriteriQuery solution

Ok, you own me some points for this =)

Hibernate: select employee0_.id as id1_2_, employee0_.address_id as address5_2_, employee0_.DEPT_ID as DEPT6_2_, employee0_.manager_id as manager7_2_, employee0_.name as name2_2_, employee0_.salary as salary3_2_, employee0_.startDate as startDat4_2_ from Employee employee0_ where employee0_.name like ? order by locate(?, employee0_.name) asc, employee0_.name asc

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);

Root<Employee> root = cq.from(Employee.class);
cq.where(cb.like(root.<String>get("name"), "%ani%"));
cq.orderBy(cb.asc(cb.locate(root.<String>get("name"), "ani")), cb.asc(root.get("name")));

TypedQuery<Employee> query2 = em.createQuery(cq);
printList(query2.getResultList());

Try the above it should work.

Employee 10: name: anil, salary: 59000, 
Employee 1: name: anirudha, salary: 55000, 
Employee 5: name: rani, 
Employee 7: name: Stephanie, salary: 54000, 

Check this out if you think ?(question mark) is not correct in the query. http://webdev.apl.jhu.edu/~jcs/ejava-javaee/coursedocs/605-784-site/docs/content/html/jpa-query-criteria-function.html#jpa-query-criteria-function-string-locate