CODEWITHSUNDEEP
mysqlspringspring-bootspring-data-jpaspring-data
Learning Only
Learning Only

Reputation: 17

How to solve this error org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: %

I'm trying to make simple search bar using spring and MySQL query. I used the below code to get list of customers but I'm getting error

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: %.

Code I used:

public List<CustDTO> getCust(String name) throws Exception {
  List<CustDTO> customerList=null;

  String queryString ="SELECT c FROM Cust c WHERE c.name LIKE %?1%";
  Query query=entityManager.createQuery(queryString);
  query.setParameter(1, name);
    
  List<Cust> result = query.getResultList();

  customerList=new ArrayList<CustDTO>();

    for (Cust customerEntity : result) {
        CustDTO customer=new CustDTO();
        customer.setName(customerEntity.getName());
        customer.setCity(customerEntity.getCity());

        customerList.add(customer);
    }
    System.out.println(customerList);
    return customerList;
  }

}

Upvotes: 1

Views: 3645

Answers (2)

Asgar
Asgar

Reputation: 2422

Yeah, Hibernate won't let you do that. If you want your like query to work, use:

   String queryString ="SELECT c FROM Cust c WHERE c.name LIKE ?1";
    Query query=entityManager.createQuery(queryString);
    query.setParameter(1, "%"+name+"%”);

Or you can even use a native query.

Upvotes: 2

zawarudo
zawarudo

Reputation: 2496

public List<CustDTO> getCust(String theSearchName) throws Exception {

Query theQuery = null;

theQuery = createQuery("from CustDTO where lower(name) like :theName", CustDTO.class);
query.setParameter("theName", "%" + theSearchName.toLowerCase() + "%");

    List<CustDTO> customers = theQuery.getResultList();
                
    return customers;

lower(name) - name is the field from your CustDTO entity.

You can also add checks if your input is empty or not

if (theSearchName != null && theSearchName.trim().length() > 0) {

        // do search with like
    }else {
        // theSearchName is empty ... so just get all customers
        theQuery =currentSession.createQuery("from CustDTO", CustDTO.class);            
    }

Upvotes: 0

Related Questions