data, jsp and jsp tag problem, best practice needed

Discussion in 'Java' started by quickcur@yahoo.com, Oct 3, 2006.

  1. Guest

    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>
    <mytag:peoplename 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
    , Oct 3, 2006
    #1
    1. Advertising

  2. Andy Dingley Guest

    wrote:

    > Any comments?


    A view or query in the database, and if you're even remotely unsure
    about this, you've failed the interview.
    Andy Dingley, Oct 3, 2006
    #2
    1. Advertising

  3. Simon Brooke Guest

    in message <>,
    ('') wrote:

    > 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>
    > <mytag:peoplename peopleid="1">
    > </c:forEach>


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

    --
    (Simon Brooke) http://www.jasmine.org.uk/~simon/

    ;; single speed mountain bikes: for people who cycle on flat mountains.
    Simon Brooke, Oct 3, 2006
    #3
  4. Mark Space Guest

    Simon Brooke wrote:
    >
    > 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.


    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.
    Mark Space, Oct 4, 2006
    #4
  5. Simon Brooke Guest

    in message <%UDUg.1437$>, Mark Space
    ('') wrote:

    > Simon Brooke wrote:
    >>
    >> 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.

    >
    > 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.

    --
    (Simon Brooke) http://www.jasmine.org.uk/~simon/

    ;; Life would be much easier if I had the source code.
    Simon Brooke, Oct 4, 2006
    #5
  6. Lew Guest

    Mark Space wrote:
    >> 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 wrote:
    > 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
    Lew, Oct 5, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Corobori
    Replies:
    5
    Views:
    338
    Corobori
    May 7, 2005
  2. shruds
    Replies:
    1
    Views:
    709
    John C. Bollinger
    Jan 27, 2006
  3. Steve
    Replies:
    3
    Views:
    2,509
    Berlin Brown
    Jan 6, 2007
  4. Alan Isaac
    Replies:
    3
    Views:
    279
    Alan Isaac
    Jun 28, 2007
  5. oldyork90
    Replies:
    1
    Views:
    144
    Jeremy J Starcher
    Sep 10, 2008
Loading...

Share This Page