CRUD SERVLET

Helper Class

package com.nsep.user.helper;
import java.sql.*;
import java.util.logging.Logger;

public class DataBaseUtil {

    Logger logger=Logger.getLogger("Logging");
    Connection conn;
    public DataBaseUtil()
    {
        try
        {//initializing the credentials
         Class.forName("oracle.jdbc.driver.OracleDriver");
         String serverName = "172.24.137.30";
         String portNumber = "1521";
         String sid = "ora10g";
         String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
         String username="e533336";
         String password="evWXebidv";
         conn =DriverManager.getConnection( url, username, password );
         logger.info("Connection");

        }

        catch ( SQLException sqlex ) 
        {
            logger.info("SQL Exception !! " +sqlex);            
            sqlex.printStackTrace();
        }
        catch (Exception ex)
        {
            logger.info("Exception has occured "+ex);
        }
    }
    public Connection getConn()
    {
        return conn;//returning the connection object 
    }
}

Helper Class

package com.nsep.user.helper;
import java.sql.*;
import java.util.logging.Logger;

public class DataBaseUtil {

    Logger logger=Logger.getLogger("Logging");
    Connection conn;
    public DataBaseUtil()
    {
        try
        {//initializing the credentials
         Class.forName("oracle.jdbc.driver.OracleDriver");
         String serverName = "172.24.137.30";
         String portNumber = "1521";
         String sid = "ora10g";
         String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
         String username="e533336";
         String password="evWXebidv";
         conn =DriverManager.getConnection( url, username, password );
         logger.info("Connection");

        }

        catch ( SQLException sqlex ) 
        {
            logger.info("SQL Exception !! " +sqlex);            
            sqlex.printStackTrace();
        }
        catch (Exception ex)
        {
            logger.info("Exception has occured "+ex);
        }
    }
    public Connection getConn()
    {
        return conn;//returning the connection object 
    }
}

package com.nsep.user.model;
public class EmployeeBean {
    int employee_id;
    String employee_name;
    String employee_role;
    long employee_phnum;
    String password;
    //the setters to set the attributes
        public void setEmployeeName(String employee_name)
        {
            this.employee_name=employee_name;
        }
        public void setPassword(String password)
        {
            this.password=password;
        }
        public void setEmployeeRole(String employee_role)
        {
            this.employee_role=employee_role;
        }
        public void setEmployeePhone(long employee_phnum)
        {
            this.employee_phnum=employee_phnum;
        }
        public void setEmployeeId(int emp_id )
        {
            this.employee_id=emp_id;
        }
    //getters to get the attributes
        public int getEmployeeId()
        {

            return employee_id;
        }
        public String getPassword()
        {
            return password;
        }
        public String getEmployeeName()
        {
            return employee_name;
        }
        public String getEmployeeRole()
        {
            return employee_role;
        }
        public long getEmployeePhone()
        {
            return employee_phnum;
        }

package com.nsep.user.controller;

import java.io.IOException;



import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Logger;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;




import com.nsep.user.dao.EmployeeDAO;
import com.nsep.user.model.EmployeeBean;

/**
 * Servlet implementation class EmployeeServlet
 */
public class EmployeeServlet extends HttpServlet {
     Logger logger=Logger.getLogger("Logging");
    private static final long serialVersionUID = 1L;


    /**
     * @see HttpServlet#HttpServlet()
     */
    public EmployeeServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }


    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub

        //method to insert value to the database
        EmployeeBean eb= new EmployeeBean();
        EmployeeDAO ed= new EmployeeDAO();
        HttpSession session=request.getSession(true);

