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

public class DatabaseServlet extends HttpServlet {


    // 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,"[database username]","[database password]");
           	stmt = con.createStatement();

            // Build the query statement and retrieve the database records
            query = "SELECT * FROM ProductSales WHERE Year='2001'";
            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 q1sales;
		String q2sales;
		String q3sales;
		String q4sales;

		// 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()) {

			q1sales = "0.0";
			q2sales = "0.0";
			q3sales = "0.0";
			q4sales = "0.0";

			productname = srs.getString("ProductName");
			q1sales = srs.getString("Quarter1sales");
			q2sales = srs.getString("Quarter2sales");
			q3sales = srs.getString("Quarter3sales");
			q4sales = srs.getString("Quarter4sales");

            if (productname.equals("ProductX")) {
				rsltStr = rsltStr +
				          "data1series1        "+ q1sales +"\n"+
				          "data2series1        "+ q2sales +"\n"+
				          "data3series1        "+ q3sales +"\n"+
				          "data4series1        "+ q4sales +"\n";
			}
            if (productname.equals("ProductY")) {
				rsltStr = rsltStr +
				          "data1series2        "+ q1sales +"\n"+
				          "data2series2        "+ q2sales +"\n"+
				          "data3series2        "+ q3sales +"\n"+
				          "data4series2        "+ q4sales +"\n";
			}
            if (productname.equals("ProductZ")) {
				rsltStr = rsltStr +
				          "data1series3        "+ q1sales +"\n"+
				          "data2series3        "+ q2sales +"\n"+
				          "data3series3        "+ q3sales +"\n"+
				          "data4series3        "+ q4sales +"\n";
			}


		  } // end while


		} // End try


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


         return(rsltStr);
				   	       }


//-----------------------------------------------------------------------------------

} // End class