1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Jdbc

Discussion in 'Java' started by nik_unique, Dec 8, 2011.

  1. nik_unique

    nik_unique New Member

    Code:
    package com.utils;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Connection ;
    
    public class ConnectionManager 
    {
      Connection con=null;
      
      public Connection getConnection()
      {
      try{
         String serverName = "172.24.137.30";
      String portNumber = "1521";
      String sid = "ora10G";
      String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
         String username =  "e526938"; 
      String password = "ffGVfacum"; 
      
         try
      {  
      Class.forName("oracle.jdbc.driver.OracleDriver");
      con = DriverManager.getConnection(url,username,password); 
      }
     
      catch (SQLException ex)
      {
         ex.printStackTrace();
      }
    }
      catch(ClassNotFoundException e)
      {
       e.printStackTrace();
      }
      return con;
    }
    }
     
    Last edited by a moderator: Dec 8, 2011
  2. nik_unique

    nik_unique New Member

    Servlet

    Code:
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
    
    {
    		
    if(null !=request.getParameter("check"))
    			{
    				
    			String act = request.getParameter("check");
    
          if (act.equalsIgnoreCase("inreq"))
    			{
    
    			
    			
    			response.setContentType("text/html");
    			
    			
    			StudId=Integer.parseInt(request.getParameter("Studentid"));
    			AccId=Integer.parseInt(request.getParameter("Accountid"));
    			AccNum=Integer.parseInt(request.getParameter("AccNum"));
    			curntBal=Integer.parseInt(request.getParameter("Currbalance"));
    			ystudy=Integer.parseInt(request.getParameter("study"));
    			comit_amt=Integer.parseInt(request.getParameter("Committedamnt"));
    			
    		
    			Bean b=new Bean();
    			b.setStudentId(StudId);
    			b.setStdAccId(AccId);
    			b.setaccnum(AccNum);
    			b.setystudy(ystudy);
    			b.setcommitamnt(comit_amt);
    			b.setCurntBal(curntBal);
    	
    			Dao create=new Dao();
    			flag=create.insertStudent(b);
    			
    			if(flag==1)
    			{
    			request.setAttribute("stuList",create.studentId);
    			request.setAttribute("accntid",create.accntId);
    			request.setAttribute("accNm",create.accnum);
    			request.setAttribute("curB",create.curntBal);
    			
    			request.setAttribute("eDate",create.curyr);
    			request.setAttribute("cmtAmt",create.commitAmt);
    
    			getServletConfig().getServletContext().getRequestDispatcher("/view_student.jsp").forward(request,response);
    			}
    			else
    				getServletConfig().getServletContext().getRequestDispatcher("/Error.jsp").forward(request,response);
    				
    			}
    }
     
    Last edited by a moderator: Dec 9, 2011
  3. nik_unique

    nik_unique New Member

    Dao

    Code:
    public int insertStudent(Bean b)
    	{
    		//This method is used for inserting values into the student account database
    		PropertyConfigurator.configure(GenericConstants.LOGPATH);
    		Statement statement=null;
    		
    		Connection con =c.getConnection();
    		logger.error("["+DateUtil.getSysDateTime()+"]::Connection Established Successfully :: CreateAccount");
    
    		logger.debug("["+DateUtil.getSysDateTime()+"]::Debugging CreateAccount "); 
    
    		logger.info("["+DateUtil.getSysDateTime()+"]::This class has the functionality of creating both Student and Nsep Account"); 
    
    		logger.warn("["+DateUtil.getSysDateTime()+"]::Inserting Student details"); 
    		
    		
    		
    		int flag=1;
    		
    		
    		try 
    		{
    			
    			statement= con.createStatement();
    			statement.executeUpdate("Insert into Student1 values(" + b.studentId + "," + b.stdAccId+ "," + b.accnum+ "," + b.currentBal + ","+b.ystudy+","+b.commitAmt+")");
    			
    		
    			
    			ResultSet rs = statement.executeQuery("SELECT * FROM Student1" );
    			
    			
    			
    			while( rs.next())
    			{		
    			
    					
    				studentId.add(rs.getInt(1));
    				accntId.add(rs.getInt(2));
    				accnum.add(rs.getInt(3));
    				 curntBal.add(rs.getInt(4));
    				 curyr.add(rs.getInt(5));
    				 
    				 commitAmt.add(rs.getInt(6));
    				
    				
    			}
    			
    			
    		}
    			
    		catch (SQLException e)
    		{
    			flag=0;
    			logger.error("SQLException in CreateAccount :: "+e);
    
    		}finally{
    			try {
    				con.close();
    			} catch (SQLException e) {
    				
    				logger.error("SQLException Unable to close connection in CreateAccount :: "+e);
    				flag=0;
    			}
    		}
    
    			return flag;
    	}
     
    Last edited by a moderator: Dec 9, 2011
  4. nik_unique

    nik_unique New Member

    Viewing from Database Jsp

    Code:
    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
        pageEncoding="ISO-8859-1"%>
        <%@ page import="java.util.*" %>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Insert title here</title>
    </head>
    <body>
    <table border="1">
    <tr>
    <td>name</td>
    <td>department</td>
    <td>Employee Id</td>
    <td>salary</td>
    </tr>
    <%
    ArrayList<Integer> id = (ArrayList)request.getAttribute("empid");
    ArrayList<Integer> salary = (ArrayList)request.getAttribute("salary");
    ArrayList<Integer> name = (ArrayList)request.getAttribute("name1");
    ArrayList<Integer> dept = (ArrayList)request.getAttribute("dep");
    %>
    <%
    if(id.size()>0)
    {
    
    	for(int i=0;i<id.size();i++)
    	{
    		%>
    		<tr>
    		<td><%=name.get(i)%>></td>
    		<td><%=dept.get(i) %></td>
    		<td><%=id.get(i) %></td>
    		<td><%=salary%></td>
    		</tr>
    		<%
       }
    }
       else { %>
    		<tr>
    		<td>No Entries Found</td>
    		</tr>
    		<%} %>
    		
    </table>
    
    </body>
    </html>
     
    Last edited by a moderator: Dec 9, 2011
  5. nik_unique

    nik_unique New Member

    Registration Jsp

    Code:
    <%@ page language="java" contentType="text/html; charset=ISO-8859-1"
        pageEncoding="ISO-8859-1"%>
    <!DOCTYPE html PUBLIC >
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Insert title here</title>
    </head>
    <body>
    <h1><centre>Employee Registration</centre></h1>
    <form name="emp" method="post"  action="ControlServlet">
    Enter Employee Name:&nbsp;&nbsp;<input type="text" name="name" size="20"><br></br>
    Enter Department:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="dept" size="20"><br></br>
    Enter Employee Id:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="id" size="20"><br></br>
    
    Enter Salary:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<select name="salary" >
     <option value=10000>10,000</option>
      <option value=20000>20,000</option>
      <option value=30000>30,000</option><br></br>
    </select>
      
      <p><input id="inputsubmit1"  type="submit" value="Submit" color="#00FFFF"></input></p>
      <p><input  type="hidden" value="reg" name="check"></input></p>
        </form>
    
    </body>
    </html>
     
    Last edited by a moderator: Dec 9, 2011
  6. ankita.r15

    ankita.r15 New Member

    hey nick u can use these files also
    jsp

    Code:
    				<%
    Iterator itr;
    ArrayList viewdata=(ArrayList)(session.getAttribute("empAllList"));
    %>
    
    <table border="2">
    
    <tr>
    <td>Employee Id</td>
    <td>Name</td>
    <td>Role</td>
    <td>Phone</td>
    </tr>
    
     <% for(itr = viewdata.iterator(); itr.hasNext();)  {
                            %><tr><%EmpRegBean b=(EmpRegBean)itr.next();%>
                            <td><%=b.getEmployeeid() %>
                            </td>
                            <td><%=b.getName() %>
                            </td>
                            <td><%=b.getRole() %>
                            </td>
                            <td><%=b.getPhone() %>
                            </td>
                            </tr><% } %>
    </table>
     
    Last edited by a moderator: Dec 9, 2011
  7. ankita.r15

    ankita.r15 New Member

    u can use this one also


    Code:
    public class Dao {
    	
    	static ConnectionManager c = new ConnectionManager();
    	static Connection con ;
    	static Statement st;
    	   static ResultSet rs = null;  
    	   static Logger logger = Logger.getLogger(Dao.class);
    	
    	   
    	   
    
    	   
    	public static ArrayList<EmpRegBean> Execute1(EmpRegBean empbean) {
    		PropertyConfigurator.configure(GenericConstants.LOGPATH);
    	
    		st=null;    
    
    		
    		String name = empbean.getName();    
    	      String role = empbean.getRole();  
    	      String phone = empbean.getPhone();  
    
    		String insertQuery =
    	            "insert into employees values( eid.nextval,'"+ name +"','"+role+"' , '"+phone+ "',eid.nextval,pid.nextval)";
    		
    		ArrayList<EmpRegBean> empRegList = new ArrayList<EmpRegBean>();
    		
    		String detailQuery = "select * from employees where employeeid = (select max(employeeid) from employees where ename = '"+name+"' group by ename)";
    		logger.error("["+DateUtil.getSysDateTime()+"]::Connection Established Successfully :: Dao");
    		logger.debug("["+DateUtil.getSysDateTime()+"]::Debugging Employee Registration - Execute1 Class"); 
    		logger.info("["+DateUtil.getSysDateTime()+"]::Registering Employee & Displaying Registered Details"); 
    		 		
    		 try 
    		   {
    		      //connect to DB 
    			 con = c.getConnection();
    			 st = con.createStatement();
    			 
    		     st.executeQuery(insertQuery);
    		     
    		      
    		      rs = st.executeQuery(detailQuery);
    		      logger.warn("["+DateUtil.getSysDateTime()+"]::Registering Employee - Execute1 Class"); 
    		      while (rs.next()) 
    		      {
    		       EmpRegBean b =new EmpRegBean();
    		       String id = rs.getString("employeeid");
    		       String name1 = rs.getString("ename");
    		       String rol = rs.getString("erole");
    		       String ph= rs.getString("ephone");
    		       String uname= rs.getString("username");
    		       String p= rs.getString("pwd");
    
    
    		       b.setEmployeeid(id);
    		       b.setRole(rol);
    		       b.setPhone(ph);
    		       b.setName(name1);
    		       b.setUserName(uname);
    		       b.setPwd(p);
    
    		       empRegList.add(b);
    		      }
    		      logger.warn("["+DateUtil.getSysDateTime()+"]::Displaying Registered Details"); 
    		   } 
    		 
    
    		 catch (SQLException e) {
    				logger.error("SQLException in Dao :: "+e);
    
    			}finally{
    				try {
    					con.close();
    				} catch (SQLException e) {
    					// TODO Auto-generated catch block
    					logger.error("SQLException Unable to close connection in Dao :: "+e);
    				}
    			}
    	
    
    		return empRegList;
    	
    }
    	
    	
    	
    	  
    	public  static ArrayList<EmpRegBean> EmpSearch(String empId)
    	{
    		PropertyConfigurator.configure(GenericConstants.LOGPATH);
    		ArrayList<EmpRegBean> empViewList = new ArrayList<EmpRegBean>();
    		  		
    		st=null;
    
    			
    	      String searchquery ="select employeeid,ename,erole,ephone from employees where employeeid='"+empId+"'";
    	      String queryfinish="commit"; 
    	      logger.error("["+DateUtil.getSysDateTime()+"]::Connection Established Successfully :: Dao");
    			logger.debug("["+DateUtil.getSysDateTime()+"]::Debugging - EmpSearch Class"); 
    			logger.info("["+DateUtil.getSysDateTime()+"]::Searching Employee Details from database"); 
    
    			
    	   try 
    	   {
    	     
    		 
    		 con = c.getConnection();
    		 
    	      st=con.createStatement();
    	      
    	      rs = st.executeQuery(searchquery);
    	      logger.warn("["+DateUtil.getSysDateTime()+"]::Searching Employee Details from database - EmpSearch Class"); 
    	      
    	      while (rs.next()) 
    	      {
    	       EmpRegBean empbean=new EmpRegBean();
    	       String id1 = rs.getString("employeeid");
    	       String sname = rs.getString("ename");
    	       String rol = rs.getString("erole");
    	       String ph= rs.getString("ephone");
    	       
    	      
    	      
    	       empbean.setEmployeeid(id1);
    	       empbean.setRole(rol);
    	       empbean.setPhone(ph);
    	       empbean.setName(sname);
    	       empViewList.add(empbean);
    	        
    	       
    	      }
    	      logger.warn("["+DateUtil.getSysDateTime()+"]::Displaying Employee Details"); 
    	      rs = st.executeQuery(queryfinish);	
    	       
    	      }
    	   catch (SQLException e) {
    			logger.error("SQLException in Dao :: "+e);
    
    		}finally{
    			try {
    				con.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				logger.error("SQLException Unable to close connection in Dao :: "+e);
    			}
    		}
    
    	return empViewList;
    }
    	
    	public static EmpRegBean EmpUpdate(EmpRegBean empbean) {
    		PropertyConfigurator.configure(GenericConstants.LOGPATH);
    
    		
    		st=null;
    		logger.error("["+DateUtil.getSysDateTime()+"]::Connection Established Successfully :: Dao");
    		logger.debug("["+DateUtil.getSysDateTime()+"]::Debugging EmpUpdate Class"); 
    		logger.info("["+DateUtil.getSysDateTime()+"]::Updating Employee Details in database"); 
    
    	try {
    		Statement st = null;    
    		con = c.getConnection();
    	    
    	    st=con.createStatement();
    	    
    	    logger.warn("["+DateUtil.getSysDateTime()+"]::Updating Employee Details in database - EmpUpdate Class");
        String name = empbean.getName();    
        String id = empbean.getEmployeeid(); 
        String role = empbean.getRole();  
        String pno = empbean.getPhone();    
    	   
       
    	String updateQuery =
              "update employees set erole='"+role+"',ephone='"+pno+"',ename='"+name+"' where employeeid="+id+"";
    	
    
         st.executeUpdate(updateQuery);
         
    		
    	}
    	catch (SQLException e) {
    		logger.error("SQLException in Dao :: "+e);
    
    	}finally{
    		try {
    			con.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			logger.error("SQLException Unable to close connection in Dao :: "+e);
    		}
    	}
    
    	return null;
    	
    }
    	
    	
    	public  static List<EmpRegBean> EmpViewAll()
    	{
    		PropertyConfigurator.configure(GenericConstants.LOGPATH);
    		ArrayList<EmpRegBean> empAllList = new ArrayList<EmpRegBean>();
    		  		
    		st=null;
    
    		
    	      String viewquery ="select employeeid,ename,erole,ephone from employees order by 1";
    	      String queryfinish="commit"; 
    	      logger.error("["+DateUtil.getSysDateTime()+"]::Connection Established Successfully :: Dao");
    			logger.debug("["+DateUtil.getSysDateTime()+"]::Debugging EmpViewAll Class"); 
    			logger.info("["+DateUtil.getSysDateTime()+"]::Dispalying All Employees Details"); 
    			
    			
    			
    	   try 
    	   {
    	     
    		 
    		 con = c.getConnection();
    		 
    	      st=con.createStatement();
    	      
    	      rs = st.executeQuery(viewquery);
    	      logger.warn("["+DateUtil.getSysDateTime()+"]::Getting Employee details from the Database - EmpViewAll Class"); 
    	      while (rs.next()) 
    	      {
    	    	  
    	       EmpRegBean empbean=new EmpRegBean();
    	       String id1 = rs.getString("employeeid");
    	       String sname = rs.getString("ename");
    	       String rol = rs.getString("erole");
    	       String ph= rs.getString("ephone");
    	       
    	      
    	        	
    	       empbean.setEmployeeid(id1);
    	       empbean.setRole(rol);
    	       empbean.setPhone(ph);
    	       empbean.setName(sname);
    	       empAllList.add(empbean);
    	        
    	       
    	      }
    	      logger.warn("["+DateUtil.getSysDateTime()+"]::Dispalying All Employees Details"); 
    	      rs = st.executeQuery(queryfinish);	
    	       
    	      }
    	   catch (SQLException e) {
    			logger.error("SQLException in Dao :: "+e);
    
    		}finally{
    			try {
    				con.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				logger.error("SQLException Unable to close connection in Dao :: "+e);
    			}
    		}
    
    	return empAllList;
    	
    }
    	
    		public static EmpRegBean EmpDelete(EmpRegBean empbean) {
    		
    		PropertyConfigurator.configure(GenericConstants.LOGPATH);
    
    		st=null;
    		String empId = empbean.getEmployeeid();
    		String deletequery = "delete from employees where employeeid ="+empId+"";	
    		String queryfinish="commit"; 
    		logger.error("["+DateUtil.getSysDateTime()+"]::Connection Established Successfully :: Dao");
    		logger.debug("["+DateUtil.getSysDateTime()+"]::Sample debug message"); 
    		logger.info("["+DateUtil.getSysDateTime()+"]::Deleting Employee Details from Database"); 
    
    	try {
    		 con = c.getConnection();
    			
    	      st=con.createStatement();
    	      logger.warn("["+DateUtil.getSysDateTime()+"]::Deleting Employee details from the Database - EmpDelete Class"); 
    
    
    
    		 
    		st.executeQuery(deletequery);
    		
    		st.executeQuery(queryfinish);
    		}
    	catch (SQLException e) {
    		logger.error("SQLException in Dao :: "+e);
    
    	}finally{
    		try {
    			con.close();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			logger.error("SQLException Unable to close connection in Dao :: "+e);
    		}
    	}
    
    	return empbean;
    	
    }
    	
    	
    }
     
    Last edited by a moderator: Dec 9, 2011
  8. ankita.r15

    ankita.r15 New Member

    this one i better

    Code:
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    		// TODO Auto-generated method stub
    		try
    		{
    			EmpRegBean empbean;
    			Transactionbean userT;
    			
    
    			
    			if(null !=request.getParameter("empcheck"))
    		     {
    		      if(request.getParameter("empcheck").equalsIgnoreCase("regemp")) {
    
    		    	  ArrayList<EmpRegBean> empRegList=new ArrayList<EmpRegBean>();
    			 empbean = new EmpRegBean();
    			 empbean.setName(request.getParameter("name"));
    			 empbean.setRole(request.getParameter("role"));
    			 empbean.setPhone(request.getParameter("phone"));
    			 empRegList=(ArrayList<EmpRegBean>) Dao.Execute1(empbean);
    			 HttpSession session = request.getSession(true);
    	    	 session.setAttribute("empRegList",empRegList);
    		     response.sendRedirect("EmpRegSuccess.jsp");
    
    		      }
    		      
    		    
    		     
    		      else if(request.getParameter("empcheck").equalsIgnoreCase("searchemp")) 
    		      {
    		    	  
    		    	  ArrayList<EmpRegBean> empViewList=new ArrayList<EmpRegBean>();
    		    	  String empId=request.getParameter("employeeid");
    		    	  empViewList= Dao.EmpSearch(empId);
    		    	  HttpSession session = request.getSession(true);
    		    	  if (empViewList.isEmpty()){
    		    		  
    		    		  RequestDispatcher rd = getServletContext().getRequestDispatcher("/EmpError.jsp");
    						rd.forward(request, response); 
    		    	 
    		    	  }
    		    	  else {
    		    		  session.setAttribute("empViewList",empViewList);
    			 		  response.sendRedirect("EmpEditDetails.jsp");
    		    	  }
    		     }
    		     
    		      else if(request.getParameter("empcheck").equalsIgnoreCase("editemp")) {	 
    	 		 empbean = new EmpRegBean();
    	 	     empbean.setName(request.getParameter("name"));
    	 	     empbean.setEmployeeid(request.getParameter("employeeid"));
    	 	     empbean.setRole(request.getParameter("role"));
    	 	     empbean.setPhone(request.getParameter("phone"));	 	 
    	 	     response.sendRedirect("EmpUpdate.jsp");
    	 	      }
    
    		      else if(request.getParameter("empcheck").equalsIgnoreCase("updateemp")) {	 
    	 		 empbean = new EmpRegBean();
    	 	     empbean.setName(request.getParameter("name"));
    	 	     empbean.setEmployeeid(request.getParameter("employeeid"));
    	 	     empbean.setRole(request.getParameter("role"));
    	 	     empbean.setPhone(request.getParameter("phone"));
    
    	 	     empbean=Dao.EmpUpdate(empbean);
    	 	 
    	 	       response.sendRedirect("EmpUpdateSuccess.jsp");
    	 	      }
    
    		      else if(request.getParameter("empcheck").equalsIgnoreCase("delsearchemp")) 
    		      {
    		    	  
    		    	  ArrayList<EmpRegBean> empViewList=new ArrayList<EmpRegBean>();
    		    	  String empId=request.getParameter("employeeid");
    		    	  empViewList= Dao.EmpSearch(empId);
    		    	  HttpSession session = request.getSession(true);
    		    	  if (empViewList.isEmpty()){
    		    		  
    		    		  RequestDispatcher rd = getServletContext().getRequestDispatcher("/EmpError.jsp");
    						rd.forward(request, response); 
    		    	 
    		    	  }
    		    	  else {
    		    		  session.setAttribute("empViewList",empViewList);
    			 		  response.sendRedirect("EmpDelDetails.jsp");
    		    	  }
    		    	  
    		     }
    
    		      else if(request.getParameter("empcheck").equalsIgnoreCase("delemp")) {	
    		    	  empbean = new EmpRegBean();
    		    	  empbean.setEmployeeid(request.getParameter("employeeid"));
    		    	  empbean = Dao.EmpDelete(empbean);		    	  
    		    	  response.sendRedirect("EmpDelSuccess.jsp");
    		      }
    
    		      else if(request.getParameter("empcheck").equalsIgnoreCase("viewallemp")) 
    		      {
    		    	  
    		    	  ArrayList<EmpRegBean> empAllList=new ArrayList<EmpRegBean>();
    		    	  empAllList= (ArrayList<EmpRegBean>) Dao.EmpViewAll();
    		    	  HttpSession session = request.getSession(true);
    		    	  session.setAttribute("empAllList",empAllList);
    		 		  response.sendRedirect("EmpViewAllDetails.jsp");
    		    
    		     }
    		      else{
    		    	   
    		    	  RequestDispatcher rd = getServletContext().getRequestDispatcher("/EmpError.jsp");
    					rd.forward(request, response);         
    			    	}
    		      }
     
    Last edited by a moderator: Dec 9, 2011
  9. shabbir

    shabbir Administrator Staff Member

Share This Page