Detailed explanation of Java stored procedure call @NamedStoredProcedureQuery example

introduction

In enterprise-level Java application development, stored procedures, as a collection of precompiled SQL statements in the database, have the advantages of efficient execution, reducing network traffic and enhancing security. The Java Persistence API (JPA) 2.1 specification introduces the @NamedStoredProcedureQuery annotation, providing a standardized and type-safe way to call stored procedures in Java applications. This article will explore in-depth how @NamedStoredProcedureQuery uses, parameter mapping, result set processing and practical experience to help developers integrate and call database stored procedures more efficiently in Java applications.

1. @NamedStoredProcedureQuery Basics

The @NamedStoredProcedureQuery annotation is an important feature introduced by the JPA 2.1 specification to define named stored procedure queries at the entity class or package level. This annotation provides a declarative method to define and manage stored procedure calls by encapsulating the details of stored procedure calls in metadata. Using @NamedStoredProcedureQuery can improve the readability and maintainability of your code while reducing the risk of errors caused by hard-coded SQL strings. This annotation also supports type mapping and result set mapping of stored procedure parameters, making stored procedure calls more type-safe.

/**
  * Basic @NamedStoredProcedureQuery definition example
  */
@Entity
@NamedStoredProcedureQuery(
    name = "",  // Name the unique identifier for stored procedure query    procedureName = "GET_EMPLOYEES_BY_DEPT",  // The actual name of the stored procedure in the database    parameters = {
        @StoredProcedureParameter(
            name = "dept_id", 
            mode = , 
            type = 
        ),
        @StoredProcedureParameter(
            name = "min_salary", 
            mode = , 
            type = 
        )
    },
    resultClasses =   // The result is mapped to the entity class)
public class Employee {
    @Id
    private Long id;
    private String name;
    private Double salary;
    // Other fields and methods...}

2. Parameter configuration and mapping

In @NamedStoredProcedureQuery, parameter configuration and mapping are implemented through @StoredProcedureParameter annotation. Each @StoredProcedureParameter defines a stored procedure parameter, including parameter name, parameter pattern, and Java type. Supported parameter modes include IN (input parameter), OUT (output parameter), INOUT (parameter that is both input and output), and REF_CURSOR (cursor used to return the result set). JPA providers will automatically process parameter type conversion and map Java types to the corresponding database type. For complex type parameters, special processing may be required based on the specific JPA implementation and database type.

/**
  * Show different types of parameter configurations
  */
@NamedStoredProcedureQuery(
    name = "",
    procedureName = "CALC_INVENTORY_VALUE",
    parameters = {
        // IN parameter - Incoming product category        @StoredProcedureParameter(
            name = "category_id", 
            mode = , 
            type = 
        ),
        // INOUT parameter - Pass in the start date and return the modified date        @StoredProcedureParameter(
            name = "date_range", 
            mode = , 
            type = 
        ),
        // OUT parameter - Returns the calculated total value        @StoredProcedureParameter(
            name = "total_value", 
            mode = , 
            type = 
        ),
        // REF_CURSOR parameter - Returns the detailed result set        @StoredProcedureParameter(
            name = "result_cursor", 
            mode = ParameterMode.REF_CURSOR, 
            type =   // The actual type is determined by the result set mapping        )
    },
    resultSetMappings = {"ProductInventoryMapping"}  // Reference custom result set mapping)
