Search like sql using '%' criteria native sql

Question

I have a code to connect PL/SQL search like using '%' and I use criteria to implement

String sql = "SELECT * FROM EMPLOYEE ";
Query query = entityManager.createNativeQuery(sql.toString());

if(searchCharacterInfo.getKeyword() != null){
      sql += " WHERE NAME LIKE %:keyword% ";
      query = entityManager.createNativeQuery(sql).setParameter("keyword", keyword);
}

List<Object> res =  query.getResultList();
return res;

When I run this code to show error:

Could not locate named parameter keyword

Please help me solve this problem!

Answer 1

The parameter placeholder isn't just blindly replaced with the placeholder value, so %:keyword% is not a legal syntax.

You need to have LIKE :keyword in SQL, and pass keyword as "%" + keyword + "%" from the Java side.

Alternately, you could concatenate strings on the SQL side: LIKE ('%' || :keyword || '%').

Answer 2

Assuming you have a model class EMPLOYEE. so you can write criteria as follows :

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<EMPLOYEE> cq = cb.createQuery(EMPLOYEE.class);
        Root<EMPLOYEE> employee = cq.from(EMPLOYEE.class);
        cq.select(employee);
        List<Predicate> predicates = new ArrayList<>();
        if (searchCharacterInfo.getKeyword() != null) {
            predicates.add(cb.like(employee.get("name"), "%" + searchCharacterInfo.getKeyword() + "%"));
        }
    
        cq.where(predicates.toArray(new Predicate[0]));
        List<EMPLOYEE> res =  entityManager.createQuery(cq).getResultList();