Connecting the Graph to a Database
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 line graph.
The method is as follows,
The full servlet code is contained in the DatabaseServlet2.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 Line Graph.
Finally the rsltStr is returned back to the calling process ( in this case our Line Graph ) and the database connection is closed.