C H A P T E R  6

Databases

In many cases, a web application is nothing more than a front end for a database (DB). In fact, what makes web pages dynamic is precisely the fact that there is a significant amount of data behind them.

A database consists of organized data—that is, the data itself and a schema that provides data structures. Nowadays, most databases are organized in tables. You can define the table characteristics independently of the actual data you’re going to store into it. This is another instance of the separation of formatting and content, which you’ve already encountered in Chapter 3, when we discussed web applications.

A database management system (DBMS), such as MySQL or PostgreSQL, is a software package that lets you create, retrieve, update, and delete (CRUD) both items of data and elements of the schema.

Therefore, when talking about a database, you need to distinguish between three aspects:

• The data it contains.
• The structure you impose on the data in order to CRUD it efficiently.
• The software that allows you to manipulate both the data itself and the database structure (the DBMS).

Working with a database means that you’re interacting with its DBMS. You can do that through a command line interface (CLI), through graphical user interfaces (GUIs) provided by the DBMS vendor and third parties, or programmatically through an API. In general, you use all three methods, each for a different purpose. The CLI is best suited for setting up the initial data structure and for testing, the API is for your web application to interact with the database to perform its tasks, and the GUI is what you use to check individual data items or fix one-off problems.

In this chapter’s examples, I’ll use MySQL as the DBMS of choice, because, first, it’s available for free, and second, it’s the most widely used of the freely available DBMSs. As such, it has been proven to work reliably in all sorts of environments. At the end of this chapter, I’ll briefly talk about possible alternatives to MySQL.

MySQL

In this section, I will explain how to install MySQL. You actually need three packages: the MySQL database server, a connector to access MySQL databases from Java (a JDBC), and the MySQL Workbench, an application that lets you easily inspect and modify databases via a comfortable GUI.

To install MySQL, do the following:

1. Go to http://dev.mysql.com/downloads/ and click on the big green button labeled "Download".
2. Ensure that the selected platform is "Microsoft Windows" and click on the "Download" button.
3. Before you can download the package, you will have to login as a user, or register to be one. Then, after selecting a mirror site, you will be able to download the MSI installer mysql-installer-5.5.21.0.msi (or a more recent version of it). This package contains all three components of MySQL. Be aware that Workbench requires ".NET 4.0" and the "Visual C++ 2010 Redistributable Package". The MySQL installer will guide you to download and install them if needed. Alternatively, you can go to http://dev.mysql.com/resources/wb52_prerequisites.html to make sure you have everything in place beforehand.
4. When you execute the MySQL installer, choose the "Full" installation.
5. By default, MySQL keeps all databases in "C:Program DataMySQLMySQL Server 5.5". I chose to change the datapath to "C:Program DataMySQLdata", because it was more convenient for access and for backup.
6. Choose "Standard Configuration", and tick both option boxes before clicking on "Next >". The first option is to run MySQL as a service from startup. The second one lets you use MySQL from the command line.
7. Keep the default configuration: developer machine, enabled TCP/IP, and create Windows service MySQL55.
8. The version of the installer I had forced me to choose a password to access the databases.

If you go to the Services control panel as explained for Tomcat, you should see the service MySQL55 running.

For the examples of this book, as all databases are accessed locally and don't contain valuable data, I decided I didn’t need a password.

To remove the password I had been forced to choose during installation, I opened a command window, attached to the directory "C:Program FilesMySQLMySQL Server 5.5in" and typed the command "mysqladmin -u root -p password". The program asked me to enter the password, which I did, and then asked me twice to enter the new password, to which I replied both times by pressing Enter.

These MySQL packages that contain all components do not necessarily include the latest versions of the connectors and of the Workbench. I didn’t particularly care about having the latest Workbench, because I intended to use it only to check simple database generated from JSP. But I was keen to have the latest Java connector. Therefore, I went to http://dev.mysql.com/downloads/connector/j/ and downloaded mysql-connector-java-5.1.18.zip.

