Welcome to Day 16! Today, you'll see one of the most important topics in JSP programming—working with databases. It's common for JSP pages to interact with databases on the server, and you'll see how that works today and tomorrow. This is one of the most rewarding—but also most complex—areas of JSP programming. Here are the topics you'll see today:
Understanding databases
Understanding Java database connectivity (JDBC)
Taking a look at basic SQL
Connecting to a database
Extracting data from a database
Understanding record sets
Today's work will start with an overview of the whole idea of databases, introducing them and how to access them using Java, and tomorrow will be a code-intensive day showing you how to work with databases in depth.
Like many other programming concepts, databases have become more complex over the years, but the basic concept is still a simple one. Say, for example, that you are in charge of teaching a class and have to record a grade for each student. You might make up a table much like this one to store the grade for each student:
Name Grade -------------------- | Margret | B | |----------|---------| | Ralph | C | |----------|---------| | Ed | B | |----------|---------| | Anne | A | |----------|---------| | Claire | A | |----------|---------| | Susan | B | |----------|---------| | Mark | B | --------------------
Tip
To get the actual documents that define SQL, as standardized by the International Standards Organization (ISO), visit www.iso.org/iso/en/prods-services/catalogue/intstandards/CatalogueListPage.CatalogueList?ICS1=35&ICS2=60, which lists the ISO's catalogue for SQL documents—they're not free, though. (Note that this URL might have changed by the time you read this; in that case, go to the ISO site, www.iso.org, click the link for Information Technology, followed by the link for Languages Used in Information Technology.)
You use JDBC to open a connection to a database, and to execute SQL queries on that database to perform operations. You'll learn about how this works today and tomorrow. JDBC is a big topic, and you'll find plenty of help on it in the Java documentation, as well as online. Here's a starter list:
http://java.sun.com/products/jdbc/index.html—. The JDBC Home Pagehttp://java.sun.com/j2se/1.3/docs/guide/jdbc/index.html—. The current JDBC documentationhttp://java.sun.com/docs/books/tutorial/jdbc/basics/index.html—. A basic JDBC tutorialhttp://developer.java.sun.com/developer/Books/JDBCTutorial/index.html—. An advanced JDBC tutorialhttp://java.sun.com/products/jdbc/faq.html—. A JDBC Frequently Asked Questions (FAQ) list for JDBC
The JDBC 3.0 API is actually made up of two packages:
Tip
You automatically get both packages when you download the Java 2 Platform Standard Edition Version 1.4 or the Java 2 Platform Enterprise Edition.
Here, you'll use the java.sql package. This package includes code to
Connect to a database (the
DriverManagerclass,Driverinterface,DriverPropertyInfoclass, and theConnectioninterface).Send SQL statements to a database (the
Statementinterface for sending basic SQL statements, thePreparedStatementinterface for sending prepared statements, and theCallableStatementinterface for calling database stored procedures).Retrieve and work with the results of an SQL query (the
ResultSetinterface).Work with SQL exceptions (the
SQLExceptionclass thrown by most methods when there is a problem accessing data and by some methods for other reasons, theSQLWarningclass thrown to indicate a warning, theBatchUpdateExceptionclass thrown to indicate that not all commands in a batch update executed successfully, and so on).
Instead of talking about all this in the abstract, it's best seen with an example, so take a look at Listing 16.1. That's the first database example you'll see in today's work, and after running it, you'll see it dissected fully.
Example 16.1. Accessing a Database (ch16_01.jsp)
<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> <HEAD> <TITLE>Accessing the Publishers Database Table</TITLE> </HEAD> <BODY> <H1>Accessing the Publishers Database Table</H1> <% Connection connection = DriverManager.getConnection( "jdbc:odbc:data", "Steve", "password"); Statement statement = connection.createStatement() ; ResultSet resultset = statement.executeQuery("select pub_name from Publishers") ; %> <TABLE BORDER="1"> <TR> <TH>Name</TH> </TR> <% while(resultset.next()){ %> <TR> <TD> <%= resultset.getString(1)%> </TD> </TR> <% } %> </TABLE> </BODY> </HTML>
All JDBC work relies on connecting to a database system of some kind, and different programmers will use different database systems. This example, ch16_01.jsp, uses Microsoft's SQL Server, because SQL Server has become very popular and is widely available on Web servers. But don't worry if you don't have access to Microsoft's SQL Server—the techniques you'll see here are applicable to nearly all database systems that JDBC can connect to. You'll see more on what database systems you can use JDBC with in a few pages—including one that you can download and install for free.
Note
The database system you use is not important as far as the basic SQL techniques you'll learn today and tomorrow, so feel free to use your own database system. There are more than a hundred such systems out there, and each database programmer will usually want to use his own system. That that's not a problem, though, because SQL has been standardized—what you learn today and tomorrow may be used on any of those SQL-compliant systems. All that's different are the details of connecting to the actual database, as you'll see in a few pages. Microsoft's SQL Server was chosen here because it's in such widespread use.
This first example opens a connection to the pubs example database that's installed with SQL Server. This database has tables in it that store data about a number of authors, books, and publishers. This example connects to the Publishers table in that database in a way that you'll see soon, and extracts the publisher's names from the field named pub_name in that table. You can see the results in an HTML table in Figure 16.1.
This example connected to the example database named pubs that's built into SQL Server and accessed the Publishers table in that database. It then obtained all the values in that table's pub_name field and listed them in an HTML table. The next question is: How did it do all that?
To see how this example works, we'll dissect this example step by step. The first step creates a connection to the database using a Connection object:
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
.
.
.
So what's going on here? The answer is that the code is initializing a JDBC driver and using it to get a connection to the database.
To install most of the drivers for these various database systems, you have to download the driver software and install it. To bypass that step here, you'll learn how to use a JDBC driver that already comes built into JDBC—the Open Database Connectivity (ODBC) driver.
Tip
Using the JDBC ODBC driver makes connecting to databases easier because you don't need to download special JDBC driver software, which is why today and tomorrow's work uses this built-in driver. However, if you're going to be doing a lot of database work, you should download or install your database system's JDBC driver, which will probably be faster and more efficient for your particular database system.
In Windows, you register an ODBC source using the control panel. How you actually perform this task varies with your version of Windows—for the details, you can select Start, Help, click the Index tab, and type in ODBC to get help on this topic. For example, in Windows 2000, you open the Administrative Tools folder in the control panel, and then double-click the Data Sources (ODBC) item, opening the ODBC Data Source Administrator dialog box. You click the User DSN tab, followed by the Add button to open the Create New Data Source dialog box you see in Figure 16.2.
Assuming you have SQL Server installed, select the SQL Server item and click Finish (if you have any other database system installed, select that instead to create an ODBC source). That opens the Microsoft SQL Server DSN Configuration dialog box you see in Figure 16.3.
You enter the name you want to give the ODBC source here—this example will just name this source data—as well as the name of the SQL Server you want to use, and click Next to open the authentication options dialog you see in Figure 16.4.
Here you can enter the name and password you use to log into the SQL Server, or just use a Windows NT login, which this example uses to make things simpler (no name and password required). When you click Next, the available databases on the SQL Server are listed in a drop-down list, as you see in Figure 16.5—choose the pubs database, which comes with SQL Server. Then click Next again, followed by Finish.
This creates an ODBC source named data connected to the SQL Server's example database, named pubs. The next step is to get access to that ODBC source in Java.
Note
You don't need ODBC to work with databases in Java, of course; this example just uses ODBC because the JDBC ODBC driver comes built into Java, no extra downloading needed. If you use a driver specific to your own database system, check the documentation of that driver to see how to connect it to a database.
To connect to a database, you need to use a JDBC driver. There's a different driver for each type of database system, and to handle these drivers, you use the java.sql.DriverManager class. You can see the methods of that class in Table 16.1.
Table 16.1. Methods of the DriverManager Class
You need to load the JDBC ODBC driver before using it, and you can do that as you see here—using the Class.forName method:
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
.
.
.
This call loads the JDBC ODBC driver ("sun.jdbc.odbc.JdbcOdbcDriver") and initializes it. If you were using another JDBC driver, such as the one for MySQL, you would change this call to load the appropriate driver, such as Class.forName("org.gjt.mm. mysql.Driver") for MySQL, after making sure the MySQL driver is in the classpath (the JDBC ODBC driver, "sun.jdbc.odbc.JdbcOdbcDriver", is built in, so you don't have to worry about the classpath). To determine which JDBC driver class to use, see your database system's documentation.
Tip
There's another way to load a JDBC driver—you can use the System.setProperty method:
System.setProperty("jdbc.drivers","sun.jdbc.odbc.JdbcOdbcDriver").
The problem with this is that your Java installation's security setup (called the security policy) might not let you modify system properties, so the code you'll see today and tomorrow uses the Class.forName method instead. It's worth noting that the Java implementations on some machines might not use the Class.forName method correctly; if you're having problems, add a call to the newInstance method:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance().
Next, the code uses the getConnection method with the JDBC-ODBC bridge driver to access the ODBC source you've named data with the URL jdbc:odbc:data, and also passes a dummy username and password (this example uses Windows NT authentication, so it doesn't need a username or password, but includes them here to show where you use them with database systems that require them):
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
.
.
.
Note
Note that after you've loaded the JDBC-ODBC driver, you can use the getConnection method to work with any ODBC source (not just data served by SQL Server).
The actual URL you use here depends on your JDBC driver—if you are using another database driver, such as the one for MySQL, you would use a different URL, such as "jdbc:mysql://localhost/data", where data is the name of the database you're working with. Some drivers let you pass data in the URL, such as "jdbc:mysql:// localhost/data ?user=Steve&password=opensesame&autoReconnect=false&initialTimeout=30"—note that the actual implementation details are up to the database driver you're using.
The getConnection method returns a Connection object that represents Java's connection with the database. You can use this Connection object to create a Statement object that holds a SQL statement—and you can use the SQL statement to extract, delete, and update records in the database. You can see the fields of the Connection interface in Table 16.2, and its methods in Table 16.3.
Table 16.2. Fields of the Connection Interface
Table 16.3. Methods of the Connection Interface
Now you've established a connection to the database. The next step is to execute a SQL statement to get the data we want from that database. To execute such a statement, you can use a java.sql.Statement object, and you can create such an object with the Connection object's createStatement method:
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement();
.
.
.
This new object, statement, which implements the java.sql.Statement interface, will hold the SQL you use to work with the database itself. You can see the methods of the java.sql.Statement interface in Table 16.4.
Table 16.4. Methods of the Statement Interface
As you can see in Table 16.4, there are four methods that will let you execute a SQL statement:
boolean execute(String sql)—. Executes a SQL statement that can return multiple results.int[] executeBatch()—. Submits a batch of commands (that is, multiple commands) to the database for execution, and if all commands execute successfully, returns an array of update counts.ResultSet executeQuery(String sql)—. Executes a SQL statement that returns a singleResultSetobject.int executeUpdate(String sql). Executes aSQL INSERT,UPDATE, orDELETEstatement that causes the database to be updated.
In today's example, the code uses the executeQuery method to execute a SQL statement using the pubs database. The idea here is to get the names of the publishers in the Publishers table in that database using the pub_name field. You can use the SQL statement "select pub_name from Publishers" to do that. This statement will return a result set with as many records as there are in the Publishers table, and each record in the result set will have only one field, pub_name. Here's what that looks like in code:
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset =
statement.executeQuery("select pub_name from Publishers") ;
.
.
.
For example, the ResultSet class's next method makes the next record the current record, and when there are no more records, this method returns a value of false. You can use that fact to loop over all the records in a result set. How do you actually read the data from the current record in a ResultSet object? Each record is made up of various fields, and each field can hold different types of data, so a ResultSet object supports many different methods to read that data. You can pass each method the number of the field (the first field is field 1, not field 0) you want to read, or the field's name. Here's a list of some of these methods—you'll see them all in detail tomorrow:
Array getArray()—. Returns the value of the specified column in the current row of thisResultSetobject as anArrayobject.BigDecimal getBigDecimal()—. Returns the value of the specified column in the current row of thisResultSetobject as ajava.math.BigDecimalobject.Blob getBlob()—. ABlobis a binary large object, which means a large amount of binary data. This method returns the value of the specified column in the current row of thisResultSetobject as aBlobobject.boolean getBoolean()—. Returns the value of the specified column in the current row of thisResultSetobject as a Boolean.byte getByte()—. Returns the value of the specified column in the current row of thisResultSetobject as abyte.byte[] getBytes()—. Returns the value of the specified column in the current row of thisResultSetobject as abytearray.Date getDate()—. Returns the value of the specified column in the current row of thisResultSetobject as ajava.sql.Dateobject.double getDouble()—. Returns the value of the specified column in the current row of thisResultSetobject as adouble.float getFloat()—. Returns the value of the specified column in the current row of thisResultSetobject as afloat.int getInt()—. Returns the value of the specified column in the current row of thisResultSetobject as anint.long getLong()—. Returns the value of the specified column in the current row of thisResultSetobject as along.Object getObject()—. Returns the value of the specified column in the current row of thisResultSetobject as anObject.short getShort()—. Returns the value of the specified column in the current row of thisResultSetobject as ashort.String getString()—. Returns the value of the specified column in the current row of thisResultSetobject as aString.Time getTime()—. Returns the value of the specified column in the current row of thisResultSetobject as ajava.sql.Timeobject.Timestamp getTimestamp()—. Returns the value of the specified column in the current row of thisResultSetobject as ajava.sql.Timestampobject.
In the records of the Publishers table in the pubs database, all fields are strings, so you can use the getString method to read the data from each field. Here's what that looks like when the code displays the publisher names it's obtained from the database:
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset =
statement.executeQuery("select pub_name from Publishers") ;
%>
<TABLE BORDER="1">
<TR>
<TH>Name</TH>
</TR>
<% while(resultset.next()){ %>
<TR>
<TD>
<%= resultset.getString(1)%>
</TD>
</TR>
<% } %>
</TABLE>
You can see the full code in Listing 16.2, and the full results in Figure 16.6.
That first example is just the beginning of the story, of course. For example, the Publishers table contains more than just the pub_name field listing the name of the publishers in that table. Here are all the available fields:
pub_id—. The publisher's IDpub_name—. The publisher's namecity—. The publisher's citystate—. The publisher's statecountry—. The publisher's country
What if you want the records you read from the database to include all these fields? The previous example used the SQL "select pub_name from Publishers" to get a set of records that only contained the publisher's name field; to get a set of records containing all fields, you can use the SQL "select * from Publishers" like this:
Statement statement = connection.createStatement() ; ResultSet resultset = statement.executeQuery("select * from Publishers") ; %> . . .
You can also display all the data from each field in an HTML table, as you see in Listing 16.2.
Example 16.2. Displaying the Whole Publishers Table (ch16_02.jsp)
<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> <HEAD> <TITLE>The Publishers Database Table </TITLE> </HEAD> <BODY> <H1>The Publishers Database Table </H1> <% Connection connection = DriverManager.getConnection( "jdbc:odbc:data", "Steve", "password"); Statement statement = connection.createStatement() ; ResultSet resultset = statement.executeQuery("select * from Publishers") ; %> <TABLE BORDER="1"> <TR> <TH>ID</TH> <TH>Name</TH> <TH>City</TH> <TH>State</TH> <TH>Country</TH> </TR> <% while(resultset.next()){ %> <TR> <TD> <%= resultset.getString(1) %></td> <TD> <%= resultset.getString(2) %></TD> <TD> <%= resultset.getString(3) %></TD> <TD> <%= resultset.getString(4) %></TD> <TD> <%= resultset.getString(5) %></TD> </TR> <% } %> </TABLE> </BODY> </HTML>
You can see the results of this new code in Figure 16.6, where you see all the fields of each record in the Publishers table.
As you can see, you can use wildcards like * in SQL. So what else does SQL have to offer us? Knowing SQL is crucial to working with databases using JDBC; so now that you've gotten a preliminary example working, the next step in building your database programming arsenal is to get a working understanding of SQL.
As you've seen, you can use SQL in Statement objects to configure what you want a database to do. Although full SQL is out of the range of this book, it'll be useful to get an overview of basic SQL here.
This discussion supposes that you're working with a database that contains a table named Customers that holds customer IDs, addresses, and so on, and a table named Orders, that holds customer orders. (This database is based on the Northwind example database that comes with SQL Server, but you don't need to have that example database in order to follow along in this topic.) The foundation of SQL is the SELECT statement, and that's the best place to start.
In SQL, you use the SELECT statement to get fields from a table; here's an example where you can get all the records in the Customers table, using the wildcard character *:
SELECT * FROM Customers
This returns a result set that holds all the records in the Customers table.
You can also use the SELECT statement to select specific fields from a table, which selects the CustomerID, Address, and City fields of all the records in the Customers table:
SELECT CustomerID, Address, City FROM Customers
This returns a result set that holds as many records as there are in the Customers table, and each record will have only a CustomerID, Address, and City field.
In SQL, you can use the WHERE clause to specify criteria that you want records to meet. For example, to select all the records in the Customers table where the City field holds Boston, you can execute this statement:
SELECT * FROM Customers WHERE City = "Boston"
You don't have to use an equals sign here; you can test fields using these operators:
>(greater than)>=(greater than or equal to)<(less than)<=(less than or equal to)BETWEENINLIKE
You've seen logical operators like < and >, of course, but what about BETWEEN, LIKE, and IN? They're coming up next.
You can use the BETWEEN clause to specify a range of values you will accept. For example, here's how to select all the records from the Customers table where the ContactName field starts with H:
SELECT * FROM Customers WHERE ContactName BETWEEN "H*" AND "I*"
Note the use of wildcard characters: "H*" and "I*". Using these wildcards lets you specify that you want all the ContactName values that start with H.
You can use the IN clause to specify a set of values that fields can match. For example, here's how you can get records with values in the City field that match Boston or Cambridge:
SELECT * FROM Customers WHERE City IN ("Boston", "Cambridge")
The LIKE clause lets you use partial string matching, which you can specify with wildcards. Here's an example that selects all the records from the Customers table in which the City field matches the wildcard string "San*":
SELECT * FROM Customers WHERE City LIKE "San*"
This creates a result set with records whose City fields match names "San*", like San Francisco or Santa Cruz.
You can also use logical operations on the clauses in your SQL statements. Here's an example that uses two criteria: the City field cannot be either Boston or Cambridge, and there must be a fax number in the Fax field (this SQL uses the NULL keyword to test whether there's anything in a field):
SELECT * FROM Customers WHERE City NOT IN ("Boston", "Cambridge") AND Fax IS NOT NULL
You can use these logical operators to connect clauses: AND, OR, and NOT. Using AND means that both clauses must be true, using OR means either one can be true, and using NOT flips the value of a clause from true to false, or from false to true.
Using SQL, you can order the records in the result set. For example, here's how you can order the records in the Customers table by CustomerID:
SELECT * FROM Customers ORDER BY CustomerID
You can also sort in descending order with the DESC keyword:
SELECT * FROM Customers ORDER BY CustomerID DESC
The names of the fields in a result set are the same as the original names they had in the original table. You might want to change those names—for example, you might want to label a field Name instead of using its original name au_lname. You can rename a field with the AS clause like this, where the SQL changes ContactName to just Name for the purposes of the returned result set:
SELECT ContactName AS Name FROM Customers
Now in the result set, the ContactName field will be called Name.
A database can hold duplicate values in the fields of the records of a table. For example, several customers come from the same cities, so they'd have the same value in the City field. You might want to take a look at all the cities represented without duplicates, and you can use the DISTINCT clause for that:
SELECT DISTINCT City FROM Customers
In addition, SQL comes with some built-in functions, such as COUNT, SUM, MIN, MAX, and AVG, that let you work with the records in a result set. Here are what these functions do:
AVG—. Returns the average value of a set of recordsCOUNT—. Returns a count of recordsMAX—. Returns the maximum value of a set of recordsMIN—. Returns the minimum value of a set of recordsSUM—. Adds values over records
You can group records with the GROUP BY clause, like this example, which groups records by city:
SELECT * FROM Customers GROUP BY City
You can use the SQL HAVING clause with GROUP BY. This clause is like the WHERE clause, but is used only with GROUP BY. It lets you specify additional criteria that records must meet, like this SQL, which specifies only records with cities that begin with "San":
SELECT * FROM Customers GROUP BY City HAVING City LIKE "San*"
Not all SQL statements are designed to return result sets. For example, you can use the DELETE statement to delete records like this, which deletes every record in the Customers table that has City values that are not Boston or Cambridge:
DELETE * FROM Customers WHERE City NOT IN ("Boston", "Cambridge")
You can use the UPDATE statement to update a database. Here's an example that changes the City to Boston in all records where it's Cambridge now:
UPDATE Customers SET City = "Boston" WHERE City = "Cambridge"
And that's it—that gives you a good foundation in basic SQL. Here's an example putting your new SQL knowledge to use; in this case, the code finds all the publishers in the Publishers table that come from Boston, as you see in Listing 16.3.
Example 16.3. Writing to a Log File (ch16_03.jsp)
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>Selecting Publishers From a Database</TITLE>
</HEAD>
<BODY>
<H1>Selecting Publishers From a Database</H1>
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement() ;
ResultSet resultset =
statement.executeQuery("select * from Publishers where city = 'Boston'") ;
%>
<TABLE BORDER="1">
<TR>
<TH>ID</TH>
<TH>Name</TH>
<TH>City</TH>
<TH>State</TH>
<TH>Country</TH>
</TR>
<% while(resultset.next()){ %>
<TR>
<TD> <%= resultset.getString(1) %></td>
<TD> <%= resultset.getString(2) %></TD>
<TD> <%= resultset.getString(3) %></TD>
<TD> <%= resultset.getString(4) %></TD>
<TD> <%= resultset.getString(5) %></TD>
</TR>
<% } %>
</TABLE>
</BODY>
</HTML>
You can see the results in Figure 16.7, where you see the single publisher from Boston displayed.
Today you saw a great deal of database programming. Today's primary work was all about understanding databases and connecting them to Java using JDBC.
Databases typically consist of one or more tables, and that a table is made up of rows and columns. The rows in a table are called records, and the columns are called fields.
You can connect to databases with the Java Database Connectivity (JDBC) packages, and you began working with the java.sql.* packages today. You learned how to use JDBC to connect to a database, how to create SQL statement objects, and how to use them to extract data from a database at a basic level. Finally, you also took a look at working with basic SQL, and how to use much of what SQL has to offer.
Tomorrow, you'll see more on working with databases as we work to extend our expertise in this area.
This workshop tests whether you understand all the concepts you learned today. It's a good idea to master today's concepts by honing your knowledge here before starting tomorrow's material. You can find the answers to the quiz questions in Appendix A.