        if(request.getParameter("Operation").equals("Insert"))
        {
            String password=request.getParameter("password");
            eb.setPassword(password);
        String employee_name=request.getParameter("emp_name");
        eb.setEmployeeName(employee_name);
        String employee_role=request.getParameter("emp_role");
        eb.setEmployeeRole(employee_role);
        long employee_phnum=Long.parseLong(request.getParameter("emp_ph_num"));
        eb.setEmployeePhone(employee_phnum);

        try
        {
        //ed.insert(eb);

        int employee_id=ed.insert(eb);

        response.sendRedirect("../jsp/Employee_Registration.jsp?id="+employee_id+"&name="+employee_name+"&role="+employee_role+"&phone="+employee_phnum);

        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }





    }

        //method to Retrieve records in database


        else if(request.getParameter("Operation").equals("Update"))
        {if(session.getAttribute("id")==null)
        {
            session.removeAttribute("id");
            session.removeAttribute("name");
            session.removeAttribute("type");
            response.sendRedirect("../Home.jsp?msg=Unauthorized Access");
            logger.info("no session");
        }
        else
        {
            eb.setEmployeeId((Integer)session.getAttribute("id"));//setting the id 
            try {
                eb=ed.select(eb);
                if(eb.getEmployeeName()==null)//If the id does not exist
                {
                    response.sendRedirect("Employee_Invalid.jsp");//hv 2 chnge
                }
                else 
                {
                request.setAttribute("com.nsep.user.model.EmployeeBean", eb);
                //I need to forward the employeeBean  object from here. 
                //session.se

            RequestDispatcher rd = getServletContext().getRequestDispatcher("/jsp/Update_Emp1.jsp");


              rd.forward(request, response);      

                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            //response.sendRedirect("Update_Emp.jsp?name="+employee_name+"&role="+employee_role+"&phone="+employee_phnum);
        }
        }

       // method to update records in database


        else if(request.getParameter("Operation").equals("Update2"))
        {   
            String employee_id = request.getParameter("emp_id");
            eb.setEmployeeId(Integer.parseInt(employee_id));
            String employee_name = request.getParameter("emp_name");
            eb.setEmployeeName(employee_name);
         String employee_phnum=request.getParameter("emp_ph_num");

        eb.setEmployeePhone(Long.parseLong(employee_phnum));
            String employee_role= request.getParameter("emp_role");
            eb.setEmployeeRole(employee_role);

         try {
                eb=ed.update(eb);
                request.setAttribute("com.nsep.user.model.EmployeeBean", eb);
                // to forward the eb object from here. 
                //session.se

                RequestDispatcher rd = getServletContext().getRequestDispatcher("/jsp/Update_Emp.jsp");

                rd.forward(request, response);    


            }catch (NullPointerException nlex)
            {
                nlex.printStackTrace();
            } 
            catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }

        //method to delete record 


        else if(request.getParameter("Operation").equals("Delete"))
        {
            eb.setEmployeeId(Integer.parseInt(request.getParameter("emp_id")));//setting the id 
            try {
                eb=ed.select(eb);
                if(eb.getEmployeeName()==null)//If the id does not exist
                {
                    response.sendRedirect("Employee_Invalid.jsp");
                }
                else
                {
                request.setAttribute("com.nsep.user.model.EmployeeBean", eb);
                //I need to forward the employeebean object from here. 
                //session.se

            RequestDispatcher rd = getServletContext().getRequestDispatcher("/jsp/Delete_Emp2.jsp");


              rd.forward(request, response);      
                }

            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            //response.sendRedirect("Update_Emp.jsp?name="+employee_name+"&role="+employee_role+"&phone="+employee_phnum);
        }



        //*block for updating the the status as Inactive*/


        else if(request.getParameter("Operation").equals("Delete2"))
        {
            String employee_id = request.getParameter("emp_id");
            logger.info(employee_id);
            eb.setEmployeeId(Integer.parseInt(employee_id));
            try {
                eb=ed.delete(eb);
                request.setAttribute("com.nsep.user.model.EmployeeBean", eb);
                RequestDispatcher rd = getServletContext().getRequestDispatcher("/jsp/Delete_Emp.jsp");

                rd.forward(request, response);  
            }
            catch (NullPointerException nlex)
            {
                nlex.printStackTrace();
            } 
            catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

        //method to search a particular record in database



        else if(request.getParameter("Operation").equals("Search"))
        {
            eb.setEmployeeId(Integer.parseInt(request.getParameter("emp_id")));//setting the id in college bean
            try {
                eb=ed.select(eb);
                if(eb.getEmployeeName()==null)//If the id does not exist
                {
                    response.sendRedirect("Employee_Invalid.jsp");//hv 2 chnge
                }
                else
                {
                request.setAttribute("com.nsep.user.model.EmployeeBean", eb);
                // to forward the employeeBean object from here. 
                //session.se

              RequestDispatcher rd = getServletContext().getRequestDispatcher("/jsp/Search_Emp.jsp");

              rd.forward(request, response);      

                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }




        /* block to list all the records*/

        //method to list all the records



        else if(request.getParameter("Operation").equals("searchall"))
        {   
            ArrayList<EmployeeBean> all_records=new ArrayList<EmployeeBean>();
            try {

                all_records=ed.getAllDetails();
                 logger.info(""+all_records.size());
            request.setAttribute("page", 1);
                request.setAttribute("EmployeeBean_list", all_records);
                RequestDispatcher rd = getServletContext().getRequestDispatcher("/jsp/List.jsp?page=1");

                rd.forward(request, response);  
            }
            catch (NullPointerException nlex)
            {
                nlex.printStackTrace();
            } 
            catch (SQLException e) {
            // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    }

DAO EXAMPLE

package com.nsep.user.dao;
import java.sql.*; 
import java.util.ArrayList;
import java.util.logging.Logger;

import com.nsep.user.helper.DataBaseUtil;

import com.nsep.user.model.EmployeeBean;
//import com.sun.corba.se.pept.transport.Connection;

public class EmployeeDAO {
    Connection conn;
    PreparedStatement statement;
    ResultSet rs;
    Logger logger=Logger.getLogger("Logging");
    public ArrayList<EmployeeBean> getAllDetails() throws SQLException
    {
        ArrayList<EmployeeBean> ar_id=new ArrayList<EmployeeBean>();
        try
        {                       
            conn =new DataBaseUtil().getConn();
            statement = conn.prepareStatement("select * from employees where empstatus='ACTIVE'");
            rs=statement.executeQuery();
            while(rs.next())
            {

                EmployeeBean eb=new EmployeeBean();;
                eb.setEmployeeId(rs.getInt(1));
                eb.setEmployeeName(rs.getString(2));
                eb.setEmployeeRole(rs.getString(3));
                eb.setEmployeePhone(rs.getLong(4));
                ar_id.add(eb);

            }

        }
        catch ( SQLException sqlex ) 
        {
            logger.info("SQL Exception !! " +sqlex);            
            sqlex.printStackTrace();
        }
        finally
        {
            statement.close();
            conn.close();
        }
        return ar_id;

    }


    //To insert values...


    public int insert(EmployeeBean eb) throws SQLException
    {   
        int employee_id=0;

        try
        {
            conn =new DataBaseUtil().getConn();
            PreparedStatement stmt=conn.prepareStatement("select seq_Employees.nextVal from dual"); 

            rs=stmt.executeQuery();
            if(rs.next())
            {
                 employee_id=rs.getInt(1);
            }


        // connect to database using DriverManager


        statement = conn.prepareStatement("INSERT INTO Employees values(?,?,?,?,?)");
        statement.setInt(1,employee_id);

        statement.setString(2,eb.getEmployeeName());
        statement.setString(3, eb.getEmployeeRole());
        statement.setLong(4, eb.getEmployeePhone());
        statement.setString(5, "ACTIVE");
        statement.executeUpdate();
        statement = conn.prepareStatement("INSERT INTO Credentials values(?,?,?,?)");
        statement.setInt(1,employee_id);
        statement.setString(2,eb.getPassword());
        statement.setString(3,eb.getEmployeeRole());
        statement.setString(4,eb.getEmployeeName());
        statement.executeUpdate();  


        }

        catch ( SQLException sqlex ) 
        {
            logger.info("SQL Exception !! " +sqlex);            
            sqlex.printStackTrace();
        }
        catch (Exception ex)
        {
            logger.info("Exception has occured "+ex);
        }
        finally
        {
        //statement.close();
        //conn.close();
        //rs.close();
        }
        return employee_id;

    }

    public EmployeeBean select(EmployeeBean eb) throws SQLException
    {
        conn =new DataBaseUtil().getConn();
        statement=conn.prepareStatement("select * from employees where empid=? and empstatus='ACTIVE'");
        statement.setInt(1, eb.getEmployeeId());
        rs=statement.executeQuery();
        if(rs.next())
        {
            //eb.setEmployeeId(rs.getInt(1));
            eb.setEmployeeName(rs.getString(2));
            eb.setEmployeeRole(rs.getString(3));
            eb.setEmployeePhone(rs.getLong(4));
        }

        return eb;
    }

    public EmployeeBean update(  EmployeeBean eb) throws SQLException
    {

        // connect to db using DriverManager
        conn =new DataBaseUtil().getConn();
         EmployeeBean nw_eb=null;
        // Create a Preparedstatement object
        try
        {
        statement=conn.prepareStatement(" update  Employees set empname=?, emprole=?, empphone=? where empid=? and empstatus='ACTIVE'");
        logger.info("Emp ID:"+eb.getEmployeeId());
        statement.setInt(4,eb.getEmployeeId());
        logger.info("setting the no");
        statement.setString(1,eb.getEmployeeName());
        statement.setString(2, eb.getEmployeeRole());
        statement.setLong(3, eb.getEmployeePhone());
        rs=statement.executeQuery();
        nw_eb=eb;
        }
        catch ( SQLException sqlex ) 
        {
            logger.info("SQL Exception !! " +sqlex);            
            sqlex.printStackTrace();
        }
        catch (Exception ex)
        {
            logger.info("Exception has occured "+ex);
        }
        finally
        {
            statement.close();
            conn.close();
            rs.close();
        }
        return nw_eb;
    }

    /*method to delete*/

    public EmployeeBean delete(EmployeeBean eb) throws SQLException
    {

        // connect to db using DriverManager
        conn =new DataBaseUtil().getConn();
        EmployeeBean nw_eb=null;
        // Create a Preparedstatement object
        try
        {   System.out.print("dfdf");
            statement=conn.prepareStatement("select * from Employees  where empid=?");

            statement.setInt(1,eb.getEmployeeId());
            //logger.info("ghgg");
            rs=statement.executeQuery();
            if(rs.next())
            {
                eb.setEmployeeId(rs.getInt(1));
                eb.setEmployeeName(rs.getString(2));
                eb.setEmployeeRole(rs.getString(3));
                eb.setEmployeePhone(rs.getLong(4));
            }
        statement=conn.prepareStatement(" update Employees set Empstatus='Inactive' where empid=?");
        //logger.info(eb.getCollegeID());
        statement.setInt(1,eb.getEmployeeId());

        rs=statement.executeQuery();

        statement=conn.prepareStatement("delete from credentials where userid=? ");
        statement.setInt(1,eb.getEmployeeId());
        //logger.info(cb.getCollegeID());
        rs=statement.executeQuery();
        nw_eb=eb;
        }
        catch ( SQLException sqlex ) 
        {
            logger.info("SQL Exception !! " +sqlex);            
            sqlex.printStackTrace();
        }
        catch (Exception ex)
        {
            logger.info("Exception has occured "+ex);
        }
        finally
        {
            statement.close();
            conn.close();
            rs.close();
        }
        return nw_eb;
    }

}