Graph Documentation - Database Access and Data Binding

	Graph Documentation - Database Access and Data Binding
	[ Home ] [ Products ] [ Downloads ] [ Purchase ] [ Support ] [ About Us ]

Database Access and Data Binding

Overview
Being able to dynamically generate graphs and charts from data stored in databases in real time makes the graphing functions incredibly powerful.

Security Considerations
In order for an Applet to directly access the data within a database it must be provided with the logon information to that database. If this information is available to an applet then it is also available to anyone else who wishes to obtain it. Hence it is never a good idea to have an applet directly access a database.

To overcome this the Graphing Applets access databases (and other data sources) via Server Side Scripts and Processes. The basic idea is that the graph function will issue a request to such a server side script for the data, this in turn will access the database and return the data back to the graph function. In this way the database logon information is kept securely on the server.

The ./ServerTemplateScripts/ directory of this package contains some example server side scripts which may be adapted for a particular implementation. In addition the Tutorials show these methods in more detail.

Data Interface Scripts can be constructed in any Server Side Scripting Language including, PHP, JSP, ASP, Servlets, Perl etc.

Essential Tutorials and more Database Methods:-

The following describes how to set up the line graph applet to use a Java Servlet to pull the data from a database

The Data Interface Servlet is designed to retrieve product sales data from a MySQL database, from a table with the following structure

Table Name : SalesBar
ProductName	Text
Year	Integer
Month	Integer
Value	Double

The servlet will pick out the sales figures for three products ( ProductX, ProductY and ProductZ) and return the data in the correct format for the graph.
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

As you will see from the code (below) the servlet connects to the database via JDBC.

The Data Interface Servlet Code

import java.sql.*;
import java.lang.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class DatabaseServlet extends HttpServlet {
//
// This simple servlet is designed to demonstrate how a servlet can be used
// to retrieve data from a database and return the data in the correct format
// to the graphing functions.
//
// As you will see the main routine ( doGet() ) uses the method
// GraphData() to construct the return data.
//
//
//-----------------------------------------------------------------------------

    // Initialise and set variables
    String url="jdbc:MySQL:///TESTDB";  // JDBC connection to a MySQL db TESTDB.
    Connection con = null;              // Database connection object
    Statement stmt = null;              // Statement String
    String query;                       // Query String

//-----------------------------------------------------------------------------
    public void doGet(HttpServletRequest req, HttpServletResponse res)
           throws ServletException, IOException {

        // Set the output characterics for the return data
        res.setContentType("text/html");
        ServletOutputStream out = res.getOutputStream();

        // Establish the database connection
        try {
            // Connect to TESTDB
            Class.forName("org.gjt.mm.mysql.Driver");
            con = DriverManager.getConnection (url,"[DB Username]","[DB Password]");
            stmt = con.createStatement();

            // Build the query statement and retrieve the database records
            query = "SELECT * FROM ProductSales WHERE Year='2006'";
            ResultSet srs = stmt.executeQuery(query);

            // Process the database records and return the Data
            out.println(GraphData(srs));

        } // End try


        // Error handling
        catch(ClassNotFoundException e) {out.println("Could not load database driver: " + e.getMessage());}
        catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}

        // All finished so close the database connection
        finally {
                 try {if (con != null) con.close();}
                 catch (SQLException e) {}
        }

    } // End doGet
//-----------------------------------------------------------------------------
    public void doPost(HttpServletRequest request,HttpServletResponse response)
                throws ServletException, IOException {doGet(request, response);}
//-----------------------------------------------------------------------------

    public static String GraphData(ResultSet srs) {

        String rsltStr = "\n";
        String productname = null;
        String jansales;
        String febsales;
        String marsales;
        String aprsales;
        String maysales;
        String junsales;
        String julsales;
        String augsales;
        String sepsales;
        String octsales;
        String novsales;
        String decsales;

        // Read through the records and construct the return string
        //   ProductX will be set series1
        //   ProductY will be set to series2
        //   ProductZ will be set to series3

        try {

          while (srs.next()) {

            jansales = "0.0";
            febsales = "0.0";
            marsales = "0.0";
            aprsales = "0.0";
            maysales = "0.0";
            junsales = "0.0";
            julsales = "0.0";
            augsales = "0.0";
            sepsales = "0.0";
            octsales = "0.0";
            novsales = "0.0";
            decsales = "0.0";

            productname = srs.getString("ProductName");

            jansales = srs.getString("m1sales");
            febsales = srs.getString("m2sales");
            marsales = srs.getString("m3sales");
            aprsales = srs.getString("m4sales");
            maysales = srs.getString("m5sales");
            junsales = srs.getString("m6sales");
            julsales = srs.getString("m7sales");
            augsales = srs.getString("m8sales");
            sepsales = srs.getString("m9sales");
            octsales = srs.getString("m10sales");
            novsales = srs.getString("m11sales");
            decsales = srs.getString("m12sales");

            if (productname.equals("ProductX")) {
                rsltStr = rsltStr +
                          "data1series1:        "+ jansales +"\n"+
                          "data2series1:        "+ febsales +"\n"+
                          "data3series1:        "+ marsales +"\n"+
                          "data4series1:        "+ aprsales +"\n"+
                          "data5series1:        "+ maysales +"\n"+
                          "data6series1:        "+ junsales +"\n";
            }
            if (productname.equals("ProductY")) {
                rsltStr = rsltStr +
                          "data1series2:        "+ jansales +"\n"+
                          "data2series2:        "+ febsales +"\n"+
                          "data3series2:        "+ marsales +"\n"+
                          "data4series2:        "+ aprsales +"\n"+
                          "data5series2:        "+ maysales +"\n"+
                          "data6series2:        "+ junsales +"\n";
            }
            if (productname.equals("ProductZ")) {
                rsltStr = rsltStr +
                          "data1series3:        "+ jansales +"\n"+
                          "data2series3:        "+ febsales +"\n"+
                          "data3series3:        "+ marsales +"\n"+
                          "data4series3:        "+ aprsales +"\n"+
                          "data5series3:        "+ maysales +"\n"+
                          "data6series3:        "+ junsales +"\n";
            }


          } // end while


        } // End try


        // Error handling
        catch(SQLException e) {rsltStr = rsltStr + "\nSQLException caught: " + e.getMessage();}


         return(rsltStr);
    }

} // End class

Custom Solutions
If would like us to perform the set-up process, create custom built data scripts or help with any other part of your web application then we are pleased to offer you the following services:-
Set-Up, Installation and Configuration Service
Web Site Components Development Service
Web Application Development Service
Custom Software Development

« back to documentation index

« back to Getting Started

[ Home ] [ Products ] [ Downloads ] [ Purchase ] [ Support ] [ About Us ]