105

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?

Manuel Drieschmanns
  • 704
  • 5
  • 26

9 Answers9

177

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

shipmaster
  • 3,994
  • 4
  • 30
  • 33
63

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

gavenkoa
  • 45,285
  • 19
  • 251
  • 303
  • 3
    Note: CONCAT(?2, '%') will add '%' to the end of the parameter, use CONCAT('%', ?2, '%') to add it to the beginning and end of parameter. – Muizz Mahdy Jan 22 '19 at 19:45
  • 1
    Adding to the comment above, in Oracle the syntax is: `CONCAT(CONCAT('%', ?2), '%')` to add % to the beginning and end of a string. – jbaranski Jan 19 '21 at 17:57
8

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))
David Carlson
  • 1,461
  • 2
  • 18
  • 18
  • 1
    for me the best solution - no concatenation, no SQL injection. – hgoebl Dec 04 '17 at 17:54
  • What if :searchStr is actually a List? Is there a way use LOCATE with lists? – Danilo Körber Sep 05 '22 at 12:03
  • MySQL text queries are case-insensitive (based on collation of the field), so this actually performs case-insensitive querying without the performance and readability impact of things like `LOWER()` – E-Riz Jun 05 '23 at 19:54
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);
Cà phê đen
  • 1,883
  • 2
  • 21
  • 20
Ishimwe Aubain Consolateur
  • 1,019
  • 11
  • 13
4

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%"));
Haroon
  • 111
  • 1
  • 3
3
  1. Use below JPQL query.
select i from Instructor i where i.address LIKE CONCAT('%',:address ,'%')");
  1. Use below Criteria code for the same:
@Test
public void findAllHavingAddressLike() {
    CriteriaBuilder cb = criteriaUtils.criteriaBuilder();
    CriteriaQuery<Instructor> cq = cb.createQuery(Instructor.class);
    Root<Instructor> root = cq.from(Instructor.class);
    printResultList(cq.select(root).where(
        cb.like(root.get(Instructor_.address), "%#1074%")));
}
aboger
  • 2,214
  • 6
  • 33
  • 47
Vaneet Kataria
  • 575
  • 5
  • 14
3

Use JpaRepository or CrudRepository as repository interface:

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Integer> {

    @Query("SELECT t from Customer t where LOWER(t.name) LIKE %:name%")
    public List<Customer> findByName(@Param("name") String name);

}


@Service(value="customerService")
public class CustomerServiceImpl implements CustomerService {

    private CustomerRepository customerRepository;
    
    //...

    @Override
    public List<Customer> pattern(String text) throws Exception {
        return customerRepository.findByName(text.toLowerCase());
    }
}
aboger
  • 2,214
  • 6
  • 33
  • 47
KarishmaP
  • 31
  • 1
2

Just leave out the ''

LIKE %:code%
Sehtim
  • 55
  • 1
  • 3
  • Downvote: not working, this changes the parameter name to code% – kaiser Sep 12 '19 at 11:00
  • This answer is very wrong and misleading, you are not allowed to do that in JPA Queries – Remy Jul 12 '21 at 18:58
  • 1
    Spring Data seems to accept this syntax (in @Query annotations), whereas JPQL does not. – TXN Jan 06 '22 at 17:58
  • Wow, this worked for me, NO idea why, seems awful but it works with Spring data when the single quotes made the query return null. – snakedog Jan 24 '23 at 18:34
0

Use JPQL query.

@Query("select e from Entity e where e.id = ?1 and e.code like CONCAT('%', CONCAT(?2, '%'))")
List<Entity> findByIdAndCodeLike(Long id, String code);
pervez
  • 1
  • 2