CSCI399
Autumn Session, 2009

Exercise 5

This exercise covers some preliminary work that you should attempt before you start Assignment 5. The tasks for this exercise provide some experience with the mechanics of building web applications that utilize JPA. JSP pages are also used - but there has been no attempt to compose "pretty" pages. Of course, one of the main reasons for using JSPs is to produce graphically effective pages – that is left as an exercise for the reader.

The exercises use the "soccerleague" table that you created in Exercise 2 and two additional tables that you must create and populate.

Note the example code is meant to serve as an example! For instance, in section 23 where there is code to add records, the “EntityManager” object is created within the scope of the transaction. Last year, lots of students rearranged the code and created the EntityManager object outside the transaction – and then found that their tables didn’t update! (If the EntityManager exists prior to the transaction, then it must be added to the transaction context – see APIs for EntityManager and UserTransaction.)

1. In a terminal (Linux shell) use sqlplus to connect to the CSCI database on wraith and create the following tables etc (remember to set LD_LIBRARY_PATH for sqlplus if you don't have it set properly in your .profile):

 
 
create sequence side7teamseq increment by 1 start with 1;
create sequence personseq increment by 1 start with 1;
 
create table side7team (
 teamid number primary key,
 teamname varchar(64)
);
 
 
create table persondata (
 personid number primary key,
 name varchar(32),
 gender varchar(8),
 myteam number,
 constraint persondata_gender_check
 check (gender in ('Male', 'Female')),
 constraint person_team
 foreign key(myteam)
 references side7team(teamid)
);
 
insert into side7team values (side7teamseq.nextval, 'Fleet of foot');
insert into side7team values (side7teamseq.nextval, 'Maulers');
insert into side7team values (side7teamseq.nextval, 'Cougars');
 
insert into persondata values (personseq.nextval, 'Adam', 'Male', 1);
insert into persondata values (personseq.nextval, 'James', 'Male', 1);
insert into persondata values (personseq.nextval, 'Malcolm', 'Male', 1);
insert into persondata values (personseq.nextval, 'Beth', 'Female', 1);
...
...
insert into persondata values (personseq.nextval, 'Dave', 'Male', 3);
insert into persondata values (personseq.nextval, 'Ed', 'Male', 3);
insert into persondata values (personseq.nextval, 'Carol', 'Female', 3);

The foreign key relationship in the tables will result in structural relationships amongst objects.

2. Start the NetBeans IDE:
3. Create a new web application, and add a Java package to source.
4. This web application requires the Toplink Essentials and JSTL libraries. Toplink Essentials has the classes from Oracle that implement the JPA specification. The JSTL (Java Standard Tag Libraries) contain the class definitions for the tag classes used in the JSPs.
   
5. Add a "persistence unit" - this is really an XML file that has data elements that identify the database server and schema that are to be used along with other meta-data that support JPA.
   
   The table generation strategy should be "None".
6. Create a new Entity class from Database.
   This is where the system does most of the work needed to compose code to handle data persistence.
   In the dialog for class creation, pick the Oracle database and then select the soccerleague table (defined in exercise 2).
   
7. Oracle's toplink defines an entity class and supplies some named queries that (bit like "PreparedStatements") that can be used to retrieve records based on restrictions on field values.

 
@Entity
@Table(name = "SOCCERLEAGUE")
@NamedQueries({
 @NamedQuery(name = "Soccerleague.findByGameid", 
 query = "SELECT s FROM Soccerleague s WHERE s.gameid = :gameid"), 
 @NamedQuery(name = "Soccerleague.findByPlayed", 
 query = "SELECT s FROM Soccerleague s WHERE s.played = :played"), 
 @NamedQuery(name = "Soccerleague.findByLocation", 
 query = "SELECT s FROM Soccerleague s WHERE s.location = :location"), 
 @NamedQuery(name = "Soccerleague.findByTeam1", 
 query = "SELECT s FROM Soccerleague s WHERE s.team1 = :team1"), 
 @NamedQuery(name = "Soccerleague.findByTeam2", 
 query = "SELECT s FROM Soccerleague s WHERE s.team2 = :team2"), 
 @NamedQuery(name = "Soccerleague.findByScore1", 
 query = "SELECT s FROM Soccerleague s WHERE s.score1 = :score1"), 
 @NamedQuery(name = "Soccerleague.findByScore2", 
 query = "SELECT s FROM Soccerleague s WHERE s.score2 = :score2")})
public class Soccerleague implements Serializable {
 private static final long serialVersionUID = 1L;
 @Id
 @Column(name = "GAMEID", nullable = false)
 private BigDecimal gameid;
 @Column(name = "PLAYED")
 @Temporal(TemporalType.DATE)
 private Date played;
 @Column(name = "LOCATION")
 private String location;
 @Column(name = "TEAM1")
 private String team1;
 @Column(name = "TEAM2")
 private String team2;
 @Column(name = "SCORE1")
 private BigInteger score1;
 @Column(name = "SCORE2")
 private BigInteger score2;
 
 public Soccerleague() {
 }
 
 public Soccerleague(BigDecimal gameid) {
 this.gameid = gameid;
 }
 
 public BigDecimal getGameid() {
 return gameid;
 }
 
 public void setGameid(BigDecimal gameid) {
 this.gameid = gameid;
 }
 
 public Date getPlayed() {
 return played;
 }
 
 public void setPlayed(Date played) {
 this.played = played;
 }
 
 
 ...
 
}
 

The only thing that the code generator missed was the definition of the associated sequence that is supposed to be used to get the primary keys.
This should be added manually:

 
public class Soccerleague implements Serializable {
 private static final long serialVersionUID = 1L;
 @Id
 @Column(name = "GAMEID", nullable = false)
 
 @GeneratedValue(generator="MySeq")
 @SequenceGenerator(name="MySeq",sequenceName="LEAGUESEQ", allocationSize=1)
 
 private BigDecimal gameid;

Each generator should get a unique name used internally by the system; this should be mapped to the name of the sequence actually defined in the database.

2. This test application is to have first a static HTML form that allows a user to enter a team name that is submitted to a servlet.
   The servlet will then retrieve all soccerleage records where the team is referenced (either as "team1" or "team2"). If there are no records, control is transferred to an error handling JSP. If there are records, the collection of matching records is passed to a reporting JSP.
3. Define a a new servlet.

 
public class CheckTeamServlet extends HttpServlet {
 private static final String jspFailPage = "ErrorReport.jsp";
 private static final String jspReporter = "ResultsReporter.jsp";
  @PersistenceUnit(unitName="JSPJPAapp1PU")
 private EntityManagerFactory emf; 
 
 private void doErrorReport(String error, HttpServletRequest request, HttpServletResponse response)
  throws ServletException, IOException {
 request.setAttribute("errMsg", error);
 RequestDispatcher dispatch =
 request.getRequestDispatcher(jspFailPage);
 dispatch.forward(request, response);
 } 
 /** 
 * Handles the HTTP <code>GET</code> method.
 * @param request servlet request
 * @param response servlet response
 */
 protected void doGet(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
  // processRequest(request, response);
 String myTeam = request.getParameter("myteam");
 if(myTeam==null || myTeam.length()<1) {
 doErrorReport("You didn't tell me your team", request, response);
 return;
 }
   EntityManager em = emf.createEntityManager(); 
 Query q = em.createNamedQuery("Soccerleague.findByTeam1");
 q.setParameter("team1", myTeam); 
 List liz1 = q.getResultList();
 q = em.createNamedQuery("Soccerleague.findByTeam2");
 q.setParameter("team2", myTeam); 
 List liz2 = q.getResultList();
 
 liz1.addAll(liz2);
 
 if(liz1.size()<1) {
 doErrorReport("Your team " + myTeam + " doesn't appear to have played any games", request, response);
 return;
 
 }
 em.close();
 request.setAttribute("matches", liz1);
 RequestDispatcher dispatch =
 request.getRequestDispatcher(jspReporter);
 dispatch.forward(request, response); 
 
 } 
 
}

This servlet doesn't generate response pages - it delegates that work to JSPs that are (supposedly) better at presentation work. The names of the JSPs are coded into the servlet (there are more elaborate ways of configuring such things).

 
 private static final String jspFailPage = "ErrorReport.jsp";
 private static final String jspReporter = "ResultsReporter.jsp";

The forwarding is done using a "RequestDispatcher". The doErrors private auxiliary function forwards error messages to an error reporter. If there are match data to report, they are forwarded to a result reporting servlet.
Data being forwarded are attached to the request:

 
 request.setAttribute("matches", liz1);
 RequestDispatcher dispatch =
 request.getRequestDispatcher(jspReporter);
 dispatch.forward(request, response); 

4. The servlet will need to use an EntityManager to access the database and retrieve data objects.
   This EntityManager needs metadata defining database engine etc; much like a "DataSource" object.
   With "DataSource" objects, the servlet has to have some "lookup" code that contacts a JNDI service and loads the datasource object with its data. Such lookup code just complicates the servlet and adds repetitive code that has to be written to get the application to work.
   "Resource injection" through "annotations" avoids the need to write any lookup code. The "annotation" will result in the compiler generating some extra code (that you never see) that will be run by the servlet container. This extra code does the lookup operation and stores the result in the servlet instance variable.

 
 @PersistenceUnit(unitName="JSPJPAapp1PU")
 private EntityManagerFactory emf; 

(The name of the persistence unit must match that used when the unit was defined - in step 5 above).

5. Really we need a customized query - equivalent to "select * from soccerleague where TEAM1='MyTeam' or TEAM2='MyTeam'. We could define such a query, but here we make use of the predefined queries associated with the soccerleague class.

 
EntityManager em = emf.createEntityManager(); 
Query q = em.createNamedQuery("Soccerleague.findByTeam1");
q.setParameter("team1", myTeam); 
List liz1 = q.getResultList();
q = em.createNamedQuery("Soccerleague.findByTeam2");
q.setParameter("team2", myTeam); 
List liz2 = q.getResultList();
liz1.addAll(liz2);
 

Logically that is equivalent to asking for a statement, running an SQL query with the statement, getting a ResultSet, looping through the ResultSet creating a new Soccerleague object for each row, filling in fields of Soccerleague object from columns in row of resultset, and then adding the Soccerleague object to a list. The new style JPA code is - simpler.

6. Two JSPs need to be defined. Both make use of the JSTL tag library so need a taglib page directive.
   The "error reporting" JSP expects a data element named errMsg (a String) to be attached to the request, this string holds the error message.

 
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">
 
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 
 
<html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>No results</title>
 </head>
 <body>
 <h2>No results!</h2>
 <p>Unable to complete your request because <c:out value="${requestScope.errMsg}" /></p>
 </body>
</html>

The result reporting JSP expects a collection called "matches" with the soccerleague entries. A loop, defined using JSTL core tags, arranges to print these entries in a HTML table.

 
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">
 
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 
 
<html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Results Page</title>
 </head>
 <body>
 <h2>Your team's record!</h2>
 <table align='center' border='1'>
 <caption class='medium'>Results</caption>
 <tr>
 <th align='center'>Date</th>
 <th align='center'>Venue</th>
 <th align='center'>Team1</th>
 <th align='center'>Team2</th>
 <th align='center'>Score1</th>
 <th align='center'>Score2</th>
 </tr>
 <c:forEach var="sl" items="${requestScope.matches}">
 <tr>
 <td><c:out value="${sl.played}"/></td>
 <td><c:out value="${sl.location}"/></td>
 <td><c:out value="${sl.team1}"/></td>
 <td><c:out value="${sl.team2}"/></td>
 <td><c:out value="${sl.score1}"/></td>
 <td><c:out value="${sl.score2}"/></td>
 </tr>
 </c:forEach>
 </table> 
 
 </body>
</html>

Note the use of "expression language" for accessing the fields of the beans - this is pseudo-Javascript style. ${requestScope.matches} - the variable called matches attached to the request. ${sl.played} - sl is an object of some type that should have a getPlayed member function, invoke that function to get a printable value.

7. Define a static HTML page that will submit a team name to the servlet:

 
<html>
 <head>
 <title></title>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 </head>
 <body>
 <form name="thisform" action="CheckTeamServlet">
  <input type="text" name="myteam" value="" size="30" />
 <input type="submit" value="Get Results" />
 </form>
 </body>
</html>
</pre>

8. Build, deploy, and run.
   Of course it runs first time:

---

9. The next servlet JSP combination will work with Entity classes for the "Seven a side team" data and associated "person" data. The code to lookup the members of a "seven a side" team is really quite simple using JPA!
10. First, create two new entity classes from the tables that you defined earlier.
    
    (The highlighting shows that Netbeans knows that if you add "Persondata" table you must add "Side7team" (the foreign key relation acts as a constraint).
11. The code generated for the Persondata entity will be similar to that generated for the SoccerLeague entity class.
    The code for the Side7team entity is a bit more interesting:

 
 
@Table(name = "SIDE7TEAM")
@NamedQueries({
 @NamedQuery(name = "Side7team.findByTeamid", query = "SELECT s FROM Side7team s WHERE s.teamid = :teamid"), 
 @NamedQuery(name = "Side7team.findByTeamname", query = "SELECT s FROM Side7team s WHERE s.teamname = :teamname")})
public class Side7team implements Serializable {
 private static final long serialVersionUID = 1L;
 @Id
 @Column(name = "TEAMID", nullable = false)
 @GeneratedValue(generator="MySeq7")
 @SequenceGenerator(name="MySeq7",sequenceName="SIDE7TEAMSEQ", allocationSize=1)
 private BigDecimal teamid;
 @Column(name = "TEAMNAME")
 private String teamname;
  @OneToMany(mappedBy = "myteam")
 private Collection<Persondata> persondataCollection;
 
 ...
  public Collection<Persondata> getPersondataCollection() {
 return persondataCollection;
 }
 ...

The database meta-data translator system has noticed the foreign key relation between the Persondata table and the Side7team table. It has inferred that each Side7team "owns" a collection of Persondata records. The classes that it generates express this relationship - via the persondataCollection data member.
If you load in a Side7team object, you can simply ask it for its team members - you get a collection of Persondata objects..
When do these members get loaded? The rules here are rather complex. Typically, they aren't loaded until you try to access them.

12. Fix up the generated entity classes by adding the appropriate references to the sequences used to generate primary keys (as shown in fragment above).
13. Create a new servlet class to handle a GET request for team details (also duplicate the static HTML form, the new one points to this second servlet).
    This servlet uses an entity manager to run one of the generated "Named Queries" to fetch the team record for named team.

 
 protected void doGet(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
 // processRequest(request, response);
 String myTeam = request.getParameter("myteam");
 if(myTeam==null || myTeam.length()<1) {
 doErrorReport("You didn't tell me your team", request, response);
 return;
 }
 EntityManager em = emf.createEntityManager(); 
 Query q = em.createNamedQuery("Side7team.findByTeamname");
 q.setParameter("teamname", myTeam); 
 Side7team s7t = (Side7team) q.getSingleResult();
 
 if(s7t==null) {
 doErrorReport("Don't think that there is a team called " +
 myTeam, request, response);
 return;
 }
 
 
 em.close();
  request.setAttribute("teaminfo", s7t);
 RequestDispatcher dispatch =
 request.getRequestDispatcher(jspReporter);
 dispatch.forward(request, response); 
 
 } 

The team record, with attached persondata records, is forwarded to the display servlet.

14. Compose the display servlet:

 
 
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">
 
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%> 
 
<html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <title>Team Page</title>
 </head>
 <body>
 <h2><c:out value="${requestScope.teaminfo.teamname}"/></h2>
 <table align='center' border='1'>
 <caption class='medium'>Members</caption>
 <tr>
 <th align='center'>Name</th>
 <th align='center'>Gender</th>
 
 </tr>
 <c:forEach var="pd" items="${requestScope.teaminfo.persondataCollection}">
 <tr>
 <td><c:out value="${pd.name}"/></td>
 <td><c:out value="${pd.gender}"/></td>
 </tr>
 </c:forEach>
 </table> 
 
 </body>
</html>
 

15. Finish the example, get it to run!
    

---

16. Finally, create a static HTML form, servlet, and JSP combination that will allow you to add a new team to the side7team table.
    The form is just a duplicate to the current ones, but it points to the new servlet class.
17. Add a new servlet "AddTeamServlet". Its doPost method picks up the team name and uses this when creating a new instance of the Side7team class.
    This object exists in memory, not the persistent data store. The servlet must use an EntityManager to "persist" the new object.
    Since the EntityManager is trying to change the database, it needs to work in a transactional context. The context is supplied by the servlet container (and "injected" into the servlet using the code that is generated for the @Resource annotation).
    The primary key for the new persisted Side7team object is arranged by the persistence system, using the sequence as specified. It gets added to the in-memory Side7team object so the key can be reported.
    The servlet passes the updated Side7team object to a JSP.

 
 
public class AddTeamServlet extends HttpServlet {
 @Resource
 private UserTransaction utx; 
 @PersistenceUnit(unitName="JSPJPAapp1PU")
 private EntityManagerFactory emf; 
 private static final String jspFailPage = "ErrorReport.jsp"; 
 private static final String jspAddReporter = "AddReport.jsp";
 
 private void doErrorReport(String error, HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
 request.setAttribute("errMsg", error);
 RequestDispatcher dispatch =
  request.getRequestDispatcher(jspFailPage);
 dispatch.forward(request, response);
 } 
 
 /** 
 * Handles the HTTP <code>POST</code> method.
 * @param request servlet request
 * @param response servlet response
 */
 protected void doPost(HttpServletRequest request, HttpServletResponse response)
 throws ServletException, IOException {
 String myTeam = request.getParameter("myteam");
 if(myTeam==null || myTeam.length()<1) { 
 doErrorReport("You didn't tell me a team name", request, response);
 return;
 }
 EntityManager em = null;
 Side7team tm7 = null;
 try {
 
 utx.begin();
 em = emf.createEntityManager();
 
 tm7 = new Side7team();
 tm7.setTeamname(myTeam);
 
 em.persist(tm7);
 utx.commit();
  
 }
 catch (javax.transaction.RollbackException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 } catch (javax.transaction.NotSupportedException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 } catch (HeuristicMixedException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 } catch (HeuristicRollbackException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 } catch (SecurityException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 } catch (IllegalStateException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 } 
 catch (SystemException ex) {
 Logger.getLogger(AddTeamServlet.class.getName()).log(Level.SEVERE, null, ex);
 }
 finally { em.close(); }
 
 
 request.setAttribute("teaminfo", tm7);
 RequestDispatcher dispatch =
  request.getRequestDispatcher(jspAddReporter);
 dispatch.forward(request, response); 
 }
 
 
}
 

18. Get the add team combination to work.

When you have completed those exercises you can start on Assignment 5.