如何映射查询以获取数据？DTO构造函数映射与数据返回问题排查

Hey, I feel your pain—spending hours on a seemingly small issue like this is the worst. Let's break down what's going wrong and fix it step by step.

First, let's identify the core problems with your current code:

• Your native SQL query only selects wm.action_description, but your StockRecoveryDTO expects 6 fields in its constructor. JPA can't map a single value to a constructor that needs 6 parameters, which is why you're getting empty arrays even with a 200 status.
• You haven't set up the proper mapping for JPA to convert the query results into your DTO instances.

Here are two solid solutions to fix this:

1. Constructor Mapping (The Direct Way) #

If you want to stick with your DTO class, you need to make sure your query returns all the fields your constructor needs, and tell JPA how to use that constructor.

Option A: Switch to JPQL with NEW Syntax #

This is the simplest approach if you don't absolutely need native SQL. Update your repository method like this:

@Repository
public interface ManagementRepository extends JpaRepository<Management,Long>,ManagementRepositoryCustom {
    @Query("SELECT NEW com.example.dto.StockRecoveryDTO(m.idProduct, m.date, m.quantityProduct, m.actionDescription, m.idAction, m.quantity) " +
            "FROM Management m " +
            "WHERE m.actionDescription = :action_description")
    List<StockRecoveryDTO> findByDog(@Param("action_description") String action_description);
}

Notes:

• Replace m.idProduct, m.date, etc., with the actual property names from your Management entity class (not the database column names).
• The order of fields in the SELECT clause must exactly match the order of parameters in your StockRecoveryDTO constructor.
• Remove nativeQuery = true since this is JPQL, not raw SQL.

Option B: Use @SqlResultSetMapping for Native SQL #

If you need to keep using native SQL, define a result set mapping to tell JPA how to map the query results to your DTO:

First, add this mapping to your Management entity class:

@Entity
@SqlResultSetMapping(
    name = "StockRecoveryDTOMapping",
    classes = @ConstructorResult(
        targetClass = StockRecoveryDTO.class,
        columns = {
            @ColumnResult(name = "id_product", type = Long.class),
            @ColumnResult(name = "date", type = String.class),
            @ColumnResult(name = "quantity_product", type = Integer.class),
            @ColumnResult(name = "action_description", type = String.class),
            @ColumnResult(name = "id_action", type = Long.class),
            @ColumnResult(name = "quantity", type = String.class)
        }
    )
)
public class Management {
    // Your existing entity properties and annotations here
}

Then update your repository query to include all required fields and reference the mapping:

@Repository
public interface ManagementRepository extends JpaRepository<Management,Long>,ManagementRepositoryCustom {
    @Query(value = "SELECT wm.id_product, wm.date, wm.quantity_product, wm.action_description, wm.id_action, wm.quantity " +
            "FROM testdb.warehouse_management wm " +
            "WHERE wm.action_description = :action_description ", 
            nativeQuery = true,
            resultSetMapping = "StockRecoveryDTOMapping")
    List<StockRecoveryDTO> findByDog(@Param("action_description") String action_description);
}

Make sure the column names in your SQL match exactly what's in @ColumnResult, and the order matches your DTO constructor's parameter order.

2. Interface Projection (A Simpler Alternative) #

If you don't need the DTO class for anything other than holding query results, interface projection is a cleaner option—no constructors required.

First, define a projection interface with getter methods matching the fields you want:

package com.example.dto;
public interface StockRecoveryProjection {
    Long getIdProduct();
    String getDate();
    int getQuantityProduct();
    String getActionDescription();
    Long getIdAction();
    String getQuantity();
}

Then update your repository method to return this interface. For JPQL:

@Query("SELECT m.idProduct as idProduct, m.date as date, m.quantityProduct as quantityProduct, m.actionDescription as actionDescription, m.idAction as idAction, m.quantity as quantity " +
        "FROM Management m " +
        "WHERE m.actionDescription = :action_description")
List<StockRecoveryProjection> findByDog(@Param("action_description") String action_description);

Or for native SQL (make sure to alias columns to match the getter names):

@Query(value = "SELECT wm.id_product as idProduct, wm.date as date, wm.quantity_product as quantityProduct, wm.action_description as actionDescription, wm.id_action as idAction, wm.quantity as quantity " +
        "FROM testdb.warehouse_management wm " +
        "WHERE wm.action_description = :action_description ", 
        nativeQuery = true)
List<StockRecoveryProjection> findByDog(@Param("action_description") String action_description);

JPA will automatically create a proxy implementation of this interface and populate the values for you.

Quick Recap of What Went Wrong #

Your original query only pulled one field, but your DTO needed six—JPA couldn't create valid DTO instances, hence the empty array. Fixing the query to return all required fields and setting up the right mapping (either constructor-based or projection) will get you the data you need.

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