File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes JPA and StoredProcedureCall Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA and StoredProcedureCall" Watch "JPA and StoredProcedureCall" New topic
Author

JPA and StoredProcedureCall

Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi,

I am learning JPA 2.0 and EclipseLink StoredProcedureCall and I am finding som problems.

This calling works ok,



but this other one does not work,



The last one throws an exception as follows,


[EL Info]: 2010-04-16 10:34:30.125--DatabaseSessionImpl(5506056)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.0.0.v20091127-r5931
[EL Config]: 2010-04-16 10:34:30.171--DatabaseSessionImpl(5506056)--Connection(9829657)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>Oracle10Platform
user name=> "employee"
datasource URL=> "jdbcracle:thin:@127.0.0.1:1521:XE"
))
[EL Config]: 2010-04-16 10:34:32.25--DatabaseSessionImpl(5506056)--Connection(30199356)--Thread(Thread[main,5,main])--Connected: jdbcracle:thin:@127.0.0.1:1521:XE
User: EMPLOYEE
Database: Oracle Version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Driver: Oracle JDBC driver Version: 10.2.0.1.0XE
[EL Info]: 2010-04-16 10:34:32.296--DatabaseSessionImpl(5506056)--Thread(Thread[main,5,main])-- login successful
Exception in thread "main" java.lang.NullPointerException
at Main.getEmployee(Main.java:78)
at Main.main(Main.java:32)


The null pointer refers to 'ClassDescriptor descriptor = getSession().getDescriptor(query.getReferenceClass());'

I know I have to configured previously to the calling but I do not know
howt to do it on Eclipse Galileo RS1 IDE

This is the Stored Procedure package body,



Any suggestion will be apreciated.

Thanks,
Jose
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
You need to add a descriptor for the Employee class.
How are you configuring your Session? You need to use either JPA annotations, orm.xml, or the EclipseLink native project.xml or code a RelationalDescriptor and add it to your Session.

Technically you can remove the lines,

>> ClassDescriptor descriptor = getSession().getDescriptor(query.getReferenceClass());
>> descriptor.getQueryManager().addQuery(query.getName(), query);

because you are passing the query to execute, you only need to add the query if executing the query by name. But you still need a descriptor for the Employee class.


TopLink : EclipseLink : Book:Java Persistence : Blog:Java Persistence Performance
Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi James,

This is my descriptor,



And I get the session this way (after login),



being this.session a private attribute of the class.

Now with this attribute I have built my query,



And this is what I get,


[EL Info]: 2010-04-20 20:21:26.671--DatabaseSessionImpl(31378924)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.0.1.v20100213-r6600
[EL Config]: 2010-04-20 20:21:26.703--DatabaseSessionImpl(31378924)--Connection(4669910)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>Oracle10Platform
user name=> "employee"
datasource URL=> "jdbc:oracle:thin:@127.0.0.1:1521:XE"
))
[EL Config]: 2010-04-20 20:21:26.953--DatabaseSessionImpl(31378924)--Connection(15532856)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@127.0.0.1:1521:XE
User: EMPLOYEE
Database: Oracle Version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Driver: Oracle JDBC driver Version: 10.2.0.1.0XE
[EL Info]: 2010-04-20 20:21:26.968--DatabaseSessionImpl(31378924)--Thread(Thread[main,5,main])-- login successful
[EL Warning]: 2010-04-20 20:21:27.25--DatabaseSessionImpl(31378924)--Thread(Thread[main,5,main])--Missing Query parameter for named argument: EMPLOYEE.EMP_ID null will be substituted. (There is no English translation for this message.)
[EL Fine]: 2010-04-20 20:21:27.25--DatabaseSessionImpl(31378924)--Connection(15532856)--Thread(Thread[main,5,main])--BEGIN EMPLOYEEDETAILPKG.GETEMPLOYEEDETAIL(EMPLOYEEID=>?, MYCSR=>?); END;
bind => [null, => MYCSR]
Ko


Please, what am I doing wrong?

Thnks,
Jose
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
Note that you don't seem to be using JPA at all, unlike your title suggests.

