public class EmployeeQuery
{
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/INFODB";

    static final String USER = "username";
    static final String PASS = "password";

    // Connection Handle
    private mConn;
    private mDate;

    public EmployeeQuery()
    {
        try {
            mDate = new Timestamp(System.currentTimeMillis());
            Class.forName(JDBC_DRIVER);
            mConn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void clean()
    {
        try {
            mConn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
    }

    private int roundMillisecondToYear(long sec)
    {
        Calendar calender = Calendar.getInstance();
        calender.setTimeInMillis(sec);
        int year = calender.get(Calendar.YEAR) - 1970;
        return year;
    }

    // Question b.1
    public int getEmployWorkingYears(String name)
    {
        int year = -1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = mConn.prepareStatement("SELECT date FROM employee WHERE name=?");
            ps.setString(1, name);
            rs = ps.executeQuery();
            if (rs.next()) {
                Timestamp date = (Timestamp) rs.getObject(1);
                long diff = date.getTime() - mDate.getTime();
                year = roundMillisecondToYear(diff);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
            }
        }
        return year;
    }

    // Question b.2
    public int getGroupWorkingYears(String root)
    {
        int year = -1;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            int sum = 0;
            Queue<String> queue = new new LinkedList<String>();
            queue.add(root);

            while (!queue.isEmpty()) {
                String name = queue.remove();
                ps = mConn.prepareStatement("SELECT member FROM belonging WHERE manager=?");
                ps.setString(1, name);
                rs = ps.executeQuery();
                while (rs.next()) {
                    String member = (String) rs.getObject(1);
                    int num = getEmployWorkingYears(member);
                    if (num != -1) {
                        queue.add(member);
                        sum += num;
                    }
                }
            }
            if (sum > 0)
                year = sum;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
            }
        }
        return year;
    }

    // Question b.3
    public List<List<String>> getEmployManagementInfo()
    {
        List<List<String>> info = new ArrayList<List<String>>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = mConn.prepareStatement("SELECT member, manager FROM belonging");
            rs = ps.executeQuery();
            while (rs.next()) {
                String member = (String) rs.getObject(1);
                String manager = (String) rs.getObject(2);
                List<String> tuple = new ArrayList<String>();
                tuple.add(member);
                tuple.add(manager);
                info.add(tuple);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
            } catch (SQLException e) {
            }
        }
        return info;
    }
}