Creating a JSP Application That Accesses DB2 Everyplace

Document options Document options requiring JavaScript are not displayed Sample code Rate this page Help us improve this content

Level: Introductory

Naveen Balani (naveenbalani@rediffmail.com), Technical Analyst, IBM

10 Apr 2003

This article discusses DB2 Everyplace support for JSP and describes how to create and deploy JSP applications to access DB2 Everyplace data from mobile applications.

© 2003 International Business Machines Corporation. All rights reserved.

Introduction

IBM® DB2® Everyplace^TM is a small-footprint relational database solution designed for personal digital assistants (PDAs), handheld personal computers, mobile phones, and embedded devices. It's part of IBM's solution for mobile computing, and enables mobile professionals such as sales people to access the vital data that they need wherever they go.

In previous articles about DB2 Everyplace, I've described:

• Creating a Mobile Application for Palm Devices Using DB2 Everyplace,
• Building Wireless Java Applications Using DB2 Everyplace, and
• Synchronizing Wireless Application Data Using DB2 Everyplace.

In this article I'll take you a step further, and describe how to create and deploy JSP applications to access DB2 Everyplace data from mobile applications. We'll go through the following steps:

• Reviewing DB2 Everyplace JSP support and custom tags
• Creating a DB2 Everyplace database table using Java^TM
• Building a sample JSP application
• Deploying and testing the sample application on a Windows® workstation using the mini HTTP Web server provided by DB2 Everyplace
• Installing DB2 Everyplace JSP support on a Windows CE® device

This article assumes that DB2 Everyplace 8.1 and SDK is installed and already configured on a Windows 2000 environment. Please refer to the documentation for DB2 Everyplace installation instructions. When you're ready to try the sample application I describe, go to the download section of this article.

Back to top

DB2 Everyplace support for JSP development

The DB2 Everyplace solution consists of the following components:

• The database engine: A relational database system designed with a small footprint for mobile devices.
• The Sync Server: A bi-directional synchronization server that moves data between the DB2 Everyplace database on the mobile device and enterprise databases.
• Mobile Application Builder: A rapid application development tool for creating the DB2 Everyplace applications that run on mobile devices.

For more information on these components, see the article Creating a Mobile Application for Palm Devices Using DB2 Everyplace.

This relational database engine delivers persistent storage for relational data and provides the ability to modify and retrieve records using SQL. Data in a DB2 Everyplace database can be accessed using several different methods, including using the Command Line Processor (CLP) to issue SQL statements and using an application that uses ODBC or JDBC^TM as a call-level interface.

In addition, DB2 Everyplace provides JSP support for database access that can be deployed to a mobile device that provides a Java^TM Runtime Environment.

JSP support is available for the following operating systems:

• Win32 (Windows NT® and Windows 2000)
• Windows CE/Pocket PC

Back to top

Creating DB2 Everyplace database tables using Java

In order to demonstrate the support of DB2 Everyplace for JSP application development, I have included a sample application with this article.

First, we'll create an ITEM table that will be accessed later by our JSP application. The ITEM table consists of item ids, item descriptions, prices and availability for each item.

For creating the ITEM Table, unzip the sample-db2jsp.zip file to c:\directory, where directory is whatever directory you choose. A folder called sample will be created. The sample directory contains the BuildDB.java and a batch file called SetupDB.bat.

For creating the database, modify SetupDB.bat so that the DB2EVERYPLACE_LOCATION variable points to the location where you installed DB2 Everyplace. When you run SetupDB.bat, it compiles and runs the BuildDB.java file.

The BuildDB class creates the ITEM table in the directory C:/sample/Data/. If you get an error while running the file, make sure you have set the correct path for DB2EVERYPLACE_LOCATION.

After running SetupDB.bat, you will receive the following message:

 
            Table: ITEM created        
            Results from table ITEM :         
                    
ITEM_ID: A1001   
ITEM_NAME: Think Pad 
ITEM_DESC : Configuration P4,256 SDRAM. 
ITEM_PRICE : $25000. 
ITEM_AVAILABLE:  Y 

Back to top

Overview of custom tags and JSP support for DB2 Everyplace

The support in DB2 Everyplace for JSP consists of two components:

• The mini HTTP Web server
• The JSP processor

The mini HTTP Web server receives requests from a Web browser and sends responses back to the Web browser using HTTP 1.1 protocol. The JSP processor parses a JSP file, generates corresponding Java source code, and compiles the source code. The Java source code may include Java Beans that generate dynamic content when the JSP page is requested. When a JSP page is requested, the mini HTTP Web server executes the corresponding Java code, and sends the output back to the Web browser as the response to the request.

The JSP application development is done on a Windows workstation and then ported to the mobile device.

JSP tags provided in DB2 Everyplace JSP support

The JSP tags in DB2 Everyplace are a subset of the JSP 1.1 specification. The tags that can be used for JSP Development are as follows:

Page directive:
The page directive defines page-dependent attributes. The syntax is as follows:

 
   <%@  
     page page_directive_attr_list %> 
     page_directive_attr_list ::=  
     {language="scriptingLanguage"} 
     {extends="className" } 
     {import="importList" } 
     {contentType="ctinfo" } 

