The SQL Mail Client is an example application that demonstrates how to construct a full-featured Java application on top of a database. The purpose of this example is to demonstrate a number of useful programming techniques in database programming.
If you would like to run this example, click the link above to download the project file. The project folder contains a folder named 'database' - in that folder you will find the SQL database for this project. Before you run the program you will need to import that database into MySQL. (My code assumes that the root password for MySQL is 'cmsc250'. If that is not the case in your installation, you will need to edit the connection string in the MessageManager.java file.)
The application we are going to construct replicates the functionality of the network mail client I showed in an earlier example. The interface to our mail client will look and function exactly the same as the original network mail client.
|
When users log in to the system, they will be presented with a list of messages that have been sent to them. Users can read messages, delete messages, and compose new messages by clicking the buttons at the bottom.
The original network mail client consisted of a frame class that implemented the interface and a model class that did the work by talking across the network with a server to accomplish tasks demanded by the interface. We are going to use a similar arrangement for our new mail application - the only difference is that now the model class will be talking to a database instead of a networked server.
The underlying database that the application uses consists of three simple tables. The users table stores user names and passwords.
| Field | Type |
|---|---|
| user | VARCHAR(32) |
| password | VARCHAR(32) |
The messages table stores information about messages.
| Field | Type |
|---|---|
| message_id | INT (primary key) |
| sender | VARCHAR(32) |
| subject | VARCHAR(255) |
| body | TEXT |
| date | DATETIME |
The TEXT data type is used to store blocks of text with unlimited size. This is exactly what we need for the bodies of mail messages. The DATETIME data type is an internal data type that MySQL uses to represent time information.
The recipients table is used to store the list of recipients for each message sent. Since individual messages can potentially have many recipients, this information has to be stored in a separate table. If a given message has more than one recipient, there will be one row in the recipients table for each recipient of the message.
| Field | Type |
|---|---|
| message_id | INT |
| recipient | VARCHAR(32) |
You can get a copy of the MySQL database for this example by clicking this link.
Before we launch into a detailed discussion of the SQL mail application, I need to introduce you to a few more advanced ideas in SQL programming.
Because relational databases are frequently made up of multiple simple tables, a common occurance is the need to access data that is distributed across multiple tables.
In the mail example we are developing information about messages is distributed across two tables. The messages table holds the bulk of information related to messages. Each message's subject, sender, body, and time stamp are all stored in this table. Because each message can have multiple recipients, the list of recipients for a given message has to be stored as several entries in a separate table of recipients.
Occasionally, we will need to draw together data that is scattered across multiple tables. The basic mechanism for doing this in SQL is the join. A join merges together a pair of tables by making a temporary table whose rows are formed by joining together the rows of the two tables. Joins are typically set up to merge rows that overlap in some way, most often by containing the same identifier.
Here is an example of a SQL select statement that uses a join to merge the message and recipient tables in our mail application.
select messages.message_id, subject, sender, recipient from messages join recipients on messages.message_id=recipients.message_id
The message_id, subject and sender fields in this query come from the messages table, while the recipient field comes from the recipients table. Rows of the merged table are formed by finding rows in the message table with a particular message_id and joining them to rows from the recipients table having the same message_id.
The picture below shows a typical result returned from this query.
 |
Note that the message with message_id of 9 appears on a couple of rows here. That particular message_id shows up in only one row of the original messages table, but since that message has two recipients that show up as two rows in the recipients table the table that results from the join will have two rows with message_id of 9, one for each of the recipients listed in the recipients table.
Here is a modified version of the SQL select statement we set up in the last section. This select statement makes use of the SQL string concatenation function, concat(), to synthesize a string from strings found in the database.
select messages.message_id,
concat(sender,':',subject) as handle,
recipient
from messages join recipients
on messages.message_id=recipients.message_id
The 'as' clause after the concat function call relabels that portion of the query with an identifier, 'handle'.
The picture below shows a typical result returned from this query.
 |
The task of concatenating the sender and subject to form a handle is something that we could have done ourselves in Java code after extracting the sender and subjects separately from a SQL query. Since MySQL can easily do this work for us, there is no reason for us to have to this work in Java. This is a general principle we should try to follow when working with a database - if there is some task that we can get the database software to do for us we should avoid doing it ourselves in Java.
The query I constructed in the last section will prove useful later for our mail application. The mail application needs to construct a JList containing handles that users will use to select individual messages. Those handles and the associated message_ids that go with them can be extracted from the database by issuing the query shown above.
A convenient short-cut to issuing common queries in SQL is to create an SQL view. A view is a stored query that acts like a table. To construct a view in NetBeans, we connect to our database in the Services pane, right-click on the Views folder in our database and select the Create View... command. In the dialog box that appears we specify a name for our view and type the SQL code for the query that the view will implement.
 |
The resulting view can be queried as would any table in the database. For example, to show only the message_ids and handles for messages destined for a particular recipient we would execute the query
select message_id,handle from handles where recipient='jgregg'
The picture below shows the results that this query would generate.
 |
