通过使用搜索字符串进行排序-JPA [英] order-by-using-search-string - JPA

问题描述

嘿，我正在寻找一种搜索文本模式(在jpa中)并对结果进行排序的方法，首先是从该字符串开始的所有结果，然后是所有其他结果. 我已经找到使用搜索字符串的MySQL订单以获取mysql的答案

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

大多数答案使用union(在jpa中不存在)并强制查询数据库或为此打开视图. (从代码中排序不是并不是一个好的解决方案，因为我们使用分页来获取部分结果，因为结果的大小可能会很大)

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)

我喜欢上面链接中的一种解决方案是: select * from employee where name like '%ani%' order by locate('ani', name) asc, name asc 源

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

在我看来，这很清楚，但是我不确定如何将其转换为jpa.似乎Order对象无法获取定位输出

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

任何想法都会受到欢迎

谢谢！

阿隆

感谢您的回复.我想与jpa critiera达成相同的目标

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

尽管上述方法效果很好.我无法将以下行添加到代码中.似乎Order对象不喜欢它们

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

试过了 order.add(new javax.persistence.criteria.Order(){

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没有抱怨，但是查询没有得到正确的顺序

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

发现我的错误！

我经过的键值已经包含％"，并且两边都没有...所以定位无法正常工作.

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

现在我在某些特殊字符上有一些奇怪的行为:如果-例如-我有单词Ghurabā，查询like %ba%会找到它.但是，locate(Ghurabā,ba)似乎将返回0-表示pattern was not found in string任何想法如何克服此问题?

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?

似乎不仅是jpa，而且是mysql的行为.

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

将返回下一个结果

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

请注意，它确实适用于常规英文字符"，但是like和locate函数之间不匹配

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

使用Collcation:utf8_general_ci(与utf_unicode_ci获得相同的结果)

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

推荐答案

这不会引起任何抱怨.

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.

休眠:选择employee0_.id作为id1_2_，选择employee0_.address_id作为 地址5_2_，员工0_.DEPT_ID为DEPT6_2_，员工0_.manager_id为 manager7_2_，employee0_.name为name2_2_，employee0_.salary为 员工的薪水3_2_，员工0_.startDate作为员工的startDat4_2_ employee0_，其中employee0_.name类似于'％ani％'，由locate('ani'， employee0_.name)asc，employee0_.name asc

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，...}

使用CriteriQuery解决方案存在相同的问题

好吧，您对我有一些看法=)

Ok, you own me some points for this =)

休眠状态:从startDat4开始选择employee0_.id作为id1_2_，employee0_.address_id作为地址5_2_，employee0_.DEPT_ID作为DEPT6_2_，employee0_.manager_id作为manager7_2_，employee0_.name作为name2_2_，employee0_.salary作为employee3 __，employee0_.startDate作为startDat4.这里的employee0_.name喜欢什么?按locate(?, employee0_.name)asc，employee0_.name asc

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

尝试上述方法即可.

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

这篇关于通过使用搜索字符串进行排序-JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！