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