//=======================================================================================
// REST AJAX Web Service
//      Receives user:   The database user id
//               pswd:   The database password  
//               db:     The database name (Mysql database name)  
//               sql:    The sql code that you want to execute  
//               host:   (optional) The hostname,    if none = localhost  
//               port:   (optional) The port number, if none = 3306  
//               format: The output format XML or JSON
//      Returns: data from MySQL database in either HTML, XML or JSON format
//               if XML  then XML standard
//               if JSON then array of objects as:
//                       [ 
//                           {"name":"value", "name2":"value2", ...}, 
//                           {"name":"value", "name2":"value2", ...} 
//                       ]  
//=======================================================================================
import javax.servlet.*;                         //for ServletException class
import javax.servlet.http.*;                    //for HttpServlet class
import java.io.*;				//for the PrintWriter
import java.sql.*;				//for the SQL calls

public class REST extends HttpServlet
{
    static String      user   = "demo";			//fields
    static String      pswd   = "demo";
    static String      db     = "demo";
    static String      sql    = "select * from instructor";
    static String      host   = "";
    static String      port   = "";
    static String      format = "";

    static String      error    = "";
    static String[]    colNames;
    static String[][]  data;

    static PrintWriter out;

//=======================================================================================
    public void doGet(HttpServletRequest request, HttpServletResponse response) 
                                throws IOException, ServletException
    {
        user   = request.getParameter("user");
        pswd   = request.getParameter("pswd");
        db     = request.getParameter("db");
        sql    = request.getParameter("sql");
        host   = request.getParameter("host");
        port   = request.getParameter("port");
        format = request.getParameter("format");

//    $sql = str_replace('~', '%', $sql);         //I used ~ instead of %
//                                                //% is used for url encoding

        execSQL();				//execute the SQL
        doPost(request, response);		//do the HTTP response
    }

//=======================================================================================
    public void execSQL()
    {
        error    = "";				//these are static variables				
        colNames = null;			//so they must be re-initialized 
        data     = null;			//for every request.
	
	Connection connect = null;
	Statement  stmt    = null;
        ResultSet  rs      = null;

        if (user == null || pswd == null || db == null || sql == null)
        {
            error = "Enter: URL?user=...&pswd=...&db=...&sql=...&host=...&port=...&format=XML|JSON";
            return;
        }
        if (host == null) 
            host = "localhost";

        String host2   = "jdbc:mysql://" + host + "/";
	String host_db = host2 + db;

        try 
        {
            Class.forName("com.mysql.jdbc.Driver");   //dynamically load
                                                      //the JDBC Drive class

            connect = DriverManager.getConnection(host_db, user, pswd);
            
	    stmt = connect.createStatement();        //create a statement object

	    rs = stmt.executeQuery(sql);

	    ResultSetMetaData metadata = rs.getMetaData();

	    int num_of_cols   = metadata.getColumnCount();

	    rs.last();						//move to last record
	    int num_of_rows   = rs.getRow(); 
	    rs.beforeFirst();					//reset to start

	    colNames = new String[num_of_cols];
	    data     = new String[num_of_rows][num_of_cols];
	    
	    for (int col=0; col < num_of_cols; col++)			//get column names 
	    {	
	    	String colName = metadata.getColumnName(col+1);		//index starts at 1 not 0 
	    	colNames[col]  = colName;
	    }

	    for (int row=0; row < num_of_rows; row++)			//loop thru each row 
	    {	
	    	rs.next(); 
	        for (int col=0; col < num_of_cols; col++)		//loop thru each column within each row 
	        {	
	    	    String colValue = rs.getString(col+1); 
	    	    data[row][col]  = colValue;
	        }
	    }
	}
	catch (Exception e) 
	{
	    error    = "Could not execute SQL - " + e;
	    colNames = null;
	    data     = null;
	}
	finally 
	{
	    try
            {
                if (rs != null)      rs.close();	
                if (stmt != null)    stmt.close();	
                if (connect != null) connect.close();
            }	
	    catch (SQLException e) 
	    {                            
		error    = "Could not close database - " + e;;
		colNames = null;
		data     = null;
	    } 
	}
    }	      

//=======================================================================================
    public void doPost(HttpServletRequest request, HttpServletResponse response) 
                                throws IOException, ServletException
    {
       out = response.getWriter();         		//reference the PrintWriter
                
       try
       {
           if (format == null || format.equalsIgnoreCase("html"))
           {
               response.setContentType("text/html");          //response type is text/html
               print_html();
           }           
           if (format.equalsIgnoreCase("json"))
           {
               response.setContentType("text/plain");         //response type is text/plain
               print_json();
           }
           if (format.equalsIgnoreCase("xml"))
           {
               response.setContentType("text/xml");           //response type is text/xml
               print_xml();
           }
        }
        catch(Exception e)
        {
        }
    }

//=======================================================================================
    public void print_html()
    {
        out.print("\n"                                  //print the page
                + "<html> \n"
                + "<head> \n"  
                + "<title>Java Web Service</title> \n"
                + "</head> \n"
                + "<body bgcolor='lightyellow'> \n"
                + "<h2 align='center'>Java SQL Query Web Service</h2> \n"
                + error + "\n"
                + "<table bgcolor=cyan> \n"
                + "<tr bgcolor=tan> \n");
                
	for (int col=0; col < colNames.length; col++)		//loop thru the column names 
	    out.print("<th>" + colNames[col] + "</th>");
	    
	out.print("</tr> \n");
	
	for (int row=0; row < data.length; row++)		//loop thru all the rows
	{
	    out.print("<tr>");
	    for (int col=0; col < data[row].length; col++)	//loop thru the column for each row 
	        out.print("<td>" + data[row][col] + "</td>");
	    out.print("</tr> \n");
	}
		 
	out.print("</table> \n"
                + "</body>  \n"
                + "</html>  \n");
    }
//=======================================================================================
    public void print_json()
    {
        if (! error.equals(""))
            out.print("[ {\"error\":\"" + error + "\"} ] \n");

        String jsonStr = "[ \n";
                        	
	for (int row=0; row < data.length; row++)			//loop thru the rows
	{
	    jsonStr += "    {";

            for (int col=0; col < data[row].length; col++)            	//loop thru the column for each row
            {
                String colName  = colNames[col];
                colName = colName.replaceAll("\\s","_");             		//if column alias has ' ' 
                jsonStr += "\"" + colName + "\":\"" + data[row][col] + "\"";     
                if (col < data[row].length-1)					//no comma after last name/value pair
                    jsonStr += ", ";
            }
            jsonStr += (row < data.length-1) ? "}, \n" : "} \n";		//no comma after last row
	}
        jsonStr +=  "] \n";

        out.print(jsonStr);            
    }

//=======================================================================================
    public void print_xml()
    {                	
        out.print("<?xml version='1.0'?> \n");
        out.print("<sqlData> \n");

        if (! error.equals(""))
            out.print("<row><error>" + error + "</error></row></sqlData> \n");

	for (int row=0; row < data.length; row++)			//loop thru the rows
	{
            out.print("    <row> \n");

            for (int col=0; col < data[row].length; col++)            	//loop thru the column for each row
            {
                String colName  = colNames[col];
                colName = colName.replaceAll("\\s","_");             	//if column alias has ' ' 
                out.print("        " +
                          "<"+ colName +">"+ data[row][col] +"</"+ colName +"> \n");     
            }
            out.print("    </row> \n");
	}
        out.print("</sqlData> \n");
    }
}