Doug has posted 2 posts at DZone. View Full User Profile

Introducing EclipseLink

06.30.2008
| 153174 views |
  • submit to reddit

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;

Published at DZone with permission of its author, Doug Clarke.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Vijay Nair replied on Tue, 2008/07/01 - 12:10pm

HI, Does this support out-of the box integration with Coherence as the second level cache or even ehCache ?

 

Didnt see any property setting for that anywhere in the examples

 

Tks..VJ 

Doug Clarke replied on Tue, 2008/07/01 - 3:10pm

VJ,

EclipseLink 1.0 has added cache interceptors to enable us to leverage grid caching solutions such as Oracle's Coherence. The actual implementation for Coherence is not available yet but is being worked on and we hope to attract other grid vendors to integrate their solutions as well. The approach taken allows you to leverage the grid as more then just a simple data cache. You should be able to leverage its distributed data porocessing capabilities for query execution and concurrency protection.

For the 1.1 release I hope to have an implementation that iullustrates how an alternate caching solution such as ehCache could be used. If interested in learning more please post a question to the users mailing list or newsgroup and we can get you connected with the developers working on the solution and the technical details necessary to use it.

I also recommend looking at our cache coordination support that is offerred out of the box. It addresses minimizing stale data in the cache in a clustered deployment.

Doug 

 

Sudhakar Ramasamy replied on Thu, 2008/07/03 - 3:12pm

From what I've read so far I haven't been able to figure out if JPA 2.0 will have a Criteria API.

Will EclipseLink 1.0 have a Criteria API or will it be included in a future release of EclipseLink as part of the JPA 2.0 implementation or as an extension?

 sud

Gordon Yorke replied on Fri, 2008/07/04 - 8:41am

As mentioned in the Early Draft a critera API for JPA 2.0 is planned.  Some of the API should be in the next Draft of the specification.  I would rather not comment on the structure of the criteria API right now as the definition is in the very early stages.

--Gordon 

Mike Keith replied on Fri, 2008/07/04 - 8:44am in response to: Sudhakar Ramasamy

Sud,

JPA 2.0 will have a Java-based expression language (sometimes called a "criteria API").  It will not be as powerful as the existing native TopLink expression API, but will offer all of the current functionality in JP QL, plus more as possible and appropriate. Although EclipseLink already includes support for a Java expression/criteria API, it will also include the new JPA 2.0 API once we have fully specified it. The EclipseLink API will continue to be offered, though, so you can use that now and migrate to the JPA version when it is specified and implemented.

-Mike

Donny A. Wijaya replied on Tue, 2008/07/08 - 10:33pm

I wonder if these annotations would be available via XML mappings? Would they (the annotations) become part of JPA standard or just part of EclipseLink? But anyway the EclipseLink looks great, StoredProc and Converter are what I want to see in the next JPA

Doug Clarke replied on Wed, 2008/07/09 - 4:37am in response to: Donny A. Wijaya

Donny,

Yes, all of the advanced features configured using annotations can also be done using XML.

For the example provided in the article I can have an EclipseLink-ORM.XML file containing:

    <converter name="money-converter" class="mypackage.MoneyConverter"/>

Then when I wish to use it on a mapping:

            <basic name="balance">
<convert>money-converter</convert>
</basic>

In EclipseLink 1.0 these XML files can be used in place of the standard JPA ones or as overrides with just the advanced features specified to enable greater portability between vendors.

It is our goal to continue to drive the advanced features we have added based on user requirements back into future versions of the standards. I will leave it to our expert group memebers to comment on the specifics but you can also get more involved by providing feedback directly to the expert group and reviewing all of the drafts that they publish (http://www.jcp.org/en/jsr/detail?id=317).

Doug

Sudhakar Ramasamy replied on Thu, 2008/07/10 - 2:32pm

Thanks for the response about the Criteria API. Can you shed some light on the integration with Teneo - the EMF to JPA persistence framework.

I'm investigating the feasibilty of an EMF - JFace Databinding - Teneo - EclipseLink - Derby stack for a rich client application and anything that makes this seamless/painless would be very helpful.

-sud 

kalle pallo replied on Tue, 2009/10/27 - 2:01pm

Advanced object-relational mappings in EclipseLink JPA offers greater flexibility when dealing with complex or legacy relational schemas. This mapping support has evolved over many years of commercial use dealing with many 'interesting' (i.e., challenging) domain models and relational schemas. hampaiden valkaisu kotona

Manish Chowdhary replied on Thu, 2012/03/22 - 8:12pm

Anyone have any recent links regarding EclipseLink that they can share? GoECart

Jason Bourne replied on Tue, 2012/10/23 - 2:04am

you’re truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Thanks for the information. And keep postingClickuplink Bookmarking

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.