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

}`

CodePudding user response：

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.

CodePudding user response：

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