使用Criteria Builder实现字符串列表IN查询时出现语法异常求助

Hey, let's break down the issue you're facing. The error message clearly points to a duplicate in clause in your generated HQL: generatedAlias0.names in (:param0, :param1) in () — that's the root cause of the syntax exception.

What's Wrong with Your Code? #

Looking at your predicate line:

Predicate hasA = builder.in(root.get(APDetails_.names).in(Arrays.asList(names.split(","))));

You're accidentally nesting two in operations here:

1. First, root.get(APDetails_.names).in(...) already creates a predicate checking if the names field is in the provided list.
2. Then you wrap that entire predicate with builder.in(...), which adds an extra, unnecessary in() clause to the query. That's why the generated HQL has two in keywords, causing the syntax error.

Correct Implementation #

You don't need to use builder.in() here — the in() method on the Path object (returned by root.get()) already gives you the valid predicate you need. Here's the fixed code:

public List<APDetails> getWP(String names) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<APDetails> query = builder.createQuery(APDetails.class);
    Root<APDetails> root = query.from(APDetails.class);
    
    // Split the input string and trim any whitespace from each element
    List<String> nameList = Arrays.stream(names.split(","))
                                  .map(String::trim)
                                  .collect(Collectors.toList());
    
    // Create the IN predicate directly from the Path object
    Predicate hasA = root.get(APDetails_.names).in(nameList);
    
    query.where(hasA); // No need for builder.and() if you only have one predicate
    List<APDetails> APs = em.createQuery(query).getResultList();
    return APs;
}

Key Improvements & Notes #

• Removed duplicate in calls: The predicate is now correctly created with root.get(APDetails_.names).in(nameList), which generates a single valid in clause in the HQL.
• Trimmed whitespace: When splitting the input string, we added map(String::trim) to handle cases where there are spaces after commas (like your input ABC MKL-56-2,ABC MKL-56-3 — though in this case there's no space, it's a safe practice to avoid missing matches due to accidental whitespace).
• Simplified where clause: Since you only have one predicate, you don't need builder.and(hasA) — just pass the predicate directly to query.where().

Edge Case Handling (Optional) #

To make your method more robust, you might want to handle cases where the input names string is empty or null:

if (names == null || names.trim().isEmpty()) {
    // Return empty list or handle as needed
    return Collections.emptyList();
}

This prevents creating an empty IN clause, which could cause another error depending on your JPA provider.

内容的提问来源于stack exchange，提问作者Utkarsh Saraf