W3C logo
Jigsaw

JSP, Beans and Databases


Jigsaw Home / Documentation Overview

Introduction

In this document we will describe how to communicate with a database in a jsp page using Java Beans and the org.w3c.tools.jdbc package.

Setup

We need three things before starting the experimentation:

  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

schema explaining 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

To describe how to use the org.w3c.tools.jdbc package we're going to create a sample application. We want to implement a search engine about movies. So we have a database with three tables: actors, movies and starring, we want to be able to insert and search data in these tables. For that we'll write some jsp pages that use some java beans.

The SQL Tables

Note: The SQL statements are compatible with postgresql, read the postgresql documentation for more details.

Movies
movieid Title Director Year
1 12 Monkeys Terry Gilliam 1995

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

Actors
actorid Firstname Lastname
1 Bruce Willis

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

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

The Java beans

For each table in our database we need to write one Java Bean that extends org.w3c.tools.jdbc.JdbcBeanAdapter or that implements the org.w3c.tools.jdbc.JdbcBeanInterface interface.

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

And now we just need to write our jsp pages, in this sample application we'll just write three pages, one used to insert data, one to search data and one to configure our beans.

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:

This will be used to initialize our beans.

addmovie.jsp

sample interface with a form

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

form to search for a movie

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

Thanks to Mark G. Woodruff (bugs fixed in findColumn)

Questions & Comments

This documentation is not complete, it needs to be improved. Send comments and questions at [email protected].