My company has a policy that all database access must be via stored procedures. I'm creating a proof of concept application to prove that hibernate can be used for persistence. I've sucessfully managed to get my obect model to persist to the database using only stored procedures when I define my mapping using Hibernate's native mapping and I'm now trying to convert these mappings to annotations.
I have managed to get everything to work, except loading my collections using named queries. There does not seem to be any way to specfy that a @NamedNativeQuery loads a collection not a single entity.
I want to convert this:
Code:
<!-- Worker.managees -->
<sql-query name="retrieveManagersWorkers" callable="true">
<load-collection alias="worker" role="uk.gov.hmrc.poc.model.Worker.managees"/>
{ ? = call RetrieveManagersWorkers(:in_manager_id) }
</sql-query>
into annotations, but there is no way to add the "load-collection" property to a @NamedNativeQuery.
When I first attempted this I used this annotation:
Code:
@NamedNativeQuery(name="retrieveManagersWorkers", callable=true, resultClass=Worker.class,
query="{ ? = call ManagementProcs.RetrieveManagersWorkers(:in_manager_id) }")})
This however just results in a NPE to be thrown when the collection is fetched.
Has anyone else worked out a way to do this?
Hibernate version: 3.2.1.ga
Mapping annotations:Code:
@Entity()
@Table(name="worker")
@NamedNativeQuery(name="retrieveManagersWorkers", callable=true, resultClass=Worker.class,
query="{ ? = call RetrieveManagersWorkers(:in_manager_id) }")
public class Worker{
@Id
private String id;
@Version
private Integer version;
@OneToMany(cascade=CascadeType.ALL)
@JoinColumn(name="manager_id")
@Loader(namedQuery="retrieveManagersWorkers")
private Set<Worker> managees = new HashSet<Worker>();
public Worker(){
id = UUID.randomUUID().toString();
}
public void addManagee(Worker managee){
managees.add(managee);
}
public Set<Worker> getManagees() {
return managees;
}
public String getId(){
return id;
}
}
Code between sessionFactory.openSession() and session.close():Code:
transaction = session.beginTransaction();
Worker manager = new Worker();
Worker managee = new Worker();
manager.addManagee(managee);
session.persist(manager);
session.flush();
session.evict(manager);
Worker managerReloaded = (Worker)session.load(Worker.class, manager.getId());
//this is the line which throws a NPE
System.out.println(managerReloaded.getManagees().size());
transaction.commit();
Full stack trace of any exception that occurs:java.lang.NullPointerException
at org.hibernate.collection.PersistentSet.size(PersistentSet.java:139)
at scratch.HibernateStoredProcCRUDTestCase.testCollection(HibernateStoredProcCRUDTestCase.java:42)
at scratch.HibernateStoredProcCRUDTestCase.testCollection(HibernateStoredProcCRUDTestCase.java:42)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.maven.surefire.junit.JUnitTestSet.execute(JUnitTestSet.java:213)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:138)
at org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:125)
at org.apache.maven.surefire.Surefire.run(Surefire.java:132)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:290)
at org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:818)
Name and version of the database you are using:Oracle 9.2.0.1.0
The generated SQL (show_sql=true):Hibernate: insert into worker (version, id) values (?, ?)
Hibernate: insert into worker (version, id) values (?, ?)
Hibernate: update worker set manager_id=? where id=?
Hibernate: select worker0_.id as id2_0_, worker0_.version as version2_0_ from worker worker0_ where worker0_.id=?
Hibernate: { ? = call RetrieveManagersWorkers(?) }
PLSQL procedures: Code:
FUNCTION RetrieveManagersWorkers (in_manager_id IN WORKER.ID%TYPE) RETURN SYS_REFCURSOR
IS
out_result SYS_REFCURSOR;
BEGIN
OPEN out_result FOR
SELECT *
FROM WORKER
WHERE MANAGER_ID = in_manager_id;
RETURN out_result;
END RetrieveManagersWorkers;
Login
FAQ
Search