Introducing EclipseLink

Using Stored Procs

There are times when users need to override the SQL EclipseLink JPA generates from JPQL and and mappings. JPA supports native queries where the resulting rows are mapped onto an Entity based on the resulting column names or through the provided result set mapping.  EclipseLink goes further and allows stored procedures to be used for any database operation and as named queries. In the example below, a named query is defined that uses a stored procedure to query for an Address entity. 

    @NamedStoredProcedureQuery(
        name="Address.findById",
        resultClass=Address.class,
        procedureName="SProc_Read_Address",
        parameters={
            @StoredProcedureParameter(name="address_id_v",
                    queryParam="ADDRESS_ID", direction=IN_OUT),
            @StoredProcedureParameter(name="street_v",
                    queryParameter="STREET",  direction=OUT),
            @StoredProcedureParameter(name="city_v",
                    queryParameter="CITY", direction=OUT),
            @StoredProcedureParameter(name="country_v",
                    queryParameter="COUNTRY", direction=OUT),
            @StoredProcedureParameter(name="province_v",
                    queryParameter="PROVINCE", direction=OUT),
            @StoredProcedureParameter(name="p_code_v",
                    queryParameter="P_CODE",  direction=OUT)
        })  

 
This stored procedure can then be excuted using the standard named query API.

Address address = (Address) em.createNamedQuery("Address.findById").
                                            setParameter("ADDRESS_ID", 1).
                                            getSingleResult();  
 

As this example illustrates, defining a named query with a stored procedure is straight forward and its execution can be invoked using the standard API. The intent is to allow users to replace the definition of a NamedQuery with a NamedStoredProcedureQuery where necessary for performance reasons or to leverage exisiting stored procedures.  The implementation of a NamedQuery is opaque to callers which means this kind of switch can be done without affecting the application's usage code. In addition to using stored procedures for named queries it is also possible to customize an entity's default queries (select, insert, update, delete) to use stored procedures as well.

Joining and Batching of Relationships

A common problem faced by many applications is excessive SQL generated as graphs of related entities are loaded. This is typically addressed through joined reading of entities to increase the amount of data retrieved in a single query.  JPA currently offers support in its query language for 'JOIN FETCH' to indicate relationships which should be joined and loaded when a query is executed. 

List<Employee> emps = em.createQuery("SELECT e FROM Employee e JOIN FETCH
                                        e.address").getResultList();

This does address many situations but it lacks support for multi-level joining and does not allow batch loading of relationships in separate joined queries. EclipseLink addresses these current limitations with query hints that can be placed on dynamic or named queries to efficiently load deep graphs using join and batch reading capabilities. In the following example, query hints are used to optimize the graph loading of a set of Employee entities using joining on the M:1 and 1:1 relationships and batching on the 1:M relationships.

List<Employee> emps = em.createQuery(
                        "SELECT e FROM Employee e ORDER BY e.lastName ASC,
                         e.firstName ASC")
                .setHint(QueryHints.FETCH, "e.address")
                .setHint(QueryHints.FETCH, "e.manager")
                .setHint(QueryHints.FETCH, "e.manager.address")
                .setHint(QueryHints.BATCH, "e.phoneNumbers")
                .getResultList();

This approach addresses the need for deep object graph loading at the query level. When users require that joining or batching be used on all queries for a specific entity's mappings they can specify these on the mappings as well. In this example the joining is configured on the mappings directly and it is specified to be outer joining as well.

    @OneToOne
    @JoinColumn(name="ADDR_ID")
    @JoinFetch(JoinFetchType.OUTER)
    private Address address;