Welcome to Day 17! Today you're going to get more of the inside story on working with databases. You'll learn how to look up data on the server for the user, how to navigate through a database, and more in-depth topics. Here's an overview of today's work:
Supporting user data lookup using databases
Navigating through a database
Getting information about a result set
Joining database tables
Creating and filling database tables
Today you're going to see some advanced topics, including joining database tables and extracting data from them, as well as creating your own database tables. Yesterday was more discussion-intensive to build your JDBC foundation—today's work is code-intensive, as you learn how to put all this JDBC expertise to work.
For example, suppose you want to let the user enter values that you will look up in a database. The pubs database you saw yesterday contains a Publishers table that lists publishers, and one of the fields in the Publishers table is pub_id. What if the user wanted to look up a publisher by ID? You can see an HTML page in Listing 17.1 that enables the user to specify what publisher to look up by ID value.
You can see this HTML page at work in Figure 17.1, where the user has entered the ID of a publisher and what she wants to look up.
When the user clicks the Submit button, the ID value is sent to a new JSP page, ch17_02.jsp, which will use that value in a SQL statement to look up the matching publisher. You can see this JSP page in Listing 17.2, but the meat is right here in this code:
String id = request.getParameter("id");
.
.
.
ResultSet resultset =
statement.executeQuery("select * from Publishers where pub_id = '" + id + "'") ;
As you can see, the idea is just to search the database for the right record using SQL.
Example 17.2. Writing to a Log File (ch17_02.jsp)
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>Fetching Data From a Database</TITLE>
</HEAD>
<BODY>
<H1>Fetching Data From a Database</H1>
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement();
String id = request.getParameter("id");
ResultSet resultset =
statement.executeQuery("select * from Publishers where pub_id = '" + id +
"'") ;
if(!resultset.next()) {
out.println("Sorry, could not find that publisher. " +
"Please <A HREF='ch16_04.html'>try again</A>.");
} else {
%>
<TABLE BORDER="1">
<TR>
<TH>ID</TH>
<TH>Name</TH>
<TH>City</TH>
<TH>State</TH>
<TH>Country</TH>
</TR>
<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>
<BR>
<A HREF='ch16_04.html'>Search again</A>.
<%
}
%>
</BODY>
</HTML>
You can see the results in Figure 17.2, where the user has found a particular publisher, whose data is displayed. As you can see, searching a database isn't hard when you use SQL and ResultSet objects.
Now it's time to take a look at the methods of ResultSet objects directly. These objects let you navigate through the records they contain, as well as perform many other operations, and knowing how to use them is basic to JDBC.
Objects of the ResultSet interface are at the very center of JDBC; you can see the fields of the java.sql.ResultSet interface in Table 17.1 and the methods of this interface in Table 17.2. Table 17.2 is long, but it's worth going through in some detail—there is a terrific amount of functionality in this interface, such as the deleteRow method, which deletes an entire row; the updateRow method, which updates the contents of a row; or the absolute method, which makes a given row the current record.
Tip
A Blob as referred to in Table 17.2 is a binary large object, which enables you to work with binary data. A Clob in the same table is a character large object, which enables you to store character data.
Table 17.1. Fields of the ResultSet Interface
Table 17.2. Methods of the ResultSet Interface
Note in particular the navigation methods you see in Table 17.2:
absolute—. Moves to a record you specify in absolute termsfirst—. Moves to the first recordlast—. Moves to the last recordnext—. Moves to the next recordprevious—. Moves to the previous recordrelative—. Moves to a record you specify in relative terms
These navigation methods let you move through a result set record by record. That's one of the fundamental skills of working with result sets, and you'll see more on how to navigate through a result set next.
Suppose you want to let the user move through a table using buttons—the Next button to go to the next record, and the Previous button to move to the previous record. To implement buttons such as these in a Web application, you'll need to keep track of the current record between accesses to your JSP page so you know what record the user is looking at when she clicks the Next or Previous buttons.
You can do that in a number of ways, such as to use a session or hidden controls. This example will use hidden controls, and includes the JavaScript to retrieve and increment the current record number, storing the incremented value in a hidden control named hidden when the user clicks the Next button:
<SCRIPT LANGUAGE="JavaScript">
<!--
function moveNext()
{
var counter = 0
counter = parseInt(document.form1.hidden.value) + 1
document.form1.hidden.value = counter
form1.submit()
}
// -->
</SCRIPT>
In your JSP code, you can get the value in the hidden control easily enough:
int current = 1;
if(request.getParameter("hidden") != null) {
current = Integer.parseInt(request.getParameter("hidden"));
}
.
.
.
int current = 1;
if(request.getParameter("hidden") != null) {
current = Integer.parseInt(request.getParameter("hidden"));
}
.
.
.
for(int loopIndex = 0; loopIndex <= current; loopIndex++){
resultset.next();
}
You can see the resulting code in Listing 17.3, which implements a Next button that enables the user to move from record to record forward-only in a table.
Example 17.3. Navigating Forward-Only in a Database Table (ch17_03.jsp)
<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ; %> <HTML> <HEAD> <TITLE>Navigating in a Database Table </TITLE> </HEAD> <BODY> <H1>Navigating in a Database Table </H1> <FORM NAME="form1" ACTION="ch17_03.jsp" METHOD="POST"> <% int current = 1; if(request.getParameter("hidden") != null) { current = Integer.parseInt(request.getParameter("hidden")); } Connection connection = DriverManager.getConnection( "jdbc:odbc:data", "Steve", "password"); Statement statement = connection.createStatement(); ResultSet resultset = statement.executeQuery("select * from Publishers"); for(int loopIndex = 0; loopIndex <= current; loopIndex++){ resultset.next(); } %> <TABLE BORDER="1"> <TR> <TH>ID</TH> <TH>Name</TH> <TH>City</TH> <TH>State</TH> <TH>Country</TH> </TR> <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> <BR> <INPUT TYPE="HIDDEN" NAME="hidden" VALUE="<%= current %>"> <INPUT TYPE="BUTTON" VALUE="Next Record" ONCLICK="moveNext()"> </FORM> <SCRIPT LANGUAGE="JavaScript"> <!-- function moveNext() { var counter = 0 counter = parseInt(document.form1.hidden.value) + 1 document.form1.hidden.value = counter form1.submit() } // --> </SCRIPT> </HTML>
You can see the results in Figure 17.3. Obviously, using a forward-only cursor is not satisfactory for the original task, which was to let the user move both forward and backward in the table. So what can you do? You can use a different type of cursor.
You can set the type of database cursor you want to use when you create the SQL statement object you will use to create the ResultSet object. Here are the types of cursors you can use (not all JDBC drivers will support all cursors):
TYPE_FORWARD_ONLY—. Creates aResultSetobject whose cursor may move forward-only.TYPE_SCROLL_INSENSITIVE—. Creates aResultSetobject that is moveable at will but not sensitive to changes made by others that might be working with the database simultaneously.TYPE_SCROLL_SENSITIVE—. Creates aResultSetobject that is moveable at will and sensitive to changes made by others who might be working with the database simultaneously.
To create a result set with a cursor that you can use to move around at will, you can use code such as this when creating the Statement object you'll use to create the result set itself:
Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
Now you can move wherever you want in the result set. For example, to find out how many records there are in a result set, you can move to the last record and use the getRow method to determine the total number of records:
resultset.last(); int rows = resultset.getRow();
You can also use the absolute method to move to any record. Use the following code to move to the record specified by the user, which is in the variable named current:
resultset.last();
int rows = resultset.getRow();
if(current <= rows){
resultset.absolute(current);
}
You can see an example that implements both a Next and Previous button using this new cursor and code in Listing 17.4.
Example 17.4. Navigating in a Database Table (ch17_04.jsp)
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>Navigating in a Database Table </TITLE>
</HEAD>
<BODY>
<H1>Navigating in a Database Table</H1>
<FORM NAME="form1" ACTION="ch17_04.jsp" METHOD="POST">
<%
int current = 1;
if(request.getParameter("hidden") != null) {
current = Integer.parseInt(request.getParameter("hidden"));
}
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet resultset =
statement.executeQuery("select * from Publishers");
if(current < 1){
current = 1;
}
resultset.last();
int rows = resultset.getRow();
if(current <= rows){
resultset.absolute(current);
}
%>
<TABLE BORDER="1">
<TR>
<TH>ID</TH>
<TH>Name</TH>
<TH>City</TH>
<TH>State</TH>
<TH>Country</TH>
</TR>
<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>
<BR>
<INPUT TYPE="HIDDEN" NAME="hidden" VALUE="<%= current %>">
<INPUT TYPE="BUTTON" VALUE="Next Record" ONCLICK="moveNext()">
<INPUT TYPE="BUTTON" VALUE="Previous Record" ONCLICK="movePrevious()">
</FORM>
<SCRIPT LANGUAGE="JavaScript">
<!--
function moveNext()
{
var counter = 0
counter = parseInt(document.form1.hidden.value) + 1
document.form1.hidden.value = counter
form1.submit()
}
function movePrevious()
{
var counter = 0
counter = parseInt(document.form1.hidden.value) - 1
document.form1.hidden.value = counter
form1.submit()
}
// -->
</SCRIPT>
</BODY>
</HTML>
You can see this new JSP page at work in Figure 17.4, where the user can move from record to record using the Next and Previous buttons.
A result set's metadata contains a great deal of information about the result set itself, and to get that metadata, you use the result set's getMetaData method. This method returns an object that implements the java.sql.ResultSetMetaData interface. You can see the fields of the ResultSetMetaData interface in Table 17.3, and its methods in Table 17.4.
Table 17.3. Fields of the java.sql.ResultSetMetaData Interface
Table 17.4. Methods of the java.sql.ResultSetMetaData Interface
For example, what if you wanted to determine the name of the fields in a table on the fly? You can do that with the metadata's getColumnName method, as you see in Listing 17.5, which extracts the data from the Publishers table and displays each column of data with the actual name of the column.
Example 17.5. Writing to a Log File (ch17_05.jsp)
<%@ page import="java.sql.*" %> <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %> <HTML> <HEAD> <TITLE>Using Table Metadata</TITLE> </HEAD> <BODY> <H1>Using Table Metadata</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><%= resultset.getMetaData().getColumnName(1)%></TH> <TH><%= resultset.getMetaData().getColumnName(2)%></TH> <TH><%= resultset.getMetaData().getColumnName(3)%></TH> <TH><%= resultset.getMetaData().getColumnName(4)%></TH> <TH><%= resultset.getMetaData().getColumnName(5)%></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 17.5, where the actual names of each of the columns are displayed at the top of each column.
You can also relate the data of two tables together in a result set. In SQL, you can do inner joins, where records must be in both tables, or outer joins, where records can be in either table. For example, the pubs example database contains a table named authors that stores information about authors, and a table named titleauthor that lists the books each author has written. However, the titleauthor table doesn't list any information about the authors themselves, just an ID value. How can you find, say, the books that each author has written, and display that information with the author's first and last names? It turns out that the authors table also gives each author's ID, so you can tie the two tables together using the ID field.
In this case, you can create a new result set containing each author's first name and last name from the authors table and the ID of the books the author has written from the titleauthor table using this SQL, which creates an inner join between those tables based on the ID field in each table:
String query = "SELECT authors.au_fname, authors.au_lname, titleauthor. title_id " +
"FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor. au_id";
You can see the code that implements this SQL in Listing 17.6.
Example 17.6. Joining Tables (ch17_06.jsp)
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
<HTML>
<HEAD>
<TITLE>Joining Tables</TITLE>
</HEAD>
<BODY>
<H1>Joining Tables</H1>
<%
Connection connection = DriverManager.getConnection(
"jdbc:odbc:data", "Steve", "password");
Statement statement = connection.createStatement();
String query = "SELECT authors.au_fname, authors.au_lname, titleauthor
.title_id " +
"FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id";
ResultSet resultset =
statement.executeQuery(query) ;
%>
<TABLE BORDER="1">
<TR>
<TH>First Name</TH>
<TH>Last Name</TH>
<TH>Book ID</TH>
</TR>
<% while(resultset.next()){ %>
<TR>
<TD> <%= resultset.getString(1) %></TD>
<TD> <%= resultset.getString(2) %></TD>
<TD> <%= resultset.getString(3) %></TD>
</TR>
<% } %>
</TABLE>
</BODY>
</HTML>
Figure 17.6 shows the results of this code. In that figure, you can see the data from the two tables were joined. In particular, notice the data for Marjorie Green in the figure—this author has two book IDs listed, because she's written two books, and both were put into the result set when the two tables were joined.
Although most JSP work with databases involves reading data from pre-existing database tables and displaying it, you can also create your own database tables on the fly. In this next example, you'll see how to create a table in a database from JSP.
For example, you can add a new table named Employees to the pubs database easily—all you have to do is use the SQL CREATE TABLE command. When you create a new table, you give its name and pass the fields in parentheses, as well as their type. This example will create an integer field named ID, and a character field 50 characters in length called Name:
Statement statement = connection.createStatement(); String command = "CREATE TABLE Employees (ID INTEGER, Name CHAR(50));"; statement.executeUpdate(command);
Note that here you must use the executeUpdate method, because you're changing the database. You can see this code at work in Listing 17.7.
Example 17.7. Creating a Database Table (ch17_07.jsp)
<%@ page import="java.sql.*" %> <HTML> <HEAD> <TITLE>Creating a Table</TITLE> </HEAD> <BODY> <H1>Creating a Table</H1> <% Connection connection = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance(); connection = DriverManager.getConnection("jdbc:odbc:data", "Steve",
You can see the result of this JSP page in Figure 17.7, where it has created the new Employees table.
A database table isn't much use without data, and you can add data to a table with the SQL INSERT command. For example, to give employee Tom Thumb the ID 1 and employee Peter Pan the ID 2, you can use this command:
String command = "INSERT INTO Employees (ID, Name) VALUES (1, 'Tom Thumb')"; statement.executeUpdate(command); command = "INSERT INTO Employees (ID, Name) VALUES (2, 'Peter Pan')"; statement.executeUpdate(command);
You can see this code at work in Listing 17.8, which inserts these two employees into the Employees table and displays the results by retrieving their records from that table.
Example 17.8. Writing to a Database Table (ch17_08.jsp)
<%@ page import="java.sql.*" %> <HTML> <HEAD> <TITLE>Filling a Table</TITLE> </HEAD> <BODY> <H1>Filling a Table</H1> <% Connection connection = null; Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection = DriverManager.getConnection("jdbc:odbc:data", "Steve", "password"); Statement statement = connection.createStatement(); String command = "INSERT INTO Employees (ID, Name) VALUES (1, 'Tom Thumb')"; statement.executeUpdate(command); command = "INSERT INTO Employees (ID, Name) VALUES (2, 'Peter Pan')"; statement.executeUpdate(command); ResultSet resultset = statement.executeQuery("select * from Employees"); while(resultset.next()){ %> <TABLE BORDER="1"> <TR> <TH>ID</TH> <TH>Name</TH> </TR> <TR> <TD> <%= resultset.getString(1) %> </TD> <TD> <%= resultset.getString(2) %> </TD> </TR> </TABLE> <% } %> </BODY> </HTML>
Figure 17.8 shows that the two records were successfully added to the new table and then retrieved.
What if you want to change the value in a field after a table has been created—and have that change made to the database as well? You can use a SQL UPDATE command such as this, which changes the name Tom Thumb to Thomas Thumb:
String command = "UPDATE Employees SET Name = 'Thomas Thumb' WHERE NAME = 'Tom Thumb'"; statement.executeUpdate(command);
If you know the ID of the record to change, you can perform the same operation:
String command = "UPDATE Employees SET Name = 'Thomas Thumb' WHERE ID = 1"; statement.executeUpdate(command);
You can also use an update method of the ResultSet object. Just as you can get Strings, doubles, and so forth with the getString and getDouble ResultSet methods, you can update the data in a field easily with ResultSet methods such as updateString and updateDouble, which automatically make the change to the database as well. For example, to change the name Tom Thumb to Thomas Thumb, you can use the following when Tom Thumb's record is the current record:
resultset.updateString(2, "Thomas Thumb");
Tip
Yesterday and today provided an introduction to the use of JDBC and SQL in JSP. Note, however, that a number of books have been written on JDBC, and many more books on SQL. There's plenty more material that can't be covered here, such as the idea of transactions, which provide a level of security in working with databases. When you perform operations in a transaction, no changes are made to the database until you call the Connection object's commit method, and if you don't want to commit those changes, you use the Connection object's rollback method. For more information, take a look at a book dedicated to JDBC.
Today you learned about a number of in-depth database topics. You learned how to implement data lookup using databases, enabling the user to send you data, and how to use that data to look up a record in a database and display it.
You also learned how to navigate through a database, which involves learning how to set the cursor type yourself when you create the Java Statement object so that you can move around at will in a result set.
In addition, you learned how to join database tables to work with two tables at once, relating them through a common field and creating a result set that includes data from both tables.
Finally, you took a look at the process of creating your own tables in a database and filling them with data. You learned how to create a table with the SQL CREATE command, and how to place data into the new table with the SQL INSERT command. Tomorrow, you'll see another way of handling data in JSP: by using XML.
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.
1: | Add First and Last buttons to the database navigation example you see in Listing 17.4 to let the user move to the first and last records. (You don't need to connect to the |
2: | Modify the example in Listing 17.4 further, adding a text field to let the user move to any record directly by number. |