Both
the applet and servlet can be set to acquire data from a
database ( or multiple database ) 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,
Table
Name : ProductSales |
|
|
ProductName |
Character |
Quarter1sales |
Integer |
Quarter2sales |
Integer |
Quarter2sales |
Integer |
Quarter2sales |
Integer |
Year |
Integer |
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 DatabaseServletPie.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.