in J2EE architecrure: JDBC queries VS. Stored procedures

Discussions

EJB design: in J2EE architecrure: JDBC queries VS. Stored procedures

  1. What I am wondering is which one is the better practice: I am about to put JDBC queries as system's middle inside session bean methods while others suggest it's a better way to code all SQL queries into stored procedures and put them along with tables in the database and system's middle is used to call those stored procedures instead to construct queries within itself. I personally prefer to treat queries as part of system's business logic instead of data model, therefore it's better to be put into java source as part of middle-tier. Otherwise It might be against MVC pattern. What you think of this? any advice or opinion are greatly welcome. Thanks -Shane
  2. IMO, stored procs are a wise coice if they are used to enhance the performance of an application. However, stored procs should not contain any business logic. They should be limited to parameterized selects, inserts, updates and deletes.
  3. there are some trade-off. If you use stored procedure, it will imporve performance, but it will lose portability (for example, SQL server is different Oracle server for implementing stored procedure.).
    He,Yihua
  4. Hi,

    Check out,

    http://www.onjava.com/pub/a/onjava/excerpt/oraclejdbc_19/index.html?page=1

    This article discuss Prepared Statements v Statements v Callable Statements in terms of performance in Oracle.

    At the end of the article is the discussion about Stored Procedures and in this test they come out as slower than Prepared Statements in Batch mode or Statements.

    Interesting stuff,
    David
  5. PS.

    I have often found out the decision on whether to use Stored Procedure depends on who has the last word in the IT department.

    I have contracted in a place where the director of engineering was an ex Oracle developer. He insisted on using Stored Procedures for data retrieval. That is the way he had always done it.

    In my current contract the head of IT is a non DB person (he is an ex opps guy). We have no direct access to the DB in terms of development. By this I mean any DB change has to go through an army of DB architects and developers. In the end I installed my own Oracle db on my machine so I could work. With this is mind I would not go near a stored procedure as it is way too much hassle to get it implemented.

    The politics of life,
    David
  6. Thank you all gurus, really helpful :)[ Go to top ]

    Shane