Online Store Application - Structuring the Database Tables

The BuyDirect application uses four tables: Categories, Products, Orders, and OrderDetails. The Categories table stores product categories, as shown in Table 14-1.

The Products table stores the details of each product sold in the online store. Table 14-2 lists the column names and data types in this table.

The Orders table, shown in Table 14-3, holds order information, including delivery addresses, credit card details, and contact names. There is a unique identifier for each order to link all shopping items in that order. These shopping items are stored in the OrderDetails table, shown in Table 14-4.

Table 14-1 The Categories Table in the Database

Table 14-2 The Products Table in the Database

Table 14-3 The Orders Table in the Database

Table 14-4 The OrderDetails Table in the Database

Java Database Connectivity (JDBC) is the technology for accessing a database and manipulating its data in a Java application. JDBC has two parts: the JDBC Core Application Programming Interface (API) and the JDBC Optional Package API. The most recent version of JDBC is 3.0 and it is included in Java 2, Standard Edition (J2SE) version 1.4.1 and later. (At the time of the writing of this book, JDBC version 4.0 was being developed.)

Basically, to access a database, you simply connect to the database server. Then you can send a SQL query to retrieve data from a table, update records, insert new records, delete data, and so on.

You need to perform four steps to access a database with JDBC:

• Load the JDBC database driver.
  
• Create a connection.
  
• Create a statement.
  
• Create a result set, if you expect the database server to send back some data.

NOTE

You need to set up an Open Database Connectivity (ODBC) Data Source Name (DSN) on your computer if you want to use the Microsoft Access database provided on the CD-ROM that accompanies this book. To set up an ODBC DSN, follow these steps:

1. Open the ODBC Data Source Administration dialog  
   box from the Control Panel.
   
2. Click the System DSN tab.
   
3. Click the Add button.
   
4. Select a driver name from the list of ODBC drivers.
   
5. Enter the information requested in the Setup dialog 
   box that appears. For accessing the buydirect.mdb 
   Microsoft Access database, the name for the DSN 
   must be buydirect.
   
6. Click OK to close the dialog box

The DatabaseUtil class, explained in the next section, provides methods for the rest of the application. For production, you may also want to use connection pooling to make your application more scalable.

As part of our BuyDirect application, various business objects need to access the database. A utility class called DatabaseUtil class is provided for this purpose. This class provides the methods used by other classes to access and manipulate the data in the database.

DatabaseUtil Class Methods

The DatabaseUtil class methods work with the Category, ProductBean, OrderBean, ShoppingCartBean, and ShoppingItemBean classes, which are described in the “Defining Classes for the Application” section later in this chapter. The DatabaseUtil class provides the following methods:

• getCategories This method accepts no argument and returns an ArrayList containing all of the categories from the Categories table. Each category is represented by the Category class. The signature of the getCategories method is as follows:

        public ArrayList getCategories()

• searchProducts This method returns an ArrayList containing all of the products found for a given search key. A product is represented by the ProductBean class. Here is the signature of the searchProducts method:

        public ArrayList searchProducts(String searchKey)

• getProductsByCategory This method returns an ArrayList containing all of the products belonging to the specified category identifier passed in as the argument to this method. A product is represented by the ProductBean class. The signature of the getProductsByCategory method is as follows:

        public ArrayList getProductsByCategory(String  
     categoryId)

• getProductDetails This method accepts a product identifier and returns the details of the product encapsulated in an instance of ProductBean. The signature of the getProductDetails method is as follows:

        public ProductDetailsBean getProductDetails(String
     productId)

• insertOrder You use this method to insert a purchase order. This method accepts two arguments: an order and a shopping cart. An order, represented by the OrderBean class, contains the delivery and payment information (the contact name, delivery address, and credit card details). The shopping cart, represented by the ShoppingCartBean class, contains all the shopping items. A shopping item is represented by the ShoppingItemBean class. Here is the signature of the insertOrder method:

     public synchronized void insertOrder (OrderBean       
     order, 
       shoppingCartBean shoppingCart) {

In addition, the DatabaseUtil class has two methods for initializing values required for accessing the database: setJdbcDriver and setDbUrl.

The DatabaseUtil Instance

The application needs only one instance of this class. To avoid creating multiple instances of this class, the application instantiates the class at startup and stores it in ServletContext, in the attribute named DATABASE_UTIL. The instance can then be retrieved by using the following code:

FacesContext facesContext = FacesContext.getCurrentInstance();
ServletContext servletContext = (ServletContext)
  facesContext.getExternalContext().getContext(); DatabaseUtil dbUtil = (DatabaseUtil)
  servletContext.getAttribute("DATABASE_UTIL");

To store the DatabaseUtil instance at application startup, you use the servlet context listener called AppContextListener, shown in Listing 14-2.

Listing 14-2  The AppContextListener Class

 package buydirect;
 import javax.servlet.ServletContext;
 import javax.servlet.ServletContextEvent;
 import javax.servlet.ServletContextListener;
 public class AppContextListener implements ServletContextListener {
  public void contextInitialized(ServletContextEvent event) {
    DatabaseUtil dbUtil = new DatabaseUtil(); 
    ServletContext servletContext = event.getServletContext();
    String jdbcDriver =
      servletContext.getInitParameter("jdbcDriver");
    String dbUrl = servletContext.getInitParameter("dbUrl");
    dbUtil.setJdbcDriver(jdbcDriver);
    dbUtil.setDbUrl(dbUrl);
    servletContext.setAttribute("DATABASE_UTIL", dbUtil);
  }
  public void contextDestroyed(ServletContextEvent event) {
    ServletContext servletContext = event.getServletContext();
    servletContext.removeAttribute("DATABASE_UTIL");
  }
}

The DatabaseUtil instance needs the dbUrl and jdbcDriver information provided as context initial parameters in the deployment descriptor (web.xml file), as follows:

 <context-param>
   <param-name>dbUrl</param-name>
   <param-value>jdbc:odbc:buydirect</param-value>
 </context-param>
 <context-param>
   <param-name>jdbcDriver</param-name>
   <param-value>sun.jdbc.odbc.JdbcOdbcDriver</param-value>    
 </context-param>

NOTE
--------------------------------------------------------------------You need to change the values of the dbUrl and jdbcDriver parameters to the values of your database server’s database URL and JDBC driver.

This means that you can use any database server from any vendor, as long as you provide the correct JDBC driver and URL to the database. Note that you must also copy the JDBC driver to the WEB-INF/lib directory.

This article is excerpted from JavaServer Faces Programming by Budi Kurniawan (McGraw-Hill, 2003; ISBN 0072229837). Check it out at your favorite bookstore today. Buy this book now.

Next: The DatabaseUtil Class >>
 

More Java Articles
More By McGraw-Hill/Osborne

Comments On: Online Store Application

· I go thru the online application, and I'm new to this topic, can anyone recreate...   >>> Post your comment now!