Using stored procedures with entity beans?

Discussions

EJB design: Using stored procedures with entity beans?

  1. Using stored procedures with entity beans? (3 messages)

    I am from the old school of Oracle application development, where putting as much business logic in the database as possible is favored. As a matter of fact, even with MSFT MTS programming, they tell you to use stored procedures for heavy-duty data manipulation, even though you may have a data access object in the middle tier. THE DAO's job is primarily providing a wrapper for calling these stored procedures using ADO/ODBC/OLE DB.

    However, so far in all the discussion groups and sample applications, I have never seen stored procedures being used once in entity beans. Are store procedures dead with the advent of J2EE, and particularly entity EJB's? Stored procedures used to come very handy for me. In some of the intranet applications I helped to develop and am developing (IMHO, not every web application is a shopping cart related one with the routine browse-add to cart-check out model), we have fairly complex business requirements, such as entering an audit trail record each time a main record is inserted/updated/deleted, or cascading an update from the master to the detail. These are best handled via stored procedures. With EJB, it seems that every record in the database has to be packaged into an entity bean before one can manipulate the data, which means each time a user updates a master record entity bean, a vector of detail record entity beans have to be created and handled in a session bean. What about the network and database overhead this creates!

    So my question is: are any of you using stored procedures with EJB's? I know stored procedures tie you down to a particular database vendor. However, in the near future I don't see my client moving away from Oracle. What do you think?

  2. EJB has session beans and entity beans.
    Entity beans are normally used to represent a row in the DB as an object so I dont think they are good candidate for what you want to do.
    I think you should write a stateless session bean.I have called Stored Procedures returning all sorts of junk including cursors using Stateless session beans.
    Use JDBC and use Stateless session beans.
    -Harshal
  3. Hi Harshal, We have a similar kind of situation. We are using EJBs with CMP as part of our application. We are using Oracle SPs in the DAO layer. There is an insert operation which inserts records into multiple tables and 4 to 5 Stored Procedures (one DAO method per SP). We are facing two different issues 1) Whenever there is an Exception, it is thrown to the EJB layer where there is a rollback(using setRollbackOnly() method) operation which is not happening. But the setRollbackOnly() method is executed. 2) Once the insertion to the tables are completed successfully, it take minutes for the data to reflect in the database or the commit() is not happening to the database. We believe that both of the issues are related. Below is a code snippet on how we call the SPs from our DAO Layer. Connection conn = getConnection(>); Connection myConn = (java.sql.Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection) conn); CallableStatement callStmt = (OracleCallableStatement) myConn.prepareCall(“{ call SP_Customer_Basic_Details(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}”); We use com.ibm.ws.rsadapter.jdbc.WSJdbcConnection and com.ibm.ws.rsadapter.jdbc.WSJdbcUtil as we use “oracle.sql.ARRAY” and “oracle.sql.ArrayDescriptor” objects to access the Oracle SPs. The connection object without the type casting of WSJdbcConnection didnt accept the “oracle.sql.ARRAY”. I have also attached the ejb-jar.xml file with this mail. Let us know if any of the Healthcare projects have used EJBs with Oracle SPs in the past. Note - The autoCommit property of the JDBC connection is set to false. There is no exclusive commit() in the SPs. Let us know if have u come across this kind of situation.
  4. I have used stored procedures for generating an audit trail for our applications. I am using a trigger to cause the stored procedure to execute. It seems to work well except that the trigger fires on every get and set method (by default). If you try to implement something trigger based, you probably should read http://theserverside.com/discussion/thread.jsp?thread_id=238
    where I had posted about problems with triggers & transactions with CMP entity EJB's.

    Phil Padgett
    pdp@biostat.ufl.edu