CODEWITHSUNDEEP
javajpa
Lejdi Prifti
Lejdi Prifti

Reputation: 29

How to write a proper statement using LIKE % in java

I want to get a list of objects from the database that matches a certain keyword in their title name.

I am using the following method but it gives me an error.

Jul 24, 2019 5:56:04 PM org.hibernate.hql.internal.ast.ErrorTracker reportError
ERROR: line 1:86: unexpected token: %
Jul 24, 2019 5:56:04 PM org.hibernate.hql.internal.ast.ErrorTracker reportError
ERROR: line 1:86: unexpected token: %
line 1:86: unexpected token: %

I am using JPA 2.0

java public List<CategoryEntity> getCategoryByName(String categoryName) {

    TypedQuery<CategoryEntity> query = entityManager.createQuery
          ("Select c From CategoryEntity c where c.categoryName LIKE %?1% 
          and c.categoryState=?2 and c.flag=:flag", CategoryEntity.class);
    query.setParameter(1, categoryName);
    query.setParameter(2, State.CREATED);
    query.setParameter("flag", true);

    return query.getResultList();
}

Upvotes: 0

Views: 53

Answers (2)

adnane_leb
adnane_leb

Reputation: 3

Change your query to the following:

Select c From CategoryEntity c where c.categoryName LIKE 1 and 
c.categoryState=?2 and c.flag=:flag

Then set your parameter:

query.setString(1, "%"+categoryName+"%");

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

One standard way of binding values with wildcards for use in LIKE expression is to bind the entire expression. Consider the following updated code for an explanation:

TypedQuery<CategoryEntity> query = entityManager
    .createQuery("SELECT c FROM CategoryEntity c WHERE c.categoryName LIKE ?1 AND c.categoryState = ?2 AND c.flag = ?3", CategoryEntity.class);
query.setParameter(1, "%" + categoryName + "%");
query.setParameter(2, State.CREATED);
query.setParameter(3, true);

The idea here is to bind %categoryName% directly to the placeholder. Note that for consistency I chose to use numbered parameters everywhere.

Upvotes: 4

Related Questions