Databases: Connectivity through Java
AAHC

Click on A to make all fonts on the page smaller.

Click on A to make all fonts on the page larger.

Click on HC to toggle high contrast mode. When you move your mouse over some bold words in high contrast mode, related words are automatically highlighted. Text is shown in black and white.


The JDBC API

The next few lessons focus on connecting Java to databases to access information.

What is JDBC?

JDBC is a Java API associated with accessing tabular data; that is, data that you'd generally want to record in a table. A table is a single store of related information; a database can consist of one or more tables of information that are related in some way. JDBC is used to process SQL statements and enable your programs to access relational databases. Like other Java APIs, the JDBC API consists of classes and interfaces written in the Java programming language. Their main purpose is to provide a standard API for database developers and make it possible to write database applications using a pure Java API, which in turn ensures that your code will be portable.

Go to the java.sql package to see some of the available classes and interfaces. Skim through the Package java.sql Description and its history. Go to the javax.sql package to see some of the extended server-side functionality that its classes and interfaces provide. This course focuses on the java.sql package. (The server-side information in javax.sql applies in the J2EE course in this series.)

What Does JDBC Help Us Do?

JDBC helps us to write platform-independent Java programs. These programs can be used to connect to a wide range of SQL databases and manipulate the data without modifying and/or recompiling the Java programs, even when moving from platform to platform or from DBMS to DBMS. In short, JDBC makes it possible to:

  1. Establish a connection with a database.
  2. Send SQL statements.
  3. Process the results.

Although JDBC provides many classes and interfaces for use with databases, those three are the most commonly used. Here we've set up a table of the common JDBC tasks and their corresponding classes or interfaces:

TaskMost Used Class or Interface
establish a connection with a databasejava.sql.Connection
send SQL statementsjava.sql.Statement
process the resultsjava.sql.ResultSet

We'll illustrate each of these tasks in an example.


Connecting to the Database

We can't do any work with the database until we connect to it.

Access to a Database

SQL can communicate with most database systems without changing the SQL commands. The MySQL database server is likely the most popular open source database software among programmers and is available for use on a wide variety of platforms. We'll use the MySQL database for our examples.

In this course, you have been granted access to the MySQL database on the O'Reilly School of Technology server. You can access that database using the same username and password that you use to log onto your courses.

NoteOST uses a master password (and Sandbox login) for all login instances. In some cases, the password for MySQL can become out of sync and cause this or a similar error:

ERROR 1045 (28000): Access denied for user '<Sandbox Login>'@'cold1.useractive.com' (using password: YES)

This error will also appear if the login or password is incorrect. However, for OST, it is most likely a “password out-of-sync” issue.

To fix this, update the password for the OST Sandbox from the My Account section of the Student Start Page (students.oreillyschool.com/student/). This will reset the password for all login instances, including MySQL. You can use your current password without changing it. Remember that the Sandbox login and password are case sensitive.

Even though SQL is standardized, each database vendor has a different interface, as well as different extensions of SQL. Our notes and examples here will be all-purpose so you can use them on this, as well as other databases.

The Driver

Programmers use many different databases. Each database needs a driver to connect it to the Java program.

There are four types of JDBC drivers. We will use a Type 4 driver, because it will communicate directly with our data source. Let's go ahead and start the Project and get the driver. Create a new databaseDriver project for this lesson.

NoteThe driver is actually a collection of Java classes that assist connectivity that are packaged into one .jar (Java ARchive) file, similar to a .zip or .tar file, used to hold multiple files. The Java VM can get into .jar files for classes and files it needs if the .jar is in the CLASSPATH.

