Monday, May 18, 2015

SQL merge statement


CREATE TABLE merge_employee
(
   id        NUMBER PRIMARY KEY,
   name      VARCHAR2 (200),
   address   VARCHAR2 (300)
);

CREATE TABLE merge_hrecord
(
   id_hr     NUMBER PRIMARY KEY,
   name      VARCHAR2 (200),
   address   VARCHAR2 (300)
);

INSERT INTO merge_employee
     VALUES (1, 'suneesh', 'XXX,Orchid Park');

INSERT INTO merge_hrecord
     VALUES (1, 'suneesh', '602,Orchid Park');

INSERT INTO merge_hrecord
     VALUES (2, 'suneesh1', '6021,Orchid Park');

COMMIT;

SELECT * FROM merge_hrecord;

SELECT *
  FROM MERGE_EMPLOYEE m;

MERGE INTO merge_employee e
     USING merge_hrecord h
        ON (e.id = h.id_hr)
WHEN MATCHED
THEN
   UPDATE SET e.address = h.address
WHEN NOT MATCHED
THEN
   INSERT     (id, address)
       VALUES (h.id_hr, h.address);

Friday, May 15, 2015

Find the 7th highest salary

Find the 7th highest salary 
sol :1 
SELECT *
FROM
  (SELECT employee_id,
    salary,
    dense_rank() over (order by salary DESC) highest_sal
  FROM EMPLOYEES
  )
WHERE highest_sal=7;

sol :2
SELECT rn ,
  salary
FROM
  (SELECT rownum rn,
    salary
  FROM
    (SELECT DISTINCT salary FROM EMPLOYEES ORDER BY salary DESC
    )
  )
WHERE rn=7;

Tuesday, February 25, 2014

Nested if else in Apache Ant

Often we may have requirement to implement nested if in Apache Ant without using ant-contrib.

My requirement was to set a xml property by the following condition

1) If the property xml file is passed using -D parameter it should take the precedence
2) If I set an environment variable for property xml path it will take the 2nd preference
3) Above two conditions are not met it will take the property from a default location.

if( -Dparameter Set)
else
{
   if(Env variable set)
   {
  } else
 {
   //take from default
 }
}

This is how I accomplish the task above :

<target name="init-pls-config-xml"
         depends="init-pls-config-dparam,int-pls-config-osset"></target>
 <target name="init-pls-config-dparam" if="pls-config">
  <echo>*********** Using pls-config file as -D param : ${pls-config} *********** </echo>
  <xmlproperty file="${pls-config}" collapseattributes="true" keeproot="false"/>
 </target>
 <target name="int-pls-config-osset" unless="pls-config"
         depends="init-pls-config-osenvxml,init-pls-config-default"></target>
 <target name="init-pls-config-osenvxml" if="OS_ENV.PLS_ANT_CONFIG">
  <echo>*********** Using pls-config file from the environment variable : ${OS_ENV.PLS_ANT_CONFIG} *********** </echo>
  <xmlproperty file="${OS_ENV.PLS_ANT_CONFIG}" collapseattributes="true"
               keeproot="false"/>
 </target>
 <target name="init-pls-config-default" unless="OS_ENV.PLS_ANT_CONFIG">
  <echo>*********** Using pls-config file from the default location: ${OS_ENV.HOME}/pls-config.xml *********** </echo>
  <xmlproperty file="${OS_ENV.HOME}/pls-config.xml" collapseattributes="true"
               keeproot="false"/>
 </target>

Thursday, February 20, 2014

Servlets: Getting the form parameters

Form parameters methods are defined in the interface ServletRequest .These are the main methods :

getParameter(String ) - Getting the parameter from the request object for the parameter name
getParameterMap() - Getting the name value map of the parameters
getParameterNames() - Returning enumeration of parameter names
getParameterValues(String) - Returns array of values for the parameter name

parameters.html :



<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"/>
        <title>reqparameters</title>
    </head>
    <body>
        <form method="POST" action="getparameters">
            <table border="1" style="width: 80%;">
                <tr>
                    <td>First Name</td>
                    <td>
                        <input name="first" type="text"/>
                    </td>
                </tr>
                
                <tr>
                    <td>Last Name</td>
                    <td>
                        <input name="last" type="text"/>
                    </td>
                </tr>
                
                <tr>
                    <td>Login Name</td>
                    <td>
                        <input name="login" type="text"/>
                    </td>
                </tr>
                
                <tr>
                    <td>&nbsp;</td>
                    <td>
                        <input name="Submit" type="submit" value="SubmitPage"/>
                    </td>
                </tr>
            </table>
        </form>
    </body>