The four valid attributes for this directive are:

• language - Must be "java".
• extends - Must appear at the beginning of the JSP file, if specified. By default, the com.ibm.db2e.jsp.server, java.io, java.sql, and java.util packages are imported.
• import - Must appear at the beginning of the JSP file, if specified.
• contentType - Can be any value (such as text/HTML, text/XML).

Include directive
The include directive is used to include data from JSPs/HTML.The syntax is as follows:

 
	<%@ include file="relativeURLspec" %> 

Scripting elements
Scripting elements are used to declare Java variables and methods used in a JSP page. The syntax is as follows:

 
	<%! declaration(s) %> 

The following scripting elements are supported:

• Scriptlets

  Scriptlets can contain any valid (Java) code fragments. The code fragments will be put in the service function of the Java class for the JSP page. The syntax is as follows:

  <% scriptlet %>

• Expressions

  Expressions are string representations of data types. The JSP Processor evaluates the expressions at run-time and converts the expressions into strings. The syntax is as follows:

  <%= expression %>

Implicit objects
When we create JSP pages, we have access to certain implicit objects. They can be used within scriptlets and expressions, without first being declared. Each implicit object has a class defined in a core Java technology or com.ibm.db2e.jsp.server package. The implicit objects are declared as shown in Table 1.

Table 1. Implicit objects

Custom tags for database access
The following tags are custom tags that you can use in your JSP application to access a DB2 Everyplace database:

• <tsx:dbconnect>

  This tag establishes a connection to a specified DB2 Everyplace database using the DB2 Everyplace JDBC driver. The syntax is as follows:

  <tsx:dbconnect id="connection_id" driver="com.ibm.db2e.jdbc.DB2eDriver" url="jdbc:db2e:database"> </tsx:dbconnect>

  
  

  where

  • id - Specifies the identifier of this connection. Do not reuse this name in the JSP page. This attribute is required.
  • driver - Specifies the DB2 Everyplace JDBC driver. This attribute is required.
  • url - Specifies the DB2 Everyplace database. The term database in the jdbc:db2e:database variable refers to the path of the DB2 Everyplace database. This attribute is required.
• <tsx:dbquery>

  This tag submits a query to the database using the connection specified through the <tsx:dbconnect> tag and produces a java.sql.ResultSet object in which the cursor points to the first row of the result set. You can reference this result set using the identifier of this query and the DB2 Everyplace JDBC interface for java.sql.ResultSet. The syntax is:

  <tsx:dbquery id="query_id" connection="connection_id" limit="value"> select_SQL_statement </tsx:dbquery>

  
  

  where

  • id - Specifies the identifier of this query. Do not reuse this query identifier in the JSP page. This attribute is required.
  • connection - Specifies the identifier of a tag in this JSP file. This attribute is required.
  • limit - Specifies the maximum number of rows that the query can return. This attribute is optional.
  • select_SQL_statement - Specifies the SQL query that you want to submit to the database. This SQL query statement can contain dynamic data.
• <tsx:dbmodify>

  This tag submits a command to modify data in the database using the connection specified through the <tsx:dbconnect> tag. The syntax is as follows:

  <tsx:dbmodify connection="connection_id"> modify_command </tsx:dbmodify>

  
  

  where

  • connection - Specifies the identifier of a tag in this JSP file. This attribute is required.
  • modify_command - Specifies the SQL command that you want to submit to the database to modify data. This modify command can contain dynamic data.
• <tsx:repeat>

  This tag is used to loop through each row in the query results. The start and stop attributes control the looping process. If you do not specify the start and stop attributes, the loop terminates when the cursor of the result set, which is referenced by the <tsx:getProperty> tag, reaches the end of the result set. This tag can be nested. The syntax is as follows:

  <tsx:repeat index="name" start="starting_index" stop="ending_index"> repeat_block </tsx:repeat>

  
  

  where

  • index - Specifies the identifier for the index of this tag. This attribute is optional.
  • start - Specifies the number of rows to skip before processing the repeat block. The default is 0. This attribute is optional.
  • stop - Specifies the ending index value for this repeat block. The default is 2,147,483,647. This attribute is optional.
  • repeat_block - Specifies the block of HTML tagging that contains the <tsx:getProperty> tag syntax and the HTML tags used to format the content. If you place a <tsx:getProperty> tag in the repeat block, the cursor advances to the next row each time the repeat block is processed.
• <tsx:getProperty>

  This tag gets the value of a ResultSet bean to display in a JSP page (the HTML result page). If this is placed inside a <tsx:repeat> repeat block, the cursor of the ResultSet bean is advanced to the next row each time the repeat block is processed. The syntax is as follows:

  <tsx:getProperty name="bean_name" property="property_name" />

  
  

  where

  • name (required) - Specifies the name of the ResultSet bean that you declared previously in a <tsx:dbquery> tag in this JSP file.
  • property (required) - Specifies the column of the ResultSet bean to access.

