[FIXED] Search like sql using '%' criteria native sql

Issue

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!

Solution

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

Answered By - Amadan
Answer Checked By - David Marino (JavaFixing Volunteer)