@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; }
@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; }
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(); // } }
@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); } } } }