daily pastebin goal
2%
SHARE
TWEET

Untitled

a guest Sep 20th, 2016 65 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. public class EmployeeQuery
  2. {
  3.     static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  4.     static final String DB_URL = "jdbc:mysql://localhost/INFODB";
  5.  
  6.     static final String USER = "username";
  7.     static final String PASS = "password";
  8.  
  9.     // Connection Handle
  10.     private mConn;
  11.     private mDate;
  12.  
  13.     public EmployeeQuery()
  14.     {
  15.         try {
  16.             mDate = new Timestamp(System.currentTimeMillis());
  17.             Class.forName(JDBC_DRIVER);
  18.             mConn = DriverManager.getConnection(DB_URL, USER, PASS);
  19.         } catch (SQLException se) {
  20.             se.printStackTrace();
  21.         } catch (Exception e) {
  22.             e.printStackTrace();
  23.         }
  24.     }
  25.  
  26.     public void clean()
  27.     {
  28.         try {
  29.             mConn.close();
  30.         } catch (SQLException se) {
  31.             se.printStackTrace();
  32.         }
  33.     }
  34.  
  35.     private int roundMillisecondToYear(long sec)
  36.     {
  37.         Calendar calender = Calendar.getInstance();
  38.         calender.setTimeInMillis(sec);
  39.         int year = calender.get(Calendar.YEAR) - 1970;
  40.         return year;
  41.     }
  42.  
  43.     // Question b.1
  44.     public int getEmployWorkingYears(String name)
  45.     {
  46.         int year = -1;
  47.         PreparedStatement ps = null;
  48.         ResultSet rs = null;
  49.         try {
  50.             ps = mConn.prepareStatement("SELECT date FROM employee WHERE name=?");
  51.             ps.setString(1, name);
  52.             rs = ps.executeQuery();
  53.             if (rs.next()) {
  54.                 Timestamp date = (Timestamp) rs.getObject(1);
  55.                 long diff = date.getTime() - mDate.getTime();
  56.                 year = roundMillisecondToYear(diff);
  57.             }
  58.         } catch (SQLException e) {
  59.             e.printStackTrace();
  60.         } finally {
  61.             try {
  62.                 rs.close();
  63.                 ps.close();
  64.             } catch (SQLException e) {
  65.             }
  66.         }
  67.         return year;
  68.     }
  69.  
  70.     // Question b.2
  71.     public int getGroupWorkingYears(String root)
  72.     {
  73.         int year = -1;
  74.         PreparedStatement ps = null;
  75.         ResultSet rs = null;
  76.         try {
  77.             int sum = 0;
  78.             Queue<String> queue = new new LinkedList<String>();
  79.             queue.add(root);
  80.  
  81.             while (!queue.isEmpty()) {
  82.                 String name = queue.remove();
  83.                 ps = mConn.prepareStatement("SELECT member FROM belonging WHERE manager=?");
  84.                 ps.setString(1, name);
  85.                 rs = ps.executeQuery();
  86.                 while (rs.next()) {
  87.                     String member = (String) rs.getObject(1);
  88.                     int num = getEmployWorkingYears(member);
  89.                     if (num != -1) {
  90.                         queue.add(member);
  91.                         sum += num;
  92.                     }
  93.                 }
  94.             }
  95.             if (sum > 0)
  96.                 year = sum;
  97.         } catch (SQLException e) {
  98.             e.printStackTrace();
  99.         } finally {
  100.             try {
  101.                 rs.close();
  102.                 ps.close();
  103.             } catch (SQLException e) {
  104.             }
  105.         }
  106.         return year;
  107.     }
  108.  
  109.     // Question b.3
  110.     public List<List<String>> getEmployManagementInfo()
  111.     {
  112.         List<List<String>> info = new ArrayList<List<String>>();
  113.         PreparedStatement ps = null;
  114.         ResultSet rs = null;
  115.         try {
  116.             ps = mConn.prepareStatement("SELECT member, manager FROM belonging");
  117.             rs = ps.executeQuery();
  118.             while (rs.next()) {
  119.                 String member = (String) rs.getObject(1);
  120.                 String manager = (String) rs.getObject(2);
  121.                 List<String> tuple = new ArrayList<String>();
  122.                 tuple.add(member);
  123.                 tuple.add(manager);
  124.                 info.add(tuple);
  125.             }
  126.         } catch (SQLException e) {
  127.             e.printStackTrace();
  128.         } finally {
  129.             try {
  130.                 rs.close();
  131.                 ps.close();
  132.             } catch (SQLException e) {
  133.             }
  134.         }
  135.         return info;
  136.     }
  137. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top