Put the driver into the CLASSPATH for your project. To do this, download the appropriate driver (we've already done this for you), then right-click the databaseDriver project, choose Build Path | Add External Archives, browse to the C:\jdbc folder and select the mysql-connector-java-5.1.5-bin.jar file:


NoteYou might find a newer version of the JDBC driver in this folder. The newer versions should be backward compatible with the versions referenced in this lesson.

Then you'll see this file:


It provides Java with a location for storing classes that it needs for this application. You will need to access this path for each project that uses the database, so remember this procedure.

It appears that Eclipse places these "external archives" into a folder called Referenced Libraries, but because this is the Referenced Library, Eclipse actually just points to the location of the real file. A CLASSPATH is a PATH to the CLASSES that Java requires for an application.

Verifying the Connection

Now let's experiment with connectivity. Create a new java4_Lesson11 project. If you're given the option to Open Associated Perspective, click No. Create a DatabaseManager class in the project as shown:

Type DatabaseManager as shown in blue:

CODE TO TYPE: DatabaseManager
package db;

import java.sql.*;

public class DatabaseManager {
    private Connection connection;                                             // The database connection object.
    private Statement statement;                                               // the database statement object, used to execute SQL commands.

    public DatabaseManager (String username, String password ) {               // the constructor for the database manager.
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username;       // our database--username is your O'Reilly login username and is passed in.
        try {
            Class.forName ("com.mysql.jdbc.Driver");                           // get the driver for this database.
            System.out.println("Driver is set; ready to go!");
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return;                                                            // cannot even find the driver--return to caller since cannot do anything.
        }

        try {                                                                     // Establish the database connection, create a statement for execution of SQL commands.
            connection = DriverManager.getConnection (url, username, password );  // username and password are passed into this Constructor.
            statement  = connection.createStatement();                            // statement used to do things in the database (e.g., create the PhoneBook table).
        }
        catch (SQLException exception ) {
            System.out.println ("\n*** SQLException caught ***\n");
            while (exception != null) 
            {                                                                     // grab the exception caught to tell us the problem.
                System.out.println ("SQLState:   " + exception.getSQLState()  );
                System.out.println ("Message:    " + exception.getMessage()   );
                System.out.println ("Error code: " + exception.getErrorCode() );
                exception = exception.getNextException ();
                System.out.println ( "" );
            }
        }
        catch (java.lang.Exception exception) {                                  // perhaps there is an exception that was not SQL related.
            exception.printStackTrace();                                         // shows a trace of the exception error--like we see in the console.
        }
    }
}   

Save it.

Let's go over this particular line of code first: String url = "jdbc:mysql://sql.useractive.com:3306/" + username; provides JDBC a way to identify a database. Its structure is such that the appropriate driver recognizes and establishes a connection with it. The driver writer determines the JDBC URL that identifies their particular driver. You need not worry about how to form a JDBC URL; just use the URL supplied with the drivers. For instance, if your username is blob, then your url to access the MySQL server at the O'Reilly School of Technology is jdbc:mysql://sql.useractive.com:3306/blob.

(The rest of the code will be explained in the next few sections of the lesson.)

The Factory Design Pattern

There are certain design patterns in Java that are used frequently. By becoming familiar with design patterns, programmers can avoid common pitfalls that may arise when using particular designs. We have already seen two such frequently used patterns: Model/View/Controller (MVC) and Producer/Consumer (in threads). The code in our example above incorporates another common design pattern called the Factory Design Pattern.

The factory method within the factory pattern produces an object. Factory methods are static methods that return an instance of the interface or class, usually through the use of a Constructor and the new command. Factory methods enable programs to produce objects without specifying the precise class that will access the object because they are often instantiated as an interface rather than a class.

Factory methods:

Common names for factory methods include getInstance() and valueOf(), though using these names is optional--choose whatever makes sense for your particular usage.

In our example, when connecting to the database, we don't use new, but obtain objects using the statements Class.forName("com.mysql.jdbc.Driver"), connection = DriverManager.getConnection(url, username, password), and statement = connection.createStatement().

Go to the java.lang.Class class and look at the static method forName(String className). This method automatically creates an instance of a driver and registers it with the DriverManager, so you don't have to create an instance of the class.

Go to the java.sql.DriverManager class and look at the static method getConnection(String url, String user, String password). The method returns an instance of the interface Connection--because it is an interface, it does not have a Constructor and cannot be created with the new command. By returning an instance of the interface, the methods are implemented and you can use them.

In the java.sql.DriverManager class, the Class.forName() is no longer needed. Later we'll comment it out and run the code to see if it works as specified.

Go to the java.sql.Connection interface and then to the createStatement() method. It returns an instance of the interface Statement. Again, because Statement is an interface, it does not have a Constructor and cannot be created with the new command.

Finally, when a method is static, it can be called from the class. Many of the methods we'll use for databases and networking will be static and many of the classes will use the factory method pattern.

Now, let's get back to our database implementation.

Testing Our Connection

It's wise to check the DatabaseManager code one step at a time, so let's make a class to instantiate it and check our connection.

The Main

In the java4_Lesson11 project, create a new PhoneBook class as shown:

Type PhoneBook as shown in blue:

CODE TO TYPE: PhoneBook
package db;

public class PhoneBook {

    public static void main(String[] args) {  // args[0] must be the username and args[1] must be the password to connect to the mysql database

        DatabaseManager databaseManager = new DatabaseManager( args[0], args[1] );  // Create the database manager.
    }
}

Save it. We have two ways to run it.

1: Providing Parameters In Code

The first way is to put your username and password right into your code. This is usually a bad practice, especially for a shared application, because whenever someone else wants to use the code, they need to edit and recompile. Still, for the sake of testing, sometimes it's convenient.

Edit PhoneBook as shown in blue below to reflect your username and password:

CODE TO EDIT: PhoneBook
package db;

public class PhoneBook {

    public static void main ( String[] args ) {                              
        // suppose your username is Iam and password is soCool
        DatabaseManager databaseManager = new DatabaseManager( "Iam", "soCool" );        
    }
}

Note that "Iam" and "soCool" are not a valid username and password; replace them with your own.

Save and run it. You'll see this in the console:

We'd better change the code back to the way it was before:

CODE TO EDIT: PhoneBook
package db;
        
public class PhoneBook {
           
    public static void main ( String[] args ) {   // args[0] must be the username and args[1] must be the password to connect to the mysql database
        DatabaseManager databaseManager = new DatabaseManager( args[0], args[1] );   // Create the database manager.
    }
}

Save it. We'll try to run it another way:

2: Giving Parameters in Eclipse

To run this program from the command line (outside of Eclipse), you would enter java PhoneBook yourUserName yourPassword (with your real username and password, of course). We use the Eclipse GUI to do this, and Eclipse enters our commands for us.

Right-click in the editor window for PhoneBook.java. Choose Run As | Run Configurations...:


In the Run window that opens, choose the Arguments tab. Provide your username and password in the Program arguments: box. Again, use the username and password you use to access the course. Click Run:


You still see this:



This problem is all too common. The Console says "Failed to load JDBC/ODBC driver." That text is from the DatabaseManager class, in the catch block around line 16.

OBSERVE: DatabaseManager Constructor
public DatabaseManager (String username, String password ) {             // the constructor for the database manager
    String url = "jdbc:mysql://sql.useractive.com:3306/" + username;    // where username is your O'Reilly login username
    try {
   Class.forName ("com.mysql.jdbc.Driver");
    }
    catch (Exception e) {
        System.out.println("Failed to load JDBC/ODBC driver.");
        return;
    }
}

We thought that Class.forName ("com.mysql.jdbc.Driver") would go and get the driver, but apparently it didn't find that driver, because it threw an Exception that was caught by our code.

We put the driver's .jar into the databaseDriver Project, and we put these Java classes into the java4_Lesson11 Project. Now we need to get the driver into the Project we're working on:

Right-click on the java4_Lesson11 Project, then choose Build Path | Add External Archives, which opens the file browser for you to get the driver. In the file dialog, start to type the path C:\jdbc\mysql-connector-java-5.1.5-bin.jar. The auto-complete should allow you to press Tab to fill in the file name mysql-connector-java-5.1.5-bin.jar.

Now your java4_Lesson11 should look like this:

Run PhoneBook again, giving it the arguments as shown above (Eclipse may remember them for us).

It's a good thing we included that System.out.println. If our program runs the way we want it to, "Driver is set; ready to go!" will appear in the Console.

Sending SQL Statements

Now that we know we have a connection, we can play with the database. We'll populate our database with some common SQL statements, specifically, methods that do the following:

Most of the method calls are to the instance of the interface Statement named statement. Even though only a few method calls are to the database, constructing them still requires a lot of code. Most of that code is contained within try/catch blocks and System.out.printlns and provides information about exceptions.

Go to the interface java.sql.Statement. Scroll to the Method Detail section (or click here). Scroll down through the methods; every one of them throws an SQLException.

TipWhen writing code with many methods that throw exceptions, localize your try/catch blocks. That is, place each statement (or small grouping) into its own try clause so you can identify the method that threw the exception.

In our example, each method we define in DatabaseManager contains relatively few method calls. And each method has its own try/catch clause for the set of method calls within the method block. There's no other way to do it.

Here's some additional information about exceptions.

Edit DatabaseManager as shown in blue:

CODE TO EDIT: DatabaseManager
package db;

import java.sql.*;

public class DatabaseManager {

    private Connection connection;  // The database connection object.
    private Statement statement;    // the database statement object, used to execute SQL commands.

    public DatabaseManager (String username, String password ) {          // the constructor for the database manager
        String url = "jdbc:mysql://sql.useractive.com:3306/" + username;  // where username is your O'Reilly login username
        try {
            Class.forName ("com.mysql.jdbc.Driver");
        }
        catch (Exception e) {
            System.out.println("Failed to load JDBC/ODBC driver.");
            return;
        }

        try {                                                                       // Establish the database connection, create a statement for execution of SQL commands.
            connection = DriverManager.getConnection (url, username, password );     // username and password are passed into this Constructor
            statement  = connection.createStatement();
            statement.execute("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18));"); // create a table in the database                                       
        }
        catch (SQLException exception ) {
            System.out.println ("\n*** SQLException caught ***\n");
            while (exception != null) 
            {                                                                         // tell us the problem
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();

                System.out.println ( "" );
            }
        }
        catch ( java.lang.Exception exception ) {
            exception.printStackTrace();
        }
    }

    public void addEntry (String name, String phoneNumber ){                       // adds an entry to the Phone Book
        try
        {
            statement.execute ( "insert into PhoneBook values ('" + name + "', '" + phoneNumber + "');" );
        }
        catch ( SQLException exception ) 
        {
            System.out.println ("\n*** SQLException caught ***\n");

            while ( exception != null) 
            {
                System.out.println ("SQLState:    " + exception.getSQLState()  );
                System.out.println ("Message:     " + exception.getMessage()   );
                System.out.println ("Error code:  " + exception.getErrorCode() );
                exception = exception.getNextException ();   
                System.out.println ( "" );
            }
        }
        catch(java.lang.Exception exception ) 
        {
            exception.printStackTrace();
        }
    }
}

Save it.

User Access and Input

Next, we need to allow a user to give commands. Granted, we don't have many commands at our disposal at this time, but we have to start somewhere, right?

In the java4_Lesson11 project, create a UserInterface class as shown:

Type UserInterface as shown in blue:

CODE TO TYPE: UserInterface
  
package db;

import java.sql.*;
import java.util.*;

public class UserInterface {

    private DatabaseManager database;                                      // the reference to the DatabaseManager object, 
                                                                          // handles all requests to access the database

    public UserInterface(DatabaseManager theDatabaseManager) {
	    database = theDatabaseManager;
    }

    public void start() {
        Scanner in = new Scanner (System.in);
        while (true) {                                   // Continue until the user quits
            System.out.println ("Click in the Console,"
             + "\n then enter a command:"
             + "\n A (then Enter) to Add a phone book entry, \n"
             + "Click red square to quit (terminate) for now.");

            String command = in.nextLine();

            if ( command.charAt(0) == 'A' )
            {
                System.out.println ("Enter name: ");
                String name = in.nextLine();
                System.out.println ("Enter phone number: ");
                String phoneNumber = in.nextLine();
                database.addEntry (name, phoneNumber);  // Add this entry to the database.
            }
        }   
    }
}

Save it.

The start() method of the class has a loop that starts with while (true). This allows the application to stay open for continuous input until the user finishes. After each user input, the loop performs the specified action and then returns to prompt again.

Edit the PhoneBook class to instantiate and start this interface as shown in blue:

CODE TO EDIT: PhoneBook
package db;

public class PhoneBook {

    public static void main ( String[] args ) {                                    // args[0] must be the username and 
                                                                                   // args[1] must be the password to connect to the mysql database
        DatabaseManager databaseManager = new DatabaseManager(args[0], args[1] );  // Create the database manager.
    
        UserInterface userInterface = new UserInterface(databaseManager );         // Create access for user input.
        userInterface.start();
    }
}

Save and run it. Make sure to click in the Console so your input goes there.

Except for the red line pointing to the Terminate button, you'll see this (the green text is our sample input):

Closing Our Connections

Never expect the user to use Ctrl+C or Terminate to end a program; that's poor design. The user may not know about these tools and more importantly, terminating an application this way might unexpectedly leave the database without a "clean-up" and information could be lost.

Run PhoneBook again to see another reason that an application must always close its open connections and processes:

The first statement that we execute after getting our connection is:

statement.execute ("create table PhoneBook (Name varchar (32), PhoneNumber varchar (18) );");

Specifically, we execute a statement to create a table named PhoneBook in the database. So, when we run it the second time, after terminating without proper procedure, we are trying to create a table that is already there. We have two ways around this problem: either avoid re-creating the table every time we access the database or remove the table when finished with the demonstration.

In the project for this lesson, you'll need to fix the problem so that we can stop our program with some dignity.

Additional Resources

Here are some additional items you can read to learn about Java and database connectivity.