Spring Boot+Spring Data JPA：如何用@SqlResultSetMapping获取复杂原生查询结果集

在Spring Boot + Spring Data JPA项目中，需要执行复杂原生SQL查询，将结果直接映射到非实体类ProductOrderInvoiceDTO用于API返回，但在Repository使用resultSetMapping="ProductOrderInvoiceMapping"时出现cannot find symbol错误，现有实现无法正常工作。

现有代码 #

ProductOrderInvoiceDTO（非实体类） #

package co.com.ssc.txdb.domain;

import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.SqlResultSetMapping;
import lombok.Data;

@Data
@SqlResultSetMapping(
    name = "ProductOrderInvoiceMapping",
    classes = {
        @ConstructorResult(
            targetClass = ProductOrderInvoiceDTO.class,
            columns = {
                @ColumnResult(name = "userData", type = String.class),
                @ColumnResult(name = "contactData", type = String.class),
                @ColumnResult(name = "customerGroupName", type = String.class),
                @ColumnResult(name = "productOrderData", type = String.class),
                @ColumnResult(name = "invoiceData", type = String.class),
                @ColumnResult(name = "productName", type = String.class),
                @ColumnResult(name = "productOrderStartDate", type = String.class),
                @ColumnResult(name = "lastInvoicePaymentDate", type = String.class),
                @ColumnResult(name = "invoiceDetailsData", type = String.class),
                @ColumnResult(name = "firstInvoiceCreatingDate", type = String.class)
            }
        )
    }
)
public class ProductOrderInvoiceDTO {

    private String userData;
    private String contactData;
    private String customerGroupName;
    private String productOrderData;
    private String invoiceData;
    private String productName;
    private String productOrderStartDate;
    private String lastInvoicePaymentDate;
    private String invoiceDetailsData;
    private String firstInvoiceCreatingDate;

    public ProductOrderInvoiceDTO(String userData, String contactData, String customerGroupName, String productOrderData, String invoiceData, String productName, String productOrderStartDate, String lastInvoicePaymentDate, String invoiceDetailsData, String firstInvoiceCreatingDate) {
        this.userData = userData;
        this.contactData = contactData;
        this.customerGroupName = customerGroupName;
        this.productOrderData = productOrderData;
        this.invoiceData = invoiceData;
        this.productName = productName;
        this.productOrderStartDate = productOrderStartDate;
        this.lastInvoicePaymentDate = lastInvoicePaymentDate;
        this.invoiceDetailsData = invoiceDetailsData;
        this.firstInvoiceCreatingDate = firstInvoiceCreatingDate;
    }
}

Repository #

package co.com.ssc.txdb.repository;

import co.com.ssc.txdb.domain.ProductOrderInvoiceDTO;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import javax.persistence.SqlResultSetMapping;

@Repository
public interface ProductOrderInvoiceRepository extends JpaRepository<ProductOrderInvoiceDTO, Long>{

    @Query(value = "select " 
            + "(select au.json_data from auth_user_contact auc join auth_user au "
            + "on (auc.user_id = au.id) where auc.contact_id = c.seq_id and "
            + "(auc.seller_profile_id = 1 or auc.seller_profile_id is null) limit 1), " 
            + "c.json_data, "
            + "cg.name, " 
            + "po.json_data, " 
            + "i.json_data, " 
            + "MIN(p.name), "
            + "(select received_start_date from productorder where product_id is not null and contact_id = c.seq_id order by seq_id limit 1), " 
            + "(select sip.transaction_date from invoice_payment sip where sip.contact_id = c.seq_id order by sip.seq_id desc limit 1), "
            + "id.json_data, "
            + "(select creating_date from invoice where contact_id = c.seq_id and type_invoice=1 order by seq_id asc limit 1) "
            + "from "
            + "invoice_details id join invoice i on (id.invoice_id = i.seq_id) " 
            + "join contact c on (i.contact_id = c.seq_id) "
            + "join customergroup cg on (c.customergroup_id = cg.seq_id) "
            + "left join productorder po on (i.productorder_id = po.seq_id) " 
            + "left join productorder_detail pd on (po.seq_id = pd.productorder_id) " 
            + "left join product p on (id.product_id = p.seq_id) " 
            + "where " 
            + "i.type_invoice = 1 " 
            + "group by " 
            + "id.json_data,i.seq_id,i.json_data, " 
            + "c.json_data,c.seq_id,cg.name, "
            + "po.json_data " 
            + "order by c.seq_id, c.json_data, po.json_data, i.seq_id ",
            nativeQuery = true, resultSetMapping ="ProductOrderInvoiceMapping")
    List<ProductOrderInvoiceDTO> getInvoiceDetails();
}

