import java.sql.*;
import java.lang.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class DatabaseServletBar 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 either the bar graph applet or servlet.
//
// As you will see the main routine ( doGet() ) uses the method
// GraphData() to construct the return data.
//
// For further information visit,
// http://www.jpowered.com/bar_graph/hbargraph/index.htm
//
//-----------------------------------------------------------------------------
    // Initialise and set variables
	String url="jdbc:MySQL:///TESTDB";  // URL specifying the JDBC connection to a MySQL database 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='2004'";
            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