package com.sist.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.sist.dao.*;

public class EmpDAO {
	private Connection conn;
	private PreparedStatement ps;
	private final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";

	public EmpDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");

		} catch (Exception ex) {
			System.out.println("EmpDAO(): " + ex.getMessage());
		}
	}

	public void getConnection() {
		try {
			conn = DriverManager.getConnection(URL, "scott", "tiger");
		} catch (Exception ex) {
			System.out.println("getConnection(): " + ex.getMessage());
		}
	}

	public void disConnection() {
		try {
			if (ps != null)
				ps.close();
			if (conn != null)
				conn.close();
		} catch (Exception ex) {
			System.out.println("disConnection(): " + ex.getMessage());
		}
	}

	public ArrayList<EmpVO> empALLdata() {
		ArrayList<EmpVO> list = new ArrayList<>();
		try {
			getConnection();
			String sql = "SELECT ename, job, hiredate, dname, loc, grade " + "FROM dept, emp,salgrade "
					+ "WHERE emp.deptno=dept.deptno " +"AND sal BETWEEN losal AND hisal " + "ORDER BY GRADE DESC";
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				EmpVO vo = new EmpVO();
				vo.setEname(rs.getString(1));
				vo.setJob(rs.getString(2));
				vo.setHiredate(rs.getDate(3));
				vo.getDvo().setDname(rs.getString(4));
				vo.getDvo().setLoc(rs.getString(5));
				vo.getSvo().setGrade(rs.getInt(6));
				list.add(vo);
			}
			rs.close();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			disConnection();
		}
		return list;
	}
}