/**
 * 基本的@NamedStoredProcedureQuery定義示例
 */
@Entity
@NamedStoredProcedureQuery(
    name = "Employee.findByDepartment",  // 命名存儲(chǔ)過(guò)程查詢的唯一標(biāo)識(shí)符
    procedureName = "GET_EMPLOYEES_BY_DEPT",  // 數(shù)據(jù)庫(kù)中存儲(chǔ)過(guò)程的實(shí)際名稱
    parameters = {
        @StoredProcedureParameter(
            name = "dept_id", 
            mode = ParameterMode.IN, 
            type = Integer.class
        ),
        @StoredProcedureParameter(
            name = "min_salary", 
            mode = ParameterMode.IN, 
            type = Double.class
        )
    },
    resultClasses = Employee.class  // 結(jié)果映射到該實(shí)體類
)
public class Employee {
    @Id
    private Long id;
    private String name;
    private Double salary;
    // 其他字段和方法...
}

/**
 * 展示不同類型的參數(shù)配置
 */
@NamedStoredProcedureQuery(
    name = "Product.calculateInventoryValue",
    procedureName = "CALC_INVENTORY_VALUE",
    parameters = {
        // IN參數(shù) - 傳入產(chǎn)品類別
        @StoredProcedureParameter(
            name = "category_id", 
            mode = ParameterMode.IN, 
            type = Integer.class
        ),
        // INOUT參數(shù) - 傳入起始日期并返回修改后的日期
        @StoredProcedureParameter(
            name = "date_range", 
            mode = ParameterMode.INOUT, 
            type = Date.class
        ),
        // OUT參數(shù) - 返回計(jì)算的總值
        @StoredProcedureParameter(
            name = "total_value", 
            mode = ParameterMode.OUT, 
            type = BigDecimal.class
        ),
        // REF_CURSOR參數(shù) - 返回詳細(xì)結(jié)果集
        @StoredProcedureParameter(
            name = "result_cursor", 
            mode = ParameterMode.REF_CURSOR, 
            type = void.class  // 實(shí)際類型由結(jié)果集映射決定
        )
    },
    resultSetMappings = {"ProductInventoryMapping"}  // 引用自定義結(jié)果集映射
)
public class Product {
    // 實(shí)體定義...
}

/**
 * 演示不同的結(jié)果集處理方法
 */
// 定義自定義結(jié)果集映射
@SqlResultSetMapping(
    name = "SalesReportMapping",
    entities = {
        @EntityResult(
            entityClass = SalesReport.class,
            fields = {
                @FieldResult(name = "id", column = "report_id"),
                @FieldResult(name = "productName", column = "product_name"),
                @FieldResult(name = "salesAmount", column = "sales_amount"),
                @FieldResult(name = "salesDate", column = "sales_date")
            }
        )
    },
    columns = {
        @ColumnResult(name = "total_revenue", type = BigDecimal.class),
        @ColumnResult(name = "market_share", type = Float.class)
    }
)
// 使用@NamedStoredProcedureQuery引用該映射
@NamedStoredProcedureQuery(
    name = "SalesReport.generateMonthlyReport",
    procedureName = "GENERATE_MONTHLY_SALES_REPORT",
    parameters = {
        @StoredProcedureParameter(
            name = "month", 
            mode = ParameterMode.IN, 
            type = Integer.class
        ),
        @StoredProcedureParameter(
            name = "year", 
            mode = ParameterMode.IN, 
            type = Integer.class
        ),
        @StoredProcedureParameter(
            name = "report_cursor", 
            mode = ParameterMode.REF_CURSOR, 
            type = void.class
        )
    },
    resultSetMappings = {"SalesReportMapping"}
)
public class SalesReport {
    // 實(shí)體定義...
}

/**
 * 演示執(zhí)行存儲(chǔ)過(guò)程查詢的方法
 */
public List<Employee> findEmployeesByDepartment(
        EntityManager entityManager, int departmentId, double minSalary) {
    // 創(chuàng)建命名存儲(chǔ)過(guò)程查詢
    StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery(
            "Employee.findByDepartment");
    // 設(shè)置輸入?yún)?shù)
    query.setParameter("dept_id", departmentId);
    query.setParameter("min_salary", minSalary);
    // 執(zhí)行查詢并返回結(jié)果
    return query.getResultList();
}
// 處理包含OUT參數(shù)的存儲(chǔ)過(guò)程
public BigDecimal calculateInventoryValue(
        EntityManager entityManager, int categoryId, Date startDate) {
    // 創(chuàng)建命名存儲(chǔ)過(guò)程查詢
    StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery(
            "Product.calculateInventoryValue");
    // 設(shè)置輸入?yún)?shù)
    query.setParameter("category_id", categoryId);
    query.setParameter("date_range", startDate);
    // 執(zhí)行查詢
    query.execute();
    // 獲取輸出參數(shù)
    Date modifiedDate = (Date) query.getOutputParameterValue("date_range");
    BigDecimal totalValue = (BigDecimal) query.getOutputParameterValue("total_value");
    // 獲取結(jié)果集
    List<Object[]> detailedResults = query.getResultList();
    // 返回計(jì)算的總值
    return totalValue;
}

