Chapter 44, "Connecting to Databases Using the java.sql Package," provided an introduction to database programming using JDBC. This chapter continues your introduction to database programming by showing you how to work with vendor-specific JDBC drivers and database servers. You'll get more hands-on experience using SQL, and you'll learn how to work with the results of SQL queries. You'll also learn about transaction processing and database security. When you finish this chapter, you'll be able to write more advanced Java database applications.
In Chapter 43, you learned about the four different types of JDBC drivers:
In this section, you'll work with three of the four driver types. You'll learn how to access a Microsoft Access 97 database using the JDBC-ODBC bridge driver and the IDS JDBC driver. You'll also learn how to access an mSQL database using a Type 4 JDBC driver. When you finish this section, you'll have enough experience working with database drivers to figure out how to use other vendor-specific JDBC drivers.
I've provided a small Microsoft Access 97 database named action.mdb in the ch45 directory. In order to use this database, you'll have to set it up as an ODBC data source. You do this using the 32-bit ODBC applet found in the Control Panel.
NOTE: If you have Microsoft Access 97 and want to use it to run the examples of this chapter, install it before setting up the action.mdb database.
After you open the applet, click the System DSN tab and then the Add button to add a new data source. Enter Actions as the name for the data source in the Data Source Name field. Click the Select button to set the path to the database to the location of the ch45 directory. (See Figure 45.1.)
NOTE: A System DSN is accessible to more than one user, while a User DSN is limited to a single user. It is important to set up your database as a System DSN, rather than a User DSN, to make it accessible to other users.
FIGURE 45.1. Setting up the Actions data source.
If you do not have Microsoft Access 97, you'll still be able to follow the example. In a later section of this chapter, "Setting Up the mSQL Server," I'll show you how to download and install the mSQL database server.
As mentioned in Chapter 44, I highly recommend using the IDS JDBC driver provided by IDS Software. It is a Type 3 JDBC driver that works with the IDS Server--a database access server that connects the IDS JDBC driver with your database. You can download an evaluation copy of the IDS Server and JDBC driver from the IDS Software Web site at http://www.idssoftware.com. The IDS software is distributed as a self-extracting executable file. Open this file and follow the installation instructions.
After installing the IDS software, read through the User's Guide. In particular, Section 1 describes how to set up the IDS Server, and Section 4 describes how to set up the IDS JDBC driver. Don't worry. You probably won't have to make many changes to configure the server and driver for your particular needs. I installed the software in the default C:\IDSServer directory on the same machine as Microsoft Access. Be sure to include the IDS JDBC driver in your CLASSPATH. I added C:\IDSServer\classes to my CLASSPATH.
[begNOTE: If your organization is intent on pursuing Java database programming, you should seriously consider obtaining a production version of the IDS Server and JDBC driver. They are an excellent solution to providing database connectivity to Java applications and applets.
Whether you have Microsoft Access or not, it will be worth your time to download and install the mSQL database server. If you don't have Microsoft Access 97, you can use mSQL to work some of the examples in this book. If you do have Microsoft Access 97, using mSQL will broaden your base of experience. You can obtain a copy of the Windows version of mSQL from ftp://bond.edu.au/pub/Minerva/msql/Contrib/Win-mSQL/.
The Windows version of mSQL is a zipped file that you must unzip before installing mSQL. After unzipping the file, read the Readme and Readme.w32 files for installation instructions. The mSQL server is easy to install and use if you follow these instructions. The mSQL.htm file located in the \DOC directory of the unzipped files provides you with all the information you need to run mSQL.
Make sure that you read the license that comes with mSQL. It is distributed as shareware and must be registered as specified in the license.
In order to connect to an mSQL server from Java, you need an appropriate JDBC driver. Luckily, an mSQL JDBC driver was developed by George Reese. You can download the latest version from http://www.imaginary.com/Java/.
The driver is distributed as a tarred, gzipped file. You can extract the contents of this file using an unarchiving tool such as WinZip. After extracting the file, add the imaginary.zip file to your CLASSPATH.
An mSQL database is provided in the ch45\ActmSQL directory. To use this database with mSQL, move the entire ActmSQL directory under c:\msql\msqldb. The c:\msql\msqldb directory is where mSQL keeps its database files. The c:\msql\msqldb\ActmSQL directory is the directory used for the ActmSQL database.
Now that you've gone to all the trouble of installing and configuring the IDS Server, the IDS JDBC driver, the mSQL server, and the mSQL JDBC driver, you can reap the fruits of your labor. The AccessApp program, shown in Listing 45.1, allows you to use a variety of JDBC drivers to access the Microsoft Access 97 database (action.mdb) and the mSQL database (ActmSQL).
import java.awt.*; import java.awt.event.*; import java.sql.*;
){kind=link}
public class AccessApp extends Frame {
TextField driver = new TextField(60);
TextField url = new TextField(60);
TextField sql = new TextField(60);
Button doIt = new Button("Do it!");
TextArea resultArea = new TextArea(10,60);
public static void main(String args[]){
AccessApp app = new AccessApp();
}
public AccessApp() {
super("AccessApp");
setup();
pack();
addWindowListener(new WindowEventHandler());
show();
}
void setup() {
setupMenuBar();
setLayout(new GridLayout(2,1));
Panel topPanel = new Panel();
topPanel.setLayout(new GridLayout(4,1));
Panel panels[]=new Panel[4];
for(int i=0;i<panels.length;++i){
panels[i]=new Panel();
panels[i].setLayout(new FlowLayout(FlowLayout.LEFT));
}
panels[0].add(new Label("Driver:"));
panels[0].add(driver);
panels[1].add(new Label("URL: "));
panels[1].add(url);
panels[2].add(new Label("SQL: "));
panels[2].add(sql);
doIt.addActionListener(new ButtonHandler());
panels[3].add(doIt);
for(int i=0;i<panels.length;++i)
topPanel.add(panels[i]);
add(topPanel);
add(resultArea);
}
void setupMenuBar() {
MenuBar menuBar = new MenuBar();
Menu fileMenu = new Menu("File");
MenuItem fileExit = new MenuItem("Exit");
fileExit.addActionListener(new MenuItemHandler());
fileMenu.add(fileExit);
menuBar.add(fileMenu);
setMenuBar(menuBar);
}
void accessDB() {
try{
// Load JDBC driver
Class.forName(driver.getText());
// Connect to database
Connection connection=DriverManager.getConnection(url.getText());
Statement statement = connection.createStatement();
// Execute SQL
boolean hasResults = statement.execute(sql.getText());
if(hasResults){
// Get results of query
ResultSet result = statement.getResultSet();
if(result!=null) displayResults(result);
}else resultArea.setText("");
// Close database connection
connection.close();
}catch(Exception ex){
resultArea.setText(ex.toString());
}
}
void displayResults(ResultSet r) throws SQLException {
ResultSetMetaData rmeta = r.getMetaData();
// Use meta data to obtain the number of columns
int numColumns=rmeta.getColumnCount();
String text="";
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
text+=rmeta.getColumnName(i)+" | ";
else
text+=rmeta.getColumnName(i);
}
text+="\n";
while(r.next()){
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
text+=r.getString(i)+" | ";
else
text+=r.getString(i).trim();
}
text+="\n";
}
resultArea.setText(text);
}
class ButtonHandler implements ActionListener {
public void actionPerformed(ActionEvent ev){
String s=ev.getActionCommand();
if(s=="Do it!") accessDB();
}
}
class MenuItemHandler implements ActionListener {
public void actionPerformed(ActionEvent ev){
String s=ev.getActionCommand();
if(s=="Exit"){
System.exit(0);
}
}
}
class WindowEventHandler extends WindowAdapter {
public void windowClosing(WindowEvent e){
System.exit(0);
}
}
}
Before you run the AccessApp program, start the IDS Server by selecting it from the Start menu. Also, open an MS-DOS window and change the directory to C:\msql. Start up the mSQL server by running the msqld.bat file.
When you run AccessApp, it displays the window shown in Figure 45.2. The Driver text field allows you to enter the name of a JDBC driver. The URL field allows you to enter the URL of a database to be accessed by the driver. The SQL field allows you to enter an SQL command to update the database or retrieve information from it. You can use this program to access the Access 97 database and the mSQL database using the JDBC-ODBC bridge, IDS JDBC driver, and mSQL JDBC driver.
Let's start with the JDBC-ODBC bridge. This driver comes with the JDK 1.2.
FIGURE 45.2. Opening window for AccessApp.
The program displays the contents of the ActionItem table, as shown in Figure 45.3.
FIGURE 45.3. Using the JDBC-ODBC bridge driver.
Now let's execute the same command using the IDS JDBC driver. The IDS driver will access the same Actions data set, but it will access it through the IDS Server.
Figure 45.4 shows the results of the query.
FIGURE 45.4. Using the IDS JDBC driver.
What, no change? Don't worry--the results should stay the same. You just executed the same command using a different driver. Try executing some other SQL commands. It really works!
So far, we've accessed the Actions data set using the JDBC-ODBC bridge driver and the IDS JDBC driver. Now we'll access the ActmSQL database using the mSQL database server and the mSQL JDBC driver.
Voilà! You have done it again, as shown in Figure 45.5.
FIGURE 45.5. Using the mSQL JDBC bridge driver.
AccessApp begins by creating and laying out the three text fields, the Do it! button, and the text area. The ButtonHandler class handles the clicking of the Do it! button by invoking the accessDB() method.
The accessDB() method invokes the forName() method of the Class class to load the JDBC driver identified by the driver variable. It then invokes the getConnection() method of the DriverManager class to establish a connection to the database at the specified URL. The createStatement() method of the Connection class is invoked to create a Statement object. The execute() method of this object is invoked to execute the SQL statement entered in the text field referenced by the sql variable.
If the execute() method returns a true value, indicating that it has produced a ResultSet object, the getResultSet() method is invoked to retrieve this object. The displayResults() method is then invoked to display the returned results in the text area.
If no results were returned by the execute() method, the text area is emptied. The close() method of the Connection interface is invoked to close the database connection.
The displayResults() method takes a ResultSet object as an argument. It invokes the getMetaData() method of the ResultSet class to retrieve a ResultSetMetaData object that describes the ResultSet object. It invokes the getColumnCount() method to determine the number of columns in the result set. Then the displayResults method retrieves each column name using the getColumnName() method of the ResultSetMetaData class. It builds a column header using these names. Each column is separated using a vertical bar (|).
After building the column header, displayResults() executes a while loop that reads each row of the ResultSet object. It uses the next() method to move between rows of the result set. It reads the column values of the row using the getString() method of the ResultSet class. The rows of the result set are formatted in the same manner as the column header. The columns of each row are separated by vertical bars. The resulting text string is then displayed in the TextArea object referenced by the resultArea variable.
Now that you have broadened your knowledge of how to use JDBC drivers to connect to databases, you are on your way to becoming a JDBC programmer. Once you connect to a database, the bulk of your programming involves executing SQL statements and displaying the results of those statements. Chapter 43 introduced you to SQL and Chapter 44 showed you how to use SQL with Java. This chapter provides you with more examples of using SQL.
The AccessApp program provides you with a good opportunity to practice the SQL that you learned in Chapter 43. You'll be executing SQL statements directly. If you were writing a Java program to act as an SQL client, you would write a GUI front end to the SQL. The GUI front end would shield your users from having to learn SQL.
Open AccessApp and enter the name of a JDBC driver in the Driver field and the URL of the Actions data set in the URL field, as shown in Figure 45.6. I'll be using the JDBC-ODBC driver this time. You can use the IDS driver or any other driver that works with ODBC.
FIGURE 45.6. Specifying the JDBC driver and database URL.
Enter the following SQL statement in the SQL field, as shown in Figure 45.7. Click Do it! to execute this statement:
CREATE TABLE Test (Col1 char(20), Col2 int, Col3 float)
This statement creates a new table named Test in the Actions data set (Microsoft Access 97 action.mdb database). The table has columns named Col1, Col2, and Col3. Col1 is a 20-character-wide text column. Col2 stores integer values, and Col3 stores floating point values.
Enter the following SQL statement and click the Do it! button (see Figure 45.8.):
INSERT INTO Test VALUES (`Java', 1, 10.0)
This statement inserts a new row into the database with the value of Col1 set to `Java', Col2 set to 1, and Col3 set to 10.0.
FIGURE 45.7. Using the CREATE TABLE statement to create the Test table.
FIGURE 45.8. Using the INSERT statement to add new rows to the Test table.
Enter the following two rows in the same manner:
INSERT INTO Test VALUES (`JDBC', 10, 1.0) INSERT INTO Test VALUES (`ODBC', 100, 0.1)
Now let's see what our database looks like. Enter the following and click the Do it! button:
SELECT * FROM Test
The text area displays the contents of the Test table, as shown in Figure 45.9.
Enter the following statement to delete all rows where the value of Col2 is 100 (see Figure 45.10.):
DELETE FROM Test WHERE Col2 = 100
Click the Do it! button to execute the DELETE statement.
FIGURE 45.9. Checking the results of the INSERT statements.
FIGURE 45.10. Using the DELETE statement to delete the last row.
Let's replace the deleted row using the following INSERT statement:
INSERT INTO Test VALUES (`SQL', 50, 2.5)
Now execute the UPDATE statement, shown in Figure 45.11:
UPDATE Test SET Col2 = 99 WHERE Col3 < 10.0
FIGURE 45.11. Using the UPDATE statement to change selected rows.
Are you curious to see what the Test table looks like? Execute the following statement to dump all rows of Test:
SELECT * FROM Test
Figure 45.12 shows the results of the query.
FIGURE 45.12. Checking the updated Test table.
Execute the following SELECT statement to rearrange the first and second columns and sort by the third column:
SELECT Col2, Col1, Col3 FROM TEST ORDER BY Col3
Figure 45.13 shows the modified output.
FIGURE 45.13. Sorting the results of a query.
As you saw in the previous section, working with SQL is pretty easy. There are only a handful of important SQL statements. Once you learn their syntax, you can accomplish quite a bit.
Connecting to databases and executing SQL statements enables you to add information to the databases and retrieve information in the form of ResultSet objects. You can think of ResultSet objects as custom tables that are generated by the database as the result of processing a SELECT statement. The SELECT statement identifies the columns to be selected and the way results should be ordered.
Accessing the data contained in a ResultSet object is pretty straightforward. However, like laying out a GUI, it can be somewhat tedious. A ResultSet object can be thought of as a window on the custom table returned as the result of processing a SELECT statement. This window lets you view only one row of the table at a time. You can move the window forward to the next row (using the next() method), but you can never move it back. This means that you have to store the information of interest in a particular row before moving onto the next row. The get methods of the ResultSet interface allow you to retrieve the information contained in a particular column of the current row. These methods convert the column data from its SQL type to a Java type. In most cases, you will use the getString() method to retrieve information as a Java String object. However, there are methods that work with all primitive types and objects. The getAsciiStream(), getUnicodeStream(), and getBinaryStream() methods that are used to retrieve large column values are Java streams. You can use the read() methods of the particular stream class to access the contents of the stream in manageable amounts.
The ResultSet interface does not provide any methods to find out how many columns are in a particular row. Instead, it provides the getMetaData() method to return a ResultSetMetaData object.
A ResultSetMetaData object provides quite a bit of information about its corresponding ResultSet object. There are several methods that return information about the columns of the ResultSet object and the table from which the columns originated, as well as other helpful information. Although you probably won't use all of these methods, you will find yourself using some of them, such as the getColumnCount() and getColumnName() methods, in almost all of your Java database applications.
So far, whenever we've executed an SQL statement, the statement has automatically updated the contents of the database. This approach to database updating works fine for some database applications but not as well for others.
Consider the case where several database tables need to be updated to input a new customer order into a database that supports an online ordering application. For example, a Customers table may need to be updated with information about the new customer, an Orders table may need to be updated with information about the order, and a ProductAvailability table may need to be updated to mark a product as sold. What happens if a database connection is broken before all three tables are updated? The database is left in a corrupted state.
Transaction processing was invented as a solution to this database update problem. A transaction consists of a group of related database operations. The SQL statements that make up a transaction update the database at the same time. Even though the statements are executed in sequence, they do not permanently update the database until they are committed. If an error occurs during the processing of a transaction, the SQL statements that make up the transaction can be rolled back.
The setAutoCommit(), commit(), and rollback() methods of the Connection interface are used to implement transaction processing. The setAutoCommit() method turns off automatic committing of SQL statements. When setAutoCommit() is turned off, SQL statements are organized into a single transaction until a commit() or rollback() method is invoked. The commit()method completes a transaction by committing all SQL statements that were executed since the last commit or rollback. The rollback()method reverses a transaction that is in progress by erasing the effects of all SQL statements that were executed since the last commit or rollback.
Transaction processing helps eliminate many problems associated with multiple database updates. However, it creates some problems of its own. Consider the case where two separate transactions occur simultaneously. An SQL statement of the first transaction changes a row in a table, and an SQL statement of the second transaction performs a query that reads the changed row. If the first transaction has not yet been committed, what row value should be reported in the query?
Transaction isolation levels are used to specify how potential conflicts between transactions should be resolved. Higher levels of transaction isolation prevent conflicts from occurring between concurrent transactions. However, this isolation comes at the expense of reduced database performance. At the highest level of transaction isolation, transactions must occur in a serial fashion, eliminating concurrent database transactions completely.
The Connection interface defines constants that can be used to specify transaction isolation levels. The getTransactionIsolation() and setTransactionIsolation() methods of the Connection interface are used to access transaction isolation levels. The supportsTransactionIsolation() method of the DatabaseMetaData interface can be used to determine what levels of transaction isolation are supported by a particular database.
The CommitApp program, shown in Listing 45.2, illustrates the use of transaction processing. I use the IDS JDBC driver in CommitApp. You can use a different driver, but you'll have to change the first couple lines of the setupDB() method to reflect your driver and database URL. If you decide to stick with the IDS driver, make sure that you start the IDS Server before running CommitApp.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
public class CommitApp extends Frame {
TextField sql = new TextField(60);
Button commit = new Button("Commit");
Button execute = new Button("Execute");
TextArea resultArea = new TextArea(10,60);
Connection connection;
public static void main(String args[]){
CommitApp app = new CommitApp();
}
public CommitApp() {
super("CommitApp");
setup();
setupDB();
pack();
addWindowListener(new WindowEventHandler());
show();
}
void setup() {
setupMenuBar();
setLayout(new GridLayout(2,1));
Panel topPanel = new Panel();
topPanel.setLayout(new GridLayout(2,1));
Panel panels[]=new Panel[2];
for(int i=0;i<panels.length;++i){
panels[i]=new Panel();
panels[i].setLayout(new FlowLayout(FlowLayout.LEFT));
}
panels[0].add(new Label("SQL: "));
panels[0].add(sql);
commit.addActionListener(new ButtonHandler());
execute.addActionListener(new ButtonHandler());
panels[1].add(commit);
panels[1].add(execute);
for(int i=0;i<panels.length;++i)
topPanel.add(panels[i]);
add(topPanel);
add(resultArea);
}
void setupMenuBar() {
MenuBar menuBar = new MenuBar();
Menu fileMenu = new Menu("File");
MenuItem fileExit = new MenuItem("Exit");
fileExit.addActionListener(new MenuItemHandler());
fileMenu.add(fileExit);
menuBar.add(fileMenu);
setMenuBar(menuBar);
}
void setupDB() {
try{
// Load IDS driver
Class.forName("ids.sql.IDSDriver");
String url = "jdbc:ids:// cx122974-a.cv1.sdca.home.com:80/";
url+="conn?dbtype=odbc&dsn='Actions'";
// Connect to database
connection=DriverManager.getConnection(url);
// Get meta data about database
DatabaseMetaData meta=connection.getMetaData();
// Determine if database supports transaction processing
if(meta.supportsTransactions())
// Turn off automatic committing of transactions
connection.setAutoCommit(false);
else{
String err="Your database server/driver does not support Âtransactions.";
System.out.println(err);
System.exit(0);
}
}catch(Exception ex){
resultArea.setText(ex.toString());
}
}
void commitTransactions() {
try{
// Commit current transaction
connection.commit();
}catch(Exception ex){
resultArea.setText(ex.toString());
}
}
void executeTransaction() {
try{
Statement statement = connection.createStatement();
// Execute SQL statement
boolean hasResults = statement.execute(sql.getText());
if(hasResults){
// Retrieve and display results
ResultSet result = statement.getResultSet();
if(result!=null) displayResults(result);
}else resultArea.setText("");
}catch(Exception ex){
resultArea.setText(ex.toString());
}
}
void displayResults(ResultSet r) throws SQLException {
ResultSetMetaData rmeta = r.getMetaData();
int numColumns=rmeta.getColumnCount();
String text="";
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
text+=rmeta.getColumnName(i)+" | ";
else
text+=rmeta.getColumnName(i);
}
text+="\n";
while(r.next()){
for(int i=1;i<=numColumns;++i) {
if(i<numColumns)
text+=r.getString(i)+" | ";
else
text+=r.getString(i).trim();
}
text+="\n";
}
resultArea.setText(text);
}
void closeConnection(){
try {
connection.close();
}catch(Exception ex){
}
}
class ButtonHandler implements ActionListener {
public void actionPerformed(ActionEvent ev){
String s=ev.getActionCommand();
if(s=="Commit") commitTransactions();
else if(s=="Execute") executeTransaction();
}
}
class MenuItemHandler implements ActionListener {
public void actionPerformed(ActionEvent ev){
String s=ev.getActionCommand();
if(s=="Exit"){
closeConnection();
System.exit(0);
}
}
}
class WindowEventHandler extends WindowAdapter {
public void windowClosing(WindowEvent e){
closeConnection();
System.exit(0);
}
}
}
Open CommitApp and list the current content of the ActionItem table using the SELECT * FROM ActionItem SQL statement. See Figure 45.14. (Click the Execute button to execute an SQL statement.)
FIGURE 45.14. Displaying the original ActionItem table.
Now insert a new row into the ActionItem table by executing the following SQL statement, as shown in Figure 45.15 (make sure that you don't click the Commit button):
INSERT INTO ActionItem VALUES(`6', `Close out quarter.', `7/15/98', 1, `Jason')
When you redisplay the ActionItem table, the newly added row is shown, as in Figure 45.16.
Exit the CommitApp program, restart IDS server, and then open CommitApp a second time. When you redisplay the ActionItem table this time, the newly added row is not displayed. (See Figure 45.17.) That's because you terminated the database connection before committing the new row.
FIGURE 45.15. Inserting a new row into the ActionItem table.
FIGURE 45.16. Displaying the updated table.
FIGURE 45.17. The transaction was not committed.
Reinsert the row by executing the following statement again:
INSERT INTO ActionItem VALUES(`6', `Close out quarter.', `7/15/98', 1, `Jason')
Now click the Commit button to commit the transaction. Exit CommitApp and then open it a third time. When you list the ActionItem table, the newly inserted row is displayed because you committed the new transaction before exiting the CommitApp program.
FIGURE 45.18. This time the transaction was committed.
CommitApp is similar to AccessApp in that it allows you to enter SQL commands directly into a database. It differs in that it uses the IDS JDBC driver (and IDS Server) and works with the action.mdb Microsoft Access 97 database that you used earlier in the chapter.
The setupDB() method is invoked during the program's initialization. It establishes a connection with the Actions data set via the IDS JDBC driver and IDS server. The getMetaData() method is invoked to retrieve the DatabaseMetaData object that describes the database to which the connection was established. The supportsTransactions() method is invoked to determine whether the database supports transaction processing. If the database does support transaction processing, the setAutoCommit() method is invoked to turn off the automatic committing of database transactions. Note that the database connection is left open.
When a user clicks the Commit or Execute button, the ButtonHandler object associated with the button checks to see what button was clicked and invokes the commitTransactions() or executeTransactions() methods.
The commitTransactions() method invokes the commit() method of the Connection interface to close the database connection.
The executeTransactions() method invokes the execute() method of the Statement class to execute the SQL statement entered by the user. If the SQL statement returns a ResultSet object, it invokes getResultSet() to retrieve the ResultSet object and displayResults() to display it.
The displayResults() method is the same method used in the AccessApp program.
Note that the MenuItemHandler and WindowEventHandler classes invoke the closeConnection() method to terminate the database connection before exiting the program.
Until now, we haven't addressed the issue of database security, which is a serious concern for most organizations. Because databases may contain sensitive information about a company's operation, it is often imperative that such information be restricted to those who are trusted to handle it in a secure manner.
The integrity of database information is also paramount. Unauthorized changes to critical database information could have an adverse impact on a company's capability to carry out business operations.
Finally, data availability is important. Users must be able to get access to database information when they need it. This access often needs to be provided 24 hours a day, seven days a week.
How does the JDBC support database security? The answer is not very comforting. The JDBC currently relies on the database server to provide security protection. You probably noticed that we didn't even need to use a password to access the databases used as examples for this chapter. That's because we were working with personal database products (Microsoft Access and mSQL). Enterprisewide database servers, such as Microsoft SQL Server, do require a user ID and password in order to establish a database connection. However, even password protection is not very secure. If passwords are not encrypted between client and server, they can be easily intercepted and compromised.
Fortunately, vendors have developed solutions to Java database security. The folks at IDS Software have incorporated Secure Socket Layer (SSL) version 3 into their IDS server product line. This feature is referred to as Secure JDBC and provides encrypted communication between the database driver and server. The Secure JDBC solution is very flexible, uses the industry standard SSL, and provides a high level of security. Chapter 5 of the IDS Server User's Manual explains the security features provided by Secure JDBC.
NOTE: The SSL protocol is an industry standard developed by Netscape Communications, Inc.
In this chapter you learned how to work with vendor-specific JDBC drivers and database servers. You used type 1 and 3 drivers with Microsoft Access, and a type 4 driver with mSQL. You then gained some more hands-on experience working with SQL and the results of SQL queries. You also learned about transaction processing and database security. In the next chapter, you'll learn how to access databases with applets.
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