1. @SqlResultSetMapping不能直接标注在非实体类上：JPA仅识别@Entity标记的实体类上的SqlResultSetMapping注解，非实体类上的该注解不会被JPA容器加载，导致找不到映射名称。
2. Repository继承错误：JpaRepository的泛型参数要求是JPA实体类（带@Entity），ProductOrderInvoiceDTO不是实体类，继承JpaRepository本身就是错误用法。
3. 原生SQL未给列别名：原SQL查询的列未指定和@ColumnResult中name匹配的别名，即使映射正确，也无法对应到DTO的构造参数。

方案一：将SqlResultSetMapping移到实体类，自定义Repository实现 #

步骤1：修改DTO，移除@SqlResultSetMapping #

package co.com.ssc.txdb.domain;

import lombok.Data;

@Data
public class ProductOrderInvoiceDTO {

    private String userData;
    private String contactData;
    private String customerGroupName;
    private String productOrderData;
    private String invoiceData;
    private String productName;
    private String productOrderStartDate;
    private String lastInvoicePaymentDate;
    private String invoiceDetailsData;
    private String firstInvoiceCreatingDate;

    public ProductOrderInvoiceDTO(String userData, String contactData, String customerGroupName, String productOrderData, String invoiceData, String productName, String productOrderStartDate, String lastInvoicePaymentDate, String invoiceDetailsData, String firstInvoiceCreatingDate) {
        this.userData = userData;
        this.contactData = contactData;
        this.customerGroupName = customerGroupName;
        this.productOrderData = productOrderData;
        this.invoiceData = invoiceData;
        this.productName = productName;
        this.productOrderStartDate = productOrderStartDate;
        this.lastInvoicePaymentDate = lastInvoicePaymentDate;
        this.invoiceDetailsData = invoiceDetailsData;
        this.firstInvoiceCreatingDate = firstInvoiceCreatingDate;
    }
}

步骤2：在任意实体类上添加@SqlResultSetMapping #

比如选择项目中已有的Invoice实体类：

import javax.persistence.Entity;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import co.com.ssc.txdb.domain.ProductOrderInvoiceDTO;

@Entity
@SqlResultSetMapping(
    name = "ProductOrderInvoiceMapping",
    classes = {
        @ConstructorResult(
            targetClass = ProductOrderInvoiceDTO.class,
            columns = {
                @ColumnResult(name = "userData", type = String.class),
                @ColumnResult(name = "contactData", type = String.class),
                @ColumnResult(name = "customerGroupName", type = String.class),
                @ColumnResult(name = "productOrderData", type = String.class),
                @ColumnResult(name = "invoiceData", type = String.class),
                @ColumnResult(name = "productName", type = String.class),
                @ColumnResult(name = "productOrderStartDate", type = String.class),
                @ColumnResult(name = "lastInvoicePaymentDate", type = String.class),
                @ColumnResult(name = "invoiceDetailsData", type = String.class),
                @ColumnResult(name = "firstInvoiceCreatingDate", type = String.class)
            }
        )
    }
)
public class Invoice {
    // 实体类原有字段、注解等内容
}

步骤3：自定义Repository实现（不继承JpaRepository） #

package co.com.ssc.txdb.repository;

import co.com.ssc.txdb.domain.ProductOrderInvoiceDTO;
import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

