import  java.io.*;
 import  java.sql.*;
 import  javax.servlet.*;
 import  javax.servlet.http.*;
// include JSON libs
 import  org.json.*;
// our package
 import  ua.od.intes.*;

/**
 * Main servlet class. Gets request from client and returns answer in json format. All possible requests are predefined. 
 * @author 
 *
 */
 public   class  CBackEnd  extends  HttpServlet {
	 public   void  doGet(HttpServletRequest request,
					  HttpServletResponse response)
					   throws  ServletException, IOException
	{		
		// always return data in json format
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		
		/*
		 *  check whitch action user requests. Action is proveded by dojo frontend
		 */
		String strAction = request.getParameter("action");
		 if (strAction ==  null )
		{
			// no action specified. return error.
			out.write(jsonError("No action specified in request", "-2"));
			out.close();
			 return ;
		}
		
		 if (strAction.equalsIgnoreCase("get_types"))
		{
			// client requies types stored in database
			out.write(getStaticData(eActions.GET_TYPES,  null ));
		}
		 else   if (strAction.equalsIgnoreCase("get_categories"))
		{
			// client requires categories stored in database		
			/*
			 * get type, for which we get categories
			 */
			 int  type =  0 ;
			 try 
			{
				type = Integer.parseInt(request.getParameter("type"));
				// params 
				Object [] pParams = {type}; 
				out.write(getStaticData(eActions.GET_CATEGORIES, pParams));		
			}
			 catch (NumberFormatException ex)
			{
				out.write(jsonError("Invalid type format", "-4"));
			}
		}
		 else   if (strAction.equalsIgnoreCase("search"))
		{
			// client requies to search for specified entity
			/*try
			{
				JSONObject jsonObj = new JSONObject();	
				jsonObj.put("result", "hello from search request");
				out.write(jsonObj.toString());
			}
			catch (JSONException ex)
			{
				throw new 
				ServletException("error forming json string", ex);		
			}*/
			String strKeyword = request.getParameter("keyword");
			 if (strKeyword.length() ==  0 )
			{
				out.write(jsonError("No keyword specified", "-4"));				
			}
			 else 
			{
				Object [] pParams = {strKeyword};
				out.write(getStaticData(eActions.GET_SEARCH_RES, pParams));
			}
		}
		
		 else   if (strAction == "homepage_data")
		{
			// client requies text for start homepage
			out.write(getStaticData(eActions.GET_HOMEPAGE_DATA,  null ));		
		}
		 else 
		{
			// this type of action is not implemented, thus return error	
			out.write(jsonError("Action: " + strAction + " not implemented", "-1"));
		}
			 
		// close output
		out.close();	  
	}


	/**
	 * Gets error code and error text and return string in json format for transmition it to client
	 * @param strError_text description of error
	 * @param strError_code numeric code of error
	 * @return				String in json format	
	 * @throws ServletException
	 */
	 private  String jsonError(String strError_text, String strError_code)
				    throws  ServletException
	{
		 try 
		{
			JSONObject jsonObj =  new  JSONObject();			
			jsonObj.put("error_text", strError_text);
			jsonObj.put("error_code", strError_code);
			 return  jsonObj.toString();			
		}
		 catch  (JSONException ex)
		{
			 throw   new  
				ServletException("error forming json string", ex);			  
		}					
	}
		
	/**
	 * 
	 * @return
	 * @throws ServletException
	 */
	 private  String getStaticData(eActions action, Object [] params )
				    throws  ServletException
	{
		/*
		 *  local variables requiered to get data from database
		 */
		Connection dbConn;
		PreparedStatement sql =  null ;
		ResultSet results;
		// open new connection
		 try 
		{
			CDBConn conn =  new  CDBConn();
			conn.connect("localhost", "root", "qwerty", "test_db");
			dbConn = conn.getConn();
		}
		 catch (ServletException ex)
		{
			 return  jsonError("Error connecting to database: " + ex.getMessage(), "-11");
		}
		// json object for returning data
		JSONObject jsonObj =  new  JSONObject();		
		org.json.JSONArray pData =  new  org.json.JSONArray();	
		 try 
		{
			// now look what we should return
			 if (action == eActions.GET_TYPES)
			{
				sql = dbConn.prepareStatement("SELECT id as id, name as name FROM types");
			} else   if  (action == eActions.GET_CATEGORIES)
			{
				sql = dbConn.prepareStatement("SELECT id as id, name as name FROM categories where type = ?");
				sql.setInt( 1 , (Integer)params[ 0 ]);
			}
			 else   if (action == eActions.GET_HOMEPAGE_DATA)
			{
				sql = dbConn.prepareStatement("SELECT descr as descr FROM texts");
			}
			 else   if (action == eActions.GET_SEARCH_RES)
			{
				// sql = dbConn.prepareStatement("SELECT descr as descr FROM texts");
				/*
				 * not implemented yet
				 */
			}

			// execute query and put results in json object
			results = sql.executeQuery();
			ResultSetMetaData metaData = results.getMetaData();
			 try 
			{									
				 while (results.next())
				{		
					JSONArray pSubData =  new  JSONArray();
					// put all returned fields into array
					 for ( int  i =  0 ; i < metaData.getColumnCount(); i++)
					{					
						pSubData.put(results.getString(i+ 1 ));
					}
					pData.put(pSubData);					
				}
				jsonObj.put("result", pData);
			}
			 catch  (JSONException ex)
			{
				 return  jsonError("internal error", "-10");		  		
			}			
		} catch (SQLException ex)
		{
			 return  jsonError("error accessing database", "-3");
		}
		// return string in json format with requiered data
		//return jsonObj.toString();
		 return  pData.toString();
	}
}

 package  ua.od.intes;

 import  java.sql.*;
 import  javax.servlet.ServletException;
