Introduction

Setup

1. Install JSP in Jigsaw (read the tutorial)
2. Install a database, (eg postgresql) if you don't have one already installed on your system.
3. Get the JDBC drivers relative to your database (you can find them at javasoft)

How it works

The system is built with some Java Beans linked to the database via a serializer (JdbcBeanSerializer). Each bean is a "mirror" of a table in the database, its properties are strictly equivalents (same name, same type) to the table colums. The serializer is in charge to build the SQL request depending on the values of the bean properties, it use a JdbcServer to send the request to the database (The JdbcServer manage a pool of persistent connections to the database). The serializer read the result and fill the bean(s) with the collected values. Then the jsp page use the serializer as an iterator and read the bean properties.

Sample application

The SQL Tables

CREATE TABLE movies (
    movieid    SERIAL NOT NULL PRIMARY KEY,
    title      varchar(255) NOT NULL,
    director   varchar(30),
    year       int4
)
      

CREATE TABLE actors (
    actorid    SERIAL NOT NULL PRIMARY KEY,
    firstname  varchar(255) NOT NULL,
    lastname   varchar(255) NOT NULL
)
      

CREATE TABLE starring (
    movieid   int4,
    actorid   int4,
    PRIMARY KEY (movieid, actorid)
)
      

The Java beans

Movie Bean

public class MovieBean extends org.w3c.tools.jdbc.JdbcBeanAdapter {
    
    protected int    movieid  = -1;
    protected String title    = null;
    protected String director = null;
    protected int    year     = -1;

    public void setMovieid(int movieid) {
        int oldvalue = this.movieid;
        this.movieid = movieid;
        pcs.firePropertyChange("movieid", oldvalue, movieid);
    }

    public int getMovieid() {
        return movieid;
    }

    public void setTitle(String title) {
        String oldvalue = this.title;
        this.title      = title;
        pcs.firePropertyChange("title", oldvalue, title);
    }

    public String getTitle() {
        return title;
    }

    public void setDirector(String director) {
        String oldvalue = this.director;
        this.director   = director;
        pcs.firePropertyChange("director", oldvalue, director);
    }

    public String getDirector() {
        return director;
    }

    public void setYear(int year) {
        int oldvalue = this.year;
        this.year    = year;
        pcs.firePropertyChange("year", oldvalue, year);
    }

    public int getYear() {
        return year;
    }
}
      

Actor Bean

public class ActorBean extends org.w3c.tools.jdbc.JdbcBeanAdapter {
    
    protected int    actorid   = -1;
    protected String firstname = null;
    protected String lastname  = null;

    public void setActorid(int actorid) {
        int oldvalue = this.actorid;
        this.actorid = actorid;
        pcs.firePropertyChange("actorid", oldvalue, actorid);
    }

    public int getActorid() {
        return actorid;
    }

    public void setFirstname(String firstname) {
        String oldvalue = this.firstname;
        this.firstname  = firstname;
        pcs.firePropertyChange("firstname", oldvalue, firstname);
    }

    public String getFirstname() {
        return firstname;
    }

    public void setLastname(String lastname) {
        String oldvalue = this.lastname;
        this.lastname   = lastname;
        pcs.firePropertyChange("lastname", oldvalue, lastname);
    }

    public String getLastname() {
        return lastname;
    }

}
      

Starring Bean

public class StarringBean extends org.w3c.tools.jdbc.JdbcBeanAdapter {
    
    protected int movieid = -1;   
    protected int actorid = -1;
    
    // related beans
    protected MovieBean movieBean = null;
    protected ActorBean actorBean = null;

    public void setMovieid(int movieid) {
        int oldvalue = this.movieid;
        this.movieid = movieid;
        pcs.firePropertyChange("movieid", oldvalue, movieid);
    }

    public int getMovieid() {
        return movieid;
    }

    public void setActorid(int actorid) {
        int oldvalue = this.actorid;
        this.actorid = actorid;
        pcs.firePropertyChange("actorid", oldvalue, actorid);
    }

    public int getActorid() {
        return actorid;
    }

    public void setMovieBean(MovieBean movieBean) {
        Object old = this.movieBean;
        this.movieBean = movieBean;
        pcs.firePropertyChange("movieBean", old, movieBean);
    }

    public MovieBean getMovieBean() {
        return movieBean;
    }

    public void setActorBean(ActorBean actorBean) {
        Object old = this.actorBean;
        this.actorBean = actorBean;
        pcs.firePropertyChange("actorBean", old, actorBean);
    }

    public ActorBean getActorBean() {
        return actorBean;
    }

}
      

As you can see, the Java Beans are strictly equivalent to the table they represents. The name of the bean properties MUST be strictly equals to the column names in the SQL table. Note that StarringBean has two special properties that are one ActorBean and one MovieBean, this will be used by the JdbcBeanSerializer to perfom SQL request on several tables at a time.