To install a JDBC, you only need to copy its JAR file into %CATALINA_HOME%lib. The MySQL installer puts it into C:Program FilesMySQLMySQL Connector J.

For easy access, you should create a shortcut pointing to the Workbench application and place it in the quickstart bar or on the desktop. The application path will look like this:

MySQL Test

Listing 6-1 shows an SQL script to create a simple database, which we will use in the E-shop application you first encountered in Chapter 3. For your reference, I have written a summary of the SQL language in Appendix B.

Line 01 removes the database. It reports an error when you use it the first time, because there is no database to delete, but it also lets you re-run the script to re-create the database from scratch. It isn’t something you would normally do in a non-test environment.

Line 02 creates a blank database named shop.

Lines 03 to 08 create a table to store book categories.

Lines 09 to 16 create a table to store book records.

Line 17 creates an index to speed up the search of categories.

Line 18 creates an index to speed up the search of books when selected by their IDs.

Lines 19 to 22 create an index to speed up the search of books when selected by their categories.

To execute the SQL script, you can use either the Command Line Client or the Workbench. You will find the script in the software package for this chapter.

To use the Command Line Client, click on "Start" and select "Programs MySQL MySQL Server 5.5 MySQL 5.5 Command Line Client". This opens a command-line window where you will first have to type the password to access the server. If you have removed the password as I suggested, you only need to hit Enter. The Client will respond by displaying the "mysql> " prompt. Open shop_create.sql with a text editor, copy everything into the clipboard, and paste it onto the Command Line Client. Listing 6-2 shows what you will get.

If you repeat the operation, you’ll see that something like "Query OK, 2 rows affected (0.56 sec)" will replace the "ERROR 1008" message.

You can achieve the same result with the Workbench. After launching it, double-click on the link "Local MySQL55" that appears under the heading "Open Connection to Start Querying". This will open a window as shown in Figure 6-1.

Figure 6-1. MySQL Workbench

If you have already created the shop database from the command line, you will see the corresponding entry in the Object Browser. If you right-click it, you will be able to choose the default database, although it is not necessary for you to do so.

In any case, to load the SQL script, click on the folder icon in the menu bar of the central pane. You can then open shop_create.sql or paste its content into the central pane. To execute it, click on the lightning icon. Figure 6-2 shows what happens when you do so.

Figure 6-2. Creating the shop database with the Workbench

Now that the database is in place, insert book categories and book records by executing the SQL script shown in Listing 6-3.

Note that you only need the "USE shop;" command if you execute the script from the command line.

After populating the database, you can look at the book records by typing a SELECT command in the central pane of Workbench (or at the "mysql> " prompt of the Command Line Client). For example, "select * from books;" will list all the books you have inserted, as shown in Figure 6-3.

Figure 6-3. List of all books

MySQL/Tomcat Test

To be sure that everything works, you still need to check that you can access the database from Tomcat using JSP. That is, that Tomcat is able to use the JDBC connector. To do so, you can use the JSP page shown in Listing 6-4, which lists all books in the database.

Here is how jdbc.jsp obtains the list of books from the database and then displays them:

05:       Load JDBC to connect to the database server.
06–07:Connect to the database.
08:       Create an empty statement to query the database.
09:       Execute the query to list all books and store the result set into a local variable.
11:       Obtain information on the structure of the result set.
12:       Obtain the number of columns of the result set.
13–17:Display the column names.
18–23:List the books one per row.
26:       Close the connection to the database server.

Don’t worry if things are not completely clear. Later in this chapter, I will explain in detail how you access a database from Java. For the time being, I just want to give you an example of how you can test database connectivity with a simple JSP page.

To execute the page, place it into the usual test folder. Figure 6-4 shows how the generated page appears in a web browser.

Figure 6-4. jdbc.jsp output

In a more realistic situation, you would replace the category identifiers with the category names, but I want to keep this first example as simple as possible.

