public class EmployeeSkillDAO
{
	private Connection conn;

	public Connection getConnection()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/pd1";
			String userName = "root";
			String password = "root";
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection successful!");
		}

		catch (ClassNotFoundException | SQLException e)
		{
			e.printStackTrace();
		}

		return conn;
	}

	public void insertEmployeeSkill(RetrieveUserInput rui)
	{
        //Get DB Connection
        DBUtil util = new DBUtil();
        Connection conn = util.getConnection();

        //create update statement
        String sql = "insert into employeeSkill(skillId, empId, rating)" + "values(?, ?, ?)";

        //use prepared statement
        try {
              PreparedStatement ps = conn.prepareStatement(sql);
              ps.setInt(1, rui.getSkillId());
              ps.setString(2, rui.getEmpName());
              ps.setDouble(3, rui.getSkillRating());
              conn.setAutoCommit(false);
              ps.executeUpdate();
              conn.commit();
        } catch (SQLException e)

        {
              try
              {
	               conn.rollback();
	               e.printStackTrace();
	          }

              catch (SQLException e1)
              {
            	  System.err.println(e1.getMessage());
              }
        }

        finally
        {
          try
          {
               conn.close();
          }

          catch (SQLException e)
          {
               System.err.println(e.getMessage());
          }
        }
  }

	public static EmployeeSkill getEmployeeBySkill(String skill)
	{
		EmployeeSkill eSkill = null;

		DBUtil util = new DBUtil();
		Connection conn = util.getConnection();
		String sql = "select id, Employee, rating from EmployeeSkill" +
		"where employeeId = ?";

		try
		{
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, skill);
			ResultSet rs = ps.executeQuery();

			while(rs.next())
			{
				int eId = rs.getInt("employeeId");
				String eName = rs.getString("employeeName");
				double rating = rs.getDouble("rating");

				eSkill.setId(eId);
				eSkill.getEmployee().setEmployeeName(eName);;
				eSkill.setRating(rating);
			}

			if(eSkill == null) throw new InvalidIdException("Sorry EmpId: " + skill + "not found.");
		}

		catch (SQLException | InvalidIdException e)
		{
			System.err.println(e.getMessage());
		}

		finally
		{
			try
			{
				conn.close();
			}

			catch (SQLException e)
			{
				System.err.println(e.getMessage());
			}
		}
		return eSkill;
	}
}