The SQL Mail client program will make use of a number of new ideas in Java programming. This section covers three new ideas in Java.
We have already seen one method for querying a database based on the use of the java.sql.Statement class. The basic idea is to prepare a string containing the desired SQL query and then hand that to a Statement object for execution. Here is an example of how this works: the method shown here runs a query against the users table in the SQL mail database to check that the given user name and password combination appears in the database. The connect() method establishes a connection to the database and initializes the stmt member variable in the usual way:
stmt = connection.createStatement();
Once the statement object has been set up we can use it to execute queries.
public boolean logIn(String user, String password) {
connect();
boolean success = false;
try {
String query = "select * from users where user=\'" + user
+ "\' and password=\'" + password + "\'";
ResultSet rset = stmt.executeQuery(query);
if (rset.next()) {
success = true;
this.name = user;
}
} catch (SQLException ex) {
this.name = null;
}
return success;
}
This approach is cumbersome, because assembling the query string from its component parts is somewhat clunky.
The PreparedStatement class offers a convenient alternative. A PreparedStatement object is an object that embeds a pre-built query string with placeholders denoted by '?' characters. To use the PreparedStatement we first call methods that fill in the placeholders with data. Once the placeholders are filled in we call a method on the PreparedStatement that launches the query.
In several places the SQL Mail client program declares PreparedStatement objects and SQL strings that can be used to initialize the objects. For example, the following pair handle the work of inserting a new user in the users table.
String newAcctSQL="insert into users (user,password) values(?,?)"; PreparedStatement newAcctStmt;
The connect() method creates the PreparedStatement object by doing
newAcctStmt = connection.prepareStatement(newAcctSQL);
The following method shows how the PreparedStatement object gets used to issue the command to insert a new user account into the users table.
public boolean createNewAccount(String user, String password) {
connect();
boolean success = false;
try {
newAcctStmt.setString(1, user);
newAcctStmt.setString(2, password);
newAcctStmt.executeUpdate();
success = true;
this.name = user;
} catch (SQLException ex) {
this.name = null;
}
return success;
}
The PreparedStatement class has two primary methods for launching queries. The executeUpdate method used here is for SQL update statements such as insert, update, and delete that modify the database. The executeQuery method returns a ResultSet and is used for select statements.
A common scenario that occurs in applications that work with a database is that application needs to show the user a list of options drawn from the database. For example, the main window of the SQL mail client needs to display a list of messages that the user can read.
 |
Each of these options represents a particular entry in a database table, and most often we will need to know the integer identifier for that item in the database. In this case, we will want to know the message_id value associated with each of the listed methods.
The first step in getting all of this to work successfully is to create a simple class that allows us to bundle together the Strings to be displayed in the list and their associated integer identifiers:
public class Item {
private int id;
private String name;
public Item(int id,String name){
this.id = id;
this.name = name;
}
public int getID() {return id; }
public String getName() { return name; }
public String toString(){
return name;
}
}
Here is the method in the SQL mail client that queries the handles view and creates and returns an array of these Item objects.
public Item[] getAllHeaders() {
ArrayList<Item> items = new ArrayList<Item>();
try {
String sqlString = "select message_id, handle from handles"
+ " where recipient='" + name + "\'";
ResultSet rset = stmt.executeQuery(sqlString);
while (rset.next()) {
items.add(new Item(rset.getInt(1), rset.getString(2)));
}
} catch (SQLException ex) {
}
Item[] holder = new Item[items.size()];
return items.toArray(holder);
}
The frame class for the SQL mail application contains a member variable msgList that is a JList object. We pass the array of Item objects returned by the method above to this JList by doing
Item[] items = mgr.getAllHeaders(); msgList.setListData(items);
If we wanted to put these items in a ComboBox instead would do something like
Item[] items = mgr.getAllHeaders(); myComboBox.setModel(new DefaultComboBoxModel(items));
The JList will want to use the objects it has been given to display a list of Strings to the user. To do this, it will go down the array of Item objects calling the toString method on each Item. That method returns the text we want displayed for that item - message headers in this case.
Once the user has selected an item in the list we can ask the JList object to return to us the selected Item object by calling the getSelectedValue method. That method returns an Object, so we have to type cast that Object to an Item. Once we have the selected Item we can ask it to give us the embedded integer identifier.
Item selected = (Item) msgList.getSelectedValue();
if(selected != null) {
int id = selected.getID();
// Do something useful with the id.
}
A small technical problem that the SQL mail client has to overcome has to do with sending messages to multiple recipients. Users of the application can send messages to multiple recipients by typing a comma separated list of recipients it the To field of the Compose Message dialog.
 |
