eclipselink stored procedures with result returned from different tables

[Rene Ummels]

Hi,

Will the book explain how to handle stored procedures? Some scenarios, for instance with EclipseLink and a mix of tables in the returned result.

We have made some stored procedure code, the result from the stored procedure containing a mix of two tables (destinationNumber from ported; networkRoutingPrefix from network).
Is the below way correct and efficient?

@Entity
@Table(name = "ported")
public class Ported implements Serializable
{
    @Id
    @Column(name = "portedNumber", length = 20)
    private String portedNumber;

    @ManyToOne
    @JoinColumn(name="NETWORKID_networkId", referencedColumnName="networkId")
    private Network networkId;

    @Column(name = "destinationNumber", nullable = true, length = 20)
    private String destinationNumber;

:

@Entity
@Table(name = "network")
public class Network implements Serializable
{
	@Id
	@Column(name = "networkId")
	private int networkId;

	@Column(name = "prefix", nullable = true, length = 8)
	private String networkRoutingPrefix;

:

@Entity
@NamedStoredProcedureQueries ({
    @NamedStoredProcedureQuery (
            name="ported.p_sel_number_info",
            resultClass=Result.class,
            procedureName="p_sel_number_info",
            returnsResultSet=false,
            parameters={   
        	@StoredProcedureParameter(queryParameter="portedNumber", name="PORTEDNUMBER", direction=Direction.IN,type=String.class),
        	@StoredProcedureParameter(queryParameter="prefix", name="NETWORKROUTINGPREFIX", direction=Direction.OUT, type=String.class),
        	@StoredProcedureParameter(queryParameter="destinationNumber", name="DESTINATIONNUMBER", direction=Direction.OUT,type=String.class)
            }
        )
})
public class Result implements Serializable
{
    @Id
    @Column(name = "portedNumber", length = 20)
    private String portedNumber;

    @Column(name = "destinationNumber", nullable = true, length = 20)
    private String destinationNumber;
    
    @Column(name = "prefix", nullable = true, length = 8)
    private String networkPrefix;

:

    public Ported findPorted(String portedNumber)
    {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("XXX");
        EntityManagerImpl em = (EntityManagerImpl) emf.createEntityManager();
        Session session = em.getActiveSession();
        StoredProcedureCall spCall = new StoredProcedureCall();
        spCall.setProcedureName("p_sel_number_info");
        spCall.addNamedArgumentValue("portedNumber", portedNumber);
        ReadObjectQuery query = new ReadObjectQuery();
        query.setReferenceClass(Result.class);
        query.setCall(spCall);
        Result result = (Result) session.executeQuery(query);

        Ported p = new Ported();
        Network n = new Network();
        if(result != null)
        {
            String destinationNumber = result.getDestinationNumber();
            String networkPrefix = result.getNetworkPrefix();
            //TODO: fill p (and n)
        }
        else
        {
            //TODO
        }
        em.close();
        emf.close();

        return p;
    }

thanks,
Rene

[James Sutherland]

Not sure what book you are referring to.

Your stored procedure call seems ok. Although odd that you define it using annotations as a named query, but then create a new call to execute it. You could just use the named query (although if your call works, it seems your procedure returns a result-set not output parameters).

[Mike Keith]

Hi Rene,

We have tried to keep the book vendor-neutral and focused on the specification itself, and since stored procedures are not defined in the spec we did not include them. However, they are on my feature list for the JPA 2.1 specification, so there is a good chance they will be in the next edition of the book.

As James mentioned, since you are defining a named query you might want to stick with that and keep the vendor-specific code out of your method.

[Rene Ummels]

Hi,

Good feedback. So, if I understand you correctly this would mean to change the code as follows, right?

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("XXX");
        EntityManager em = emf.createEntityManager();
        Query query = em.createNamedQuery("ported.p_sel_number_info");
        query.setParameter("@portedNumber", portedNumber);
        Result result = (Result) query.getSingleResult();

kind regards,
Rene

[Mike Keith]

There is no @ in the JPA query parameter name. It is just the name that you specified as the queryParameter when you defined the query, which was portedNumber.

[Rene Ummels]

Thanks Mike.
I now substituted:

query.setParameter("@portedNumber", portedNumber);

by:

query.setParameter("portedNumber", portedNumber);

Then I executed the createNamedQuery() code but I get an exception:
Caused by: java.lang.IllegalArgumentException: NamedQuery of name: ported.p_sel_number_info not found.
How can I solve this?


thanks,
Rene

[Mike Keith]

Hi Rene,

Your entity manager should be from the EMF of the same persistence unit as the Result entity or the query name won't be in that namespace. If you think that is okay in your app then you might want to go through the EclipseLink forums for more specific help. They are pretty helpful there.

[Rene Ummels]

Hi,

The actual solution was to update the sybase stored procedure from:

select @portedNumber,@prefix,@destinationNumber

to:

select portedNumber=@portedNumber,prefix=@prefix,destinationNumber=@destinationNumber

In this way, the columns were mapped correctly.


br,
Rene