It is necessary to fire a PropertyChangeEvent each time a property is modified (the Serializer needs it).

The Jsp pages

config.jsp

<jsp:useBean id="configbean" 
             class="org.w3c.tools.jdbc.JdbcBeanAdapter" 
             scope="session" >
    <jsp:setProperty name="configbean" 
                     property="jdbcDriver" 
                     value="org.postgresql.Driver" />
    <jsp:setProperty name="configbean" 
                     property="jdbcURI" 
                     value="jdbc:postgresql://our.db.host/ourdb" />
    <jsp:setProperty name="configbean" 
                     property="jdbcUser" 
                     value="joeuser" />
    <jsp:setProperty name="configbean" 
                     property="jdbcPassword" 
                     value="our password" />
</jsp:useBean>

In config.jsp we specify the common values for our jdbc beans:

• The jdbc driver
• The database URI
• The jdbc username
• The jdbc password

This will be used to initialize our beans.

addmovie.jsp

First we initialize moviebean:

<%@ page import="org.w3c.tools.jdbc.*" %>
<%@ include file="config.jsp" %>
<!-- initialize moviebean and specify the table name "movies" -->
<jsp:useBean id="moviebean" class="MovieBean" scope="request">
    <%
        JdbcBeanUtil.initializeBean(configbean, moviebean, "movies");
    %>
</jsp:useBean>
        

As you can see, the serializer is used to insert the values received during a POST request into the database.

<!-- set properties -->
<jsp:setProperty name="moviebean" property="*" />

<!-- html starts here -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
  <head>
    <title>Add Movie</title>
  </head>
  <body>
    <h1>Add Movie</h1>
<%
    if (request.getMethod().equals("POST")) {
       // Insert movie in database
       if (moviebean.getSerializer().insert()) {
%>
     Movie added!
<%
       } else {
%>
     SQL Error.
<%
    } else {
%>
    <form method="POST">
       <table border="0">
       <tr>
       <td>Title : </td><td><input type="text" name="title"></td>
       </tr><tr>
       <td>Director: </td><td><input type="text" name="director"></td>
       </tr><tr>
       <td>Year : </td><td><input type="text" name="year" size="4"></td>
       </tr><tr>
       <td colspan="2"><input type="submit"></td>
       </tr>
       </table>
    </form>
<%
    }
%>
  </body>
</html>
      

This is a sample, we should verify that all mandatory properties are specified and valid before calling the serializer (moviebean.getSerializer().insert()).

search.jsp

In the first part of the jsp page we initialize our three beans.

<%@ page import="org.w3c.tools.jdbc.*" %>
<%@ include file="config.jsp" %>

<!-- initialize moviebean and specify the table name "movies" -->
<jsp:useBean id="moviebean" class="MovieBean" scope="request">
    <%
        JdbcBeanUtil.initializeBean(configbean, moviebean, "movies");
    %>
</jsp:useBean>

<!-- initialize actorbean and specify the table name "actors" -->
<jsp:useBean id="actorbean" class="ActorBean" scope="request">
    <%
        JdbcBeanUtil.initializeBean(configbean, actorbean, "actors");
    %>
</jsp:useBean>

<!-- initialize starringbean and specify the table name "starrings" -->
<jsp:useBean id="starringbean" class="StarringBean" scope="request">
    <%
        JdbcBeanUtil.initializeBean(configbean, starringbean, "starrings");
        starringBean.setMovieBean(moviebean);
        starringBean.setActorBean(actorbean);
    %>
</jsp:useBean>

As you can see, starringbean is associated with moviebean and actorbean. Then moviebean and actorbean will be automatically updated by the starringbean serializer during the result iteration.

<!-- set properties -->
<jsp:setProperty name="actorbean" property="*" />

<!-- html starts here -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><html>
  <head>
    <title>Search movies</title>
  </head>
  <body>
<%
    if (request.getMethod().equals("POST")) {
        JdbcBeanSerializer serializer = starringbean.getSerializer();
        serializer.select();
        if (serializer.first()) {
            do {
%>
    <!-- display result -->
    <jsp:getProperty name="moviebean" property="title"/>,
    (<jsp:getProperty name="moviebean" property="year"/>)<br>
    Directed by <jsp:getProperty name="moviebean" property="director"/>
    <hr>
<%            
            } while (serializer.next());
        }
    } else {
%>
    <h1>Search movies starring:</h1>
       <form method="POST">
       <table border="0">
       <tr>
       <td>Actor's Firstname : </td>
       <td><input type="text" name="firstname"></td>
       </tr><tr>
       <td>Actor's Lastname: </td>
       <td><input type="text" name="lastname"></td>
       </tr><tr>
       <td colspan="2"><input type="submit"></td>
       </tr>
       </table>
    </form>
<%
    }
%>
    <hr>
  </body>
</html>
      

Acknowledgments

Questions & Comments