//libs for connection polling
 import  javax.sql.DataSource;
 import  javax.naming.*;


/**
 *	This class establishes connection to database and provides connection descriptor. 
 *	Supports connection polling. 
 *	@author		
 *  @version	0.1
 */ 


/**
 * 	Database connection class
 */
 public   class  CDBConn {
	// connection descriptor
	 private  Connection dbConn;
	
	/**	Connection to database with connection polling. You would configure Tomcat properly to use this
	 * 
	 * @throws ServletException
	 */
	 public   void  connect()
				    throws  ServletException
	{
		 try 
		{
			// load proper class for mysql jdbc driver at runtime
			 Class .forName("org.gjt.mm.mysql.Driver");
			
			 try 
			{
				/*
				 *  connection polling
				 */
				 try  
				{	
					Context initCtx =  new  InitialContext();
					 if (initCtx ==  null  ) 
				           throw   new  ServletException("No Context");
		            Context envCtx = (Context) initCtx.lookup("java:comp/env");
		            Object obj = envCtx.lookup("jdbc/conversion");
		            DataSource ds = (DataSource)obj;
					dbConn = ds.getConnection();
				}
				 catch  (NamingException ex)
				{
				       throw   new  ServletException(
					        "Cannot retrieve java:comp/env/jdbc/conversion",ex);
				}
			}
			 catch  (SQLException ex)
			{
				 throw   new  
			      ServletException("error connecting to database.", ex);
			}
		}
		 catch (ClassNotFoundException ex)
		{
			 throw   new  
		      ServletException("JDBC Driver not found.", ex);			
		}	
	}
	
	/**
	 * Connection to database without connection polling. 
	 * @param username
	 * @param passwd
	 * @param dbName
	 * @throws ServletException
	 */
	 public   void  connect(String host, String username, String passwd, String dbName)
				    throws  ServletException
	{
		 try 
		{
			// load proper class for mysql jdbc driver at runtime
			 Class .forName("org.gjt.mm.mysql.Driver");
			
			 try 
			{
				dbConn = DriverManager.getConnection("jdbc:mysql://"+host+"/"+dbName+"?" + "user="+username+"&password=" + passwd);
			}
			 catch  (SQLException ex)
			{
				 throw   new  
			      ServletException("error connecting to database.", ex);
			}
		}
		 catch (ClassNotFoundException ex)
		{
			 throw   new  
		      ServletException("JDBC Driver not found.", ex);			
		}	
	}

	/**
	 * closes openned connection to database
	 * @throws ServletException
	 */
	 public   void  disconnect()
	   			 throws  ServletException	
	{
		 try 
		{
			dbConn.close();
		}
		 catch  (SQLException ex)
		{
			 throw   new  
		      ServletException("error disconnecting from database.", ex);
		}		
	}
	
	/**
	 * Returns connection descriptor for openned connection
	 * @return Connection descriptor
	 */
	 public  Connection getConn()
					   throws  ServletException
	{
		 if (dbConn ==  null )
			 throw   new  ServletException("connection not openned");
		 else 
			 return  dbConn;
	}
}

 package  ua.od.intes;

/**
 * Enum for simpler identification of actions to perform
 * @author 
 *
 */
 public  enum eActions {
	GET_TYPES,
	GET_CATEGORIES,
	GET_HOMEPAGE_DATA,
	GET_SEARCH_RES
}