Parameter in like clause JPQL

Question

I am trying to write a JPQL query with a like clause:

LIKE '%:code%'

I would like to have code=4 and find

455
554
646
...

I cannot pass :code = '%value%'

namedQuery.setParameter("%" + this.value + "%");

because in another place I need :value not wrapped by the % chars. Any help?

Solution 1

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' .

Solution 2

I don't use named parameters for all queries. For example it is unusual to use named parameters in JpaRepository.

To workaround I use JPQL CONCAT function (this code emulate start with):

@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

Solution 3

You could use the JPA LOCATE function.

LOCATE(searchString, candidateString [, startIndex]): Returns the first index of searchString in candidateString. Positions are 1-based. If the string is not found, returns 0.

FYI: The documentation on my top google hit had the parameters reversed.

SELECT 
  e
FROM 
  entity e
WHERE
  (0 < LOCATE(:searchStr, e.property))

Solution 4

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

Solution 5

There is nice like() method in JPA criteria API. Try to use that, hope it will help.

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

View more solutions

Comments

[László van den Hoek]

For the record, this does not leave you open to JPQL injection attacks because this.value is automatically properly escaped for you.

[Gustavo]

This "%" + this.value + "%" is what is escaped.

[EM-Creations]

How do I make this case-insensitive?

[hgoebl]

for me the best solution - no concatenation, no SQL injection.

[Muizz Mahdy]

Note: CONCAT(?2, '%') will add '%' to the end of the parameter, use CONCAT('%', ?2, '%') to add it to the beginning and end of parameter.

[kaiser]

Downvote: not working, this changes the parameter name to code%

[jbaranski]

Adding to the comment above, in Oracle the syntax is: CONCAT(CONCAT('%', ?2), '%') to add % to the beginning and end of a string.

[Remy]

This answer is very wrong and misleading, you are not allowed to do that in JPA Queries

[TXN]

Spring Data seems to accept this syntax (in @Query annotations), whereas JPQL does not.