JavaServer Pages, Second Edition

7.4 Other User Preferences

So far, the user preferences page has dealt only with simple properties: the ones that do not involve the join tables. The problem with the remaining properties is twofold: figuring out how to (1) display the user's current choices and (2) allow them to be changed. The solutions to these problems will be different for sections and keywords because of the different ways this information will be used.

A good way to figure out how to tackle such problems is to solve first them in raw SQL. Then the SQL can be moved into the bean. Showing the list of sections the user has selected not to display is easy:

select section.name
from section,user_sections
where section.section_id = user_sections.section_id
and user_section.user_id = ?

The question mark would get filled in with a sql:param from the current user.

Unfortunately, what is needed is the inverse of this: a list of all the sections that the user does want. This could be done in three steps: (1) run the query, (2) run another query to get the list of all sections, and (3) remove the items in the first list from the second list in some Java code. This would work, but as a general rule, it is worth trying to use as few queries as possible and to do as much work with those queries as possible. This is partly for the sake of efficiency, as each query will take some time and impose some overhead on the database and the network. In general, databases will also be able to manipulate data more efficiently than the equivalent Java code.

It turns out that it is possible to cook up a query that will do all the necessary work in one step. This conceptually does the same thing that could be done manually in Java: Select the items from user_section, and remove the matching items from section:

select * from section
where section_id not in
(select section_id from user_section
 where user_info_id = ?)

The inner select, the one in parentheses, retrieves the list of sections that the user does not want; then the SQL keywords not in remove those sections from the outer select. Now that the query has been designed, it will be put into the EditionBean as a new property: selectedSections. This will alter the section list in the navigation as shown in Listing 7.5.

Listing 7.5 The customized section list

<%@ taglib prefix="c"
    uri="http://java.sun.com/jstl/core" %>

<jsp:useBean
  id="edition7"
  class="com.awl.jspbook.ch07.EditionBean"
  scope="session"/>

<jsp:useBean
  id="currentSection"
  class="com.awl.jspbook.ch07.SectionBean"/>

<jsp:setProperty
  name="currentSection"
  property="sectionId"/>

<c:forEach items="${edition7.selectedSections}"
           var="section">

  <c:if
   test="${currentSection.sectionId == section.sectionId}">
   *
  </c:if>

  <a href="<c:url value="section.jsp">
   <c:param name="sectionId" value="${section.sectionId}"/>
  </c:url>"><c:out value="${section.name}"/></a><br>
</c:forEach>

One other point needs to be made about this example. The query to get sections requires user_id as a parameter. If the user has not yet logged in, no ID will be available, and the query will fail. This condition could be checked in the JSP with a c:choose tag. If user.userId is empty, the page would then do what it did previously and iterate the sections from edition.sections. However, this test has been placed in the bean for all the usual reasons about keeping the view simple. In this case, it would be more correct to put this check in the controller, as the model needs to be controlled based on an external criterion.

Similarly, it will be necessary to notify the EditionBean of the user's ID when the user logs in. This can be done with a simple addition to the login handler page:

<c:set name="edition7.userId" value="${user7.userId}"/>

Now that the navigation can make use of the user's section choices, a means for the user to alter them is needed. The logical user interface for this would be a list of every section, with a check box next to the ones the user would like to see. When the user goes to edit the list, the sections already selected should be checked so the user does not have to reenter the choices whenever adding or removing only one.

This again requires connection between the section and user_info tables but with an additional complication. The page cannot show only the sections the user has selected, as that would not allow the person to add one. Nor can it show only the ones the user has not selected, as there would then be no way to remove one. This could be handled with two queries, first iterating one set of sections and then the other. A better solution would be to select all the sections in one shot, along with an indicator as to whether the user has selected each.

This can be done with yet another feature of SQL: outer join. The idea is that a normal, or inner, join between two tables A and B will have one row for each value common to both tables. An outer join might have one row for every row in A. If B has matching data, that data will be available; if not, those values will be marked as NULL.

To make these ideas more concrete, consider the two tables defined next.

create table character (character_id int,
                        character_name char(10))
create table actor (actor_id int, actor_name char(10))

insert into character values(1,'John Crichton')
insert into character values(2,'Aeryn Sun')
insert into character values(3,'Chiana')

insert into actor values(1,'Ben Browder')
insert into actor values(3,'Gigi Edgley')

A regular inner join could be used to get a list of actors and characters:

select character_name,actor_name
from actor, character
where character_id = actor_id

The result would be in the following table:

John Crichton	Ben Browder
Chiana	Gigi Edgley

