AddressBook/src/AddressesDB.java

Notes

Purpose
The purpose of the AddressBook.AddressesDB Java class is to establish a connection to the Public database and provide methods for safely accessing and updating the Addresses table held in that database without conflicting with other users accessing the same data.

Only one connection to the database is being shared by all the users of the AddressBook web application. This is perfectly adequate as long as the number of concurrent AddressBook users is going to be excessive. Keep It Simple Stupid - only add support for multiple connections if it is needed. Note, however, that if you are going to add these complexities, then do it here in this Java class.

AddressesDB Constructor
The AddressesDB constructor establishes a connection to the Public database.

Until recently, if you wanted to establish a connection to a database from a Servlet running under Tomcat you would have to code the JDBC driver, database URL, database username and database password into the Servlet like this:

    Class.forName ("org.gjt.mm.mysql.Driver").newInstance ();
    connection = DriverManager.getConnection ("jdbc:mysql://localhost/Public", "mysqlusername", "mysqlpassword");

It is not a good idea to code such information in the application's source code. They are all likely to change over time. Tomcat 4 solves all this by allowing the Servlet to access Tomcat and retrieve a connection from the DataSource that is defined in context.xml.

First the javax.naming.InitialContext is retrieved so that a JNDI lookup may be performed:

    InitialContext initialContext = new InitialContext ();

Then AddressBook's context is retrieved:

    Context envContext = (Context) initialContext.lookup ("java:comp/env");

Then the DataSource itself is retrieved:

    DataSource dataSource = (DataSource) envContext.lookup ("jdbc/Public");

Then the connection is retrieved from the DataSource:

    this.connection = dataSource.getConnection ();

Although the DataSource parameters were defined by Ant using the context.xml file, they may be changed using Tomcat's administration facilities. See Tomcat Administration for further information.

getConnection and releaseConnection
To ensure that a connection to the database can be shared safely, the connection resource is controlled using the synchronized methods getConnection and releaseConnection.

getConnection
First getConnection waits until the connection becomes free:

    while (this.connectionFree == false)
    {
        try
        {
            wait ();
        }
        catch (InterruptedException e)
        {
        }
    }

Then it sets the connection free indicator to not free, thus locking the connection until releaseConnection is called:

    this.connectionFree = false;

Then it notifies any other AddressDB threads that are waiting for a connection:

    notify ();

Finally it returns the connection:

    return this.connection;

releaseConnection
First releaseConnection waits until the connection becomes not free:

    while (this.connectionFree == true)
    {
        try
        {
            wait ();
        }
        catch (InterruptedException e)
        {
        }
    }

Then it sets the connection free indicator to free, thus unlocking the connection until getConnection is called:

    this.connectionFree = true;

Finally it notifies any other AddressDB threads that are waiting for a connection:

    notify ();

All the routines that access the database employ getConnection and releaseConnection, so that only one thread at a time may access the database.

getAddress
The getAddress method takes an id parameter and returns the Address for that id; if the address for that id cannot be found a null Address is returned.

First getAddress gets a free connection:

    this.getConnection ();

Then getAddress creates a select statement, and inserts the id in the appropriate place:

    PreparedStatement preparedStatement = this.connection.prepareStatement ("SELECT * FROM Addresses WHERE id = ?");
    preparedStatement.setString (1, id);

Then getAddress executes the select statement:

    ResultSet resultSet = preparedStatement.executeQuery ();

If the select statement returned data, then an Address is assembled from the data; the sql query is closed; the connection is released; and the Address is returned:

    if (resultSet.next ())
    {
        Address address = new Address
        (
            resultSet.getString (1), resultSet.getString (2), resultSet.getString (3),
            resultSet.getString (4), resultSet.getString (5), resultSet.getString (6), resultSet.getString (7)
        );
        preparedStatement.close ();
        this.releaseConnection ();
        return address;
    }

If the select statement returned no data, the select statement is closed; the connection is released; and a null Address is returned:

    else
    {
        preparedStatement.close ();
        this.releaseConnection ();
        return null;
    }

addAddress, deleteAddress, modifyAddress, and getAddresses
All these methods use similar techniques to the getAddress method.

close
close simply closes the connection:

    this.connection.close ();

Complete Source Code


//
//  AddressesDB.java
//

package AddressBook;

import java.sql.*;
import java.util.*;
import javax.sql.*;
import javax.naming.*;

public class AddressesDB
{
    Connection connection;
    private boolean connectionFree = true;
    private ArrayList addresses;