There is one thing, though, that deserves a comment: it is bad practice to hard code in a page the name of the database, of the user ID, and of the access password. Sooner or later, you might need to change one of those parameters, and the last thing you want to do is to go through all your pages to do it. The initialization parameters exist precisely to avoid such error-prone procedure.

First, you need to include the parameter definitions in the web.xml file that’s inside the WEB-INF folder of your application’s root directory. You need to insert the lines shown in Listing 6-5 within the body of the web-app element.

In the example, my-database-name would be jdbc:mysql://localhost:3306/shop, my-userID would be root, and my-password would be the empty string (i.e., nothing).

To access the parameters from within any JSP page, you then just need to type something like the following:

After that, you can replace lines 6 and 7 of the example with:

Database Basics

In some cases, a DB might contain a small amount of data, have a simple structure, and reside together with the application software on a home PC. In other cases, at the higher end of the scale, it might hold millions of records, have a data structure of great complexity, and run on a cluster of powerful servers (e.g., with MySQL Cluster).

In any case, regardless of size, environment, and complexity, the DBMS is organized around the client/server architecture. The system on which your DB resides is the server, and the system from which you need to access your DB is the client, even when they’re one and the same PC. Therefore, in order to be able to work with data and a data structure, you first have to establish a connection from the client to the database on the server. To be able to do so, you need the following three pieces of information:

• The URL of your server
• A user ID that allows you to access the DB
• The password that goes together with the User ID

Once you establish the connection, you can then begin to manipulate the DB structure and its content via SQL statements. Be aware that although you need to provide a User ID and password when you connect to the server, this doesn’t automatically mean that a user has access to all databases on the same server. You can (and, in most cases, should) allow access to specific databases to some users and not others. In fact, you can define one or more new users for each new database you create and group them according to the capabilities they are required to have (i.e., database administrators, developers, etc.). This ensures total confidentiality of data when several users share a database server. It’s good practice to define different users for different applications so that you don’t risk “cross-polluting” data.

In 1986, the American National Standards Institute (ANSI) adopted SQL as a standard, and ISO followed suit one year later. The current standard is ISO/IEC 9075, but, unfortunately, it’s not freely available. If you want to have it, you have to buy it from ANSI or ISO. The SQL standard has been widely adopted, and, as a result, most of what I’m going to say concerning SQL actually applies to all DBMSs. As you can imagine, there are still proprietary additions and variations that, in some cases, make SQL less portable than what it could and should be, but it won’t affect us.

The SQL standard specifies at least 27 basic statements with numerous variants. They are alter, based on, begin, close, commit, connect, create, declare, delete, describe, disconnect, drop, end, event, execute, fetch, grant, insert, open, prepare, revoke, rollback, select, set, show, update, and whenever (see Appendix B for the details). In total, at the last count, 231 words were reserved by SQL as keywords. Therefore, it should be clear that in this chapter, I couldn’t possibly give you more than a small introduction to SQL. Appendix B provides a more detailed SQL reference to help you along. Also, if you search Apress for “SQL”, you will get a list of more than a hundred books. “Beginning SQL Queries” (www.apress.com/9781590599433) might be a good starting point.

The basic structural elements of a DB are rows, columns, tables, and indices. In non SQL terms, rows are data records, columns identify the record fields, tables are named collections of records, and indices are ordered lists of records.

To design a database for a web application, you basically associate a table to each Java class that represents the data you need to store permanently. Each attribute of your class then becomes a column of your table. In a sense, to express it in OO terminology, each row corresponds to an instantiation of your class containing different data. For example, in the E-shop application, book categories are modeled to reflect the Java class shown in Listing 6-6.

Accordingly, to store categories in the shop database, you can use the following SQL statement to create a table named categories, extracted from shop_create.sql (see Listing 6.1):

