Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
Seems reasonable - i'll try creating a pojo in this manner with the query right inside of it and see if performance is an issue or not - thanks. –  Zack Macomber May 30 '12 at 20:22

What I do in that situation and works for me is:

  1. Create a regular view that represents what the HTML table should look like
  2. Create a POJO that reads that view and produces a tab-separated text output
  3. Create a POJO that receives the tab-separated data and paints HTML table

Future functionalities can use the view created at step 1.

Future functionalities can use POJO created at step 2.

share|improve this answer

Your stated requirements don't show the need for Materialized View. You could use regular views if you want. What will make the real difference is how you code the SQL and how you design the indexes in addition to how much data you fetch in each call to the database. You could even code the logic to combine the tables in stored procs and with single invocation get your results.

share|improve this answer
    
+1: It will be about 100 times faster to write and debug a SQL view than to write equivalent Java code. You almost never want to do table joins outside SQL. –  kevin cline May 30 '12 at 20:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.