Building JSP Applications @ POWERBUILDER JOURNAL

Building JSP Applications

Welcome to JSP Corner. For those of you who are just joining us, in this column I've been demonstrating how to write JSP applications; I've focused on writing the Sybase eMusic application and converting it from PowerDynamo to JavaServer Pages (JSP).

Although the focus is on building JSP applications using EAServer, the information covered in Parts 1-5 (PBDJ, Vol. 8, issues 9, 10, 12; Vol. 9, issues 3, 5) is portable across any servlet/JSP container. In Part 6 we will add database access to the eMusic application, so we need to prepare the application server. This requires J2EE-related features, so from this point on our application may not work in non-J2EE compliant containers. However, it should still run in any J2EE-compliant application server since the only aspect of this article that's specific to EAServer is the setting up of the database connection cache.

JDBC Connection Caches
A pool of database connections helps applications increase performance and minimize resource usage on the server while also providing a way for a database to provide concurrent access to a large number of users. Figure 1 illustrates what the eMusic application looks like using a pool of database connections to access a database. For a more detailed explanation of connection caches, check out some of the resources listed at the end of this article.

The J2EE specification requires compliant application servers to support the pooling of database connections (and other resources) that can be accessed via JNDI (Java Naming and Directory Interface); however, the servlet and JSP specifications don't have any such provisions so not all standalone JSP/servlet containers provide these features. For example, the servlet/JSP reference implementation Tomcat 3.x doesn't support database connection pooling or JNDI. However, the newer 4.x releases of Tomcat do. The Sybase application server, EAServer 3.6.1, which is J2EE 1.2 certified, and EAServer 4.1, which is J2EE 1.3 certified, both support resource pooling and JNDI so we'll be able to take advantage of them.

Setting Up the eMusic Database
The database, emusic.db, is a Sybase Adaptive Server Anywhere (ASA) DBMS. At this point I encourage you to download the database for this application from www.TamingJaguar.com. Once it's downloaded, a shortcut to starting it up can be created using the following command:

"%ASA_HOME%\win32\dbsrv7.exe" -c 8m
C:\emusic.db

The paths and exe may need to be modified depending on the version of ASA you're running. ASA_HOME represents the install directory of the ASA product. Once this is done you should be able to start up the database.

Setting Up Connection Caches in EAServer Once the database is up and running we'll need to create a connection cache in Jaguar/EAServer using the Jaguar Manager. To do this right-click on the connection cache folder and choose "New Connection Cache" from the popup menu. When the "Create Connection Cache" dialog window opens, type the name of the connection cache as shown in Figure 2. After this is done the Properties window will open as shown in Figure 3.

On the General tab we need to fill in the "Database Connection" information. This information is used by the application server to create the physical database connections to the ASA database. In the Server Name field, type the JDBC URL that tells the JDBC driver which host and port the ASA database is listening on. For the eMusic ASA database type the following:

jdbc:sybase:Tds:localhost:2638

Enter the user ID and password for the database under the User Name and Password fields. For the eMusic database we're using the ASA default values of "dba" and "sql", respectively.

For those new to Java, the JDBC technology is an API that lets you access a database from the Java language. JDBC allows you to:

Establish a database connection
Send SQL statements
Process results

The API is designed to work with different JDBC drivers, allowing access to any vendor's databases that have a JDBC driver.

On the Driver tab under the "DLL or Class name" field, add the name of the ASA JDBC driver and type the following:

com.sybase.jdbc2.jdbc.SybDriver

Be sure to click the radio button next to JDBC as well.

On the Cache tab make sure the "enable cache-by-name access" checkbox is enabled. This allows a connection cache to be accessed using the name specified in the dialog window shown in Figure 2. Forgetting to enable this option is a typical cause of failure when attempting to look up a cached database connection. Return to the General tab and click the refresh button to make sure the cache has all the information and then click the ping button to see if the cache works.

Once these steps are completed, we've finished configuring the connection cache in EAServer 4.x. If you're using EAServer 3.6.1, you need to click on the server's folder and expand the Jaguar tree-view item. Under the item is an Installed Connection Caches folder. Add the newly created connection cache here and then refresh or restart the Jaguar server.

Preparing the Application for Deployment
In Part 2, we discussed the WAR file used to deploy a Web application that's built with servlet and JSP technologies. One part of the WAR file is the web.xml file that's under the WEB-INF folder. This file is the deployment descriptor and contains information that the container uses to configure itself to run the Web application.

In the web.xml file, tell the J2EE container that the application needs to use a database connection that's being cached by the server. The following resource-ref element does this:

<resource-ref>
<description>DataSource for ASA
database</description >
<res-ref-name>jdbc/eMusicDB</res-ref-name>
<restype>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

