Java framework "suggestion" for persisting the results from an Oracle 9i stored procedure using Apache Tomcat

Question

I am developing a Java servlet which calls an Oracle stored procedure. The stored procedure is likely to "grow" over time, and I have concerns the amount of time taken to "display the results on a web page".

While I am at the implementation stage, I would like some suggestions of a Persistence framework which will work on Apache Tomcat 5.5? I see two approaches to persisting the database results. A scheduled database query every N minutes, or something which utilises triggers.

Hibernate seems like the obvious answer, but I have never called stored procedures from Hibernate (HQL and Criteria). Is there a more appropriate framework which can be used?

Answer 1

Almost any Java ORM will work in a servlet container envirnment like Tomcat: Hibernate, Cayenne, TopLink...but it won't help you one bit with improving the stored procedure execution speed and rendering the page faster.

Here's some general advice:

• don't use stored procedures (vendor lock in, maintenance nightmare, spaghetti code, sub-standard development language etc.)
• use an ORM
• start with the basic performance optimization strategies to get better response times: make sure your indexes make sense, tweak the database configuration, introduce redundant values in the database, use triggers to recalculate them if absolutely necessary, if you don't need the absolute freshest data, pre-calculate results periodically and simply read them when necessary etc.

Answer 2

I would suggest, consider not using a persistence framework at this stage. Just use straight JDBC to save the results. From reading your post, it does not sound like you really need it.

Consider spring if you would like to simplify your application interacting with JDBC.

Although, database independence is nice. I will assume you are running 1. complex logic with the pl/sql or 2. transforming a massive amount of data. Consider using oracle DMBS_SCHEDULER with triggers.

Answer 3

Hibernate seems like the obvious answer, but I have never called stored procedures from Hibernate (HQL and Criteria).

Not that obvious, not with stored procedures, and especially not if you are just calling a stored procedure and if you are not deal with object persistence (CRUD operations).

Is there a more appropriate framework which can be used?

Straight JDBC, or Spring StoredProcedure, or iBATIS. But not Hibernate, it doesn't really have any benefit for your use case.