package com.cg.ems.dao;
import com.cg.ems.dto.Employee;
import com.cg.ems.exception.EmployeeException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.cg.ems.util.DbUtil;


public class EmployeeDao implements IEmployeeDao
{
	private static final Logger myLog=Logger.getLogger(EmployeeDao.class);
	static Connection conn=null;
	static PreparedStatement pstm=null;
	@Override
	public int addEmployee(Employee employee) throws EmployeeException
	{
		 conn=DbUtil.getConnection();
		int employeeId= getId();
		int status=0;
		String query="INSERT INTO EmployeeDB VALUES(?,?)";
		try{
			pstm=conn.prepareStatement(query);
			pstm.setInt(1,employeeId);
			pstm.setString(2,employee.getName());

			status=pstm.executeUpdate();
			if(status==1)
			System.out.println("Quer Executed");
			myLog.info("Data Inserted..."+employeeId);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
			try {
				throw new EmployeeException("Problem in Insert");
			} catch (EmployeeException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		finally{
			try{
				pstm.close();
				conn.close();
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		return employeeId;
	}
	@Override
	public List<Employee> showall() throws EmployeeException
	{
		List<Employee>myList=new ArrayList<Employee>();
		conn=DbUtil.getConnection();
		String query="SELECT id,name FROM EmployeeDB";
		try {
			pstm=conn.prepareStatement(query);
			ResultSet res=pstm.executeQuery();
			while(res.next())
			{
				Employee emp=new Employee();
				emp.setId(res.getInt("id"));
				emp.setName(res.getString("name"));

				myList.add(emp);
			}
			}
		catch (SQLException e)
			{
			e.printStackTrace();
			throw new EmployeeException("Problem in show...");
			}
		finally
		{
			try{
				pstm.close();
				conn.close();
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		return myList;
	}
	@Override
	public Employee searchEmployee(int id) throws EmployeeException
	{
		Employee eSearch=null;
		try {
			conn=DbUtil.getConnection();
			String querythree="SELECT id,name FROM EmployeeDB WHERE id=?";
			pstm=conn.prepareStatement(querythree);
			pstm.setInt(1,id);
			ResultSet resOne=pstm.executeQuery();
			while(resOne.next())
			{
				 eSearch=new Employee();
				 eSearch.setId(resOne.getInt("id"));
				eSearch.setName(resOne.getString("name"));
			}

		}
		catch (EmployeeException | SQLException e)
		{
			e.printStackTrace();
			throw new EmployeeException("Problem in Search...");
		}
		finally
		{
			try{
				pstm.close();
				conn.close();
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		return eSearch;
	}
	public void removeEmployee(int id) throws EmployeeException
	{
		Employee eSearch=null;
		try {
			conn=DbUtil.getConnection();
			String querythree="Delete FROM EmployeeDB WHERE id=?";
			pstm=conn.prepareStatement(querythree);
			pstm.setInt(1,id);
			ResultSet resOne=pstm.executeQuery();
			System.out.println("Employee Deleted");

		}
		catch (EmployeeException | SQLException e)
		{
			e.printStackTrace();
			throw new EmployeeException("Problem in Removing Employee...");
		}
		finally
		{
			try{
				pstm.close();
				conn.close();
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	public static int getId() throws EmployeeException
	{
		int empId=0;
	try {
		conn=DbUtil.getConnection();
		String queryFive="SELECT emp_id_seq.nextval FROM DUAL";
		pstm=conn.prepareStatement(queryFive);
		ResultSet resTwo=pstm.executeQuery();
		while(resTwo.next())
		{
		empId=resTwo.getInt(1);
		}
	    }
	catch (EmployeeException | SQLException e)
	    {
		// TODO Auto-generated catch block
		e.printStackTrace();
		throw new EmployeeException("Problem in Getting Id");
	    }
		return empId;

	}
}
==============================================================================
package com.cg.ems.dao;
import java.util.List;

import com.cg.ems.dto.Employee;
import com.cg.ems.exception.EmployeeException;



public interface IEmployeeDao
{
	public int addEmployee(Employee employee) throws EmployeeException;

	public List<Employee> showall() throws EmployeeException;

	public Employee searchEmployee(int id) throws EmployeeException;

	public void removeEmployee(int id) throws EmployeeException;
}
==================================================================================
package com.cg.ems.dto;


	public class Employee
	{
		//Variable Declaration
		private int id;
		private String name;

		//Getters and Setters
		public int getId()
		{
			return id;
		}
		public void setId(int id)
		{
			this.id = id;
		}
		public String getName()
		{
			return name;
		}
		public void setName(String name)
		{
			this.name = name;
		}

		//Constructor Block
		public Employee()
		{
			super();
		}
		public Employee(int id, String name)
		{
			super();
			this.id = id;
			this.name = name;
		}

		//Override Methods Do Not Touch
		@Override
		public int hashCode() {
			final int prime = 31;
			int result = 1;
			result = prime * result + id;
			result = prime * result + ((name == null) ? 0 : name.hashCode());
			return result;
		}
		@Override
		public boolean equals(Object obj) {
			if (this == obj)
				return true;
			if (obj == null)
				return false;
			if (getClass() != obj.getClass())
				return false;
			Employee other = (Employee) obj;
			if (id != other.id)
				return false;
			if (name == null) {
				if (other.name != null)
					return false;
			} else if (!name.equals(other.name))
				return false;
			return true;
		}
		@Override
		public String toString() {
			return "Employee [id=" + id + ", name=" + name + "]";
		}
	}
  ==================================================================================
  package com.cg.ems.exception;


public class EmployeeException extends Exception
{
	public EmployeeException()
	{
		super();
	}

	public EmployeeException(String msg) {
		super(msg);
	}

}
======================================================================================
package com.cg.ems.service;
import java.util.List;

import com.cg.ems.dao.EmployeeDao;
import com.cg.ems.dao.IEmployeeDao;
import com.cg.ems.dto.Employee;
import com.cg.ems.exception.EmployeeException;



public class EmployeeService implements  IEmployeeService
{
	IEmployeeDao empDao=new EmployeeDao();
	@Override
	public int addEmployee(Employee employee) throws EmployeeException
	{
		return empDao.addEmployee(employee);
	}
	@Override
	public List<Employee> showall() throws EmployeeException
	{
		return empDao.showall();
	}
	@Override
	public Employee searchEmployee(int id) throws EmployeeException
	{
		return empDao.searchEmployee(id);
	}
	@Override
	public void removeEmployee(int id) throws EmployeeException
	{
		empDao.removeEmployee(id);
	}

}
=====================================================================================
package com.cg.ems.service;
import java.util.List;

import com.cg.ems.dto.Employee;
import com.cg.ems.exception.EmployeeException;



public interface IEmployeeService
{
	public int addEmployee(Employee employee) throws EmployeeException;

	public List<Employee> showall() throws EmployeeException;

	public Employee searchEmployee(int id) throws EmployeeException;

	public void removeEmployee(int id) throws EmployeeException;
}
================================================================================
package com.cg.ems.ui;


import java.util.List;
import java.util.Scanner;

import com.cg.ems.dto.Employee;
import com.cg.ems.exception.EmployeeException;
import com.cg.ems.service.EmployeeService;
import com.cg.ems.service.IEmployeeService;

public class EmployeeUserInterface
{

	public static void main(String[] args) throws EmployeeException
	{
		IEmployeeService empservice=new EmployeeService();
		int choice=0,id=0; Employee mySearchEmp=null;
		Scanner object = new Scanner(System.in);
	do{
		printDetail();
		System.out.println("Enter your Choice");
		choice=object.nextInt();
		switch(choice)
		{
		case 1://Add Employee
		int msg=0;
		System.out.println("Add Employee Case");
		//System.out.println("Enter Id:");
		//int empId=object.nextInt();
		System.out.println("Enter Name:");
		String empName=object.next();



	//Employee Object Declare and Initialize
			Employee emp=new Employee();
			emp.setName(empName);

	//Calling Service Layer
			msg = empservice.addEmployee(emp);
			if(msg==0)
			{
			System.out.println("Data not Inserted");
			}
			else
			{
			System.out.println("Data Inserted Product Id is:"+msg);
			}
			break;

		case 2://show all Employee
			List<Employee> myEmp=null;
			try {
				myEmp = empservice.showall();
			} catch (EmployeeException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				System.out.println(e.getMessage());
			}
			for (Employee emp1 : myEmp)
			{
			System.out.println("Id is"+emp1.getId());
			System.out.println("Name is"+emp1.getName());
			}
			break;
		case 3://Search
			System.out.println("Enter Product Id.");
			 id=object.nextInt();
			 mySearchEmp=null;
			try{
			mySearchEmp=empservice.searchEmployee(id);
			}
			catch(EmployeeException e)
			{
				e.printStackTrace();
				System.out.println(e.getMessage());
			}
			//System.out.println("Id is"+mySearchEmp.getId());
			System.out.println("Name is"+mySearchEmp.getName());
			break;
		case 4://Remove
			System.out.println("Enter Employee Id.");
			id=object.nextInt();
			mySearchEmp=null;
			try{
				empservice.removeEmployee(id);
				}
				catch(EmployeeException e)
				{
					e.printStackTrace();
					System.out.println(e.getMessage());
				}
			break;
		case 5://Exit
			System.exit(0);
			break;
		default:
		System.out.println("Wrong Input");
		break;
		}
	}while(choice!=5);
	}
	public static void printDetail()
	{
	System.out.println("********************************");
	System.out.println("1.Add Employee");
	System.out.println("2.Show Employee");
	System.out.println("3.Search Product");
	System.out.println("4.Remove Product");
	System.out.println("5.Exit");
	System.out.println("*********************************");
	}
}
=======================================================================================
package com.cg.ems.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.log4j.Logger;
import com.cg.ems.exception.EmployeeException;

public class DbUtil {
	public static Connection conn=null;
	private static final Logger mylogger = Logger.getLogger(DbUtil.class);
	public static Connection getConnection() throws EmployeeException

	{
		FileInputStream fileRead;
		try {
			fileRead = new FileInputStream("oracle.properties");
			Properties pros =new Properties();
			pros.load(fileRead);	//Load Properties File

			String driver=pros.getProperty("oracle.driver");
			String url=pros.getProperty("oracle.url");
			String uname=pros.getProperty("oracle.username");
			String upass=pros.getProperty("oracle.password");

//Load The Driver
			Class.forName(driver);
//Making Connection
			conn=DriverManager.getConnection(url, uname, upass);
			mylogger.info("Connection Established...");
		}
		catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Connection not Established......."+e);
			throw new EmployeeException("Connection Not Established......");
		}
		return conn;
	}

}
=====================================================================================
package JunitTest;
import static org.junit.Assert.*;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.cg.ems.dao.EmployeeDao;
import com.cg.ems.dto.Employee;
import com.cg.ems.exception.EmployeeException;
public class EmployeeDaoTest
{
	Employee emp=null;
	EmployeeDao empDao=null;
	@Before
	public void callBefore(){
		emp=new Employee();
		emp.setName("aaaa");
		empDao=new EmployeeDao();
	}
	@Test
	public void myTestCase() throws EmployeeException{
		assertEquals(106,empDao.addEmployee(emp));

	}
	@After
	public void callAfter(){

	}

}
======================================================================
log4j.rootLogger=DEBUG, file

log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=application.log
log4j.appender.file.MaxFileSize=5MB
log4j.appender.file.MaxBackupIndex=10
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n
================================================================================================
oracle.driver=oracle.jdbc.driver.OracleDriver
oracle.url=jdbc:oracle:thin:@localhost:1521:xe
oracle.username=system
oracle.password=Capgemini123
====================================================================================================