使用JPA调用Postgres函数返回<unnamed portal 4>，求结果获取方法

Hey there! That <unnamed portal 4> output you're seeing is Postgres' way of telling you that the stored procedure returned a result set (a "portal" in Postgres terms), but your JPA StoredProcedureQuery isn't properly set up to fetch and map that result to your Java objects. Let's walk through how to fix this step by step, tailored to your Java 8/Spring Boot 2/JPA/Postgres stack.

1. First, Understand the Root Cause #

Postgres uses portals to represent active result sets from queries or stored procedures. When you see <unnamed portal X>, it means JPA has detected the result set but hasn't been instructed to extract the actual data from it. This usually happens if:

• Your stored procedure returns a tabular result set, but you haven't configured the query to map it to a Java class or DTO.
• You're not calling the right method to retrieve the results (like skipping getResultList() after executing the query).

2. Fix #1: Use @NamedStoredProcedureQuery for Mapped Entities #

If your stored procedure returns data that maps directly to an existing JPA entity, define a named stored procedure query on your entity class. Here's a concrete example:

Example Entity with Stored Procedure Mapping #

import javax.persistence.*;
import java.util.List;

@Entity
@NamedStoredProcedureQuery(
    name = "getUserOrders",
    procedureName = "fetch_user_orders", // Your Postgres stored procedure name
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "user_id", type = Long.class)
    },
    resultClasses = Order.class // The entity to map the result set to
)
public class Order {
    @Id
    private Long id;
    private String orderNumber;
    private Double totalAmount;
    // Getters, setters, and default constructor
}

Execute It in Your Service/Repository #

@Service
public class OrderService {
    @PersistenceContext
    private EntityManager em;

    public List<Order> getUserOrders(Long userId) {
        StoredProcedureQuery query = em.createNamedStoredProcedureQuery("getUserOrders");
        query.setParameter("user_id", userId);
        // This is critical: call getResultList() to pull actual data from the portal
        return query.getResultList();
    }
}

3. Fix #2: Manual Configuration for DTOs or Unmapped Results #

If you're returning a custom result set that doesn't map to an entity (like a summary DTO), use manual result mapping:

Step 1: Create a DTO Class #

public class OrderSummaryDTO {
    private String orderNumber;
    private Double totalAmount;

    // Constructor matching the stored procedure's result column order
    public OrderSummaryDTO(String orderNumber, Double totalAmount) {
        this.orderNumber = orderNumber;
        this.totalAmount = totalAmount;
    }

    // Getters
}

Step 2: Execute and Map the Result #

public List<OrderSummaryDTO> getOrderSummaries(Long userId) {
    StoredProcedureQuery query = em.createStoredProcedureQuery("fetch_order_summaries");
    query.registerStoredProcedureParameter("user_id", Long.class, ParameterMode.IN);
    query.setParameter("user_id", userId);

    // For Postgres procedures that return a cursor explicitly, register the REF_CURSOR parameter
    query.registerStoredProcedureParameter(ParameterMode.REF_CURSOR, void.class);
    
    // Map raw result rows to your DTO
    return query.getResultList().stream()
            .map(row -> new OrderSummaryDTO((String) row[0], (Double) row[1]))
            .collect(Collectors.toList());
}

4. Handling Multiple Result Sets #

If your stored procedure returns multiple result sets, iterate through them using getNextResult():

StoredProcedureQuery query = em.createStoredProcedureQuery("get_multiple_results");
query.execute();

// First result set
List<Order> orders = query.getResultList();
// Move to the next result set
query.getNextResult();
List<Payment> payments = query.getResultList();

5. Verify Your Postgres Stored Procedure #

Make sure your procedure is actually returning a result set (not just output parameters). For example:

-- Procedure returning a result set
CREATE OR REPLACE PROCEDURE fetch_user_orders(user_id BIGINT)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT id, order_number, total_amount FROM orders WHERE user_id = $1;
END;
$$;

-- Or a function (often preferred for returning result sets in Postgres)
CREATE OR REPLACE FUNCTION fetch_user_orders(user_id BIGINT)
RETURNS SETOF orders
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT * FROM orders WHERE user_id = $1;
END;
$$;

Key Takeaways #

• Always call getResultList() (or getSingleResult() for single records) after executing the query—this is how you extract usable data from the portal.
• Map the result set to either an existing entity or a custom DTO to avoid getting the raw portal reference.
• For Postgres, pay attention to whether your procedure uses a REF_CURSOR or directly returns a set of rows.

内容的提问来源于stack exchange，提问作者B Rajasekhar Reddy