Section 13.4. Relational Databases | Introduction to Computing & Programming Algebra in Java(c) A Multimedia Approach

[Page 466 (continued)]

13.4. Relational Databases

Most modern databases are relational databases. In relational databases, information is stored in tables (Figure 13.8). Columns in a relational table are named, and rows of data are assumed to be related.

Figure 13.8. An example relational table.

Complex relationships are stored across multiple tables. Let's say that you have a bunch of pictures of people, and you want to keep track of which people are in which picturesthere can be more than one person in a given picture. You might record a structure like that in a collection of tables: Person, Picture, and PicturePerson. The table Person would store information about a person. The table Picture would store information about a picture. And the table PicturePerson would hold the relationships between the picture and the person or people in it (Figure 13.9).

[Page 467]

Figure 13.9. Representing more complex relationships across multiple tables.

How would you use tables like in Figure 13.9 to find a picture that has Tammy in it? You start up by looking up the person identifier (PersonID) in the Person table (6), then find a picture which has that person identifier associated with it (6) in the PicturePerson table, then use the picture identifier (PictureID) to look up the picture name in the Picture table, to get the result of tammy.jpg. How about figuring out who's in that picture? You can look up the picture identifier in the Picture table, then find the person identifiers that are related to that picture identifier in the PicturePerson table, and then look up the people's names in the Person table.

We call this use of multiple tables to answer a query (a request for information from a database) a join. Database joins work best if the tables are kept simple, where there is only a single relation per row.

13.4.1. SQL

How do you communicate what you are looking for to a database? You typically use SQL (Structured Query Language) to manipulate and query databases. There are actually several languages that are part of the SQL database language family, but we're not going to make distinctions here. SQL is a large and complex programming language, and we're not going to even attempt to go over all of it. But we are going to touch on enough of it to give you a sense of what SQL is like and how it's used.

[Page 468]

SQL can be used with many different databases, including Microsoft Access. There are also freely available databases like MySQL that use SQL and can be controlled from Java. If you want to play with the examples we'll be doing here, you can either use Microsoft Access or MySQL.

If you want to use MySQL you will need to install it from http://www.mysql.com. You will also need to download the driver that allows Java programs to talk to the MySQL database.

13.4.2. Getting Started: Drivers and Connections

To manipulate a relational database from Java, you need to work with objects in the java.sql package. The first thing you need to do is load the driver class. The driver is the object that knows how to handle requests to that particular database. You load a database driver by using the following:

Class.forName("driverName");

This uses a class (static) method on the class Class that loads a class based on its name. The driverName is the full name (the package name followed by '.' then the class name) of the driver class. To connect to an Access database you can use the sun.jdbc.odbc.JdbcOdbcDriver class. To connect to a MySQL database, you can use the com.mysql.jdbc.Driver class.

Anytime you want to communicate with the database you will need to create a Connection object. A connection specifies the database that you want to communicate with and optionally ensures that you protect the database by using a login and password.

Connection connection =    DriverManager.getConnection(url,"login","password");

There is also a version of the getConnection method that only takes the URL. This is a class (static) method on the DriverManager class. To connect to an Access database we use a JDBC to ODBC bridge. JDBC refers to the classes in the java.sql package that work with databases. ODBC stands for Open DataBase Connectivity and was developed by Microsoft. The JDBC to ODBC bridge translates JDBC operations into ODBC operations. In order to use this we need to create an ODBC data source.

On a Windows machine open the control panel and open the ODBC Data Source Administrator window (Figure 13.10). Make sure that the USER DSN tab is showing and then click on ADD to add a new data source.

Figure 13.10. ODBC data source administrator window. (This item is displayed on page 469 in the print version)

This will display a window titled "Create New Data Source" (Figure 13.11). Click on Microsoft Access Driver and then on Finish to create a data source to a Microsoft Access database.

Figure 13.11. Create new data source window. (This item is displayed on page 469 in the print version)

This will let you map an ODBC name to a Microsoft Access file. Use this window (Figure 13.12) to create a person data source that maps to the person.mdb database in the bookClasses directory.

Figure 13.12. An ODBC data source for the Microsoft Access person database. (This item is displayed on page 470 in the print version)

Many databases limit the number of connections that there can be to the database at the same time. However, many different programs can be connected to a database at the same time. So when you use a connection in your program, you need to close it when you are finished with it, to let other programs use it. It is best to create a connection each time you need to communicate with a database and then close it after each communication. Most database drivers pool (keep track of) connections so that when you create one, the driver takes one from the pool of free connections and when you close a connection, the driver releases it to the free pool. To close a connection use:

[Page 469]

[Page 470]

connection.close();

All of the methods that work with databases can cause an SQLException. You will need to either throw exceptions or catch them. To catch the exceptions, put the code that can cause exceptions in a TRy block and catch the exceptions in catch blocks.

