Database-assisted Web Publishing using Java Stored
Procedure
Table Of Contents
- Overview of the Sample Application
- Application Architecture Diagram
- User Notes with Screen Shots
- Prerequisites
- Installation and Configuration
- Description of Sample files
- References
1. Overview of the Sample Application
|
Back to top |
This sample illustrates efficient and accurate Web publishing using Java
stored procedures. The sample implements a Product Information system
that offers two features to the user -
- Searching for any product. The search results are cached in a web
object cache that uses Oracle Web Object Cache for caching. The products
list gets invalidated and populated in the cache whenever the products
table is modified.
- Addition of product details to the database. Product Name, Description
and Price are taken as input from the user and this information is added
to the database. This functionality is provided to demonstrate one of
the ways to incur cache invalidation but the same can be accomplished
by other mechanisms such as a direct SQL statement from SQLPLUS.
An event driven Web-Content publishing design is used to ensure that
the response to all requests reflect the current data state. The application
refers to a database table for processing requests. At any point of time
the information in the table could be modified. In between these data
modification events the content is static and is handled by the Web Object
Cache. Upon the database modification event, the database trigger associated
with the table is fired that causes the cache to be repopulated with new
data.
This approach has the added advantage that it handles every data modification
event regardless of its origin. For example, the table can be updated
by Web Clients and SQL console. So it is not just the Web-originated events
that are handled.
However, a careful analysis of the problem context has to be made before
using this publishing approach as the solution. Typical applications include
product catalog, price list, rate engines, etc where product information,
price, rate that are not subject to frequent changes but can be changed
by other applications. However, if the events occur more frequently than
the client's requests, this approach creates even more overhead on the
server than the traditional request-driven publishing. Also this approach
is not applicable with variable data selection criteria. Consider online
banking where clients may request account statements for unpredictable
time periods. Such requests can be serviced with request-driven publishing
only.
2. Application Architecture Diagram
|
Back to top |
The architecture of this sample application is depicted in Figure 1.1.
All client requests are handled by Java Server Pages. The Java Server
Page in turn looks up the database or the Web Object Cache to process
these requests. The product queries are cached using Web Object Cache
tags. The first query fetches the data from the products table in the
database. The next time onwards the query results are retrieved from the
cache and round-trips to the database are thus avoided.
Product information could be added to the database through Java Server
Pages or by directly accesssing the table. The products table in the database
has a trigger associated with it. Any modifications to the table will
fire the trigger which in turn will execute a Java Stored Procedure. This
stored procedure invokes a Java Server Page for invalidating the Web Object
Cache. This will force the state to be refreshed from the database by
the very next query.
Java stored procedures are a powerful mechanisms for bridging SQL, Java,
J2EE, XML and Web Services; for more details, refer to the white paper
- Unleash
the Power of Java stored procedures
Figure 1.1- Application Architecture Diagram
3. User Notes with Screen Shots
|
Back to top |
The products query screen as shown in Figure1.2 enables the user to provide
search criteria to lookup any product. The product name could be suffixed
with '%' for wildcard search. Entering a product name and clicking on
the Search button, all products that satisfy the search criterion are
queried from the database This page also displays the time taken for the
search operation. There is also a provision for the user to navigate to
the Products Addition page through the "Add a Product" link.
Figure 1.2- Products Query Screen
The products addition screen as shown in Figure 1.3 enables the user
to add a product to the database. For the sake of simplicity, only the
product name, and list price have been made mandatory fields. On filling
up the required fields and clicking on Add Product button a corresponding
row is added to the product_information table in the database. This page
also displays the time taken for the insert operation. There is also a
provision for the user to navigate to the Products Query page through
the "View Products" link.
Figure 1.3 - Products Addition Screen
4. Prerequisites
|
Back to top |
- Oracle9i Database version 9.2 or later. You can download
the Oracle9i database from the Oracle Technology Network.
- Oracle9iAS Containers for J2EE (OC4J), 9.0.2 or later, available here.
or
Oracle9i JDeveloper available here
- If you wish to deploy the application to standalone OC4J then you
must download and install the Jakarta
Ant utility version 1.4
5. Installation and Configuration
|
Back to top |
The section has step-by-step instructions to run this application successfully.
This will be discussed in the following subsections
Notation
Extracting source code of the Application
Configuring the Application
Deploying the Application
Running the Application
- %SAMPLE_HOME%—The directory where the JWCacheSample.jar
file was unzipped to. This directory contains the JSP files, the SRC
directories and ANT build script.
- %OC4J_HOME%—The directory where OC4J is installed. For
example, if oc4j_extended.zip was unzipped to the C drive, then %OC4J_HOME%
would be c:\.
- %ANT_HOME%—The directory where the Ant distribution has
been unpacked.
- %JDEV_HOME%—The directory where Oracle9i JDeveloper
is installed.
The application files are stored in an archive named JWCacheSample.jar.
Download the archive and extract it to
a convenient directory (%SAMPLE_HOME%). Execute following command to extract
the files:
jar xvf JWCacheSample.jar
The archive JWCacheSample.jar extracts the files that implement the Web
Object Cache Invalidation demo. All the files are extracted into the JWCacheSample
directory. This directory will have all the sources as described later.
-
This demo requires some database tables to be created in the database.
The script for creating the required database objects and populating them
with sample data is provided in the file Create.sql located in the directory
%SAMPLE_HOME%/JWCacheSample/config.
Edit this file to specify your application server host and port number.
You will need to modify this line :
execute dbms_java.grant_permission('JWCACHE','SYS:java.net.SocketPermission','<server-host-name>:<port>','connect,resolve');
For example,
execute dbms_java.grant_permission('JWCACHE','SYS:java.net.SocketPermission','incq184b.idc.oracle.com:8888','connect,resolve');
- You need to execute the script Create.sql. Connect to your database
as any user and run the following command from the SQL prompt
SQL>@%SAMPLE_HOME%/JWCacheSample/config/Create.sql
This creates a new database user "JWCACHE" (password - "JWCACHE")
with all the required tables and privileges.
- This demo requires the Java Stored Procedure to be loaded to the database.
This is done from a command prompt as shown below (Ensure the %ORACLE_HOME%\bin
is added to your system path before you run this step)
loadjava -thin -verbose
-user jwcache/jwcache@<hostname>:<port>:<SID>
%SAMPLE_HOME%\JWCacheSample\HttpCallout.java
where,
|
hostname
|
= host name where the database is installed
|
|
port
|
= TNS listener port of the database
|
|
SID
|
= database name
|
For example,
loadjava -thin -verbose
-user jwcache/jwcache@insn104a.idc.oracle.com:1521:otn9i
c:\JWCacheSample\src\HttpCallout.java
- Edit the file JWCache.sql located in the directory %SAMPLE_HOME%\JWCacheSample\config
Change getURL('<hostname>','<portnumber>','/jwcache/productinfo/JDBCInv.jsp')
to point to the URL where the sample application will be setup.
where <hostname> : The URL for the application Server. For eg.
incq184b.idc.oracle.com
and <port> : Port No. on which the server is hosted. By default
this value is 8888.
Now connect to your database as "jwcache" user and run the
script JWCache.sql at the SQL prompt :
SQL>@%SAMPLE_HOME%\JWCacheSample\config\JWCache.sql
This sql script publishes the stored procedure created in the previous
step to the databsae and creates a trigger that invokes HttpCallout.java
whenever product_information table in the jwcache schema is modified.
- Edit the OCS4J.properties file in %SAMPLE_HOME%\JWCacheSample\config
and change the value of diskpath to point to
a directory where you want Java Object Cache to output the persistent
cache.
For example,
diskPath = c:\temp\cache
- Edit the file global-web-application.xml and add an initialization
parameter to the Jsp servlet
<servlet>
<servlet-name>jsp</servlet-name>
<servlet-class>oracle.jsp.runtimev2.JspServlet</servlet-class>
<init-param>
<param-name>ocs4j_file</param-name>
<param-value>%SAMPLE_HOME%/JWCacheSample/config/OCS4J.properties</param-value>
</init-param>
</servlet>
If you are using stand-alone OC4J, then global-web-application.xml will
be located in %OC4J_HOME%\j2ee\home\config.
If you are using Oracle9i JDeveloper, then global-web-application.xml
will be located in %JDEV_HOME%\jdev\system\oc4j_config
- Edit Connection.properties file located in the directory %SAMPLE_HOME%\JWCacheSample\config.
Change the hostname, SID and Port to those of the database instance you
want to connect to.
Change the following lines:
| HostName |
= |
insn104a.idc.oracle.com |
| SID |
= |
otn9i |
| Port |
= |
1521 |
| UserName |
= |
jwcache |
| Password |
= |
jwcache |
- If you are using stand-alone OC4J ensure that the Java Object Cache's
cache.jar is available in %OC4J_HOME%\javacache\lib directory.
If you are using Oracle9i JDeveloper, then within %JDEV_HOME%
create the following directory structure : javacache\lib.
Copy %SAMPLE_HOME%\JWCacheSample\config\cache.jar to the lib
directory created above.
The application can be deployed to an OC4J instance in one of the following
ways
Deploying the Application to Oracle9i JDeveloper's
embedded OC4J
EAR( Enterprise Application aRchive ) Deployment
- Open Oracle9i JDeveloper.
- Navigate to %SAMPLE_HOME%\JWCacheSample
- Open the file JWCacheSample.jws and expand JWCacheSample.jws node
by clicking it.
- Expand JWCacheSample.jpr node by clicking it. The entire source code
has been organized by directories.
- You can run the application from JDeveloper simply by clicking on
Run JWCacheSample.jpr on the Run Menu.
- Ensure that %ANT_HOME% is in the system PATH. Run ant -buildfile build.xml
from %SAMPLE_HOME%\JWCacheSample
- Start OC4J .[ java -jar oc4j.jar from %OC4J_HOME%\j2ee\home]
- Deploy the EAR from %OC4J_HOME%\j2ee\home using
java -jar admin.jar ormi://localhost <uid> <password>
-deploy -file %SAMPLE_HOME%\JWCacheSample\JWCacheDemo.ear -deploymentName
jwcache
This will deploy the application with name "jwcache"
Now, bind the web application to the web site using the following command,
java -jar admin.jar ormi://localhost <uid> <password>
-bindWebApp jwcache JWCacheDemo http-web-site /jwcache
where,
| http-web-site |
: the website to bind the web application |
| uid |
: admin user id |
| password |
: admin password |
- This completes the deployment to the standalone OC4J server. You can
access the application following the steps given under Running
the application.
Note : To undeploy the application use Java -jar admin.jar ormi://localhost:<port>
<uid> <password> -undeploy jwcache
- Open a browser and type the URL to access the application. The URL
should be of the following type
http://<server_host_name>:<port>/jwcache/productinfo/JDBCQuery.jsp
where <server_host_name> : The URL for the application Server.
For eg. incq184b.idc.oracle.com
and <port> : Port No. on which the server is hosted. By default
this value is 8888.
- You can now query for different products. Using % and clicking on the
Search Button will retrieve all products from the database.
- You can navigate to the Add Products page using the link 'Add a Product'.
6. Description of Sample Files
|
Back to top |
| Directory |
Filename |
Description |
| JWCacheSample |
build.xml |
Project build file for generating deployment file using ANT |
| JWCacheSample.jws |
Oracle9i JDeveloper workspace file |
| JWCacheSample.jpr |
Oracle9i JDeveloper project file |
| JWCacheSample\config |
application.xml |
Configuration file for the Application Server |
| Connection.properties |
This file has the details of the database connection parameters |
| Create.sql |
The sql script used to create a database user and all the tables
required by the sample application |
| JWCache.sql |
The sql script is used to publish the java stored procedure to the
database and create a trigger for invoking the java stored procedure |
| cache.jar |
This jar contains Object Caching Service for Java classes. |
| OCS4J.properties |
This file is used to configure the root directory for the Object
Caching Service for Java. |
| JWCacheSample\doc |
Readme.html |
This file |
| JWCacheSample\doc\images |
GIF Files |
Image files used within the Readme |
| JWCacheSample\src\oracle\otnsamples\jwcache |
ProductsBean.java |
Java Bean is used by the JSPs and handles all the database
operations |
| JWCacheSample\src |
HttpCallout.java |
Java Stored Procedure that calls out JDBCInv.jsp to invalidate
the Web Object Cache |
| JWCacheSample\productinfo |
JDBCQuery.jsp |
Retrieves product information from database and caches the
same |
| JDBCInsert.jsp |
Adds product information to the database |
| JDBCInv.jsp |
Invalidates the Web Object Cache used by JDBCQuery.jsp |
| ErrorHandler.jsp |
Error Page for all the JSPs |
| JWCacheSample\productinfo\web-inf |
web.xml |
Configuration file for the Application Server |
| wcache.xml |
This file contains web object cache repository configuration. |
| jwcache.tld |
The complete tag library description file for the OracleJSP
Web Object Cache tag library. |
7. References
|
Back to top |
Revision history: Sep 30, 2002
Please enter your comments about this sample in the OTN
Sample Code Discussion Forum.
|
Bookmark
