I have about 12 Oracle tables that represent data that's being integrated from an external system into my web application. This data is going to be used in an informational and comparative manner for the clients using my web application.
On one particular page of my web application, I need to combine data from 3 – 5 Oracle tables for display as an HTML table on the page.
We are NOT currently using a framework (Apache Struts for instance) and we're not in a position to move this Java web application into one at this moment (I'm trying to get us there…).
I am certainly not an architect, but I see one of two ways that I can effectively build this page (I know there are other ways, but these seem like they would be good ones…):
1. Create an Oracle Materialized View that represents what the HTML table should look like and then create a POJO based on the View that I can then integrate into my JSP.
2. Create POJOs that represent the Oracle tables themselves and then create another POJO that is the View used for the HTML table and then integrate that POJO into my JSP.
To me, it seems the Materialized View would possibly offer quicker results which is always what we strive for in web applications. But, if I just create 12 POJOs that represent the Oracle tables and then build POJO Views off of those, I have the advantage of keeping all the code in one place for this and possibility for creating any number of different views and reusable components in my web application.
Any thoughts on which one might be the better route? Or, maybe you know of an even better one?
Best Answer
The most obvious drawback to a materialized view is its effect on inserts, updates, and deletes, or as a result of them. If your database is non-volatile, this is a non-issue.
The second issue would be future maintenance--a change in requirements for the html table would require you to revise the JSP, your POJOs, your SQL, and your view. If you were to move the logic of the view into your SQL, then that's one fewer thing to modify.
So, couldn't you simply have a pojo that is loaded by a query that runs against the 12 tables? With the right indexes, it should still be somewhat performant. If it isn't, I'd say go with the Materialized View.