Chapter 1. Integrating JSP and Data
 | CONTENTS |  |
IN THIS CHAPTER
- Using JSP with a Database
- Other Considerations
- Basic Design Concepts
- Summary
One of the most important concepts in building any application is the concept of data. Our applications are driven by data, and the tools that specialize in the handling of data form a central part of any application. XML is a specification that is geared to handle data. However, in using XML, it quickly becomes important to have access to several other tools. Usually a database is the first tool that will be used alongside XML. Oftentimes, the data for our XML files will come directly from a database. In addition, it will be important to import data from an XML file into a database. Using a database is important enough that this first chapter is dedicated to showing how to access and submit data to a database.
This chapter has two goals. The first is to show you how to access a database from JSP. If you are already familiar with using a database within JSP, it's advisable that you proceed to Chapter 2, "Introduction to XML/XSL." It's important to note that several chapters in this book will use a database in the examples. This makes setting up a test database very important. Instructions for setting up the MySQL test database used in all the examples can be found in Appendix A,"Setting Up."
The second goal is to ensure that newer JSP programmers have a chance to get more familiar with JSP before moving further into the book. This chapter is intended to be extremely accessible to newer JSP programmers regardless of their current skill level. This means that the chapter is very basic; more advanced JSP programmers will only want to skim this chapter quickly. Programmers who are just starting to learn about JSP are encouraged to first read Appendix B,"Introduction to JSP and How Things Work."
Using JSP with a Database
This section will not cover how to build or maintain a database. In addition, due to the vast number of databases, it would be impossible to cover special database-specific details. This means the examples here will be kept as generic as possible to help ease the process of porting code from database to database. The examples in this chapter will be using the MySQL database.
To make life simple, we are going to build several examples. The first example will be used to enter data into the database. The second example will be used to view the data in the database. The logic in these examples assumes that the BannerAds table has been created according to the instructions for setting up the MySQL database found in Appendix A.
Entering the Data
The actual process of entering data should be split across several JSP pages. One page will be a data input page. This page will only concern itself with gathering the data. The second page will be an action page that will run on the JSP server to process the results created through the input page. It's a good idea to split logic across several JSP pages. This is a perfect example of a case in which this is true. It's possible to build a single page to perform all the tasks of the input and action pages. Using one page would leave the code entangled and difficult to maintain with too much happening for a single page. This is especially true with more complicated database pages. Using two pages makes the logic more closely match the process and makes it easier for other programmers to understand what is going on within the pages.
The input page, shown in Listing 1.1, gathers data from the user and should be saved as webapps/xmlbook/chapter1/UpdateTableForm.jsp.
Listing 1.1 UpdateTableForm.jsp
<%@page contentType="text/html"%> <html> <head><title>Update Table Form</title></head> <body> <form action="UpdateTableAction.jsp" method="post" name="update"> <table> <tr> <td>Name</td> <td><input type="text" name="name" value="" size="40"/></td> </tr> <tr> <td>LinkText</td> <td><input type="text" name="linktext" value="" size="50"/></td> </tr> <tr> <td>Link</td> <td><input type="text" name="link" value="" size="50"/></td> </tr> <tr><td> Action </td> <td> <input type="radio" name="Action" value="update" /> Update <input type="radio" name="Action" value="delete" /> Delete <input type="radio" name="Action" value="new" checked /> New </td> </tr> <tr><td><input type="SUBMIT" value="Submit" /> </td> </tr> </table> </form> </body> </html>
This doesn't need to be a JSP page. It could be kept as a straight HTML page. However, we've kept it as a JSP page out of habit. Typically, an input page such as this would have other active features such as security validation, which would require it to use some JSP processing.
Now it's time to build an action page. This page, whose code is shown in Listing 1.2, will receive the data from the input page and then update the database. Save this file as webapps/xmlbook/chapter1/UpdateTableAction.jsp.
Listing 1.2 UpdateTableAction.jsp
<%@page import = "java.sql.*" %> <% /* Step 1) Get the data from the form */ String ls_name = request.getParameter("name"); String ls_link = request.getParameter("link"); String ls_linktext= request.getParameter("linktext"); String ls_action = request.getParameter("Action"); /* Step 2) Initialize Variables */ String ls_result = "Database was updated"; String ls_query = ""; String ls_dburl = "jdbc:mysql://localhost/xmlbook"; String ls_dbdriver = "org.gjt.mm.mysql.Driver"; /* Step 3) Create a query */ if (ls_action.equals("new")) { ls_query = " insert into BannerAds (name,link,linktext)"; ls_query += " values ("; ls_query += "'" + ls_name + "',"; ls_query += "'" + ls_link + "',"; ls_query += "'" + ls_linktext + "')"; } if (ls_action.equals("delete")) { ls_query = " delete from BannerAds where name = "; ls_query += "'" + ls_name + "'"; } if (ls_action.equals("update")) { ls_query = " update BannerAds"; ls_query += " set link= " + "'" + ls_link + "',"; ls_query += " set linktext= " + "'" + ls_linktext + "'"; ls_query += " where name = " + "'" + ls_name + "'"; } /* Step 4) Make a database connection */ Connection l_dbconn = null; try { Class.forName(ls_dbdriver); /* getConnection(URL,User,Pw) */ l_dbconn = DriverManager.getConnection(ls_dburl) ; /*create a SQL Statement */ Statement l_statement = l_dbconn.createStatement(); l_statement.execute(ls_query); } catch (ClassNotFoundException e) { ls_result = " Error creating database drive class!"; ls_result += " <br/>" + e.toString(); } catch (SQLException e) { ls_result = " Error processing the SQL!"; ls_result += " <br/>" + e.toString(); } finally { /* We must close the database connection now */ try { if (l_dbconn != null) { l_dbconn.close(); } } catch (SQLException e) { ls_result = "Error in closing connection."; ls_result += " <br/>" + e.toString(); } } %> <html> <head><title>Updating a Database</title></head> <body> The following query was submitted: <br/><br/> <%=ls_query%> <br/><br/> The Result was: <br/><br/> <%=ls_result%> <br/><br/> <a href="UpdateTableForm.jsp">Enter another value</a> </body> </html> Now it's time to run everything and then discuss what's happening behind the scenes. First, let's launch the UpdateTableForm.jsp page to start the data entry process, as Figure 1.1 shows.
Figure 1.1. Running UpdateTableForm.jsp.
Let's go ahead and enter some data. As sample data, the examples here are using the following values: site1, JSP Site, and www.jspinsider.com. Once this page is submitted it will trigger the UpdateTableAction.jsp action page to produce the results shown in Figure 1.2.
Figure 1.2. Results from UpdateTableAction.jsp.
Reviewing the Code for Entering Data
It's time to review and examine the UpdateTableAction.jsp page. The first step is to import the JDBC classes:
<%@page import = "java.sql.*" %>
Then the page needs to gather up the results from the UpdateTableForm.jsp page. Notice that the user request for this page comes as a result of clicking the Submit button on UpdateTableForm.jsp. This submits the form and puts the results in the HTTP header. In JSP, the request implicit object can be used to gather these values. The getParameter method is used to collect the value of the name/value pair. Each name/value pair contains the name of the form element and the value that was submitted with that element. This code grabs the value of the form element named name and puts it into the string variable ls_name.
String ls_name = request.getParameter("name"); The next major step is to initialize the JDBC setting information. The code will need the URL at which the database is open for client connections and the JDBC driver used to connect with the database. Note that xmlbook in the following line of code represents the database name. The values for our example are
String ls_dburl = "jdbc:mysql://localhost/xmlbook"; String ls_dbdriver = "org.gjt.mm.mysql.Driver";
The next few lines are where the code creates a SQL statement to be executed by the database. In this example, several different SQL commands exist since entering, modifying, and deleting data require slightly different SQL statements. For example, the following code generates an update statement for when it is required to edit an existing record:
if (ls_action.equals("update")) { ls_query = " update BannerAds"; ls_query += " set link= " + "'" + ls_link + "',"; ls_query += " set linktext= " + "'" + ls_linktext + "'"; ls_query += " where name = " + "'" + ls_name + "'"; } Now the fun stuff happens; we get to connect and use the database. The first step in using a database is creating a Connection object:
Connection l_dbconn = null;
The Connection object is our door into the database. Within the context of the Connection, the SQL statements will be executed and the results returned to us for our use.
All functions of connecting and using a database should be enclosed within a Java try-catch block. A lot can go wrong when working with a database. This means that the code needs to be ready to handle any exceptions that might happen. Notice that the Connection object is created before the try-catch block. We have a subtle reason behind our timing in declaring our Connection object. Database connections are both expensive in processing and precious in that they are usually limited in quantity. This means that database resources are often managed by a Web application. A database connection should always be closed after the code is finished with the connection. The best place to close a database connection is within the finally block of the try-catch block. In this example, the following code exists to perform the closing task:
finally { /* We must close the database connection now */ try { if (l_dbconn != null) { l_dbconn.close(); } } catch (SQLException e) { ls_result = "Error in closing connection."; ls_result += " <br/>" + e.toString(); } } The example only attempts to close the connection if it was actually created. If it wasn't opened, it would still be null and nothing would happen. The advantage of using the finally block to close the database connection is that the database will still close even when an error happens within the try-catch block.
Okay, back to the example. After the connection object is created, the next step is to load the JDBC driver:
Class.forName(ls_dbdriver);
The act of loading the JDBC driver has the effect of registering the driver with the DriverManager object. This means that once the JDBC driver is loaded, it's possible to create the connection to the database:
l_dbconn = DriverManager.getConnection(ls_dburl);
The getConnection function is overloaded and has several formats. In this example, the database doesn't need a user or a password to log in successfully. However, most of the time a database will require an account. When this is the case, the following code can be used to connect to the database:
l_dbconn = DriverManager.getConnection(databaseURL,User,Password);
The code will apply SQL statements to the database through this connection. To do so, a Statement object is required to run the actual SQL statement that we have:
Statement l_statement = l_dbconn.createStatement();
The Statement object is used to execute queries and get any returning data (also known as a ResultSet) from the query. It's important to note that a Statement can only have one open ResultSet at any one moment. The code needs to be written so that only one thread will be using a Statement object at a given moment. The code in this example is effectively thread safe.
The last step is to actually execute the SQL statement:
l_statement.execute(ls_query);
JDBC will take the SQL statement and execute it against the database. In the preceding code snippets, the data is only being put into the database. This means that we don't need to capture a ResultSet.
One last piece of JDBC code to review involves catching the exceptions:
catch (SQLException e) { ls_result = " Error processing the SQL!"; ls_result += " <br/>" + e.toString(); } As stated earlier, database interactions are prone to many types of errors. This means that capturing exceptions is especially important in JDBC interactions. As a rule, a programmer should always capture SQLExceptions. The choice should be made to either handle the error, or at the very least report the exception to the user. It is a poor programming practice to disregard errors, as they happen fairly regularly.
The rest of the page is straightforward HTML. The only new piece of code we haven't used yet is the JSP expression statement:
<%=ls_query%>
The JSP expression is a handy shortcut method for dumping the string value of an object to the output stream. This is no different from using an out.print statement. As to which method is better, it comes down to which method is most convenient at the time of use within the code.
Viewing the Data
The first example built a page to enter data into the database. Now it's time to build a page to view this data. This example is called UsingACursor.jsp. The name reflects the fact that the code will loop through a database using a cursor. Ironically, you won't see any mention of a cursor in the code, since the code will default to a forward only cursor. What all this means is that when we get the data, we need to loop through the ResultSet one row at a time. Since it is a forward only cursor, it's only possible to move forward; the cursor can't go back to a record once it has moved to the next record. There are several different types of cursors, but forward only cursors are the easiest and fastest type to use. Armed with this brief explanation, let's plow forward and write the example page, as shown in Listing 1.3. This file should be saved as webapps/xmlbook/chapter1/UsingACursor.jsp.
Listing 1.3 UsingACursor.jsp
<%@page import = "java.sql.*" %> <% /* Step 1) Initialize Variables */ String ls_result = "Nothing Happened"; String ls_query = "select name,link,linktext from BannerAds"; String ls_dburl = "jdbc:mysql://localhost/xmlbook"; String ls_dbdriver = "org.gjt.mm.mysql.Driver"; /* Step 2) Make a database connection */ Connection dbconn = null; try { Class.forName(ls_dbdriver); dbconn = DriverManager.getConnection(ls_dburl); /*create a SQL Statement */ Statement statement = dbconn.createStatement(); if (statement.execute(ls_query)) { /* Step 3) If we have a result lets loop through to get the data */ ResultSet results = statement.getResultSet(); ResultSetMetaData metadata = results.getMetaData(); /* validate result. Note switch to while loop if we plan on multiple results from query */ if(results != null ) { /* Use ResultSetMetaData object to determine the columns */ int li_columns = metadata.getColumnCount(); ls_result = "<tr>"; for ( int i = 1; i <= li_columns; i++) {ls_result += "<td>" + metadata.getColumnLabel(i) + "</td>";} ls_result += "</tr>"; /* Loop through the columns and append data to our table */ while(results.next()) { results.getRow(); ls_result += "<tr>"; for ( int i = 1; i <= li_columns; i++) { ls_result += "<td>" + results.getObject(i).toString() + "</td>"; } ls_result += "</tr>"; } } } } catch (ClassNotFoundException e) { ls_result = " <tr><td> Error creating database drive class!" ; ls_result += " <br/>" + e.toString() + "</td></tr>"; } catch (SQLException e) { ls_result = " <tr><td> Error processing the SQL!"; ls_result += " <br/>" + e.toString()+ "</td></tr>"; } finally { /* We must close the database connection now */ try { if (dbconn != null) { dbconn.close(); } } catch (SQLException e) { ls_result = " <tr><td> Error in closing connection."; ls_result += " <br/>" + e.toString() + "</td></tr>"; } } %> <html> <head><title>Viewing a Database Table</title></head> <body> The Result was: <table border="1"> <%=ls_result%> </table> </body> </html> This page is very similar to UpdateTableAction.jsp, but instead of launching a query to modify the database, it launches a query to retrieve data. The hard work is just looping through a ResultSet object (the cursor) . As the code loops through the data, the example will convert the data into a string to be displayed on the HTML page.
The page produces the results shown in Figure 1.3.
Figure 1.3. Results from UsingACursor.jsp.
We are only reviewing the sections of the page that differ from the UpdateTableAction.jsp page found in Listing 1.2.
This example runs a very different style of SQL:
String ls_query = "select name,link,linktext from BannerAds";
This select statement will return all of the data from the BannerAds table stored in the xmlbook database. It begins by using the keyword select and listing the field names to be selected. Then the table name after the from keyword tells the statement which table to use.
Later, the code executes the SQL against the database:
if (statement.execute(ls_query))
In this call, the code checks to see whether a ResultSet is created upon execution of the SQL. If the statement is true then there exists a ResultSet (the results from the SQL query).
ResultSet results = statement.getResultSet(); ResultSetMetaData metadata = results.getMetaData();
The next important step is to grab both the ResultSet, which has our data from the SQL query, and the ResultSetMetaData object, which contains the data that describes the ResultSet. First, the ResultSetMetaData will be examined to determine how many columns are in the returned dataset. Notice that we know the number of columns because we explicitly selected particular columns in the query statement earlier. We are including this process to demonstrate how to do it when the number of columns returned is unknown:
int li_columns = metadata.getColumnCount();
Once the number of columns has been determined, it becomes easy to loop through and build a list of the column names:
ls_result = "<tr>"; for ( int i = 1; i <= li_columns; i++) {ls_result += "<td>" + metadata.getColumnLabel(i) + "</td>";} ls_result += "</tr>"; Notice that the code wraps the results with HTML. It does this so that when the string result is sent out to the browser, it will be formatted as an HTML table.
Once the column headers have been created, the next step is to loop through the results and create rows for each record. This string will be put into the ls_result table string:
while(results.next()) { results.getRow(); ls_result += "<tr>"; for ( int i = 1; i <= li_columns; i++) { ls_result += "<td>" + results.getObject(i).toString() + "</td>"; } ls_result += "</tr>"; } The results.next() statement shows how to move through the data one row at a time. As long as this statement evaluates to a true result, there is a new row to process. Also notice that when actually pulling the data out of the result, we don't always know right away what type of data is being returned. Since we want a string to display, this code uses a shortcut by getting the data as type object:
results.getObject(i).toString()
Then we use the toString() function to easily convert it to a String in the same call.
Finally, in the HTML portion of the JSP page, the preformatted result string is placed into a table:
<table border="1"> <%=ls_result%> </table>
Overall, this example illustrates the basic steps of pushing data into and out of a database. After this, the next step is determining what to do with the data. In later chapters, we'll write some examples that convert data to XML from a database resultset.
Other Considerations
We need to quickly cover a few more issues in this chapter. These are things that programmers may not learn unless they are lucky enough to have well-trained coworkers, or have been burned on a project.
Connection Pooling
The code examples didn't use connection pooling. As a rule, connection pooling should be used to speed up database access and reduce the number of database connections used by any Web application. It turns out that one of the more expensive operations you can perform in terms of time and resources is simply connecting to the database. Each connection to a database takes time to perform and resources to manage. If a site had 10,000 users, it would be impractical to use 10,000 connections. Instead, it's better to pool the connections and reuse them on a need only basis. This prevents the overhead of creating and destroying connections repeatedly. Instead, they remain open and are available for use through connection management.
For example, if a project has 10,000 users, it might only need 500 connections at any one moment. Connection pooling software tends to be simple to use within a project. Most J2EE application servers will offer some form of connection pooling. For smaller JSP projects, you can find connection pooling from several resources. One good open source project to check out is PoolMan, which can be downloaded at http://www.codestudio.com. In addition, in JDBC 3.0, the drivers can be built to include connection pooling to make it transparent to the programmer.
Testing Components
In JSP, a programmer has many opportunities to use prebuilt components. As a rule, however, you should never assume that a component is perfect. If you are seeing strange behavior within your code, always perform a reality check to make sure that the component is working as expected. This holds true for any component, including JDBC drivers.
A Story of a Driver
This story will illustrate why early testing is important when using any component in a Java project.
On this project, we were using the Sybase SQL Server Database and Sybase JConnect, a free JDBC driver. The driver was installed and everything worked perfectly. That is, at first it did. Everything worked well for the initial simple SQL queries. As soon as the queries began to get larger, though, the application began to have problems.
The code was using a scrolling cursor and the query speed was pathetic. A SQL query that should run in a minute was taking the code 15 minutes to process. This was a problem. In investigating the problem, we closely reexamined the documentation for clues. Fortunately, the problem was listed in the documentation. The problem was that for large cursors, a scrolling cursor could have poor performance and cause crashes due to memory usage. Because this project had extra large cursors, this was a serious problem.
At this point, the options were to find a new driver that handled scrolling cursors better or rebuild the logic. Since budget was an issue, we chose to keep the JConnect driver and update the code to use a more limited forward cursor. After we rewrote the code, the program worked well enough for the project to move onward again. Then we began to notice a string of mysterious packet dropping errors.
After doing some research on the Sybase listserv, we discovered that other users were having the same problem and that it was a networking issue with the driver and NT. Luckily for us, the project was developed under NT, but deployed on Unix. Thankfully, the problem was specific to NT and never occurred on the Unix box.
The project taught us some important lessons. The most important was that one shouldn't assume that a JDBC driver or any other component will always work as expected. Nothing is bug free, and budgeting time and resources to deal with unexpected problems will in the end save money and permit the timely delivery of projects.
Testing for Scale
A common mistake made with database use in a project is to neglect to fully test load your application. Usually, smaller test databases are used to build a system. This makes life simple for initial building. However, SQL is easy to write and not always easy to optimize. As an example, on one project we had some SQL that worked fine on the test database and ran in under a minute. Once we moved the SQL to use production data, the same SQL took over 2 hours to run against the several million rows of data. The problem was fixed with some SQL optimization and some sweat, ending up with runtimes around 5 minutes. In this case, you should focus on the consequence, not the actual problem, because it's the lesson that is important. Always test at some point with data that closely matches the size load of your project. It's easy to build a system to work with small datasets. However, the large final dataset will always clobber your system if you haven't prepared for it.
This lesson also applies to using XML datasets. In fact, this lesson is probably more critical when dealing with XML. While databases are finely optimized to work with large datasets, the same cannot be said about all XML parsers, and especially our own hand written logic. When using small amounts of data, most everything works fine. Once we get into larger XML files or databases, the rules change and every ounce of performance can make or break a project.
Basic Design Concepts
As a rule of thumb, JSP should be used as a presentation layer. This means that the logic within a JSP page should be geared towards only generating the actual formatting of the output. All reusable or modular logic should be pushed into a JavaBean or a tag library. Generally, these modular objects are also referred to as business objects. The term business object is used because business logic should generally be placed into JavaBeans. The reason for this is that moving code into a lower level, such as a JavaBean, makes it reusable across many pages. The other benefit is that the final code in the JSP page will be modular and easier to update and maintain. We will demonstrate this by using a readily available database tag library. If you are interested in learning how to build and use your own tag library, check out Appendix C,"Tag Library."
Conversely, you should avoid putting presentation level logic (any logic used to create the display) into lower components such as JavaBeans or tag libraries. The reason pertains to flexibility; placing presentation logic in a lower level object forces the object into only producing a certain type of formatted output. As an example, if you built an object that produced hard-coded HTML, it would be difficult to use the same object to produce an XML document. If you look back to the UsingACursor.jsp example, the code from the result would only work for an HTML table. It would be awkward to recode everything in order to change the format or to add special formatting.
Using a Tag library
Generally, data presentation and data processing should be kept separate. In the UsingACursor.jsp page, we kept everything as a single JSP page to illustrate the coding of the JDBC logic. However, from a practical point of view this code should be built as a reusable JavaBean or tag library. The nice thing about JSP is that many people are already writing tag libraries that can be used with little extra effort. In fact, we can go ahead and use a prebuilt tag library to quickly recode the page.
The Jakarta project has a collection of open source tag libraries for anyone to freely use within a project. The one tag library we are interested in is the DBTags custom tag library. This library has a series of prebuilt functions to both read and write to a database. The actual description and code can be downloaded from http://jakarta.apache.org/taglibs/doc/dbtags-doc/intro.html.
After downloading the DBTags tag library, you will need to perform the following steps:
-
Copy the .tld (tag library descriptor) file into the xmlbook/WEB-INF directory.
-
Copy the tag library's JAR file into the xmlbook/WEB-INF/lib directory.
-
Modify the xmlbook/WEB-INF/web.xml file. Add the following lines between the <web-app></web-app> tags.
<taglib> <taglib-uri>http://jakarta.apache.org/taglibs/dbtags</taglib-uri> <taglib-location>/WEB-INF/dbtags.tld</taglib-location> </taglib>
-
Restart Tomcat. This will permit Tomcat to find and register the database tag library.
Now we are ready to build a JSP page to view the data. Save the file shown in Listing 1.4 as webapps/xmlbook/chapter1/ViewTable.jsp.
Listing 1.4 ViewTable.jsp
<%@ taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %> <html> <head><title>Using Tags For DataBase Access</title></head> <body> <%-- Step 1) Make a database connection --%> <sql:connection id="connect"> <sql:url>jdbc:mysql://localhost/xmlbook</sql:url> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> </sql:connection> <%-- Step 2) Create a query --%> <table border="1"> <sql:statement id="statement" conn="connect"> <sql:query> select name,link,linktext from BannerAds </sql:query> <%-- Step 2a) loop through the query result --%> <tr> <td>Name</td> <td>Link</td> <td>LinkText</td> </tr> <sql:resultSet id="data"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3"/></td> </tr> </sql:resultSet> </sql:statement> </table> <%-- Step 3) Close the Database Connection --%> <sql:closeConnection conn="connect"/> </body> </html>
Running this page will produce results similar to Figure 1.4.
Figure 1.4. Results from ViewTable.jsp.
Let's review what happened in this page.
The first line tells the JSP container where to find the tag library. Tomcat looks up the URI (uniform resource identifier) reference in the web.xml file and obtains a prefix to use when referencing the database tags:
<%@ taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>
The prefix means that in this example, any tag starting with <sql: will resolve to the DBTags custom tag library.
The code follows a path of logic almost identical to that of the database examples. The difference is that the custom tags are performing the JDBC logic. All a programmer needs to do is provide the custom tags with the connection and SQL information. Following the logic, the next step is to tell the tag library where to make the database connection:
<sql:connection id="connect"> <sql:url>jdbc:mysql://localhost/xmlbook</sql:url> <sql:driver>org.gjt.mm.mysql.Driver</sql:driver> </sql:connection>
Then the code tells the tag library which connection to use and which SQL statement to apply to the database:
<sql:statement id="statement" conn="connect"> <sql:query> select name,link,linktext from BannerAds </sql:query>
After we have the SQL statement loaded, it is possible to use a result tag to loop through and extract the data. In this example, the code puts the data into a table row, but it could just as easily put the data into an XML file or another format:
<sql:resultSet id="data"> <tr> <td><sql:getColumn position="1"/></td> <td><sql:getColumn position="2"/></td> <td><sql:getColumn position="3"/></td> </tr> </sql:resultSet>
Finally, notice that the code still has to close the database connection:
<sql:closeConnection conn="connect"/>
The custom tag library we are using is still young. At the time of this book's publication, it didn't have a method to extract the field names. Therefore, the example page had the column names hard-coded:
<tr> <td>Name</td> <td>Link</td> <td>LinkText</td> </tr>
A future release of this tag library will probably include a method to extract field names. However, we can add this functionality ourselves if we want to. This particular tag library is open source, which means that anyone can add his or her own tags to the library, including one to resolve field names. Also, if any of the included custom tags don't work the way your project requires, it's possible to recode the tags to better suit your needs. This is one of the benefits of open source tag libraries.
Summary
This chapter has reviewed some of the basics of JSP and how to connect to a database. Keep in mind that using a database is a subject that could fill an entire book on its own. We intentionally kept the code and information presented in this chapter simple and brief. The goal was to give just enough information to get you started down the road to using a database. You should walk away knowing how to connect, update, and retrieve data from a database. With this starting knowledge, you can now begin to learn XML. Throughout the book, we will create examples that use both XML and a database at the same time (in most real-world projects, using a mixture of XML and a database will be a given). This will be especially true in Chapter 11, "Using XML in Reporting Systems," where we will create an automatic system to populate the database with thousands of records to test a sample reporting system.
Now, the book gets into the fun stuff. The first step will be to review XML. The next chapter will introduce XML, XSL, and XPath. It covers most of the basics of using XML and includes quite a bit of information. so get a cup of coffee and enjoy the ride.
| CONTENTS | |
![JSP and XML[c] Integrating XML and Web Services in Your JSP Application](/icons/blank_book.jpg "JSP and XML[c] Integrating XML and Web Services in Your JSP Application"){kind=icon}
EAN: N/A
Pages: 26