Java Database Connectivity- Exercise: Page 1
Programming in Java 6 with Swing and Servlets Part 1
Exercises: Database Access Using JDBC
In this exercise, we will implement a graphical front end to provide user access to a database. We will use the capability of the ResultSet class to scroll through records in both forward and backward directions. The ResultSet class is a table of data representing a database result set, which is usually generated by executing a statement that queries the database, and maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next() method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set. A default ResultSet object is not updatable and has a cursor that moves forward only.
It is possible to produce ResultSet objects that are scrollable and/or updatable in the JDBC2.0 API provided with the Java SDK Standard Edition 1.2, although not all databases support these ResultSet operations. We will produce a ResultSet that can utilize both the next() and previous() methods to move the cursor in both directions, but is not updatable. Standard SQL statements to insert and delete records will be used to update the database. A new ResultSet will be generated after each of these operations to maintain concurrency.
The program is a simple data browsing application that will display a Contact's name and email, which is stored in a Cloudscape database file. The Cloudscape database comes with WebSphere Studio Application Developer. When the application starts, a connection to the database is established and the first row in the ResultSet is displayed. The user can progress forwards or backwards through the results using the buttons in the window. Buttons will also be provided to allow the user to insert or delete data from the database.
The user of the program controls database access by using four buttons on the window. These buttons are as follows.
- Insert Data
- Delete Data
- Next Row
- Previous Row
A short description of the function of each of these buttons is given below.
Insert Data – Retrieves data from the Name and Email data fields, inserts the data into the underlying database and rebuilds the ResultSet.
Delete Data – Deletes the current row from the underlying database and rebuilds the ResultSet.
Next Row – Retrieves data from the next row and displays it in the data fields.
Previous Row – Retrieves data from the previous row and displays it in the data fields.
Part One: Create and Populate a MySql Database
A simple database must be created in order to do the exercises in this module. This module uses the MySql database but any database with a JDBC driver available for it will work. The script below will create the database, tables and populate it. MySql includes a command-line tool called mysql. To run the script simple type
mysql < ModuleEleven.sql
If your database installation requires a username and password, use this command line:
mysql -u username –p < ModuleEleven.sql
MySql will prompt for the password. After running this command your database is ready for the exercises below. For your information, the ModuleEleven.sql script is shown in the figure below:
DROP DATABASE ModuleEleven;
CREATE DATABASE ModuleEleven;
USE ModuleEleven;
DROP TABLE IF EXISTS Contact;
CREATE TABLE Contact
(
NAMEVARCHAR(64),
EMAILVARCHAR(64)
);
INSERT INTO Contact ( NAME, EMAIL )
values ( 'Kevin Rodgers', 'Quest Diagnotics');
INSERT INTO Contact ( NAME, EMAIL )
values ( 'Nancy Myers', '');
INSERT INTO Contact ( NAME, EMAIL )
values ( 'Morgan Wiggens', '');
INSERT INTO Contact ( NAME, EMAIL )
values ( 'John Smith', '');
INSERT INTO Contact ( NAME, EMAIL )
values ( 'Jane Evans', '');
Part Two: GUI Design and ContactDB Project
- Create a new project directory for ModuleEleven. On windows create the C:\labs\ModuleElevendirectory.
- Create a new java application in your ModuleEleven directory called ContactDB.java. The ContactDB class is a Swing application that extends JFrame.
- The user interface is simply four buttons and two text fields to hold the data. This is conveniently rendered using a 4 by 2 GridLayout. The figure below represents the look of the ContactDB application.
- Add an event handler to close the window. This handler must also close the connection to the database. First, modify the import statements to import all classes from the javax.swing package and import the event and sql packages :
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;
- Add the following instance variables for the database connection, jdbc driver, query, statement and result set to the other instance variables:
private Connection conn;
private ResultSet rs;
private Statement stmt;
private String connStr = "jdbc:mysql:///ModuleEleven";
private String jdbcDrv = "org.gjt.mm.mysql.Driver";
private String query = "select * from Contact.Contact";
private JTextField nameField = new JTextField();
private JTextField emailField = new JTextField();
- Add a Window closing event handler that will check the Connection, Statement and ResultSet objects. If they are not null, they will be closed and then the application will exit. Place this code in the initialize() method as shown below:
private void initialize()
{
this.addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
try
{
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
} catch (Exception ex) {}
finally
{
System.exit(0);
}
}
});
this.setContentPane( getMainPanel() );
initDB();
pack();
}
- Place the database initialization code in the following method:
public void initDB()
{
try
{
// Check existence of driver class, establish connection
Class.forName(jdbcDrv);
} catch( ClassNotFoundException e )
{
JOptionPane.showMessageDialog(this,
"JDBC driver class : " + jdbcDrv + "\n",
"Driver Not Found",
JOptionPane.ERROR_MESSAGE);
return;
}
try
{
conn = DriverManager.getConnection(connStr);
// Create Statement where ResultSets will be scrollable
// backwards and forwards, are not sensitive to
// changes made by others and are not updateable
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// Attempt to create ResultSet from query and set data
// from first row into text fields
rs = stmt.executeQuery(query);
// Make sure there is at least one row in ResultSet
if(rs.next())
{
nameField.setText(rs.getString(1));
emailField.setText(rs.getString(2));
} else
JOptionPane.showMessageDialog(this,
"No rows found using query: \n" + query,
"ResultSet Information",
JOptionPane.PLAIN_MESSAGE);
// catch SQLExceptions and loop through all
} catch (SQLException se)
{
String eMsg = "";
while(se != null)
{
eMsg += "SQLException: " + se.getMessage() + "\n"
+ "\n" + "ANSI-92 SQL State: " + se.getSQLState()
+ "\n" + "Vendor Error Code: "+se.getErrorCode();
se = se.getNextException( );
}
System.err.println(eMsg);
}
}
- Build the primary GUI panel for the application in the getMainPanel() method. For the moment in response to a button click action, simple print an indication that the button action has indeed fired.
public JPanel getMainPanel()
{
JPanel panel = new JPanel( new GridLayout( 4, 2, 10,10 ) );
panel.add( new JLabel( "Name: ", SwingConstants.RIGHT ) );
panel.add( nameField );
panel.add( new JLabel( "Email: ", SwingConstants.RIGHT ) );
panel.add( emailField );
JButton button = new JButton( "Delete Data" );
button.addActionListener( new ActionListener()
{
public void actionPerformed( ActionEvent e )
{
System.out.println("Delete");
}
});
panel.add( button );
button = new JButton( "Insert Data" );
button.addActionListener( new ActionListener()
{
public void actionPerformed( ActionEvent e )
{
System.out.println("Insert");
}
});
panel.add( button );
button = new JButton( "Next" );
button.addActionListener( new ActionListener()
{
public void actionPerformed( ActionEvent e )
{
System.out.println("Next");
}
});
panel.add( button );
button = new JButton( "Previous" );
button.addActionListener( new ActionListener()
{
public void actionPerformed( ActionEvent e )
{
System.out.println("Previous");
}
});
panel.add( button );
return panel;
}
- Add a constructor for ContactDB.
public ContactDB()
{
super("Database Browser");
initialize();
}
- Modify the main() method to instantiate a ContactDB object and start it running as shown below:
public static void main(String[] args)
{
ContactDB contact = new ContactDB();
Contact.setVisible(true);
}
- Save your file, compile. Resolve any compiler errors and run ContactDB. It will not access the database yet, but it should display the window and respond to your buttons and to the button to exit the application. Verify that all your action listeners are working and then exit the application.
Part Three: Connecting to the Database and Moving through the ResultSet
Now it's time to navigate the database, create a ResultSet and program the event handlers for the Next Row and Previous Row buttons that will move through the ResultSet. When the program is started, the connection to the database should be initialized in the constructor for ContactDB, the ResultSet should be created and populated and the first row displayed in the text fields. When the user presses the Next Row button, the ResultSet is queried to determine if it is on the last row. If it is, the user is informed of this and the cursor is not moved. If it is not on the last row, the next() method is called to retrieve the next row and the results displayed in the text fields. When the user presses the Previous Row button, the ResultSet is queried to determine if it is on the first row. If it is, the user is informed of this and the cursor is not moved. If it is not on the first row, the previous() method is called to retrieve the previous row and the results displayed in the text fields.
To use the JDBC driver for the MySql database we will have to use an external java library with the ContactDB program. The name of this library is mm.mysql.jar. Your instructor will show you where to get this file. Using an external jar file requires us to add elements the the classpath of the javac and java commands. The figure below shows ContactDB being compiled and executed using the mm.mysql.jar library.
javac –classpath . ContactDB.java
java –classpath .;mm.mysql.jar ContactDB
Note: The period in the classpaths above indicates the current directory. We’re included the current directory and the jar file. The semi-colon is the path separator on Windows systems. On Unix systems the separator is the colon.
- Change the actionPerformed method for the Next button method by adding the following database code to move to the next record in the RecordSet.
public void actionPerformed( ActionEvent e )
{
try
{
// is cursor already on last record?
if(rs.isLast())
JOptionPane.showMessageDialog(ContactDB.this,
"Cannot move beyond last record",
"Warning",
JOptionPane.WARNING_MESSAGE);
else
{
// cursor is not on last record, get next
rs.next();
nameField.setText(rs.getString(1));
emailField.setText(rs.getString(2));
}
} catch (SQLException se)
{
String eMsg = "";
while(se != null)
{
eMsg += "SQLException: "
+ se.getMessage() + "\n"
+ "\n" + "ANSI-92 SQL State: "
+ se.getSQLState() + "\n"
+ "Vendor Error Code: "
+ se.getErrorCode();
}
System.err.println(eMsg);
}
}
- Change the actionPerformed method for the Previous button method by adding the following database code to move to the previous record in the RecordSet.
public void actionPerformed( ActionEvent e )
{
try
{
// is cursor already on first record?
if(rs.isFirst())
JOptionPane.showMessageDialog(ContactDB.this,
"Cannot move before first record",
"Warning",
JOptionPane.WARNING_MESSAGE);
else
{
// cursor is not on first record, get prev
rs.previous();
nameField.setText(rs.getString(1));
emailField.setText(rs.getString(2));
}
} catch (SQLException se)
{
String eMsg = "";
while(se != null)
{
eMsg += "SQLException: "
+ se.getMessage() + "\n"
+ "\n" + "ANSI-92 SQL State: "
+ se.getSQLState() + "\n"
+ "Vendor Error Code: "
+ se.getErrorCode();
}
System.err.println(eMsg);
}
}
- Save your work, rebuild and run the application. You should be able to navigate through the rows in both forward and backward directions.
Part Four: Inserting and Deleting Records
When the user presses the Delete Data button, the current row from the ResultSet is deleted, the data is removed from the underlying database using a SQL delete statement and the ResultSet is rebuilt. When the user presses the Insert Data button, the data is retrieved from the text fields and inserted into the underlying database using a SQL insert statement and the ResultSet is rebuilt.
- Change the actionPerformed method for the Delete Data button method by adding the following database code to remove the current record in the RecordSet.
public void actionPerformed( ActionEvent e )
{
try
{
String SQLstr = "delete from Contact where ";
SQLstr += "NAME = '" + nameField.getText()
+ "' and ";
SQLstr += "EMAIL = '" + emailField.getText()
+ "'";
// Delete the record
stmt.execute(SQLstr);
JOptionPane.showMessageDialog(ContactDB.this,
"Deleted " + stmt.getUpdateCount()
+ " record(s).",
"Delete Information",
JOptionPane.PLAIN_MESSAGE);
// get new result set, position at first record
rs = stmt.executeQuery(query);
if(rs.next())
{
nameField.setText(rs.getString(1));
emailField.setText(rs.getString(2));
} else
JOptionPane.showMessageDialog(ContactDB.this,
"No data found", "No Data",
JOptionPane.PLAIN_MESSAGE);
} catch (SQLException se)
{
String eMsg = "";
while(se != null)
{
eMsg += "SQLException: "
+ se.getMessage() + "\n"
+ "\n" + "ANSI-92 SQL State: "
+ se.getSQLState()
+ "\n" + "Vendor Error Code: "
+ se.getErrorCode();
}
System.err.println(eMsg);
}
}
- Change the actionPerformed method for the Insert Data button method by adding the following database code to insert the current field values into the database.
public void actionPerformed( ActionEvent e )
{
try
{
// Execute the insert statement
String SQLstr = "insert into Contact values ('";
SQLstr += nameField.getText() + "','";
SQLstr += emailField.getText() + "')";
stmt.execute(SQLstr);
JOptionPane.showMessageDialog(ContactDB.this,
"Inserted " + stmt.getUpdateCount()
+ " record(s).",
"Insert Information",
JOptionPane.PLAIN_MESSAGE);
// get new result set, position at first record
rs = stmt.executeQuery(query);
if(rs.next())
{
nameField.setText(rs.getString(1));
emailField.setText(rs.getString(2));
} else
JOptionPane.showMessageDialog(ContactDB.this,
"No data found", "No Data",
JOptionPane.PLAIN_MESSAGE);
} catch (SQLException se){
String eMsg = "";
while(se != null)
{
eMsg += "SQLException: " + se.getMessage()
+ "\n"
+ "\n" + "ANSI-92 SQL State: "
+ se.getSQLState()
+ "\n" + "Vendor Error Code: "
+ se.getErrorCode();
}
System.err.println(eMsg);
}
}
- Save your work and rebuild the project.
Part Five: Test the Application
- Run the ContactDB application and test all the functions. Initially, the application should successfully connect to the database and display the first record.
- Use the Previous Row button to test the ability of the application to handle a user attempt to go beyond the first row. You should see a message window appear informing the user that they cannot move beyond the first row and the cursor will not move from the current position.
- Use the Next Row button to move through the records in the database and attempt to move beyond the last record. A similar message should alert you that you cannot move beyond the end of the records.
- Insert a record into the database by entering text into the Name and Email fields and then clicking Insert Data button. You should see a message that the row was successfully inserted, a new ResultSet will be retrieved with the first row displayed. Navigate through the ResultSet to ascertain that the data was entered into the database.
- Finally, navigate to a record and click the Delete Data button. You should see a message that the row was successfully deleted, a new ResultSet will be retrieved with the first row displayed. Navigate through the ResultSet to ascertain that the data was deleted from the database.
Part Six: The ContactDB code
This application was one of the more complex you were asked to code. The code is reproduced here for your reference complete with some JavaDoc contents.
Note: By including javadoc comments in you application (i.e. comments at the beginning of methods that start with /** and end with */ ) you can make your code self-documenting. Issue the command
javadoc –d docsContactDB.java
to produce HTML documentation of your code in the directory “docs.”
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;
/**
* ContactDB is an implementation of a personal
* Addressbook Database application.
*/
public class ContactDB extends JFrame
{
/** The database connection */
private Connection conn = null;
/** The results of a database operation */
private ResultSet rs = null;
/** A representation of an SQL statement */
private Statement stmt = null;
/** The connection string to a database */
private String connStr = "jdbc:mysql:///ModuleEleven";
/** The JDBC driver class */
private String jdbcDrv = "org.gjt.mm.mysql.Driver";
/** Select all records from the database */
private String query = "select * from Contact";
/** a person's name */
JTextField nameField = new JTextField();
/** a person's email address */
JTextField emailField = new JTextField();
/**
* zero argument constructor
*/
public ContactDB()
{
super("Database Browser");
initialize();
}
/**
* Build the GUI and start the database and
* prepare the application for use.
*/
private void initialize()
{
this.setContentPane( getMainPanel() );
this.addWindowListener(new WindowAdapter()
{
public void windowClosing(WindowEvent e)
{
try
{
if(rs != null)
rs.close();
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
} catch (Exception ex) {}
finally
{ System.exit(0); }
}
});
initDB();
pack();
}
/**
* Initialize the database.
*/
public void initDB()
{
try
{
// Check existence of driver class, establish connection
Class.forName(jdbcDrv);
} catch( ClassNotFoundException e )