Example #1
1
  @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();
    }
  }