如何用Spring JPA Specification构建含NOT EXISTS与自连接的SQL查询？

Let's break down how to build the Specification that matches your target SQL query. First, we'll start with a mapped entity class, then construct the Specification to handle all the conditions, subquery, grouping, and projection logic.

Step 1: Entity Class #

First, define your entity to map directly to table_name. Adjust field types to match your actual database schema:

@Entity
@Table(name = "table_name")
public class TableEntity {
    @Id
    private Long id;
    
    private String col1;
    private String col2;
    private Integer col3;
    private Integer col4;
    private String status;
    
    @Column(name = "parent_id")
    private Long parentId; // Maps to the parent_id column

    // Getters and setters
}

Step 2: Specification Implementation #

Now create the Specification class that replicates your SQL logic exactly:

import jakarta.persistence.criteria.*;
import org.springframework.data.jpa.domain.Specification;

import java.util.Arrays;
import java.util.List;

public class TableSpecification implements Specification<TableEntity> {

    // Define filter values (make these configurable if needed)
    private final List<Integer> targetCol4Values = Arrays.asList(1, 2, 3);
    private final List<String> targetStatusValues = Arrays.asList("STATUS_1", "STATUS_2");
    private final List<String> subqueryStatusValues = Arrays.asList("STATUS_3", "STATUS_4");

    @Override
    public Predicate toPredicate(Root<TableEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        // 1. Build main WHERE clause predicates
        Predicate col4InPredicate = root.get("col4").in(targetCol4Values);
        Predicate statusInPredicate = root.get("status").in(targetStatusValues);

        // 2. Build the NOT EXISTS subquery
        Subquery<Long> subquery = query.subquery(Long.class);
        Root<TableEntity> t2 = subquery.from(TableEntity.class);
        
        // Match t1.id = t2.parent_id
        Predicate parentIdMatch = cb.equal(t2.get("parentId"), root.get("id"));
        // Match t2.status IN ('STATUS_3','STATUS_4')
        Predicate subqueryStatusIn = t2.get("status").in(subqueryStatusValues);
        
        subquery.select(cb.literal(1L)) // Mimic the "SELECT 1" from your SQL
                .where(cb.and(parentIdMatch, subqueryStatusIn));

        Predicate notExistsPredicate = cb.not(cb.exists(subquery));

        // Combine all main predicates with AND
        Predicate finalWherePredicate = cb.and(col4InPredicate, statusInPredicate, notExistsPredicate);

        // 3. Configure GROUP BY and SELECT projection
        // Skip this for count queries to avoid invalid SQL
        if (!query.getResultType().equals(Long.class)) {
            // Group by col1 and col2
            query.groupBy(root.get("col1"), root.get("col2"));
            
            // Select col1, col2, MAX(col3) (aliased for easy result retrieval)
            query.multiselect(
                    root.get("col1"),
                    root.get("col2"),
                    cb.max(root.get("col3")).alias("maxCol3")
            );
        }

        return finalWherePredicate;
    }
}

Step 3: Using the Specification #

Use this Specification with your JpaRepository to fetch results. You have two common options for retrieving the projected data:

Option 1: Using Tuple (No DTO Needed) #

If you don't want to create a dedicated class, use Tuple to access the results dynamically:

// Repository interface
public interface TableEntityRepository extends JpaRepository<TableEntity, Long>, JpaSpecificationExecutor<TableEntity> {
}

// In your service/controller:
List<Tuple> results = tableEntityRepository.findAll(new TableSpecification());

for (Tuple tuple : results) {
    String col1 = tuple.get("col1", String.class);
    String col2 = tuple.get("col2", String.class);
    Integer maxCol3 = tuple.get("maxCol3", Integer.class);
    // Process your data here
}

Option 2: Using a Type-Safe DTO #

For cleaner, type-safe results, create a DTO and adjust the projection logic:

// DTO class with constructor matching projection order
public class TableProjection {
    private String col1;
    private String col2;
    private Integer maxCol3;

    public TableProjection(String col1, String col2, Integer maxCol3) {
        this.col1 = col1;
        this.col2 = col2;
        this.maxCol3 = maxCol3;
    }

    // Getters
}

Modify the multiselect section in the Specification to use the DTO constructor:

query.multiselect(
        cb.construct(TableProjection.class,
                root.get("col1"),
                root.get("col2"),
                cb.max(root.get("col3"))
        )
);

Then retrieve results as:

List<TableProjection> results = tableEntityRepository.findAll(new TableSpecification());

Key Notes #

• The check for query.getResultType() != Long.class ensures count operations (like countAll(Specification)) work correctly, as count queries don't support grouping or custom projections.
• Adjust field types (e.g., Long vs Integer for id) to match your actual database schema.
• If column names differ from field names, use @Column annotations on entity fields to map them properly.

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