@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// 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 set;
}
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().openSession();
Transaction tx = session.beginTransaction();
try {
// 【永續物件實體查詢************************************************************************************************************************】
Criteria query = session.createCriteria(EmpVO.class);
query.addOrder(Order.asc("empno"));
// 【可封裝查詢條件】
// query.add(Restrictions.eq("empno", 7001));
// query.add( Restrictions.like("ename", "%K%"));
// query.add(Restrictions.eq("hiredate", java.sql.Date.valueOf("1981-11-17")));
// 【範例查詢】
// EmpVO empVO = new EmpVO();
// empVO.setEmpno(7001);
// empVO.setEname("KING");
// empVO.setHiredate(java.sql.Date.valueOf("1981-11-17"));
// query.add(Example.create(empVO));
// 【可分頁】
// query.setFirstResult(0); //0,3,6,9,12...
// query.setMaxResults(3); //假設每頁有3筆
// 【測試查詢結果】
// 【query.list();直接回傳List】
List<EmpVO> list = query.list();
for (EmpVO aEmp : list) {
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();
}
tx.commit();
} catch (RuntimeException ex) {
if (tx != null) tx.rollback();
throw ex; // System.out.println(ex.getMessage());
} finally {
session.close();
HibernateUtil.getSessionFactory().close();
}
}