Example #1
0
  @Override
  public DeptVO findByPrimaryKey(Integer deptno) {

    DeptVO deptVO = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);
      pstmt = con.prepareStatement(GET_ONE_STMT);

      pstmt.setInt(1, deptno);

      rs = pstmt.executeQuery();

      while (rs.next()) {
        // deptVO 也稱為 Domain objects
        deptVO = new DeptVO();
        deptVO.setDeptno(rs.getInt("deptno"));
        deptVO.setDname(rs.getString("dname"));
        deptVO.setLoc(rs.getString("loc"));
      }

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      throw new RuntimeException("A database error occured. " + se.getMessage());
      // Clean up JDBC resources
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
    return deptVO;
  }
Example #2
0
  @Override
  public List<DeptVO> getAll() {
    List<DeptVO> list = new ArrayList<DeptVO>();
    DeptVO deptVO = null;

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);
      pstmt = con.prepareStatement(GET_ALL_STMT);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        deptVO = new DeptVO();
        deptVO.setDeptno(rs.getInt("deptno"));
        deptVO.setDname(rs.getString("dname"));
        deptVO.setLoc(rs.getString("loc"));
        list.add(deptVO); // Store the row in the list
      }

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      throw new RuntimeException("A database error occured. " + se.getMessage());
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
    return list;
  }
Example #3
0
  public static void main(String[] args) {

    DeptJDBCDAO dao = new DeptJDBCDAO();

    // 新增
    //		DeptVO deptVO1 = new DeptVO();
    //		deptVO1.setDname("製造部");
    //		deptVO1.setLoc("中國江西");
    //		dao.insert(deptVO1);

    // 修改
    //		DeptVO deptVO2 = new DeptVO();
    //		deptVO2.setDeptno(10);
    //		deptVO2.setDname("財務部2");
    //		deptVO2.setLoc("臺灣台北2");
    //		dao.update(deptVO2);

    // 刪除
    //		dao.delete(30);

    // 查詢
    //		DeptVO deptVO3 = dao.findByPrimaryKey(10);
    //		System.out.print(deptVO3.getDeptno() + ",");
    //		System.out.print(deptVO3.getDname() + ",");
    //		System.out.println(deptVO3.getLoc());
    //		System.out.println("---------------------");

    // 查詢部門
    List<DeptVO> list = dao.getAll();
    for (DeptVO aDept : list) {
      System.out.print(aDept.getDeptno() + ",");
      System.out.print(aDept.getDname() + ",");
      System.out.print(aDept.getLoc());
      System.out.println();
    }

    // 查詢某部門的員工
    //		Set<EmpVO> set = dao.getEmpsByDeptno(10);
    //		for (EmpVO aEmp : set) {
    //			System.out.print(aEmp.getEmpno() + ",");
    //			System.out.print(aEmp.getEname() + ",");
    //			System.out.print(aEmp.getJob() + ",");
    //			System.out.print(aEmp.getHiredate() + ",");
    //			System.out.print(aEmp.getSal() + ",");
    //			System.out.print(aEmp.getComm() + ",");
    //			System.out.print(aEmp.getDeptno());
    //			System.out.println();
    //		}
  }
Example #4
0
  @Override
  public void update(DeptVO deptVO) {

    Connection con = null;
    PreparedStatement pstmt = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);
      pstmt = con.prepareStatement(UPDATE);

      pstmt.setString(1, deptVO.getDname());
      pstmt.setString(2, deptVO.getLoc());
      pstmt.setInt(3, deptVO.getDeptno());

      pstmt.executeUpdate();

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      throw new RuntimeException("A database error occured. " + se.getMessage());
      // Clean up JDBC resources
    } finally {
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
  }