Here is a class that shows how to load a driver and create and close a Connection object.

Program 126. Database Manager Class (This item is displayed on pages 470 - 472 in the print version)

import java.sql.*; /**  * Class that handles the connection with the database  */ public class DatabaseManager {   ////////////// fields ////////////////////////////   private String driverName;   private String urlStr;   ////////////   constructors /////////////////////   /**    * Constructor that takes the driver name and url    * @param driver the class that communicates with the 
[Page 471]    * database    * @param url the url of the database as a string    */   public DatabaseManager(String driver, String url)   {     this.driverName = driver;     this.urlStr = url;     // try the following     try {       // load the driver class       Class.forName(driver);     } catch (ClassNotFoundException ex) {       SimpleOutput.showError("Can't find the driver class " +                              driver + ", check the classpath");     }   }   /////////////// methods //////////////////////////   /**    * Method for testing the connection    */   public void testConnection()   {     // try the following     try {     // open the connection to the database     Connection connection =       DriverManager.getConnection(this.urlStr);     // tell the user the connection was opened     System.out.println("Connection established");     // close the connection     connection.close();     // tell the user the connection was closed     System.out.println("The connection was closed");     } catch (SQLException ex) {       SimpleOutput.showError("Trouble with the " +                              "database connection");       ex.printStackTrace();     }    } 
[Page 472]   /* main for testing */   public static void main(String[] args)   {     // create the database manager for an Access database     DatabaseManager dbManager =       new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",                           "jdbc:odbc:person");    // create the database manager for a MySQL database    // new DatabaseManager("com.mysql.jdbc.Driver",    //                     "jdbc:mysql://localhost:3306/person");    // test a query     dbManager.testQuery("Select FirstName, Age From " +                         "Person Where Age > 40", 2);   } }

13.4.3. Querying the Database

Think about selecting data in a database as literally selecting, as you would in a word-processor or spreadsheet, the rows that you want in the table. Some examples of selection commands in SQL are:

Select * From Person Select FirstName, Age From Person Select * from Person Where Age > 40 Select LastName, Age From Person Where Age > 40

The general syntax for a simple query is: Select fieldList From Table.

The field list is a comma-separated list of field (column) names. You can ask for just one field. You can also use '*' for the field name to get all of the fields (columns) in the table. You can also add a condition to the query using the keyword "Where" as shown above.

To send a query to a database we need to create a Statement. We use the Connection method createStatement to do this:

Statement statement = connection.createStatement();

There can only be one statement open at a time on a connection. So we have to use this and close it before we can open another on the same connection.

To execute a query use:

ResultSet rs = statement.executeQuery(queryStr);

The returned ResultSet is like a temporary table that holds the result of the query. To process it we need to walk through each row that is returned and get the data from each column. When it is returned the cursor (a pointer to the current row) is positioned before the first row. This may sound strange but it allows you to use a while loop and loop while the next method returns true, which means that there is a next row. The next method also changes the cursor to point to the next row. It is possible that there are no rows in the result set.

[Page 473]

To process the column data in the result set we use the getXXX(int index) methods where the XXX stands for the type of data that we want to get. The index stands for the column number which starts with 1 (not 0). Or you can use getXXX(String colName) where colName is the name of the column.

Common Bug: Result Sets Column Numbers Start with 1

Most of the items that we have been working with in Java start with the first item at index 0. However, the result set column numbers start with 1. This means that if you are looping through the columns, it is better to start your index variable at 1 and loop while it is less than or equal to the desired number of times you want to loop.

After you process a result set, you must close it and then close the statement. Remember that all of the methods that work with a database can cause a SQLException so all the code needs to be in a TRy block. Here is an example method that executes a passed query and prints the data from the result set.

Program 127. Method to Display the Results of a Database Query (This item is displayed on pages 473 - 474 in the print version)

/**  * Method to test a query and print the results  * @param query the query to execute  * @param numCols the number of columns in the result  */ public void testQuery(String query, int numCols) {   // try the following   try {   // open the connection to the database   Connection connection =     DriverManager.getConnection(this.urlStr);   // create a statement   Statement statement = connection.createStatement();   // execute the query   ResultSet rs = statement.executeQuery(query);   // print out the results   while (rs.next())   {     for (int i = 1; i <= numCols; i++)     {       System.out.print(rs.getString(i) + ", ");     }     System.out.println();   } 
[Page 474]   // close the result set   rs.close();   // close the statement   statement.close();   // close the connection   connection.close();   } catch (SQLException ex) {     SimpleOutput.showError("Trouble with the database " +                            urlStr);     ex.printStackTrace();   } }

This method can be executed by the following main method.

/* main for testing */ public static void main(String[] args) {   // create the database manager for an Access database   DatabaseManager dbManager =     new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",                         "jdbc:odbc:person");  // create the database manager for a MySQL database  // new DatabaseManager("com.mysql.jdbc.Driver",  //                     "jdbc:mysql://localhost:3306/person");   // test a query   dbManager.testQuery("Select FirstName, Age From Person", 2); }

Executing this main will result in the following:

Mark, 42, Barb, 43, Matthew, 13, Tammy, 25, Katherine, 10, Jennifer, 7, Jakita, 27,

We can narrow the selection by adding a condition to the query.

Program 128. Trying a Query (This item is displayed on pages 474 - 475 in the print version)

/* main for testing */ public static void main(String[] args) 
[Page 475]{   // create the database manager for an Access database   DatabaseManager dbManager =     new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",                         "jdbc:odbc:person");  // create the database manager for a MySQL database  // new DatabaseManager("com.mysql.jdbc.Driver",  //                     "jdbc:mysql://localhost:3306/person");  // test a query  dbManager.testQuery("Select FirstName, Age From " +                       "Person Where Age > 40", 2); }

Executing this main will result in:

Mark, 42, Barb, 43,

We can now think about doing a join using a conditional select. We want to select the pictures that have Jennifer in them. So, we want the names of the pictures that have a PictureID that matches a PictureID in the PicturePerson table that has her PersonID associated with it. You can use the keyword "And" to combine conditionals in SQL. You also need to enclose strings in SQL in quotes. The easiest way to do this in Java is to enclose the strings in single quotes because Java strings use double quotes to signal the beginning and ending of a Java string.

public static void main(String[] args) {   // create the database manager for an Access database   DatabaseManager dbManager =     new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",                         "jdbc:odbc:person");  // create the database manager for a MySQL database  // new DatabaseManager("com.mysql.jdbc.Driver",  //                     "jdbc:mysql://localhost:3306/person");   // test a query   dbManager.testQuery("Select per.FirstName, " +                       "pict.FileName From " +                       "Picture as pict, Person as per, " +                       "PicturePerson as pictPer " +                       "Where per.FirstName = " +                       "'Jennifer' And " +                       "pictPer.PersonID = per.PersonID And " +                       "pict.PictureID = pictPer.PictureID", 2); }

Notice that you can give the table name an alias (another name that you can use to refer to it). Also notice that you need to qualify the field name with the table name when you are pulling data from more than one table. You do this by adding the table name or table alias then '.' then the field (column) name.

[Page 476]

Running this main will result in the following output:

Jennifer, jennySoccer.jpg, Jennifer, MattJennyCorn.jpg,

13.4.4. Using a Database to Build Web Pages

Now let's get back to our WebPageWriter class. We can store information in our database, retrieve it, then put it in our Web pagejust like Amazon, CNN, and eBay.

Program 129. Building a Web Page with Database Content (This item is displayed on pages 476 - 477 in the print version)

/**  * Method for writing a homepage for the passed  * first name that displays her/his interests and age  * @param name the person's first name  * @param interests a list of the person's interests  */ public void writeHomepageV5(String name,                             String interests) {   // Get a DatabaseManager object   DatabaseManager dbManager =     new DatabaseManager("sun.jdbc.odbc.JdbcOdbcDriver",                         "jdbc:odbc:person");   // get this person's age   String age =     dbManager.getStringForQuery(        "Select Age From Person " +        "Where FirstName='" + name + "'");   // try the following   try {     // open a file for writing     BufferedWriter writer =       new BufferedWriter(new FileWriter(name + ".html"));     // write the start     writeStart(writer);     // write the header     writeHead(writer,name + "'s Homepage");     // write the body     writeBody(writer,"<h1>Welcome to " + name +               "'s Homepage</h1>" +               "<p> I am interested in " + interests +               ".</p><p>I am " + age + " years old</p>"); 
[Page 477]     // end the page     writeEnd(writer);     // close the writer     writer.close();   } catch (Exception ex) {     ex.printStackTrace();   } }

You can run this with the following main:

public static void main(String[] args) {   WebPageWriter writer = new WebPageWriter();   writer.writeHomepageV5("Matthew","playing video games"); }

Now we can think about how a large Web site like CNN.com works. Reporters enter stories into a database distributed all over the world. Editors (also distributed, or all in one place) retrieve stories from the database, update them, then store them back. On a regular basis (perhaps more often when a hot story emerges) the Web page generation program runs, gathers up the stories, and generates the HTML. POOF! You have a large Web site! Databases are really critical to how large Web-sites run.

Most Java-generated HTML pages are actually generated by servlets. These are Java programs that run on a Web server and dynamically create HTML pages, often based on user input. Many of these servlets are automatically created from JavaServer Pages (JSP). These are HTML pages that have Java mixed in or that have special tags that work with Java classes to generate content for the pages.