The application has to break that comma separated String into individual recipients for insertion into the recipients table. The best way to do this is to use a StringTokenizer object. This object gets initialized with the String and a String containing separator characters (spaces and commas in this case).
StringTokenizer tokens = new StringTokenizer(toString," ,");
Once the StringTokenizer has been set up we can iterate over the object using the hasMoreTokens and nextToken methods, which will break the string into individual recipients using the separators specified.
while(tokens.hasMoreTokens()) {
String recipient = tokens.nextToken();
// Put the recipient in the recipients table
}
We are now ready to describe the full structure of the SQL mail client program. The program has two primary classes: a frame class ClientFrame that implement the main applicatio window and a model class MessageManager that is the primary data handling class for the application.
Here is an outline of the structure of the MessageManager class.
public class MessageManager {
Connection connection;
Statement stmt;
// This is the name of the user who is currently logged in.
String name = null;
public MessageManager() {}
public String getUserName() {}
public boolean logIn(String user, String password) {}
public boolean createNewAccount(String user, String password) {}
public Message getMessage(int id) {}
public void deleteMessage(int id) {}
public void sendMessage(Message msg) {}
public void logOut() {}
public Item[] getAllHeaders() {}
private void connect() {}
private void shutDown() {}
}
Most of these methods are fairly obvious and self-explanatory. I have already discussed some of the methods above. Below I will discuss some of the more interesting methods I have not already covered.
When the user wants to read a message, they will select the message from a list of messages displayed in the main window. As discussed above, the frame class will ask the JList holding the list of messages to give it the currently selected Item object. That Item object contains the message_id for the message in question. The getMessage method of the MessageManager uses that identifier to fetch the full message and return it in the form of a Message object.
public Message getMessage(int id) {
Message result = null;
try {
String sqlStr = "select sender, subject, body, date"
+ " from messages where message_id=" + id;
ResultSet rset = stmt.executeQuery(sqlStr);
if (rset.next()) {
String sender = rset.getString(1);
String subject = rset.getString(2);
String body = rset.getString(3);
String time = rset.getString(4);
result = new Message(sender,name,subject,body,time);
}
} catch (SQLException ex) {
System.out.println("An SQL error took place."
+ ex.getMessage());
}
return result;
}
What we see here is fairly typical for Java applications that work with a database. Data comes up from the database in the form of isolated bits of primitive data. That data is usually bundled into objects for the benefit of the Java portion of the application.
Here is the code for sending a message.
String insertMsgSQL = "insert into messages (subject,body,sender,date) values (?,?,?,CURDATE())";
PreparedStatement insertMsgStmt;
String insertRecipientSQL = "insert into recipients (message_id,recipient) values (?,?)";
PreparedStatement insertRecipientStmt;
public void sendMessage(Message msg) {
try {
// Data in this message has to be spread over two tables, the messages
// table and the recipients table. We do the insertion into the messages
// table so the database can generate a messageID for this message. We
// then use that messageID to do the insertions into the recipients table.
insertMsgStmt.setString(1, msg.getSubject());
insertMsgStmt.setString(2, msg.getContents());
insertMsgStmt.setString(3, msg.getSender());
insertMsgStmt.executeUpdate();
// The id for the new message is generated automatically by the database
// We have to query the database to see what it is.
String query2 = "select LAST_INSERT_ID()";
ResultSet rset = stmt.executeQuery(query2);
if (rset.next()) {
int messageID = Integer.parseInt(rset.getString(1));
StringTokenizer tokens = new StringTokenizer(msg.getRecipients(), " ,");
insertRecipientStmt.setInt(1, messageID);
while (tokens.hasMoreTokens()) {
insertRecipientStmt.setString(2, tokens.nextToken());
insertRecipientStmt.executeUpdate();
}
}
} catch (SQLException ex) {
System.out.println("An SQL error took place." + ex.getMessage());
}
}
Sending a message is a two stage process. In the first stage, we put most of the data related to the message into the messages table. The only thing we don't specify in that insert statement is the message_id for the message - that gets generated automatically by MySQL when we insert the new row. In the second stage we have to put a row in the recipients table for each recipient of the message. As discussed above, we can use a StringTokenizer to break the recipients string into individual recipients for insertion. Each row that we insert in the recipients table will need the message_id for the message in question. We can determine what that message_id is by launching a special SQL query:
select LAST_INSERT_ID()
This returns the last identifier generated on the insertion that created the row for this message in the messages table.
Because the data for a messages is spread over two tables, when it come time to delete a message we have to deal with two separate tables.
The first thing to do is to remove the entry in the recipients table that corresponds to the message and recipient we want removed. Next, we have to check to see if we just removed the last recipient for that particular message. If so, we also have to go into the message table to remove the message.
public void deleteMessage(int id) {
try {
String whereClause = "where message_id=" + id;
String sqlStr = "delete from recipients " + whereClause
+ " and recipient=\'" + this.name + "\'";
stmt.execute(sqlStr);
// If we just removed the last recipient for this message, remove it from the
// messages table, too.
sqlStr = "select recipient from recipients " + whereClause;
ResultSet results = stmt.executeQuery(sqlStr);
if (!results.isBeforeFirst()) {
sqlStr = "delete from messages " + whereClause;
stmt.execute(sqlStr);
}
} catch (SQLException ex) {
System.out.println("An SQL error took place." + ex.getMessage());
}
}