/**
 * 展示批量定義和組織方法
 */
// 在實(shí)體類上批量定義多個(gè)存儲(chǔ)過(guò)程查詢
@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name = "Customer.findTopSpenders",
        procedureName = "GET_TOP_SPENDERS",
        parameters = {
            @StoredProcedureParameter(
                name = "limit_count", 
                mode = ParameterMode.IN, 
                type = Integer.class
            )
        },
        resultClasses = Customer.class
    ),
    @NamedStoredProcedureQuery(
        name = "Customer.updateLoyaltyPoints",
        procedureName = "UPDATE_LOYALTY_POINTS",
        parameters = {
            @StoredProcedureParameter(
                name = "customer_id", 
                mode = ParameterMode.IN, 
                type = Long.class
            ),
            @StoredProcedureParameter(
                name = "points", 
                mode = ParameterMode.IN, 
                type = Integer.class
            ),
            @StoredProcedureParameter(
                name = "success", 
                mode = ParameterMode.OUT, 
                type = Boolean.class
            )
        }
    )
})
public class Customer {
    // 實(shí)體定義...
}
// 使用非實(shí)體類集中管理存儲(chǔ)過(guò)程定義
@NamedStoredProcedureQueries({
    // 系統(tǒng)報(bào)表相關(guān)存儲(chǔ)過(guò)程
    @NamedStoredProcedureQuery(
        name = "Reports.dailySales",
        procedureName = "GENERATE_DAILY_SALES_REPORT",
        // 參數(shù)定義...
    ),
    @NamedStoredProcedureQuery(
        name = "Reports.inventoryStatus",
        procedureName = "GENERATE_INVENTORY_STATUS",
        // 參數(shù)定義...
    )
})
public class ReportProcedures {
    // 這不是一個(gè)實(shí)體類，僅用于組織存儲(chǔ)過(guò)程定義
}

/**
 * 演示異常處理和事務(wù)管理
 */
@Transactional
public boolean updateCustomerLoyaltyPoints(EntityManager entityManager, 
                                         long customerId, int points) {
    try {
        // 創(chuàng)建存儲(chǔ)過(guò)程查詢
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery(
                "Customer.updateLoyaltyPoints");
        // 設(shè)置參數(shù)
        query.setParameter("customer_id", customerId);
        query.setParameter("points", points);
        // 執(zhí)行存儲(chǔ)過(guò)程
        query.execute();
        // 獲取結(jié)果
        Boolean success = (Boolean) query.getOutputParameterValue("success");
        return success != null && success;
    } catch (QueryTimeoutException e) {
        // 處理查詢超時(shí)
        log.error("Stored procedure timed out: {}", e.getMessage());
        throw new ServiceException("Operation timed out, please try again later", e);
    } catch (PersistenceException e) {
        // 處理其他持久化異常
        log.error("Error executing stored procedure: {}", e.getMessage());
        throw new ServiceException("Unable to update loyalty points", e);
    }
}

/**
 * 展示最佳實(shí)踐和性能優(yōu)化
 */
// 使用緩存優(yōu)化頻繁調(diào)用的查詢
@NamedStoredProcedureQuery(
    name = "Product.getProductDetails",
    procedureName = "GET_PRODUCT_DETAILS",
    parameters = {
        @StoredProcedureParameter(
            name = "product_id", 
            mode = ParameterMode.IN, 
            type = Long.class
        )
    },
    resultClasses = ProductDetails.class
)
public class ProductQueryRepository {
    private final EntityManager entityManager;
    private final CacheManager cacheManager;
    public ProductDetails getProductDetails(Long productId) {
        // 檢查緩存
        Cache cache = cacheManager.getCache("productDetails");
        ProductDetails cachedDetails = cache.get(productId, ProductDetails.class);
        if (cachedDetails != null) {
            return cachedDetails;
        }
        // 緩存未命中，執(zhí)行存儲(chǔ)過(guò)程
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery(
                "Product.getProductDetails");
        query.setParameter("product_id", productId);
        try {
            ProductDetails details = (ProductDetails) query.getSingleResult();
            // 更新緩存
            cache.put(productId, details);
            return details;
        } catch (NoResultException e) {
            return null;
        }
    }
}