@Repository
public class ProductOrderInvoiceRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @SuppressWarnings("unchecked")
    public List<ProductOrderInvoiceDTO> getInvoiceDetails() {
        String sql = "select " 
                + "(select au.json_data from auth_user_contact auc join auth_user au "
                + "on (auc.user_id = au.id) where auc.contact_id = c.seq_id and "
                + "(auc.seller_profile_id = 1 or auc.seller_profile_id is null) limit 1) as userData, " 
                + "c.json_data as contactData, "
                + "cg.name as customerGroupName, " 
                + "po.json_data as productOrderData, " 
                + "i.json_data as invoiceData, " 
                + "MIN(p.name) as productName, "
                + "(select received_start_date from productorder where product_id is not null and contact_id = c.seq_id order by seq_id limit 1) as productOrderStartDate, " 
                + "(select sip.transaction_date from invoice_payment sip where sip.contact_id = c.seq_id order by sip.seq_id desc limit 1) as lastInvoicePaymentDate, "
                + "id.json_data as invoiceDetailsData, "
                + "(select creating_date from invoice where contact_id = c.seq_id and type_invoice=1 order by seq_id asc limit 1) as firstInvoiceCreatingDate "
                + "from "
                + "invoice_details id join invoice i on (id.invoice_id = i.seq_id) " 
                + "join contact c on (i.contact_id = c.seq_id) "
                + "join customergroup cg on (c.customergroup_id = cg.seq_id) "
                + "left join productorder po on (i.productorder_id = po.seq_id) " 
                + "left join productorder_detail pd on (po.seq_id = pd.productorder_id) " 
                + "left join product p on (id.product_id = p.seq_id) " 
                + "where " 
                + "i.type_invoice = 1 " 
                + "group by " 
                + "id.json_data,i.seq_id,i.json_data, " 
                + "c.json_data,c.seq_id,cg.name, "
                + "po.json_data " 
                + "order by c.seq_id, c.json_data, po.json_data, i.seq_id ";

        return entityManager.createNativeQuery(sql, "ProductOrderInvoiceMapping")
                .getResultList();
    }
}

方案二：使用Spring Data JPA的投影（更简洁） #

如果DTO的构造参数和查询列顺序、类型完全匹配，可直接用原生查询返回DTO，无需SqlResultSetMapping：

步骤1：保留现有DTO（确保构造函数正确） #

步骤2：修改Repository接口 #

package co.com.ssc.txdb.repository;

import co.com.ssc.txdb.domain.ProductOrderInvoiceDTO;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface ProductOrderInvoiceRepository extends Repository<ProductOrderInvoiceDTO, Long> {

    @Query(value = "select " 
            + "(select au.json_data from auth_user_contact auc join auth_user au "
            + "on (auc.user_id = au.id) where auc.contact_id = c.seq_id and "
            + "(auc.seller_profile_id = 1 or auc.seller_profile_id is null) limit 1), " 
            + "c.json_data, "
            + "cg.name, " 
            + "po.json_data, " 
            + "i.json_data, " 
            + "MIN(p.name), "
            + "(select received_start_date from productorder where product_id is not null and contact_id = c.seq_id order by seq_id limit 1), " 
            + "(select sip.transaction_date from invoice_payment sip where sip.contact_id = c.seq_id order by sip.seq_id desc limit 1), "
            + "id.json_data, "
            + "(select creating_date from invoice where contact_id = c.seq_id and type_invoice=1 order by seq_id asc limit 1) "
            + "from "
            + "invoice_details id join invoice i on (id.invoice_id = i.seq_id) " 
            + "join contact c on (i.contact_id = c.seq_id) "
            + "join customergroup cg on (c.customergroup_id = cg.seq_id) "
            + "left join productorder po on (i.productorder_id = po.seq_id) " 
            + "left join productorder_detail pd on (po.seq_id = pd.productorder_id) " 
            + "left join product p on (id.product_id = p.seq_id) " 
            + "where " 
            + "i.type_invoice = 1 " 
            + "group by " 
            + "id.json_data,i.seq_id,i.json_data, " 
            + "c.json_data,c.seq_id,cg.name, "
            + "po.json_data " 
            + "order by c.seq_id, c.json_data, po.json_data, i.seq_id ",
            nativeQuery = true)
    List<ProductOrderInvoiceDTO> getInvoiceDetails();
}

注意：这种方式要求查询结果的列顺序、数量、类型必须和DTO构造函数的参数完全一致。

• 原生SQL查询的列信息必须和DTO的构造参数匹配：方案一通过别名匹配@ColumnResult的name，方案二通过顺序和类型直接匹配。
• 不要给非实体类标注JPA实体相关注解，SqlResultSetMapping必须放在实体类或ORM配置文件中。
• 处理非实体类查询时，优先考虑Spring Data的投影机制，或自定义EntityManager实现，避免错误使用JpaRepository。

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