data, jsp and jsp tag problem, best practice needed

[quickcur]

Hi, All,

I think you all find this situation very often

Suppose you have database tables

People table

id, name, address, telephone
1, Smith, 1 street, 1234
2, Mick, 2 Ave, 2345
3, XX, 4 bld, 9877


Group table

peopleid, groupid, groupname
1, 111, "good people"
2, 111, "good people"
3, 222, "bad people"

Suppose you have to display a group with all people's name in a jsp
page. You can query the group table. But it has only people id, not
people name. We have to link them. To do this, we have different
optioins:

1. Creae a database view which links group, peopleid, people name
together. You query this view and display the page. But if you have
many tables, and use this case frequently, you have to create a lot of
views.
2. Query group table and people table and do some parsing in the jsp
page. It works but it makes the jsp page very complicated.
3. Write customer tag which query the people table. For example,

<c:forEach ...loop through all peopleid in the group>
<mytageoplename peopleid="1">
</c:forEach>

Inside the tag, I query the people table with the peopleid and write
out the name. The jsp page is clean, no views. But it involvs a lot of
database connection. i am not sure about the perfermance.

Any comments?

Thanks,

qq

 

[Andy Dingley]

Any comments?

A view or query in the database, and if you're even remotely unsure
about this, you've failed the interview.

 

[Simon Brooke]

in message <[email protected]>,

Hi, All,

I think you all find this situation very often

Suppose you have database tables

People table

id, name, address, telephone
1, Smith, 1 street, 1234
2, Mick, 2 Ave, 2345
3, XX, 4 bld, 9877


Group table

peopleid, groupid, groupname
1, 111, "good people"
2, 111, "good people"
3, 222, "bad people"

You don't, that's not normalised.

You have

create table people -- although I would prefer 'person'
(
id serial primary key,
name varchar( 20) not null,
address varchar( 128), -- or in an auxiliary table
telephone varchar( 14)
);

create table group
(
id serial primary key,
name varchar( 20) not null,
-- although it might be better to use
-- name as a natural primary key
);

create table ln_people_group
(
person int not null,
group int not null
constraint ln_people_group_person foreign key ( person)
references people
match full
on delete cascade,
constraint ln_people_group_group foreign key ( group)
references group
match full
on delete cascade
);

Suppose you have to display a group with all people's name in a jsp
page. You can query the group table. But it has only people id, not
people name. We have to link them. To do this, we have different
optioins:

1. Creae a database view which links group, peopleid, people name
together. You query this view and display the page. But if you have
many tables, and use this case frequently, you have to create a lot of
views.

Yes. The database is highly optimised to make this efficient.

2. Query group table and people table and do some parsing in the jsp
page. It works but it makes the jsp page very complicated.

For heaven's sake no. Mixing logic and presentation is the worst possible
news. A JSP page is not the right place for any serious logic.

3. Write customer tag which query the people table. For example,

<c:forEach ...loop through all peopleid in the group>
<mytageoplename peopleid="1">
</c:forEach>

No. Better than (2) but not nearly as good as (1).

 

[Mark Space]

Yes. The database is highly optimised to make this efficient.


For heaven's sake no. Mixing logic and presentation is the worst possible
news. A JSP page is not the right place for any serious logic.

For a simple relation like this, wouldn't it just be easier to use a
SELECT that does the join for you, and not bother with a view? I can
see avoiding lots of jsp, but I don't really see the need for a view in
this particular case.

Precompile the SELECT, stuff it away in a class, and then you can just
call a class method when you need it. Easy to change later if ya gotta.

 

[Simon Brooke]

For a simple relation like this, wouldn't it just be easier to use a
SELECT that does the join for you, and not bother with a view? I can
see avoiding lots of jsp, but I don't really see the need for a view in
this particular case.

Precompile the SELECT, stuff it away in a class, and then you can just
call a class method when you need it. Easy to change later if ya gotta.

Aesthetics. I prefer to keep all the database code in one place - in the
SQL script file(s) - and not embed complex SQL in Java. So if I'm going to
join two tables I do that either as a view or else in a configuration file
that's read by the application, rather than hard-code the SQL string.

 

[Lew]

Aesthetics. I prefer to keep all the database code in one place - in the
SQL script file(s) - and not embed complex SQL in Java. So if I'm going to
join two tables I do that either as a view or else in a configuration file
that's read by the application, rather than hard-code the SQL string.

Rrr?

First of all, "complex" SQL?

Second, I find moving the SQL string out to a configuration (or other such)
file to be overkill. Engineering concerns might trump aesthetics. Or they
might coincide; some find simplicity and elegance to be very aesthetic. Form
follows function. Better engineered equals prettier.

SQL is as much part of the logic of the app as the Java constructs also
"hard-coded" into the source, e.g., the data structure that will hold the
result of the query. Let's call that structure PersonGroupInfo for the cited
example. The database structure, the SQL query and the Java structure will
exist in tandem perforce. (Because they all express the underlying model.)

A standard way to isolate SQL is to encapsulate it in a data-access object
(DAO).

For our example, declare a SQL string in the DAO class:

"SELECT p.*, g.id AS group_id, g.name AS group_name
FROM people p INNER JOIN ln_people_group l ON l.person = p.id
INNER JOIN group g ON l.group = g.id"

Sure, make this a static final String, and use it in a PreparedStatement
within a method of the DAO.

Now have your business logic put in a call to the DAO:

List <PersonGroupInfo> pgInfo = dao.listPersonGroupInfo(); // encapsulated!

Voilà! Your business logic is plenty isolated from the details of database
access without bothering with extra text files.

- Lew