-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: Stored Proc with Hibernate
PostPosted: Tue Aug 26, 2008 10:20 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:38 am
Posts: 22
Hi

I've got a stored procedure like this:

CALL LOGIN (?,?,?,?)

I can call it using JDBC using a callable statement (basic JDBC), but not in Hibernate (with JPA).

I've tried a few things, such as

Code:
Query q = entityManager.createNativeQuery("CALL LOGIN (:accountNumber, :surname, :address, :folio)");
q.setParameter("accountNumber", "1234567");
q.setParameter("surname", "Smith");
Object o = q.getSingleResult();


Using JDBC I'd call 'registerOutParameter' for :addesss and :folio (they are what the procedure returns to me) but I can't see any way to do this with JPA.

I read that the Stored Proc may need changing to return a ResultSet to get Hibernate to recognise it at all.

Please could someone recommend the best way of working with these kinds of procedures?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 27, 2008 12:12 am 
Regular
Regular

Joined: Sun Oct 26, 2003 9:02 pm
Posts: 90
I don't see anyway that you can do this with EntityManager. I think you will have to get the Hibernate Session object from the EntityManager and then get the JDBC connection and do it the old way.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Aug 27, 2008 6:01 am 
Beginner
Beginner

Joined: Thu Apr 12, 2007 10:38 am
Posts: 22
Thanks nestorjb, that's helped a lot.

On a side note, it took me a while to figure out that getDelegate was the method I needed to call on EntityManager to get the session. So now, for the benefit of anyone else having the same question, I just do this:

Code:
Connection con = ((Session)entityManager.getDelegate()).connection();
         con.setAutoCommit(false);
         CallableStatement cs = con
               .prepareCall("CALL LOGIN (?,?,?,?)");
         cs.setString(1, accountNumber);
         cs.setString(2, lastName);
         cs.registerOutParameter(3, java.sql.Types.CHAR);
         cs.registerOutParameter(4, java.sql.Types.CHAR);
         cs.execute();


...and it works!

I noticed that the .connection method is deprecated, although it's not appearing on the site's API docs yet. I checked the source code and it mentions this:

Quote:
Replacement depends on need; for doing direct JDBC stuff use
* {@link #doWork}; for opening a 'temporary Session' use (TBD).



Sounds like the replacement isn't quite ready yet, so I'll continue using .connection for now. If I shouldn't, pls let me know

Thanks again


Top
 Profile  
 
 Post subject: Accessing connection from session
PostPosted: Thu Sep 25, 2008 4:14 pm 
Newbie

Joined: Thu Sep 25, 2008 4:00 pm
Posts: 1
This is the way to access connection now
SessionFactoryImplementor sessionFactory = (SessionFactoryImplementor)new Configuration().configure().buildSessionFactory();
try{
Connection con = sessionFactory.getConnectionProvider().getConnection();

}catch(Exception e){
e.printStackTrace();
}


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.