13.4. Relational DatabasesMost 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). 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. SQLHow 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. 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 ConnectionsTo 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. |
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 |
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.
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.
|
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.
/** * 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(); } |
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.
/* main for testing */ public static void main(String[] args) |
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.
Running this main will result in the following output:
Jennifer, jennySoccer.jpg, Jennifer, MattJennyCorn.jpg,
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.
/** * 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>"); |
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.