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
}

 import  java.io.*;
 import  javax.servlet.*;
 import  javax.servlet.http.*;
 import  ua.od.intes.*;
 import  java.lang.reflect.*;
/**
 * Main servlet class. Gets request from client, loades proper class, responsible for 
 * that request and returns answer in json format. 
 * @author Krot Vyacheslav
 *
 */
 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");
		String strParams = request.getParameter("params");
		 if (strAction ==  null )
		{
			// no action specified. return error.
			out.write(CUtils.jsonError("No action specified in request"));
			out.close();
			 return ;
		}

		// load proper class and invoke single method returning required data in json format
		// all data to function is passed as single json string 
		 try 
		{
			// load class
			 Class  requestHandler =  Class .forName("ua.od.intes." + strAction);
			// parameters list for method to invoke
			 Class  [] paramTypes = {String. class };  
			Method handlerMethod = requestHandler.getMethod("getData", paramTypes);
			// create new instance of class
			Object handlerInstance = requestHandler.newInstance();
				
			// fill params
			Object[] parameters = { strParams };
			// invoke method
			Object retobj = handlerMethod.invoke(handlerInstance, parameters);
			out.write(retobj.toString());
		}
		 catch (ClassNotFoundException ex)
		{
			out.write(CUtils.jsonError("Internal error: Class for required action not found"));
		}
		 catch (Exception ex)
		{
			out.write(CUtils.jsonError("Internal error:" + ex.getCause().getMessage()));
			ex.printStackTrace();
		}
		 finally 
		{
			out.close();
		}
	}	
}

 package  ua.od.intes;

 import  java.sql.*;
 import  javax.sql.DataSource;
 import  javax.naming.*;
 import  java.io.File;
 import  java.util.*;
 import  javax.xml.parsers.DocumentBuilder;
 import  javax.xml.parsers.DocumentBuilderFactory;
 import  org.w3c.dom.Document;
 import  org.w3c.dom.Element;
 import  org.w3c.dom.Node;
 import  org.w3c.dom.NodeList;

/**
 *	Establishes connection to database and provides connection descriptor. 
 *	Supports connection pooling. 
 *	@author		Krot Vyacheslav
 *  @version	0.1
 */ 
 public   class  CDBConn 
{
	// connection descriptor
	 private  Connection dbConn;

	// file with connection parameters
	 private  String configFileName = "webapps\\test_3\\config\\config.xml";
	
	/**
	 * Openes new connection to database
	 * @param withConnectionPooling Determines wheather to use or not connection pooling
	 * @throws Exception If an error occures it's description is returned in Exception
	 */
	 public   void  connect( boolean  withConnectionPooling)
				 throws  Exception
	{
		 try 
		{
			// load proper class for mysql jdbc driver at runtime
			 Class .forName("org.gjt.mm.mysql.Driver");			
			 try 
			{			
				 if (withConnectionPooling == true)
				{
					// connection pooling
					 try  
					{	
						// Create a JNDI Initial context to be able to lookup  the DataSource
						InitialContext ctx =  new  InitialContext();
						// Lookup the DataSource
						DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/conversion");
						dbConn = ds.getConnection();
					}
					 catch  (NamingException ex)
					{
						 throw   new  Exception(
						        "Cannot retrieve java:comp/env/jdbc/conversion", ex);
					}
				}
				 else 
				{
					HashMap connParams = readConfig(configFileName);
					dbConn = DriverManager.getConnection(
							"jdbc:mysql://" + connParams.get("hostname") + "/" + connParams.get("db_name") + "?" + 
							"user=" + connParams.get("username") + "&password=" + connParams.get("passwd"));					
				}
			}
			 catch  (SQLException ex)
			{
				 throw   new  
			      Exception("Error connecting to database", ex);
			}
		}
		 catch (ClassNotFoundException ex)
		{
			 throw   new  
		      Exception("Error conneting to database: JDBC driver not found", ex);			
		}	
	}
	

	/**
	 * Closes openned connection to database
	 * @throws Exception
	 */
	 public   void  disconnect()
	   			 throws  Exception	
	{
		 if (dbConn ==  null )
		{
			 throw   new  
		      Exception("Failed to close connection: connection not openned");			
		}
		 try 
		{				
			dbConn.close();
			dbConn =  null ;
		}
		 catch  (SQLException ex)
		{
			 throw   new  
		      Exception("Failed to close connection", ex);
		}		
	}
	
