Both
the applet and servlet can be set to acquire data from
a database ( or multiple databases ) by utilizing a server
side script or process. In the previous section ( Connecting
the Graph to a Server Process. ) We saw how to connect
both the applet and servlet to a server side process.
The
server side process can be written in any language of
your choice and as such can be written to acquire data
from any data source desired (databases, other processes,
files etc.). Here we will demonstrate how to create a
Java Servlet which acquires data from a single database
and presents the data in the correct format to either
the graph applet or graph servlet.
Our
example is designed to retrieve product sales data from
a MySQL database, from a table with the following structure,
Our
servlet will pick out the sales figures for the year 2001
for three products ( ProductX, ProductY and ProductZ) and
return the data in the correct format for the Pie Chart.
The
method is as follows,
-
Set the output characteristics for the return data
-
Establish the database connection
-
Build the query statement and retrieve the database
records
-
Process the database records and return the Data
-
All finished so close the database connection
The
full servlet code is contained in the DatabaseServlet.java
file in the ServerTemplateScripts directory.
(Click
here to view the code).
As
you will see from the code the servlet connects to the
database via JDBC and as such it is essential to have
the correct JDBC drivers for your database. ( For further
information on JDBC drivers please see http://industry.java.sun.com/products/jdbc/drivers
)
The
SQL statement used to retrieve the data from the database
is simply,
SELECT * FROM
ProductSales WHERE Year='2001'
The
returned result set from this statement is then passed
to a little routine, GraphData, which reads through the
records picking out the relevant records for the three
products. Also note in this routine how the "rsltStr"
is constructed to provide the data in the correct format
for the Pie Chart.
Finally
the rsltStr is returned back to the calling process ( in
this case our Pie Chart ) and the database connection is
closed.
[