    public AddressesDB ()
    throws Exception
    {
        try
        {
            InitialContext initialContext = new InitialContext ();
            Context envContext = (Context) initialContext.lookup ("java:comp/env");
            DataSource dataSource = (DataSource) envContext.lookup ("jdbc/Public");
            this.connection = dataSource.getConnection ();
        }
        catch (Exception e)
        {
            throw new Exception ("Couldn't open connection to Public database: " + e.getMessage ());
        }
    }

    protected synchronized Connection getConnection ()
    {
        while (this.connectionFree == false)
        {
            try
            {
                wait ();
            }
            catch (InterruptedException e)
            {
            }
        }
        this.connectionFree = false;
        notify ();
        return this.connection;
    }

    protected synchronized void releaseConnection ()
    {
        while (this.connectionFree == true)
        {
            try
            {
                wait ();
            }
            catch (InterruptedException e)
            {
            }
        }
        this.connectionFree = true;
        notify ();
    }

    public Address getAddress (String id)
    {
        try
        {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement ("SELECT id, surname, firstname, street, district, city, postcode FROM Addresses WHERE id = ?");
            preparedStatement.setString (1, id);
            ResultSet resultSet = preparedStatement.executeQuery ();
            if (resultSet.next ())
            {
                Address address = new Address
                (
                    resultSet.getString (1), resultSet.getString (2), resultSet.getString (3),
                    resultSet.getString (4), resultSet.getString (5), resultSet.getString (6), resultSet.getString (7)
                );
                preparedStatement.close ();
                this.releaseConnection ();
                return address;
            }
            else
            {
                preparedStatement.close ();
                this.releaseConnection ();
                return null;
            }
        }
        catch (SQLException e)
        {
            this.releaseConnection ();
            return null;
        }
    }

    public int addAddress (Address address)
    {
        int rowsAffected = 0;
        try
        {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement ("INSERT INTO Addresses (surname, firstname, street, district, city, postcode) VALUES (?, ?, ?, ?, ?, ?)");
            preparedStatement.setString (1, address.getSurname ());
            preparedStatement.setString (2, address.getFirstname ());
            preparedStatement.setString (3, address.getStreet ());
            preparedStatement.setString (4, address.getDistrict ());
            preparedStatement.setString (5, address.getCity ());
            preparedStatement.setString (6, address.getPostcode ());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            this.releaseConnection ();
        }
        catch (SQLException e)
        {
            this.releaseConnection ();
            return 0;
        }
        return rowsAffected;
    }

    public int deleteAddress (String id)
    {
        int rowsAffected = 0;
        try
        {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement ("DELETE FROM Addresses WHERE id = ?");
            preparedStatement.setString (1, id);
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            this.releaseConnection ();
        }
        catch (SQLException e)
        {
            this.releaseConnection ();
            return 0;
        }
        return rowsAffected;
    }

    public int modifyAddress (Address address)
    {
        int rowsAffected = 0;
        try
        {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement ("UPDATE Addresses SET surname=?, firstname=?, street=?, district=?, city=?, postcode=? WHERE id =?");
            preparedStatement.setString (1, address.getSurname ());
            preparedStatement.setString (2, address.getFirstname ());
            preparedStatement.setString (3, address.getStreet ());
            preparedStatement.setString (4, address.getDistrict ());
            preparedStatement.setString (5, address.getCity ());
            preparedStatement.setString (6, address.getPostcode ());
            preparedStatement.setString (7, address.getId ());
            rowsAffected = preparedStatement.executeUpdate ();
            preparedStatement.close ();
            this.releaseConnection ();
        }
        catch (SQLException e)
        {
            this.releaseConnection ();
            return 0;
        }
        return rowsAffected;
    }

    public Collection getAddresses ()
    {
        addresses = new ArrayList ();
        try
        {
            this.getConnection ();
            PreparedStatement preparedStatement = this.connection.prepareStatement ("SELECT id, surname, firstname, street, district, city, postcode FROM Addresses");
            ResultSet resultSet = preparedStatement.executeQuery ();
            while (resultSet.next ())
            {
                Address address = new Address
                (
                    resultSet.getString (1), resultSet.getString (2), resultSet.getString (3),
                    resultSet.getString (4), resultSet.getString (5), resultSet.getString (6), resultSet.getString (7)
                );
                addresses.add (address);
            }
            preparedStatement.close ();
        }
        catch (SQLException e)
        {
            return null;
        }
        this.releaseConnection ();
        Collections.sort (addresses);
        return addresses;
    }

    public void close ()
    {
        try
        {
            this.connection.close ();
        }
        catch (SQLException e)
        {
            System.out.println (e.getMessage ());
        }
    }

}