	/**
	 * Returns connection descriptor for openned connection
	 * @return Connection descriptor
	 * @throws Exception
	 */
	 public  Connection getConn()
					   throws  Exception
	{
		 if (dbConn ==  null )
			 throw   new  Exception("Failed to return connection: connection not openned");
		 else 
			 return  dbConn;
	}
	
	/**
	 * Reades parameters of connection from specified XML file. Format of file is strongly defined
	 * @param filename
	 * @return HashMap with corresponding parameters
	 * @throws ServletException
	 */
	 public  HashMap readConfig(String filename)
				 throws  Exception
	{
		// returned results
		HashMap<String,String> map =  new  HashMap();
		 try  
		{
			// open file with XML config
			File file =  new  File(filename);
			DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
			DocumentBuilder db = dbf.newDocumentBuilder();
			Document doc = db.parse(file);
			doc.getDocumentElement().normalize();
			NodeList nodeLst = doc.getElementsByTagName("db_config");
			 if (nodeLst.getLength() !=  1 )
			{
				 throw   new  Exception("Failed to loading config file: invalid format",  new  java.lang.Throwable());
			}			
			// read data from node
			Node cNode = nodeLst.item( 0 );
			 if  (cNode.getNodeType() == Node.ELEMENT_NODE) 
			{
				Element fstElmnt = (Element) cNode;
				NodeList lstNmElmntLst = fstElmnt.getElementsByTagName("username");
				Element lstNmElmnt = (Element) lstNmElmntLst.item( 0 );
				NodeList lstNm = lstNmElmnt.getChildNodes();
				map.put("username", ((Node) lstNm.item( 0 )).getNodeValue());
				
				lstNmElmntLst = fstElmnt.getElementsByTagName("passwd");
				lstNmElmnt = (Element) lstNmElmntLst.item( 0 );
				lstNm = lstNmElmnt.getChildNodes();
				map.put("passwd", ((Node) lstNm.item( 0 )).getNodeValue());
				
				lstNmElmntLst = fstElmnt.getElementsByTagName("hostname");
				lstNmElmnt = (Element) lstNmElmntLst.item( 0 );
				lstNm = lstNmElmnt.getChildNodes();
				map.put("hostname", ((Node) lstNm.item( 0 )).getNodeValue());

				lstNmElmntLst = fstElmnt.getElementsByTagName("db_name");
				lstNmElmnt = (Element) lstNmElmntLst.item( 0 );
				lstNm = lstNmElmnt.getChildNodes();
				map.put("db_name", ((Node) lstNm.item( 0 )).getNodeValue());
			}
			 else 
			{
				 throw   new  Exception("Failed to load configuration file: invalid format");
			}
			
		} 
		 catch  (Exception ex) 
		{
			 throw   new  Exception("Failed to load configuration file", ex);
		}	
		
		 return  map;
	}	
}

 package  ua.od.intes;

 import  java.sql.*;

 import  org.json.*;

/**
 * Gets list of types from database
 * @author Krot Vyacheslav
 *
 */
 public   class  GetTypes 
{
	 public  String getData(String strParams)
				   throws  Exception
	{
		// local variables requiered to get data from database
		Connection dbConn =  null ;
		PreparedStatement stmt =  null ;
		ResultSet results =  null ;
	
		// open new connection
		CDBConn conn =  new  CDBConn();
		conn.connect(true);
		dbConn = conn.getConn();
		
		// json object for returning data	
		org.json.JSONArray pData =  new  org.json.JSONArray();	
		 try 
		{
			stmt = dbConn.prepareStatement("SELECT id, name FROM types");

			// execute query and put results in json object
			results = stmt.executeQuery();
			ResultSetMetaData metaData = results.getMetaData();
			 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);					
			}
		}
		 catch (SQLException ex)
		{
			 throw   new  
				Exception("Error reading data from database", ex);
		}
		 finally 
		{
			// close connection and statement
			 try 
			{
				results.close();
				results =  null ;
				stmt.close();
				stmt =  null ;
			}
			 catch (Exception ex)
			{  
				// nothing could be done about it
			}
			conn.disconnect();
		}
		 return  pData.toString();
	}
}