You need to add the descriptor to the Session (once, ideally before login, or after getting the Session the first time).
Do not set a new descriptor on the query.

You could also use JPA and annotate the class, and use the JPA EntityManager to access the stored procedure either using a @NamedStoredProedureQuery, or creating a JPA Query from the StoredProcedureCall.

Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
Hi,

I did it the way you say and I get no exception but instead of it
I have got no data.

Here is the code,



And now the output,


[EL Info]: 2010-04-21 22:37:38.468--DatabaseSessionImpl(25853693)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.0.1.v20100213-r6600
[EL Config]: 2010-04-21 22:37:38.484--DatabaseSessionImpl(25853693)--Connection(26680060)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>Oracle10Platform
user name=> "employee"
datasource URL=> "jdbcracle:thin:@127.0.0.1:1521:XE"
))
[EL Config]: 2010-04-21 22:37:38.937--DatabaseSessionImpl(25853693)--Connection(7804298)--Thread(Thread[main,5,main])--Connected: jdbcracle:thin:@127.0.0.1:1521:XE
User: EMPLOYEE
Database: Oracle Version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Driver: Oracle JDBC driver Version: 10.2.0.1.0XE
[EL Info]: 2010-04-21 22:37:39.14--DatabaseSessionImpl(25853693)--Thread(Thread[main,5,main])-- login successful
[EL Fine]: 2010-04-21 22:37:39.406--DatabaseSessionImpl(25853693)--Connection(7804298)--Thread(Thread[main,5,main])--BEGIN EMPLOYEEDETAILPKG.GETEMPLOYEEDETAIL(EMPLOYEEID=>?, MYCSR=>?); END;
bind => [32, => MYCSR]
EMP_ID => 32
F_NAME => null
L_NAME => null


It looks strange because I have got the employee.getEmpId() but nothing about fName and lName attributes.

Will you explain me what is happening?

Thanks in advance,
Jose
James Sutherland
Ranch Hand

Joined: Oct 01, 2007
Posts: 553
In your descriptor you only mapped the id, you must map everything you want to be mapped.
Jose Alvarez de Lara
Ranch Hand

Joined: May 10, 2008
Posts: 92
GOT IT!!!

This is the output,


[EL Info]: 2010-04-22 20:40:20.89--DatabaseSessionImpl(25853693)--Thread(Thread[main,5,main])--EclipseLink, version: Eclipse Persistence Services - 2.0.1.v20100213-r6600
[EL Config]: 2010-04-22 20:40:20.921--DatabaseSessionImpl(25853693)--Connection(26680060)--Thread(Thread[main,5,main])--connecting(DatabaseLogin(
platform=>Oracle10Platform
user name=> "employee"
datasource URL=> "jdbc:oracle:thin:@127.0.0.1:1521:XE"
))
[EL Config]: 2010-04-22 20:40:21.531--DatabaseSessionImpl(25853693)--Connection(7804298)--Thread(Thread[main,5,main])--Connected: jdbc:oracle:thin:@127.0.0.1:1521:XE
User: EMPLOYEE
Database: Oracle Version: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Driver: Oracle JDBC driver Version: 10.2.0.1.0XE
[EL Info]: 2010-04-22 20:40:21.812--DatabaseSessionImpl(25853693)--Thread(Thread[main,5,main])-- login successful
[EL Fine]: 2010-04-22 20:40:22.187--DatabaseSessionImpl(25853693)--Connection(7804298)--Thread(Thread[main,5,main])--BEGIN EMPLOYEEDETAILPKG.GETEMPLOYEEDETAIL(EMPLOYEEID=>?, MYCSR=>?); END;
bind => [32, => MYCSR]
EMP_ID => 32
F_NAME => Jill
L_NAME => May


In the beginning and during the process I was thinking this seemed quite complicated,
but now that we have got it I have changed my mind.

This shoud be the base to develop Stored Procedures Calling.

Thanks James for your kind helping. Thanks a lot.

Best Regards,
Jose
 
jQuery in Action, 2nd edition
 
subject: JPA and StoredProcedureCall