在@Query中使用动态VALUES实现JOIN的方法

Great question! That VALUES clause approach is a smart way to get custom ordering along with your filter, and adapting it for dynamic enum lists (1-10 values) in Spring Data JPA is totally doable. Here are a few practical, production-ready ways to implement it:

1. SpEL Expression + String Splicing (Simple & Direct) #

Since your enum list is small (1-10 values), you can dynamically build the VALUES clause as a string and inject it via a SpEL parameter. This keeps your @Query clean and aligns with your original SQL structure.

Step 1: Define Your Enum & Repository Method #

First, let's assume your enum looks like this:

public enum CommentAttribute {
    FOO, BAR, BAZ; // Add your other enum values here
}

Then create a repository method with a native query that accepts the pre-built VALUES string:

@Repository
public interface CommentRepository extends JpaRepository<Comment, Long> {
    @Query(value = """
        SELECT c.* 
        FROM comments c 
        JOIN (VALUES :valuePairs) AS x(attribute, ordering) 
            ON c.attribute = x.attribute 
        ORDER BY x.ordering
    """, nativeQuery = true)
    List<Comment> findByAttributesOrdered(@Param("valuePairs") String valuePairs);
}

Step 2: Build the Dynamic VALUES String #

Create a helper method to convert your enum list into the comma-separated (enum, order) pairs your query needs. Since we're using enums, there's no SQL injection risk here—values are strictly controlled by your enum definition:

public String buildValuePairs(List<CommentAttribute> targetAttributes) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < targetAttributes.size(); i++) {
        // Use the enum's name() for the attribute value, and index+1 for ordering
        sb.append(String.format("('%s', %d)", targetAttributes.get(i).name(), i + 1));
        if (i < targetAttributes.size() - 1) {
            sb.append(", ");
        }
    }
    return sb.toString();
}

Step 3: Call the Repository #

When you need to run the query, pass in the built string:

// Example: Dynamic list of enums
List<CommentAttribute> filterAttrs = List.of(CommentAttribute.FOO, CommentAttribute.BAZ);
String valuePairs = buildValuePairs(filterAttrs);
List<Comment> sortedComments = commentRepository.findByAttributesOrdered(valuePairs);

2. Criteria API (Type-Safe & Injection-Proof) #

If you prefer to avoid string splicing entirely, use JPA's Criteria API to dynamically build the filter and custom ordering. This is more verbose but fully type-safe and avoids any raw SQL string handling.

@Repository
public class CommentCustomRepositoryImpl implements CommentCustomRepository {
    private final EntityManager entityManager;

    public CommentCustomRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<Comment> findByAttributesOrdered(List<CommentAttribute> targetAttributes) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Comment> query = cb.createQuery(Comment.class);
        Root<Comment> commentRoot = query.from(Comment.class);

        // Filter comments to only match the target enums
        Predicate attributeFilter = commentRoot.get("attribute").in(targetAttributes);

        // Build a CASE expression to define custom ordering based on the input list
        Expression<Integer> orderExpression = cb.selectCase();
        for (int i = 0; i < targetAttributes.size(); i++) {
            orderExpression = orderExpression.when(
                cb.equal(commentRoot.get("attribute"), targetAttributes.get(i)),
                i + 1 // Order position matches the list index
            );
        }
        // Push any unmatched attributes to the end
        orderExpression = orderExpression.otherwise(targetAttributes.size() + 1);

        // Finalize the query
        query.where(attributeFilter)
             .orderBy(cb.asc(orderExpression));

        return entityManager.createQuery(query).getResultList();
    }
}

3. PostgreSQL-Specific Shortcut (If Using Postgres) #

If your database is PostgreSQL, you can leverage the array_position function to avoid the VALUES join entirely. This is a cleaner approach for Postgres users:

@Repository
public interface CommentRepository extends JpaRepository<Comment, Long> {
    @Query(value = """
        SELECT c.* 
        FROM comments c 
        WHERE c.attribute = ANY(:attributeNames)
        ORDER BY array_position(:attributeArray, c.attribute)
    """, nativeQuery = true)
    List<Comment> findByAttributesOrdered(
        @Param("attributeNames") List<String> attributeNames,
        @Param("attributeArray") String[] attributeArray
    );
}

Call it by converting your enum list to strings/arrays:

List<CommentAttribute> filterAttrs = List.of(CommentAttribute.BAR, CommentAttribute.FOO);
List<String> attrNames = filterAttrs.stream().map(CommentAttribute::name).toList();
String[] attrArray = attrNames.toArray(new String[0]);

List<Comment> sortedComments = commentRepository.findByAttributesOrdered(attrNames, attrArray);

All three methods work well for 1-10 enum values—pick the one that fits your database setup and coding style best!

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