However, this table is missing information about characters for whom the corresponding actor is not available. This can be remedied with an outer join:

select character_name,actor_name
from character
left join actor
on character_id = actor_id

This produces the following table:

John Crichton	Ben Browder
Aeryn Sun	NULL
Chiana	Gigi Edgley

This table contains all the information we have available and might serve to remind someone to insert "Claudia Black" into the actor table at some point. It is now fairly straightforward to use these ideas to construct an equivalent query for users and sections, with an extra field to indicate which ones the user does not want:

select section_name,name,user_id from section
left join user_section
on section.section_id = user_section.section_id
where user_section.user_id = ?

The result will have one row for each section. For those that the user does not want, the row will also have the user's ID; sections that the user does want will have a value of NULL for this column.

Hiding this query in the EditionBean will require a little more work. The easiest way to do this is to add a new property, selected, to the SectionBean and let the EditionBean set this property based on the results of the query. Pages can then obtain this specially marked list of sections through a new allSections property, which can be used in the user preferences page, as shown in Listing 7.6.^[1]

^[1] Of course, it would also be possible to use this property instead of selectedSections in the navigation by using the value of the selected flag to determine whether to show the section. However, doing it that way would have missed out on a perfect opportunity to introduce the concept of nested selects, which is well worth knowing.

Listing 7.6 Selecting sections

<jsp:setProperty
  name="user7"
  property="clearSections"
  value="true"/>

<tr>
  <td class="label">Which sections do you want?</td>
  <td>
  <c:forEach items="${edition7.allSections}" var="section">
    <input
      type="checkbox"
      name="sections"
      value="<c:out value="${section.sectionId}"/>"
      <c:if test="${section.selected}">CHECKED</c:if>>
    <c:out value="${section.name}"/><br>
  </c:forEach>
  </td>
</tr>

Note that options are marked as checked if the corresponding field is empty, because the user should be shown the sections wanted, but the table keeps track of those not wanted.

When the form is submitted, the UserInfoBean will get passed an array of selected section IDs, which it must then use to add or remove entries in the user_section table. This requires a bit of data manipulation in the Java layer, which can be found in the code for the UserInfoBean on the CD-ROM accompanying this book.

The keywords list, which will work almost exactly the same as the section list, will use an outer join to select all the available keywords and simultaneously flag which ones the user has selected. The result is easily added to the user preferences page and is shown in Listing 7.7.

Listing 7.7 Selecting keywords

<jsp:setProperty
  name="user7"
  property="clearKeywords"
  value="true"/>

<tr>
  <td class="label">
    Select keywords in which you are interested:
  </td>
  <td>
    <c:forEach items="${edition7.allKeywords}" var="word">
      <input
        type="checkbox"
        name="keywords"
        value="<c:out value="${word.keywordId}"/>"
      <c:if test="${word.selected}">CHECKED</c:if>>
      <c:out value="${word.name}"/><br>
    </c:forEach>
  </td>
</tr>

The new user customization page, with these two options added, is shown in Figure 7.2.

Figure 7.2. The new customization page.

[View full size image]

Finally, the user's selected keywords and the set of keywords associated with each article will be used to compute for each article a score that will be displayed on the front page and the section page. Such a score can draw the user's attention to stories he or she is most likely to find interesting.

This score will be computed by examining each keyword; if both the user and the article either have or do not have that keyword, it will count for one point. The final score will then be the total number of points, divided by the total number of keywords and multiplied by 100 to produce a percentage. Of course, such a complex calculation should never be done in the view, so it will be added to the ArticleBean. The ArticleBean will therefore need to know for which user its score should be computed, but this is easily handled by a jsp:setProperty tag. This modifies the section page as shown in Listing 7.8, with the result shown in Figure 7.3.

Figure 7.3. The new section page.

[View full size image]

Listing 7.8 The new section page

<jsp:useBean
  id="currentSection"
  class="com.awl.jspbook.ch07.SectionBean"/>

<jsp:setProperty
  name="currentSection"
  property="sectionId"/>
<dl>
  <c:forEach items="${currentSection.articles}"
             var="article">
    <dt><a href="<c:url value="article.jsp">
    <c:param name="articleId"
             value="${article.articleId}"/>
    </c:url>"><c:out value="${article.headline}"/></a>
    <c:if test="${user7.isLoggedIn}">
      <c:set target="${article}"
        property="userInfoId"
        value="${user7.userInfoId}"/>

      (Score: <c:out value="${article.score}"/>)
    </c:if>
    <dd><c:out value="${article.summary}"/>
  </c:forEach>
</dl>