public class Product {
    // Entity definition...}

3. Result set processing

@NamedStoredProcedureQuery supports multiple result set processing methods to accommodate different stored procedure return types. For stored procedures that return a single result set, you can use the resultClasses property to map the results to the entity class directly. For situations where multiple result sets are returned or complex mappings are required, the resultSetMappings property can be used to reference the predefined @SqlResultSetMapping. For stored procedures that return REF_CURSOR, the JPA provider converts the cursor to a Java result set. The results of stored procedures can also be obtained and processed through the return value of the createStoredProcedureQuery method of EntityManager.

/**
  * Demonstrate different results set processing methods
  */
// Define custom result set mapping@SqlResultSetMapping(
    name = "SalesReportMapping",
    entities = {
        @EntityResult(
            entityClass = ,
            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 = ),
        @ColumnResult(name = "market_share", type = )
    }
)
// Reference the map using @NamedStoredProcedureQuery@NamedStoredProcedureQuery(
    name = "",
    procedureName = "GENERATE_MONTHLY_SALES_REPORT",
    parameters = {
        @StoredProcedureParameter(
            name = "month", 
            mode = , 
            type = 
        ),
        @StoredProcedureParameter(
            name = "year", 
            mode = , 
            type = 
        ),
        @StoredProcedureParameter(
            name = "report_cursor", 
            mode = ParameterMode.REF_CURSOR, 
            type = 
        )
    },
    resultSetMappings = {"SalesReportMapping"}
)
public class SalesReport {
    // Entity definition...}

4. Execute stored procedure query

After defining @NamedStoredProcedureQuery, the named stored procedure query can be easily executed through EntityManager. EntityManager provides the createNamedStoredProcedureQuery method, accepts the name of the named stored procedure query as a parameter, and returns the StoredProcedureQuery object. Through the StoredProcedureQuery object, you can set parameter values, execute queries and get results. For stored procedures containing OUT or INOUT parameters, the output parameter value can be obtained through the getOutputParameterValue method after execution. For stored procedures that return a result set, you can call the getResultList or getSingleResult method to get the result.

/**
  * Demonstrate how to execute stored procedure queries
  */
public List&lt;Employee&gt; findEmployeesByDepartment(
        EntityManager entityManager, int departmentId, double minSalary) {
    // Create a named stored procedure query    StoredProcedureQuery query = (
            "");
    // Set input parameters    ("dept_id", departmentId);
    ("min_salary", minSalary);
    // Execute the query and return the result    return ();
}
// Handle stored procedures containing OUT parameterspublic BigDecimal calculateInventoryValue(
        EntityManager entityManager, int categoryId, Date startDate) {
    // Create a named stored procedure query    StoredProcedureQuery query = (
            "");
    // Set input parameters    ("category_id", categoryId);
    ("date_range", startDate);
    // Execute query    ();
    // Get the output parameters    Date modifiedDate = (Date) ("date_range");
    BigDecimal totalValue = (BigDecimal) ("total_value");
    // Get the result set    List&lt;Object[]&gt; detailedResults = ();
    // Return the calculated total value    return totalValue;
}

5. Batch definition and organization

For complex applications that contain a large number of stored procedure calls, multiple named stored procedure queries can be defined in batches using the @NamedStoredProcedureQueries annotation. In order to maintain the maintainability of the code, it is recommended to organize stored procedure definitions according to functional modules or business areas. Stored procedure queries directly related to the entity can be defined at the entity class level, or special non-entity classes can be created to centrally manage stored procedure definitions. Through reasonable naming conventions, such as the "{entity name}.{operation}" format, the purpose of stored procedure query can be clearer.

/**
  * Show batch definition and organizational method
  */