The res-ref-name element is the name of the resource, in this case a JDBC connection cache. The resource name is the one that the Web application will use in a JNDI lookup. The name of the resource is relative to the JNDI root naming context. The res-type element tells the container which Java class is being cached. For JDBC connections it's always the javax.sql.DataSource class. The res-auth element tells the application server who is responsible for authenticating the database connection - the container or the application. In this case the value "Container" tells the application server that the database connections should be authenticated using the user ID and password that were provided when the connection cache was configured.

Finishing Up the Configuration in EAServer
When the Web application is deployed, the resource-ref specified in the web.xml file is created as one of the Web application properties. Using the Jaguar Manager we can access these properties by right-clicking on the Web application folder and choosing "Web Application Properties..." from the popup menu. You can see the resource-ref by choosing the Resource Refs tab (see Figure 4).

To make sure the Web application uses the correct database connections, link the name of the connection cache in the application server (eMusicDB) with the resource name used in the application (jdbc/eMusicDB). This is done by highlighting the resource factory on the top of the window shown in Figure 4 and selecting the name of the connection cache created in EAServer from the dropdown list provided in the Resource Link field. Most errors that are encountered when attempting to use the database connection occur because this step has been omitted.

Accessing the Connection Cache
Now that the application server has been configured for database use, let's write a simple servlet to make sure everything is working; I'll demonstrate how to use JNDI to look up a database connection. The complete code for this servlet is shown in Listing 1.

For those new to Java, the JNDI technology is an API that lets you access a variety of naming and directory services. JNDI allows you to:

Access a naming context
Look up resources
Use resources

The J2EE specification requires a compliant application server to provide a JNDI naming environment. This naming environment provided by the application server has a name context root of:

java:comp/env/

The name context is like a directory structure for an operating system. There's a root directory, and additional directories can be added under it, as well as under subdirectories. We can specify additional contexts under the root to group similar resources. For example, in the web.xml file we created a resource-ref for the database connection cache. The name for the resource-ref was listed as jdbc/eMusicDB. This means that this resource can be found under the following name space:

java:comp/env/jdbc/

To look up resources in the code use the JNDI API found in the javax.naming package. First we need to get the initial context for performing naming operations. This is done with the following code:

InitialContext ctx = new
InitialContext();

We don't need to specify which JNDI implementation to use since the default here is the one supplied by the J2EE application server. However, like JDBC, the JNDI API can be used to access a variety of naming and directory services like LDAP and file systems depending on the driver (or implementation).

Once we have the initial context we can use it to perform a lookup using the resource-ref name (also known as the JNDI name) of the resource. The lookup method returns a java.lang.Object, so we need to cast the object reference to the proper type. In this case we'll get back a DataSource instance. This code sample shows how it's done: DataSource ds=(DataSource)ctx.lookup

("java:comp/env/jdbc/eMusicDB");

The DataSource is part of the JDBC API and can be found in the javax.sql package. This class can be used to get a java.sql.Connection instance from the connection cache, which is required in order to do anything useful with the database. The following code sample shows how to do this:

Connection _conn =
ds.getConnection();

Updated Application
The eMusic application WAR file has been updated and placed on www.TamingJaguar.com. The eMusicConnCache servlet has been added to the application and can be accessed via http://localhost:8080/emusic/eMusicConnCache.

Wrapping Up
We covered a lot of material so that we could access a database from the eMusic application. Many of the technologies, including J2EE, JDBC, and JNDI, were not covered in much detail due to space constraints. I tried to present enough information to enable you set up the connection cache in EAServer, so in Part 7 we can start adding more functionality to the application. If you're interested in finding out more about these technologies, check out the Resources section as well as SYS-CON's popular Java Developer's Journal.

Resources

J2EE: http://java.sun.com/j2ee/

JDBC: http://java.sun.com/jdbc/

JNDI: http://java.sun.com/jndi/

Connection Caching:
- Jaguar/EAServer documentation
- Barlotta, M. (1999). Jaguar Development with PowerBuilder 7. Manning Publications.
- Hatton, T. (2000). "Jaguar Connection Caches." PowerBuilder Developer's Journal. SYS-CON Media, Inc. Vol. 7, issue 7.
- Barlotta, M. (2000). "Sanity Checking an EAServer Connection Cache." PowerBuilder Developer's Journal. SYS-CON Media, Inc. Vol. 7, issue 10.

Related Links

About Mike Barlotta
Background Information: Michael Barlotta is the Director of Technology at AEGIS.net Inc (www.AEGIS.net). Mike is a Sun Certified Java Programmer and is recognized as an expert on the Sybase application server EAServer (Jaguar CTS), mentoring clients and speaking at conferences on the topic. He is the author of several books including Taming Jaguar and Jaguar Development with PowerBuilder 7 (both by Manning Publications). Be sure to check out Mikes Web site www.TamingJaguar.com.