</html>

 The servlet :

package view;

import java.io.IOException;
import java.io.PrintWriter;

import java.util.Enumeration;

import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

@WebServlet(name = "GetParameters", urlPatterns = { "/getparameters" })
public class GetParameters extends HttpServlet {
    private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType(CONTENT_TYPE);
        PrintWriter out = response.getWriter();
        out.println("<html>");
        out.println("<head><title>GetParameters</title></head>");
        out.println("<body>");
        out.println("<p>The servlet has received a GET. This is the reply.</p>");
        Enumeration params = request.getParameterNames();
        while(params.hasMoreElements()) {
            String name =(String)params.nextElement();
            String value = request.getParameter(name);
            out.println(" Name = "+name+" : Value "+value);
        }
        System.out.println(" Request hash Map = "+request.getParameterMap());
        out.println("</body></html>");
        out.close();
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}



Wednesday, February 19, 2014

Upload Servlet

upload.html :

<html>
<body>
<form enctype="multipart/form-data" method="POST" action="uploadservlet">
<input type="file" size="20" name="FileToUpload" value="Select a File" >
<input type="submit" name="UploadFile" value="Upload">

</form>
</body>
</html>

Servlet :

package view;

import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;

import java.util.Enumeration;

import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;

@WebServlet(name = "UploadServlet", urlPatterns = { "/uploadservlet" })
public class UploadServlet extends HttpServlet {
    static final String dir = "D:/tmp";
    private static final String CONTENT_TYPE = "text/html; charset=windows-1252";

    public void init(ServletConfig config) throws ServletException {
        super.init(config);
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
       
       

        PrintWriter outHTML = response.getWriter();
        outHTML.println("Done ");
      
        ServletInputStream is = request.getInputStream();
        BufferedInputStream bis = new BufferedInputStream(is);
        BufferedReader reader = new BufferedReader(new InputStreamReader(is));

        File file = new File("D:\\Projects\\tmp\\out.txt");
        FileWriter out = new FileWriter(file);
        int i;
        while ((i = reader.read()) != -1) {
            out.write(i);
        }
        out.close();
        bis.close();
    }

    @Override
    protected void doGet(HttpServletRequest httpServletRequest,
                         HttpServletResponse httpServletResponse) throws ServletException, IOException {
        // TODO Implement this method
        doPost(httpServletRequest, httpServletResponse);
    }
}

doGet(),doPost() , doPut(),doHead()

  • Get append the data to the URL as name value pair.
  • Post sends the data separately in the body of the request 
  • Get is for small amount of data 
  • Get requests can be bookmarked 
  • Get is used for simple html requests 
  • In Post request the query limit is unlimited 
  • User seen the data in the URL in Get request, can be a security issue. Post requests the data is hidden.
  • Get request is always ascii. Post can be both ascii and binary.
  • Post is used for posting long messages 
  • Put() is uploading the file to the server. But many container it wont work and Post is used instead.
  • Get request can be triggered by clicking on a link ,Typing address ,location.href and submitting a page with method ="get" in the form element.
  • Post is triggered when submitting an html form.
  • head() returns same header that Get return .But nobody or content 
  • Receives and return small amount of message faster and light weight.
 

How the servlets works

While start up the servlet container pre-load servlets in the memory if the servlet is defined in the web.xml.If not there in the web.xml the servlet is getting loaded on first time access.First time the servlet loads it will call the init() method. Init method is getting used to initialize the objects like db connection file connection etc.Each time the user make a request to the servlet the service method is getting called which is overridden in doGet() and doPost() methods.The response from these method back to container and to the browser.Web application is a collection of jsp,sevlets,images ,css etc , packaged to a .war file.Servlet container normally have one instance of servlet.If the servlet implements single threaded model multiple instance of servlets are possible.In order to convert your servlet into distributable the servlet should implement single threaded model and in the deployment descriptor make the servlet as distributable.