Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement