Java JPQL like 子句中的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1341104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parameter in like clause JPQL
提问by
I am trying to write a JPQL query with a like clause:
我正在尝试使用 like 子句编写 JPQL 查询：
LIKE '%:code%'
I would like to have code=4 and find
我想要 code=4 并找到
455 554 646 ...
I cannot pass :code = '%value%'
我不能通过 :code = '%value%'
namedQuery.setParameter("%" + this.value + "%");
because in another place I need :valuenot wrapped by the %chars. Any help?
因为在另一个地方我不需要:value被%字符包裹。有什么帮助吗？
回答by shipmaster
If you do
如果你这样做
LIKE :code
and then do
然后做
namedQuery.setParameter("code", "%" + this.value + "%");
Then value remains free from the '%' sign. If you need to use it somewhere else in the same query simply use another parameter name other than 'code' .
然后值保持不受“%”符号的影响。如果您需要在同一查询中的其他地方使用它，只需使用 'code' 以外的另一个参数名称。
回答by gavenkoa
I don't use named parameters for all queries. For example it is unusual to use named parameters in JpaRepository.
我不为所有查询使用命名参数。例如，在JpaRepository 中使用命名参数是不常见的。
To workaround I use JPQL CONCATfunction (this code emulate start with):
要解决方法，我使用 JPQL CONCAT函数（此代码模拟以开头）：
@Repository
public interface BranchRepository extends JpaRepository<Branch, String> {
private static final String QUERY = "select b from Branch b"
+ " left join b.filial f"
+ " where f.id = ?1 and b.id like CONCAT(?2, '%')";
@Query(QUERY)
List<Branch> findByFilialAndBranchLike(String filialId, String branchCode);
}
I found this technique in excellent docs: http://openjpa.apache.org/builds/1.0.1/apache-openjpa-1.0.1/docs/manual/jpa_overview_query.html
我在优秀的文档中发现了这种技术：http: //openjpa.apache.org/builds/1.0.1/apache-openjpa-1.0.1/docs/manual/jpa_overview_query.html
回答by David Carlson
You could use the JPA LOCATE function.
您可以使用JPA LOCATE 函数。
LOCATE(searchString, candidateString [, startIndex]): Returns the first index of searchString in candidateString. Positions are 1-based. If the string is not found, returns 0.
LOCATE(searchString,CandidateString [, startIndex])：返回候选字符串中 searchString 的第一个索引。位置是从 1 开始的。如果未找到该字符串，则返回 0。
FYI: The documentation on my top google hithad the parameters reversed.
仅供参考：我最热门的 google上的文档颠倒了参数。
SELECT
e
FROM
entity e
WHERE
(0 < LOCATE(:searchStr, e.property))
回答by Sehtim
Just leave out the ''
只需省略''
LIKE %:code%
回答by Ishimwe Aubain Consolateur
I don't know if I am late or out of scope but in my opinion I could do it like:
我不知道我是迟到还是超出范围，但在我看来，我可以这样做：
String orgName = "anyParamValue";
Query q = em.createQuery("Select O from Organization O where O.orgName LIKE '%:orgName%'");
q.setParameter("orgName", orgName);
回答by Haroon
There is nice like() method in JPA criteria API. Try to use that, hope it will help.
JPA 标准 API 中有很好的 like() 方法。尝试使用它，希望它会有所帮助。
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery criteriaQuery = cb.createQuery(Employees.class);
Root<Employees> rootOfQuery = criteriaQuery.from(Employees.class);
criteriaQuery.select(rootOfQuery).where(cb.like(rootOfQuery.get("firstName"), "H%"));
回答by Vaneet Kataria
- Use below JPQL query.
- 使用以下 JPQL 查询。
select i from Instructor i where i.address LIKE CONCAT('%',:address ,'%')");
select i from Instructor i where i.address LIKE CONCAT('%',:address ,'%')");
Use below Criteria code for the same:
@Test public void findAllHavingAddressLike() {CriteriaBuilder cb = criteriaUtils.criteriaBuilder(); CriteriaQuery cq = cb.createQuery(Instructor.class); Root root = cq.from(Instructor.class); printResultList(cq.select(root) .where (cb.like(root.get(Instructor_.address), "%#1074%"))); }
使用以下标准代码相同：
@Test public void findAllHavingAddressLike() {CriteriaBuilder cb = criteriaUtils.criteriaBuilder(); CriteriaQuery cq = cb.createQuery(Instructor.class); Root root = cq.from(Instructor.class); printResultList(cq.select(root) .where (cb.like(root.get(Instructor_.address), "%#1074%"))); }
