You can define the following named native query with appropriate sql result set mapping:
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.ConstructorResult;
import javax.persistence.ColumnResult;
@Entity
@NamedNativeQuery(
name = "find_stock_akhir_dto",
query =
"SELECT " +
" stock_akhir.product_id AS productId, " +
" stock_akhir.product_code AS productCode, " +
" SUM(stock_akhir.qty) as stockAkhir " +
"FROM book_stock stock_akhir " +
"where warehouse_code = :warehouseCode " +
" AND product_code IN :productCodes " +
"GROUP BY product_id, product_code, warehouse_id, warehouse_code",
resultSetMapping = "stock_akhir_dto"
)
@SqlResultSetMapping(
name = "stock_akhir_dto",
classes = @ConstructorResult(
targetClass = StockAkhirDto.class,
columns = {
@ColumnResult(name = "productId", type = Long.class),
@ColumnResult(name = "productCode", type = String.class),
@ColumnResult(name = "stockAkhir", type = Integer.class)
}
)
)
public class SomeEntity
{
}
and then use it:
@Repository
public interface StockRepository extends RevisionRepository<Stock, Long, Integer>, JpaRepository<Stock, Long> {
@Query(name = "find_stock_akhir_dto", nativeQuery = true)
List<StockAkhirDto> findStockAkhirPerProductIn(
@Param("warehouseCode") String warehouseCode,
@Param("productCodes") Set<String> productCode
);
}