CODEWITHSUNDEEP
oracle-databasespringjpapersistence
Gnanz
Gnanz

Reputation: 1873

Implementing DB Session connectivity using JPA persistence in Spring

Hi All We are developing an application in Struts2-Spring-JPA, In this application we are using sessions.xml and persistence.xml to manage oracle DB sessions.

In Spring serviceimpl class constructor by calling getSession() method we are initializing the serverSession variable.

here i have attached the coeds we are using the project.

In Impl Constructor

serverSession=getSession();

and in methods for executing procedures

    try {
        createConnection(serverSession);
        /* call stored procudure */
        StoredProcedureCall call = new StoredProcedureCall();
        call.setProcedureName("ORACLE_USP");
        call.addNamedArgumentValue("ARG1", value);
        call.addNamedOutputArgument("OUTPUTVAL", "P_OUTPUTVAL", Integer.class);

        ValueReadQuery query = new ValueReadQuery();
        query.setCall(call);
        actionplanforum_id = (Integer) serverSession.executeQuery(query); 
    } catch (DatabaseException e) {
        LOGGER.error("DatabaseException" + e); 
    } finally {
        releaseJTSCon(serverSession);
    } 


   protected ServerSession getSession() throws Exception {  
        ServerSession sSession = (ServerSession) SessionManager.getManager().getSession("dbsession");  
        if (!sSession.isConnected()) {  
              sSession.connect();  
          }  
      return sSession;  
 }  

   public void createConnection(ServerSession sSession) {  
       if (!sSession.isLoggedIn()) {  
           sSession.login();  
        }  
}   

     protected void releaseJTSCon(ServerSession sSession) {  
        try {    
             sSession.releaseJTSConnection();  
         }catch (DatabaseException e) {  
             LOGGER.error("Error in releasing DB connection resources");  
        }

Is this a correct approach without using EntityManger, when using this approach when more traffic comes i have many db connections open in oracle(mostly in idle stage).

Can anybody help in implementing the right approach.

Upvotes: 1

Views: 803

Answers (1)

James
James

Reputation: 18389

Are you using JPA, or the native API?

To do this with JPA you just need to,

StoredProcedureCall call = ...
Query query = em.unwrap(JpaEntityManager).createQuery(call); // or use getDelegate() if JPA 1.0.
List result = query.getResultList();

If you are intending to use the native API then your code in not correct. Remove the connect() call, and the releaseJTSConnection() call, niether of these are API methods, the API is defined in the Session, UnitOfWork, and Server interfaces. Remove the login() as the session return from SessionManager should already be connected. You should acquire and release a ClientSession from the ServerSession to execute the query, and probably a UnitOfWork, if you wish the query to be transactional.

Upvotes: 2

Related Questions