Each SQL statement consists of a verb that defines the operation to be done (create table in this example), the identifier of the object operated on (shop.categories in this example), and one or more operation parameters, often enclosed in parentheses. When more than one object or parameter is needed, they’re usually comma-separated. In the example, the first two parameters define the DB columns category_id and category_name. Notice how the attributes specified in the SQL statement match those defined in the Java class. When creating this table, I also told MySQL to create an index of category_id by declaring the column to contain unique values and designating it as the primary key of the table. The purpose is to speed up DB operations, although in this case, given the small size of the table, it obviously won’t make any practical difference.

Use this code, which creates three new rows, to store new records in a DB, extracted from shop_populate.sql (see Listing 6.2):

Incidentally, be aware that SQL, contrary to Java, is not case-sensitive.

Use the powerful select SQL statement to read data. It lets you create complex queries that include sorting the data. Here’s a simple example:

To retrieve all columns of a table, you replace the comma-separated list of columns with an asterisk. The where clause can consist of several conditions composed by means of logical operators.

You use the update statement to modify row contents:

Using delete you can remove rows:

You can also operate on the data structure. To do so, you use the alter statement, as in the following example:

This lets you add a column to an existing table. If you replace add with modify or drop, the alter statement will let you redefine a column or remove it.

In general, the SQL statements are grouped depending on their purposes. Table 6-1 gives you a summary of their classification.

In this chapter, I’ll explain how to execute any SQL statement, but we’ll concentrate mainly on DML.

SQL Scripts

As I have already said, a CLI is useful to initialize a database. As a CLI, MySQL makes available the program “MySQL Command Line Client,” which starts in a DOS window and attempts at once to establish a connection as the default User ID to the default server. If you’ve set up MySQL as I suggested at the beginning of this chapter, the default user will be root and the default host will be localhost. After providing the correct password, you get a mysql> prompt and can start executing SQL statements.

You can play around with the commands, but the best way to use the CLI is with SQL scripts. These are plain-text files containing the statements you want to execute. At the mysql prompt, you only need to type backslash-period-space (. ) followed by the script file name, and off you go. In fact, you must use scripts if you want to ensure that your steps are repeatable and correctable. Listing 6-7 shows the third (and last) SQL script needed to configure the database of the E-shop application.

Notice that the primary (i.e., unique) keys of both order_details and orders are automatically generated by MySQL as ever increasing numbers, while the primary keys of books and categories are hard-coded in shop_create.sql (see Listing 6.1). As the category and book IDs are not visible to the user, I could have let MySQL generate them as well. I didn’t do it because the book and category records are created by hand anyway, and to add an ID didn’t seem a big deal. Perhaps it is due to my tendency to minimize the use of automatic mechanisms when they are not necessary, in order to retain more control. The downside of this is of course that manual entries are in general more error prone and require more maintenance effort.

To write comments in an SQL script, you enclose them between /* and */, like Java’s block comments.

Java API

You operate on databases by executing SQL statements. To do so from within Java/JSP, you need an API consisting of several interfaces, classes, and method definitions. The API is included in the class libraries java.sql and javax.sql of JDK version 7. Additionally, you also need a driver that implements that API for the specific DBMS (i.e., MySQL) in the native code of your system (i.e., an Intel/Windows PC). To work with MySQL, you use as driver the MySQL Connector/J version 5, which is a type 4 JDBC driver (see sidebar).

JDBC DRIVERS

Connecting to the Database

The first step to access a database from Java is to load the driver, without which nothing will work. To do so, you execute the method Class.forName("com.mysql.jdbc.Driver"). In the E-shop application, you do this in the init method of the servlet (see Listing 3-9).

To be able to switch from MySQL to other DBMSs without much effort, store the driver name in an init parameter defined in WEB-INFweb.xml as follows:

This way, you can load it as follows when initializing the servlet:

Once you load the driver, you also need to connect to the database before you can access its content. In the E-shop application, you do this by executing a data manager (of type DataManager, defined in WEB-INFclasseseshopmodelDataManager.java) method, as shown in the following line of code:

The data manager’s getConnection method, in turn, obtains the connection from the JDBC driver, as shown in the fragment in Listing 6-8.

