| Most Internet users are familiar with online forums. These systems go by many names message boards, discussion threads, newsgroups, bulletin boards. Online forums allow users to post messages to an area so the messages can be viewed by other users. Users can post messages as well as responses to other people's messages. Figure 7.1 shows our forum application. 
As you can see, this Web application allows users to post messages. To accomplish this, the application requires several interrelated services: Our forum application provides each of these services using JSTL SQL tags. One common thread to all of these services is the underlying database. Database Layout Behind every good Web application is a database structure. Our forum application is no exception. In this section, we discuss the structure of the forum database. This structure will remain the same throughout the multiple versions of this application that we develop in the next few chapters. The forum application has been tested primarily with MySQL and Microsoft Access. You can obtain drivers for both at no charge. For more information on how to install MySQL, refer to Appendix C, "Installing MySQL." NOTE Even if you do not own a copy of Microsoft Access, you can download the necessary drivers from Microsoft. With these drivers installed, you will be able to run the Access examples presented in this chapter. Download these drivers from http://www.microsoft.com/data/download.htm.
Setting Up the Forum on SQL Databases First, we examine how to set up the forum on a SQL database. To properly set up your database for the forum, you must create a database named forum and assign a user named forumuser to that database. The procedure for creating databases in MySQL is covered in Appendix C. Once you've created this database, you must create the appropriate tables. You can do this using the database-creation script shown in Listing 7.1. Executing the script against your database will set up the appropriate tables. We tested our script with MySQL; if you are using another database, you may have to make some minor changes. Listing 7.1 Forum-Creation Script (createforum.sql)DROP DATABASE IF EXISTS forum; CREATE DATABASE forum; USE forum; CREATE TABLE t_access( c_access VARCHAR(1) NOT NULL, c_description VARCHAR(32) NOT NULL, PRIMARY KEY (c_access) ); INSERT INTO t_access(c_access,c_description) VALUES( 'A','Admin User' ); INSERT INTO t_access(c_access,c_description) VALUES( 'R','Regular User' ); INSERT INTO t_access(c_access,c_description) VALUES( 'G','Guest User' ); CREATE TABLE t_users( c_uid VARCHAR(32) NOT NULL, c_pwd VARCHAR(32) NOT NULL, c_accesses INTEGER NOT NULL, c_first DATETIME NOT NULL, c_last DATETIME NOT NULL, c_bad INTEGER NOT NULL, c_posted INTEGER NOT NULL, c_type VARCHAR(1) NOT NULL, PRIMARY KEY(c_uid), FOREIGN KEY(c_type) REFERENCES t_access(c_access) ); INSERT INTO t_users(c_uid,c_pwd,c_accesses,c_first,c_last,c_bad,c_posted,c_type) values( 'ADMIN','admin',0,'04-01-02','2002-04-01',0,0,'A' ); INSERT INTO t_users(c_uid,c_pwd,c_accesses,c_first,c_last,c_bad,c_posted,c_type) values( 'TESTUSER','test',0,'04-01-02','2002-04-01',0,0,'A' ); INSERT INTO t_users(c_uid,c_pwd,c_accesses,c_first,c_last,c_bad,c_posted,c_type) values( 'TESTGUEST','test',0,'04-01-02','2002-01-02',0,0,'A' ); CREATE TABLE t_forums( c_code VARCHAR(4) NOT NULL, c_name VARCHAR(50) NOT NULL, c_sequence INTEGER NOT NULL, PRIMARY KEY(c_code) ); INSERT INTO t_forums(c_code,c_name,c_sequence) values( 'GNRL','General Discussion',1 ); INSERT INTO t_forums(c_code,c_name,c_sequence) values( 'SUPP','On-Line Support',2 ); CREATE TABLE t_messages( c_forum_code VARCHAR(4) NOT NULL, c_number INTEGER NOT NULL, c_posted DATETIME NOT NULL, c_subject VARCHAR(50) NOT NULL, c_sender VARCHAR(32) NOT NULL, c_message TEXT NOT NULL, PRIMARY KEY(c_forum_code,c_number), FOREIGN KEY(c_sender) REFERENCES t_users(c_uid) ); Executing this script from MySQL is relatively easy. The procedure for executing the script and setting up MySQL is covered in Appendix C. Setting Up the Forum on Microsoft Access The examples provided in this chapter will also work with the Microsoft Access database. To use our examples, you must have a copy of the database file forum.mdb. You can find this file with the other source code examples at http://www.sams.com/. You should copy the forum.mdb file to your hard drive. If you own a copy of Microsoft Access, you can double-click forum.mdb to see the contents of the database. Once you install the file, you must create a data source name (DSN) for it. You do this in the Data Sources (ODBC) window. If you are using Windows XP, you'll find this item under Administrative Tools. If you are using an earlier copy of Windows, Data Sources (ODBC) is accessible from the main Control Panel. At this point, double-click Data Sources (ODBC) to open the ODBC Data Source Administrator window. From here, you can configure new DSNs. You must create a DSN for the forum application to work properly with Microsoft Access. This will allow JSTL to locate forum.mdb. Click the System DSN tab; you want to create a system-level DSN that will be accessible to every user of the operating system. Click the Add button and select the Microsoft Access Driver (*.mdb) option. Then, click OK to open the Microsoft Access ODBC configuration window. There are only two pieces of information that you must supply. First, you must provide a name for your DSN. The DSN that you should use for our example is forum. Second, you must provide the location of the forum.mdb file. To do so, click the Select button on the Database tab. If you have properly configured your DSN, you should see the window shown in Figure 7.2. 
Database Tables Your database should now be properly configured for either MySQL, Microsoft Access, or another database system. Now let's examine the structure of the database. Figure 7.3 shows the Access Relationships window, which contains the layout of the database. This layout applies regardless of which database you are using. 
To ensure compatibility with the largest number of databases, you must follow certain conventions when naming tables and columns. For example, you cannot use a reserved word for the name of a column or table. It would be illegal, for instance, to have a table named select because this keyword is reserved for a commonly used SQL command. Certain databases will permit you to enclose the keyword in brackets to designate it as a column or table name. Unfortunately, support for this is not universal. At this point, we'll describe the naming convention used for tables and columns in this book. All tables are prefixed with t_. For example, the access type table used by the forum application is named t_access, not just access. Column names are prefixed with c_. For example, if we need a column that stores a description, we use the name c_description. This allows us to be sure that the names used in our application are compatible with any database system, regardless of the reserved words imposed by that database. Let's now examine the tables that make up our forum application; we begin with the access table. This table stores the different access modes, or user types, that the system allows. This table is summarized in Table 7.1. Table 7.1. The t_access Table | c_access | VARCHAR | 1 | PK,NOT NULL | | c_description | VARCHAR | 32 | NOT NULL | This table allows our forum application to store its three user types: -
Administrator, who is the super user and who can perform any action. -
Regular User, who can post and read messages but cannot perform any administrative actions. -
Guest, a user who has just registered. A guest may only read messages until his or her access has been upgraded by an administrative user. As you can see in Table 7.1, the t_access table has two columns. The first, c_access, contains the access code for the row. Of the three defined users, the administrators use A, the regular users use R, and the guests use G. The c_description column is used to specify the verbal user type, such as Administrator or Guest. This table is not modified as the application runs, since the number of user types is fixed. The next table that we examine is the table that stores the user identification for the users who are registered to access the forum application. This table, named t_users, is shown in Table 7.2. Table 7.2. The t_users Table | c_uid | VARCHAR | 32 | PK,NOT NULL | | c_pwd | VARCHAR | 32 | NOT NULL | | c_accesses | INTEGER | | NOT NULL | | c_first | DATETIME | | NOT NULL | | c_last | DATETIME | | NOT NULL | | c_bad | INTEGER | | NOT NULL | | c_posted | INTEGER | | NOT NULL | | c_type | VARCHAR | 1 | FK:t_access,NOT NULL | The primary key for this table is the c_uid column. This column specifies the user's login name, which, along with the password, is required to gain access to the system. The password is stored in the c_pwd column in an unencrypted format. For a production application, we suggest that you encrypt this password for added security. JSTL is not well suited to password encryption; password encryption is normally done in an EJB or custom tag library. We discuss password encryption in greater detail in Chapter 11, "Creating Your Own Tag Libraries." The c_type column contains a foreign key to the t_access table. Using this key, we are able to determine the user's type. This will become important later for security reasons. The other columns in the t_users table simply store statistical information about the user. We won't use all of these columns in this chapter. We use the c_posted column in Chapter 11 when we expand this application to use custom tag libraries. Our application provides several forums that the user can choose to read or post messages to. The system must track the individual forums available. These forums are stored in the t_forums table, shown in Table 7.3. Table 7.3. The t_forums Table | c_code | VARCHAR | 4 | PK,NOT NULL | | c_name | VARCHAR | 50 | NOT NULL | | c_sequence | INTEGER | | NOT NULL | Three pieces of information are stored about a forum. The first is the forum code, which is the primary key for this forum; it should be a four-character abbreviation. The second is the name of the forum, which is stored in the c_name column. Finally, a sequence number is stored in c_sequence; this number allows the forums to be ordered. When the forums are displayed, they are sorted by sequence number. This allows the administrator to change the order in which the forums are displayed. This table contains no foreign keys to other tables. The last table that we will examine is the table that stores the actual messages the users will read and post. This table, named t_messages, is shown in Table 7.4. Table 7.4. The t_messages Table | c_forum_code | VARCHAR | 4 | PK,NOT NULL, FK:t_forums | | c_number | INTEGER | | PK,NOT NULL | | c_posted | DATETIME | | NOT NULL | | c_subject | VARCHAR | 50 | NOT NULL | | c_sender | VARCHAR | 32 | FK:t_users,NOT NULL | | c_message | TEXT | | NOT NULL | This table has a combined primary key that is made up of the c_forum_code and c_number columns. The c_forum_code key is also a foreign key to the c_forums table. The c_forum_code column designates which forum this message belongs to. The c_number column contains a number assigned to individual messages within a forum. Messages are assigned increasingly higher numbers as they are posted to a forum. The person who posted this message is identified in the c_sender column, which is a foreign key to the t_users table. The actual text of the message is stored in the c_message column. An entry in this column should be defined as a text type that can hold larger amounts of data. In MySQL, you'd use the TEXT type. In Microsoft Access, you'd use the MEMO type. The database holds all of the user information for our forum application. This database is used by a series of JSP pages that contain the JSTL that make up this application. Now that we have reviewed that database, let's examine the structure of the JSP pages that make up our forum application. Screen Layout Web applications consist of many Web pages that are interconnected through hyperlinks, posts, and other forms of Web navigation. Figure 7.4 shows the flow from page to page in our forum application. 
As you can see in Figure 7.4, there are two entry points for the Web application: -
index.jsp, an index file that contains the driver used for MySQL -
index-msa.jsp, an index file that contains the driver used for Microsoft Access Except for these two index files, all of the JSP files remain the same as those in the database programs. Only a few lines are different between index.jsp and index-msa.jsp. The difference is in how these files access the database. Let's now see how JDBC drivers are used to access the database. |
Print 
E-Mail 
Add Note 
Add Bookmark