// Batch define multiple stored procedure queries on entity class@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
        name = "",
        procedureName = "GET_TOP_SPENDERS",
        parameters = {
            @StoredProcedureParameter(
                name = "limit_count", 
                mode = , 
                type = 
            )
        },
        resultClasses = 
    ),
    @NamedStoredProcedureQuery(
        name = "",
        procedureName = "UPDATE_LOYALTY_POINTS",
        parameters = {
            @StoredProcedureParameter(
                name = "customer_id", 
                mode = , 
                type = 
            ),
            @StoredProcedureParameter(
                name = "points", 
                mode = , 
                type = 
            ),
            @StoredProcedureParameter(
                name = "success", 
                mode = , 
                type = 
            )
        }
    )
})
public class Customer {
    // Entity definition...}
// Centrally manage stored procedure definitions using non-entity classes@NamedStoredProcedureQueries({
    // System report-related stored procedures    @NamedStoredProcedureQuery(
        name = "",
        procedureName = "GENERATE_DAILY_SALES_REPORT",
        // Parameter definition...    ),
    @NamedStoredProcedureQuery(
        name = "",
        procedureName = "GENERATE_INVENTORY_STATUS",
        // Parameter definition...    )
})
public class ReportProcedures {
    // This is not an entity class, only used to organize stored procedure definitions}

6. Handle exceptions and transactions

Correct handling of exceptions and managing transactions are key to ensuring data consistency when calling stored procedures. Stored procedure calls may throw PersistenceException or subclasses thereof, such as QueryTimeoutException or LockTimeoutException. Appropriate exception handling logic should be implemented, including recording error messages and providing user-friendly error messages. Stored procedure calls inherit the current JPA transaction context and can use @Transactional annotation or programmatic transaction management to control transaction boundaries. To ensure data consistency, transaction management best practices should be followed.

/**
  * Demonstrate exception handling and transaction management
  */
@Transactional
public boolean updateCustomerLoyaltyPoints(EntityManager entityManager, 
                                         long customerId, int points) {
    try {
        // Create stored procedure query        StoredProcedureQuery query = (
                "");
        // Set parameters        ("customer_id", customerId);
        ("points", points);
        // Execute stored procedures        ();
        // Get results        Boolean success = (Boolean) ("success");
        return success != null &amp;&amp; success;
    } catch (QueryTimeoutException e) {
        // Processing query timeout        ("Stored procedure timed out: {}", ());
        throw new ServiceException("Operation timed out, please try again later", e);
    } catch (PersistenceException e) {
        // Handle other persistent exceptions        ("Error executing stored procedure: {}", ());
        throw new ServiceException("Unable to update loyalty points", e);
    }
}

7. Best practices and performance optimization

When using @NamedStoredProcedureQuery in a real project, following some best practices can improve code quality and application performance. Named stored procedure query definitions should be kept in sync with database stored procedures. It is recommended to use the database migration tool to manage the version of stored procedures. For frequently called stored procedures, you can consider using result cache to improve performance. Parameter naming should be descriptive and consistent with stored procedure documentation. The complexity of stored procedures should be controlled within a reasonable range to avoid excessive functionality in a single stored procedure. For compatibility issues with different databases, JPA provider-specific extension functions should be used or database dialect abstraction layer should be implemented.

/**
  * Demonstrate best practices and performance optimizations
  */
// Use cache to optimize frequently called queries@NamedStoredProcedureQuery(
    name = "",
    procedureName = "GET_PRODUCT_DETAILS",
    parameters = {
        @StoredProcedureParameter(
            name = "product_id", 
            mode = , 
            type = 
        )
    },
    resultClasses = 
)
public class ProductQueryRepository {
    private final EntityManager entityManager;
    private final CacheManager cacheManager;
    public ProductDetails getProductDetails(Long productId) {
        // Check the cache        Cache cache = ("productDetails");
        ProductDetails cachedDetails = (productId, );
        if (cachedDetails != null) {
            return cachedDetails;
        }
        // Cache misses and execute stored procedures        StoredProcedureQuery query = (
                "");
        ("product_id", productId);
        try {
            ProductDetails details = (ProductDetails) ();
            // Update cache            (productId, details);
            return details;
        } catch (NoResultException e) {
            return null;
        }
    }
}

Summarize

The @NamedStoredProcedureQuery annotation provides a standardized and type-safe way to call database stored procedures in Java applications. By declaratively defining stored procedure calls, developers can reduce hard-coded SQL strings and improve code readability and maintainability. This article discusses the basic knowledge of @NamedStoredProcedureQuery, parameter configuration and mapping, result set processing, execution methods, batch definition and organization, exception and transaction processing, and best practices and performance optimization. By using @NamedStoredProcedureQuery rationally, Java developers can effectively integrate database stored procedures, leverage the performance advantages of stored procedures while keeping the code clear and maintainable. In actual projects, appropriate technical solutions should be selected based on specific needs and scenarios, and a balance between ORM functions and native stored procedure calls should be found to build efficient and reliable enterprise-level applications.

This is the article about Java stored procedure call: @NamedStoredProcedureQuery. For more related Java stored procedure call content, please search for my previous article or continue browsing the related articles below. I hope everyone will support me in the future!