To be able to change the database, the user ID, or the password without having to rebuild the application, you define them in servlet initialization parameters as you did for the name of the JDBC driver and as I showed earlier in this chapter:

Port 3306 is the default for MySQL and can be configured differently. Obviously, in real life, you would use a different user and, most importantly, define a password.

Once you finish working with a database, you should always close the connection by executing connection.close(). E-shop does it via another data manager’s method, as shown in Listing 6-9.

Before you can start hacking at your database, you still need to create an object of type java.sql.Statement, as it is through the methods of that object that you execute SQL statements. Use this code to create a statement:

Once you’re done with one statement, you should release it immediately with stmt.close(), because it takes a non-negligible amount of space, and you want to be sure that it doesn’t hang around while your page does other things.

Accessing Data

The Statement class has 40 methods, plus some more inherited ones. Nevertheless, two methods are likely to satisfy most of your needs: executeQuery and executeUpdate.

The executeQuery Method

You use this method to execute a select SQL statement, like this:

In the example, the method returns in the variable rs of type java.sql.ResultSet all the books in category 1, sorted by author name and title. The rows in the result set only contain the columns specified in the select statement, which in this example are book_id, title, and author.

At any given time, you can only access the row of the result set pointed to by the so-called cursor, and by default you can only move the cursor forward. The usual way of accessing the rows of the result set is to start from the first one and “go down” in sequence. For example, with the shop database, the following code:

would produce the following output:

The next method moves the cursor down one row. After the cursor goes past the last row, next() returns false, and the while loop terminates. Initially, the cursor is positioned before the first row. Therefore, you have to execute next() once in order to access the very first row.

Besides next(), there are other methods that let you reposition your cursor. Five of them return a boolean such as next(), which returns true if the cursor points to a row. They are absolute(row-position), first(), last(), previous(), and relative(number-of-rows). The beforeFirst() and afterLast()methods also move the cursor but are of type void, because they always succeed. The isBeforeFirst(), isFirst(), isLast(), and isAfterLast() methods check whether the cursor is in the corresponding positions, while getRow() returns the position of the row currently pointed to by the cursor.

Keep in mind that in order to be able to move the cursor around, you have to specify a couple of attributes when you create the statement—that is, before you actually execute the query. This is how you do it:

ResultSet.TYPE_SCROLL_INSENSITIVE is what allows you to move the cursor forth and back within the result set. This parameter can only have one of the following two other values: ResultSet.TYPE_FORWARD_ONLY (the default) and ResultSet.TYPE_SCROLL_SENSITIVE. The difference between SENSITIVE and INSENSITIVE is that with INSENSITIVE, you’re not affected by changes made to the result set while you’re working with it (more about this in a moment). This is probably what you want.

ResultSet.CONCUR_READ_ONLY states that you don’t want to modify the result set. This is the default, and it makes sense in most cases. The alternative is to specify ResultSet.CONCUR_UPDATABLE, which allows you to insert, delete, and modify result rows. Now you can see why you might like to use ResultSet.TYPE_SCROLL_SENSITIVE as the first parameter: it lets you see the modifications made to the result set after you started working with it, rather than showing how it was before those changes. On the other hand, in a complex application with several threads operating on the same result set, you’ll probably prefer to ignore the changes made in other threads. In such a situation, it would have to be 100 percent clear which thread would be allowed to modify which rows; otherwise, you’d end up with a mess.

ResultSet provides several methods for retrieving a column value in different formats, given a column position or its label. For example, the following two methods will return the same value:

The column position refers to the columns specified in the select statement. Notice that the column numbering begins with 1, not with 0 as is customary in Java. The types available are Array, BigDecimal, Blob, boolean, byte, byte[], Clob, Date, double, float, InputStream, int, long, NClob, Object, Reader, Ref, RowId, short, SQLXML, String, Time, Timestamp, and URL (see Appendix B for more details). For most of these types exists a corresponding update method, which lets you modify a column. For example, the following code writes “Joe Bloke” in the author column of the current row of the result set:

Note that there are no update methods for the types InputStream, Reader, and URL. You can also set a column to null with the methods updateNull(column-index) and updateNull(column-label).

ResultSet provides more than two dozen additional methods that let you do things such as transfer changes from an updated result set to the actual database or refresh a row that somebody else might have modified in the actual database after you performed the query. One method that you might find useful returns the column position in your result set given its name:

The result set is automatically disposed of when the corresponding statement is closed. Therefore, you don’t really need to execute rs.close(), as long as you immediately close the statement when you no longer need it.

The executeUpdate Method

You can use this method to execute the SQL statements insert, update, and delete. For example, if you want to add a new book category to the E-shop example, you do something like this:

You don’t need to define all the columns, because the undefined fields are set automatically to their corresponding default values. That said, as I haven’t specified any default in the definition of the categories table, the following statement would result in the field category_name being set to null:

To avoid this occurrence, I could have defined the category_name column with a default:

Transactions

In E-shop, I have defined two separate tables for data associated with a book order: one for the customer data, and one for the individual books ordered (see Listing 6-7). It would be bad if you completely lost an order, but perhaps it would be even worse if you lost some items and only processed a partial order. It would also be a problem if you saved the order details in the database but failed to save the customer data. That would leave some “orphaned” book items with no information concerning the buyer. You don’t need to worry about this if you save the customer data first: then, by the time you start saving the order details, the customer record is already on disk. But how do you ensure that the database only contains complete orders?

Normally, when you execute an SQL insert, the data is immediately stored into the database. To ensure the completion of orders, you could keep track of the updates you’ve already successfully executed and reverse them if you cannot complete the whole order. However, this would be very complicated, and there would be no guarantee of success. Moreover, in a more complex application, there might be several operations proceeding simultaneously and causing the same database records to be accessed concurrently. The solution is a built-in, foolproof mechanism capable of ensuring that some complex transactions are done “in one shot” or not at all.

This mechanism is actually quite simple. It works like this:

1. Immediately after connecting to the DB with conn = DriverManager.getConnection(...), execute conn.setAutoCommit(false). This tells MySQL not to make permanent changes to the database until you confirm them.
2. Perform all the updates that form your complex transaction. Be sure that you place them inside a try block as part of a try/catch construct.
3. In the catch block, include the statement conn.rollback(). If one of the updates fails, an SQLException will be thrown, and when the catch block is executed, the rollback will cause MySQL to “forget” the uncommitted updates.
4. When all the updates have completed without being interrupted by any exception, execute conn.commit() to tell MySQL that it can finalize the updates.

DB Access in E-shop

As I mentioned in Chapter 3, all database operations are concentrated in the data model of an MVC architecture. JSP modules interact with the database by executing methods of the DataManager class, which accept and/or return data in the form of Java beans. By mediating DB access via the data manager and Java beans, you ensure that the view and the model can be developed independently.

Figure 6-5 shows the structure of the model.

Figure 6-5. The data model structure

The DataManager class sets up and closes connections to the database; however, concerning table access, it only acts as a clearinghouse. Specific classes perform the actual operations on individual tables. In this way, you ensure that changes to individual tables have the minimum impact on the application. This is actually an example of the Java EnterPrise Edition pattern called Data Access Object (DAO).

For example, the JSP page that displays the book details obtains the information concerning the requested book by executing the following method of the data manager:

It is the getBookByID method in BookPeer.java that performs the actual database access, as shown in Listing 6-10.

In line 3, you open the database connection by invoking a method of the data manager that also reports an error in case of failure. Then you start a try block where you do the actual work. In the corresponding catch block, you display an error message (line 22), and in the finally block (line 25), you close the DB connection. Remember that the finally block is executed whether the try succeeds or not. In this way, you ensure that the connection is closed in case of failure.