With this information we can move on to build our JSP application which accesses the DB2 Everyplace table we created earlier.

Back to top

Building the JSP application

The folder c:\sample\jsp that was extracted earlier contains the ITEMSelectView.jsp file, our application. The ITEMSelectView.jsp application displays all the items present in the ITEM table. The ITEMSelectView.jsp makes use of the JSP tags discussed earlier.

Let's move on to analyze some important snippets of the code. We'll start with creating our database connection.

 
	<tsx:dbconnect 
	id="conn"   
	url="jdbc:db2e:c:/sample/data/"  
	driver="com.ibm.db2e.jdbc.DB2eDriver"> 
	</tsx:dbconnect> 

Here the URL we specify is the location of the database, in this case: c:/sample/data/.

We execute the SQL query by using the <tsx:dbquery> tag as shown below:

 
<tsx:dbquery connection="conn" id="Query1DBBean">    
      SELECT * FROM ITEM	 
</tsx:dbquery> 

We then retrieve the results from database query using <tsx:getProperty> as shown below:

 
<tsx:getProperty name="Query1DBBean" property="ITEM_ID" /> 

This will retrieve the value from ITEM_ID column. The technique will be the same for remaining columns that we wish to retrieve.

Next let's move on to deploy our JSP.

Back to top

Deploying and testing the JSP

The developed application can be tested on a Windows workstation using the mini HTTP Web server provided by DB2 Everyplace. Follow these steps to test your application on Windows:

1. Browse to DB2EVERYPLACE_LOCATION\Clients\JSP\WIN32 . Edit the file MiniHttpConfig.properties and modify the entry JspPath= c:\\sample\\jsp to point to your location of the JSP files.
2. Execute the runJspServer.bat file for executing the MiniHttpServer.
3. Open up the browser and type in http://localhost/ITEMSelectView.jsp. You will receive the following message indicating that JSP processor has successfully compiled our JSP file. Click on ITEMSelectView.jsp file to view the results.

   DB2 Everyplace JSP Compiler JSP page: c:\sample\jsp\ITEMSelectView.jsp Parsing ... Compiling ... Done. Try it: ITEMSelectView.jsp

   
   

The following screen (Figure 1) appears, displaying the data from the Item's table.

Next we move on to deploy the application on a Windows CE device.

Back to top

Deploying the application on the Windows CE device

After developing the JSP application on a workstation, only the class files for the application need to be copied to the mobile device along with the mini HTTP Web server for executing the JSPs.

The following steps may be used for deploying this application, or any JSP application, on Windows CE devices.

1. Download and install the J9 JVM runtime environment for your mobile device.
2. Copy the following files from your development workstation to the WINDOWS directory of your mobile device:

   DB2EVERYPLACE_LOCATION \Clients\WinCE\database\ver\processor_type\DB2e.dll DB2EVERYPLACE_LOCATION \Clients\WinCE\database\ver\processor_type\db2ejdbc.dll DB2EVERYPLACE_LOCATION\Clients\WinCE\database\jdbc\db2ejdbc.jar

   
   

   where processor_type is the processor type for your device and ver is the version type for your device.

3. Copy the following files from DB2EVERYPLACE_LOCATION\SDK\JSP\WINCE to the root location of your device.

   minijsp.jar MiniHttpServer.lnk

   
   

   Copy the file MiniHttpConfig.properties from DB2EVERYPLACE_LOCATION\Clients\JSP\WIN32 to root location of your device.

4. Open a File Explorer and click on the MiniHttpServer shortcut in the root directory to start the MiniHttpServer server.
5. Create the following directories:

   sample\data sample\jsp

   
   

   and copy the *.class from c:\sample\jsp to sample\jsp on your device, and all files from c:\sample\data to sample\data on your device.

6. Enter the following URL in a Web browser:

   http://localhost/ITEMSelectView.jsp

   
   

   You should see a table containing the ITEM_ Desc values.

Back to top

Summary

In this article we successfully created and deployed a JSP application that accesses data stored in a DB2 Everyplace database. We also carried out steps to deploy the application on a Windows CE device. This knowledge can be further used to develop complex JSP applications to your Windows CE/Pocket PC device.

Back to top

Download

Name	Size	Download method
sample-db2jsp.zip	12 KB	FTP|HTTP

	Information about download methods

Resources

• Creating a Mobile Application for Palm Devices Using DB2 Everyplace
  
  
• Building Wireless Java Applications Using DB2 Everyplace
  
  
• Synchronizing Wireless Application Data Using DB2 Everyplace
  
  
• How to Set Up and Execute a Mobile Project with DB2 Everyplace
  
  
• IBM DB2 Everyplace Wireless Data Synchronization with Nokia 92xx Communicators
  

About the author

		Naveen Balani spends most of his time designing and developing J2EE-based products. He has written various articles for IBM in the past on topics including Web Services, AXIS, JMS, DB2 XML Extender, WebSphere® Studio, MQSeries®, JavaTM Wireless Devices and DB2 Everyplace, and Wireless Data Synchronization. You can reach him at naveenbalani@rediffmail.com .

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top