Okay, let's break down why your LIKE filter isn't working in JPA while it works fine when running the SQL directly. This is a classic case of small string concatenation or query handling mistakes throwing off your results, so let's go through the most likely causes and fixes:

1. The most probable culprit: Incorrect condition check in your ternary operator #

Look closely at your code's ternary expression:

sb.append(StringUtils.isNotEmpty(name)? "UPPER(B.NAME) LIKE '%"+name.toUpperCase()+ "%'":"";

If you accidentally used StringUtils.isEmpty(name) instead of isNotEmpty, then when name has a value (like "abc"), the condition would evaluate to false, and you'd append an empty string to the query. That turns your subquery into:

Select ID FROM B Where 

Most databases will ignore this invalid Where clause (with nothing following it) and return all records from table B. Then your main query's B_ID IN(...) would include every B ID, resulting in all AB records being returned.

2. Syntax error in string concatenation #

Your code is missing a closing parenthesis for the append() method call. The line should end with an extra ):

sb.append(StringUtils.isNotEmpty(name)? "UPPER(B.NAME) LIKE '%"+name.toUpperCase()+ "%'":"");

While a missing parenthesis should prevent compilation, if you somehow got this to run (maybe a typo in your actual code), it could generate malformed SQL that the database handles unexpectedly (again, ignoring the broken Where clause and returning all B records).

3. JPA EntityManager cache interference #

If you ran a version of this query without the LIKE filter earlier in the same EntityManager session, JPA's first-level cache might be returning cached results instead of executing the new query. To test this, clear the cache before running your query:

em.clear(); // Clear first-level cache
List<Object> result= em.createNativeQuery(sb.toString()).getResultList();

4. Hidden string escaping issues #

While less likely with direct string concatenation, if your name value contained special characters (like single quotes), it could break the SQL syntax or turn your LIKE pattern into a literal match instead of a wildcard. For example, a name with a single quote would create unbalanced quotes in the SQL, leading to unexpected behavior.

Fixes to avoid this problem entirely #

Stop manually concatenating SQL strings—it's error-prone and risky for SQL injection. Instead, use parameter binding with JPA's NativeQuery:

Option 1: Parameterized Native Query

String name = "abc";
String sql = "Select A_ID from AB Where B_ID IN(Select ID FROM B Where UPPER(B.NAME) LIKE CONCAT('%', UPPER(:name), '%'))";

List<Object> result = em.createNativeQuery(sql)
    .setParameter("name", name)
    .getResultList();

Option 2: Dynamic query with safe parameter binding

If you need to conditionally add the filter only when name is not empty:

String name = "abc";
StringBuilder sb = new StringBuilder("Select A_ID from AB Where B_ID IN(Select ID FROM B");

if (StringUtils.isNotEmpty(name)) {
    sb.append(" Where UPPER(B.NAME) LIKE CONCAT('%', UPPER(:name), '%')");
}
sb.append(")");

Query query = em.createNativeQuery(sb.toString());
if (StringUtils.isNotEmpty(name)) {
    query.setParameter("name", name);
}

List<Object> result = query.getResultList();

First step to confirm the issue: print the final SQL string before passing it to createNativeQuery. This will immediately show you if the LIKE filter is actually present in the query JPA is executing, versus what you're running manually.

内容的提问来源于stack exchange，提问作者K Guru