Inside the outermost try (lines 5–20), you create a statement and set up the query string before starting a second try block (lines 9–17). Similar to what you did concerning the connection, you use the finally block to close the statement (line 19).

This is a technique of general applicability: every time you do something that needs to be undone, take care of it immediately inside a try block by placing the “undoing” statement in the corresponding finally. In this way, you’ll be sure not to leave any “ghosts” behind you. It’s true that Java’s garbage-collection mechanism should take care of removing unreferenced objects, but it’s good practice to clean up behind yourself as you go, especially when you’re dealing with databases and potentially large objects, such as statements and result sets. At the very least, your application will work more efficiently. And it feels good to write “clean” code.

Line 10 is where you actually execute the query. You know that you’re not going to get more than one row in the result set, because the book_id is a unique key of the book table.

You might be thinking, “Why should I go through the data manager at all? Couldn’t I simply execute the BookPeer method from JSP?” Well, you could, but it wouldn’t be clean, and dirtiness sooner or later causes problems.

Furthermore, consider the more complex case in which you want to save an order. From the JSP point of view, you only want to call a method of the data manager that takes care of both the customer’s data and the shopping cart. Behind the scenes, though, two different tables need to be updated: one for the orders and one for the order details. Therefore, it makes a lot of sense to execute the overall transaction in the data manager (see Listing 6-11) while leaving the updates of individual tables to the peer classes.

The two lines in bold show you how the data manager asks the peer classes of the tables orders and order_details to do the update. Notice that you pass to them the same statement and order ID. Listing 6-12 shows insertOrder, one of the two methods that do the updates.

Listing 6-13 shows the other method, insertOrderDetails.

The methods throw the SQL exception rather than catch it locally, so that the data manager’s method catches it.

What about the XML Syntax?

In the previous chapter, you have learned about writing JSP documents instead of JPS pages. What impact does that have on what I just said about database access? None! This is a consequence of the MVC model: JSP is the view, while only the model has to do with databases.

However, the switch from traditional to XML syntax has an impact on how you execute the data manager methods. For example, you can write the JSP page OrderConfirmation.jsp to save an order in the database with a couple of scriptlets, as shown in Listing 6-14.

Or you can write the JSP document OrderConfirmation.jspx, as shown in Listing 6-15. I have included the whole E-shop project converted to XML format in the software package for this chapter. You will find it both in WAR format and already expanded in the folder named eshopx. To launch it, similarly to eshop, type http://localhost:8080/eshopx/shop.

Let’s concentrate on the highlighted code, where the actual work is done. The saving of the order information in the database, which you do in the JSP page (Listing 6-14) by executing a data manager’s method (lines 19–22), you do in the JSP document (Listing 6-15) by executing a custom action (line 31). The same custom action also invalidates the session (which was done in line 24 of the JSP page).

The if/else Java construct in lines 23, 31–32, and 34 of the JSP page becomes in the JSP document the JSTL core construct choose/when/otherwise in lines 32–33, 38–39, and 41–42.

Informing the user of the order acceptance is in HTML and remains basically the same (JSP lines 26–29 become JSPX lines 34–37). In fact, you could have replaced the scripting expression of the JSP page with the EL expression of the JSP document, making the code identical.

The introduction of the custom action insertOrder is necessary because scriptlets, being Java code, can make assignments and execute methods, while EL expressions cannot. Therefore, when you remove scriptlets because they’re not valid XML code, you have to move the computation to Java beans or custom actions.

In line 5 of OrderConfirmation.jspx, you declare eshop.tld, which contains the definition of the insertOrder action (see Listing 6-16).

As you can see, you pass two parameters to the custom action: the name of the variable where the order ID is to be returned, and an object containing the customer data (name, address, and credit-card information). You don’t absolutely need the second parameter, because the action code could have retrieved the customer data from the page context as follows:

On the other hand, you could have passed to the action a third parameter referencing the shopping cart, but I decided to let the action retrieve it from the session as follows:

It’s not always obvious what constitutes a better design. I felt that the shopping cart, being a session attribute, was obviously shared across JSP documents. Therefore, it was OK for the action to retrieve it directly from the session. The customer data, however, was a page attribute, normally not shared with other modules. Passing it “behind the scenes” to a Java class didn’t seem appropriate. Listing 6-17 shows you the action code in its entirety.

Notice how you obtain the servlet context (corresponding to the JSP implicit object application) from pageContext, and from it the data manager, so that you can then execute the same insertOrder method you invoked directly from within the JSP page.

The highlighted line shows that I suppressed a warning. I did it because Eclipse kept complaining about typecasting of a generic Object to the Hashtable type. Normally, a warning tells you that something might be wrong. The use of @suppressWarnings is usually bad practice and encourages a sloppy programming style. In this particular case, I was left with no choice, because Eclipse’s warning was unjustified.

Possible Alternatives to MySQL

There’s no general reason why you shouldn’t use MySQL in your applications. Nevertheless, you do have alternatives worth mentioning. I have only tested E-shop with MySQL, but I expect it to work exactly the same with other DBMSs.

If you switch DBMSs, there’s a good chance that you’ll just need to change the values of the init parameters jdbcDriver and dbUrl in web.xml from these values for MySQL:

to the values for the other DBMS.

For example, for PostgreSQL (http://www.postgresql.org/), the values would look like this:

For Firebird (http://www.firebirdsql.org/), the values could look like this:

Sun Microsystems reports that 221 different JDBC drivers exist (see http://developers.sun.com/product/jdbc/drivers). Therefore, you should be able to find the driver you need to connect to any database, although it might not be freely available.

If you don’t find the right JDBC driver or if it’s too expensive, you might be able to use the JDBC ODBC bridge included in the JVM to connect to any ODBC-compliant database. ODBC refers to an API supported by many database vendors on basically all operating systems. With the JDBC-ODBC bridge, you can also access Microsoft Excel files as if they were a database. For example, let’s suppose that you have the spreadsheet shown in Figure 6-6.

Figure 6-6. table.xls

To be able to access it via the JDBC-ODBC bridge, you first need to associate the file with an ODBC data source. To do so, go to Start Settings Control Panel Administrative Tools Data Sources (ODBC). There, click on the System DSN tab and then on the Add button, as shown in Figure 6-7.

Figure 6-7. ODBC Data Source control panel

This opens the Create New Data Source dialog. Scroll the list of possible data sources until you find Microsoft Excel Driver (*.xls). Select it and click on the Finish button. Despite the name of the button, you’re not done yet! A new dialog called ODBC Microsoft Excel Setup opens, which lets you select the Excel file and associate it with a data source name. See Figure 6-8.

Figure 6-8. ODBC Microsoft Excel setup

Click on the Select Workbook... button to select the file. Notice that I have placed table.xls in ROOT estsxls, together with the JSP page to access it, but it doesn’t need to be there. Also, I have chosen tab as a data source name, but you’re free to choose any name.

Listing 6-18 shows you a little JSP page to access table.xls as if it were a database.

Notice that in the select statement, I have used [zzz$] as a table name to access the worksheet named zzz. Figure 6-9 shows the output of xsl.jsp.

Figure 6-9. The output of xls.jsp

One word of warning: you will fail to establish the Java connection if you have the file already open in Excel, because Excel opens it exclusively. It will not fail if, when you set up the data source as shown in Figure 6-8, you tick the Read Only box.

Summary

In this chapter, I introduced you to working with databases and SQL. I explained how to access databases from JSP via the Java SQL API. In particular, I showed you how to establish a connection, insert data, and perform queries. To complete the summary of essential DB operations, I also described how to group elementary updates into transactions.

To bring it all together, I described the design of database operations in the E-shop application and showed you their implementation both with scriptlets and with the XML syntax. Finally, I mentioned possible alternatives to MySQL and described how you can access a spreadsheet from JSP as if it were a database.

Brace yourself, because in the next chapter I will finally talk about JSF!