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

public class LineData 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 graphing applet or servlet.
//
// You may freely copy and modify this script to suite your own
// requirements.
//
// For further information visit,
// http://www.jpowered.com/line_graph/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
	int        datacount;

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

 		    out.println("  <!-- Chart Data -->  \n");

			// Performing SQL query for Product X
			query = "Select Value from SalesLine where Year=2004 and Product='X' ORDER BY Month";
			ResultSet srs1 = stmt.executeQuery(query);

            // Write out the data for Series 1
            datacount = 1;
			while (srs1.next()) {
				out.println("data"+datacount+"series1: "+srs1.getString("Value")+"\n");
				datacount++;
			}

			// Performing SQL query for Product Y
			query = "Select Value from SalesLine where Year=2004 and Product='Y' ORDER BY Month";
			ResultSet srs2 = stmt.executeQuery(query);

            // Write out the data for Series 2
            datacount = 1;
			while (srs2.next()) {
				out.println("data"+datacount+"series2: "+srs2.getString("Value")+"\n");
				datacount++;
			}

		} // 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);}
//-----------------------------------------------------------------------------
} // End class