On Fri, 14 Mar 2003, Pat Quinn wrote:
> Date: Fri, 14 Mar 2003 16:05:34 +0000
> From: Pat Quinn <[EMAIL PROTECTED]>
> Reply-To: Struts Users Mailing List <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: Re: Data driven struts application
>
> I spent some time thinking over this very topic. I finally went with the
> following approach:
>
>
> * I used JDBC (NO Entity Beans got bitten bably there in the past) to query
> the data base and then created value objects which i then inserted into Form
> beans.
>
This is pretty much what RowSetDynaClass does for you, without the need to
create classes for your value objects.
> * For read only data access i used an optimised SQL statement (plse see
> below) which returns only enough data to render the current screen to the
> client.
>
This is almost always a really smart strategy -- not only should you limit
which columns are retrieved to only those that meet the requirements, you
should (more importantly) limit the number of rows that is retrieved, as
Pat points out below.
Two possible concerns are that you'll be tying the persistence tier and
the view tier together, because (for example) the page author has to know
about the column names in the database, and the business logic has to be
tweaked every time the page author needs a new column added. To mitigate
these problems, I suggest using two strategies:
* Store the actual SQL query sources into properties files, so that
it can be modified easily without touching the business logic
classes. (This can also be a lifesaver if you have to support
more than one database, where the SQL query syntax has to change
slightly.)
* Use the column aliasing capability of SQL (the "as" modifier) to
convert the database column names into simple names that the
page author can expect, and document (for him or her) only those
names. For example, a query might say something like:
select customer-account-number as account,
external-display-name as name
from customers
where ...;
and the page author will see properties named "account" and "name",
no matter what the database actually uses.
> As the user pages through the results i use the paging start index to
> control the SQL select statement so that i only ever fetch enough data for
> the next screen.
>
> So if the user pages from page 1 to 20, and i display 10 records per page i
> don't need to process from record 1 to 200 to get the data i need, nor do i
> need to keep my connection open. I always get back my data in blocks of 10
> records.
>
>
> select [$ColumnNames], counter from ( select [$ColumnNames], rownum as
> counter from (select [$ColumnNames] from my_table where [$WhereClause]))
> where counter between [Start Index] and [End Index]
>
> The SQL could be optimised a little more I’m not an SQL guru!!!
>
>
> I could also implement a caching mechanism to increase performance a little
> more.
>
>
> Any ways thats my two cents!!!!
>
Craig McClanahan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
