Beispiel #1
0
  public List<Account> findByCondition(
      String idcardNo, String realName, String loginName, String status, int page, int pageSize)
      throws DAOException {
    // 用来封住查询条件的值
    List<Object> params = new ArrayList<Object>();
    StringBuffer sb = buildFIndSQL(idcardNo, realName, loginName, status, page, pageSize, params);
    List<Account> accs = new ArrayList<Account>();
    Connection conn = DBUtil.getConnection();
    try {
      PreparedStatement pstmt = conn.prepareStatement(sb.toString());
      for (int i = 0; i < params.size(); i++) {
        pstmt.setObject(i + 1, params.get(i));
      }
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        Account a = createAccount(rs);
        accs.add(a);
      }
    } catch (Exception e) {
      e.printStackTrace();
      throw new DAOException("查询账务账户失败", e);
    } finally {
      DBUtil.closeConnection();
    }

    return accs;
  }
Beispiel #2
0
 public Account findById(int id) throws DAOException {
   String sql = "select * from account where id=?";
   Connection conn = DBUtil.getConnection();
   try {
     PreparedStatement pstmt = conn.prepareStatement(sql);
     pstmt.setInt(1, id);
     ResultSet rs = pstmt.executeQuery();
     if (rs.next()) {
       return createAccount(rs);
     }
   } catch (Exception e) {
     e.printStackTrace();
     throw new DAOException("根据id查询账务账号信息失败!", e);
   } finally {
     DBUtil.closeConnection();
   }
   return null;
 }
Beispiel #3
0
 public void pauseAccount(int id) throws DAOException {
   String sql = "update account set status='1',pause_date=sysdate where id=?";
   Connection conn = DBUtil.getConnection();
   try {
     conn.setAutoCommit(false);
     PreparedStatement pstmt = conn.prepareStatement(sql);
     pstmt.setInt(1, id);
     pstmt.executeUpdate();
     conn.commit();
   } catch (SQLException e) {
     e.printStackTrace();
     try {
       conn.rollback();
     } catch (SQLException e1) {
       e1.printStackTrace();
     }
     throw new DAOException("暂停账务账号失败!", e);
   } finally {
     DBUtil.closeConnection();
   }
 }
Beispiel #4
0
  public Account findByIdcardNo(String idcardNo) throws DAOException {
    if (idcardNo == null) {
      return null;
    }
    String sql = "select * from account where idcard_no=?";
    Connection conn = DBUtil.getConnection();
    PreparedStatement pstmt;
    try {
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, idcardNo);
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) {
        return createAccount(rs);
      }
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } finally {
      DBUtil.closeConnection();
    }

    return null;
  }
Beispiel #5
0
  public int findTotalPage(
      String idcardNo, String realName, String loginName, String status, int pageSize)
      throws DAOException {
    // 拼查询总行数的sql
    StringBuffer sb = new StringBuffer();
    sb.append("select count(*) from account where 1=1");
    List<Object> params = new ArrayList<Object>();
    if (idcardNo != null && idcardNo.length() > 0) {
      sb.append("and idcard_no=? ");
      params.add(idcardNo);
    }
    if (realName != null && realName.length() > 0) {
      sb.append("and real_name=? ");
      params.add(realName);
    }
    if (loginName != null && loginName.length() > 0) {
      sb.append("and login_name=? ");
      params.add(loginName);
    }
    if (status != null && !status.equals("-1")) {
      sb.append("and status=? ");
      params.add(status);
    }
    Connection conn = DBUtil.getConnection();
    try {
      PreparedStatement pstmt = conn.prepareStatement(sb.toString());
      for (int i = 0; i < params.size(); i++) {
        pstmt.setObject(i + 1, params.get(i));
      }
      ResultSet rs = pstmt.executeQuery();
      if (rs.next()) {
        int rows = rs.getInt(1);
        if (rows % pageSize == 0) {
          return rows / pageSize;
        } else {
          return rows / pageSize + 1;
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DAOException("查询总页数失败!", e);
    }

    return 0;
  }
Beispiel #6
0
  public void addAccount(Account account) throws DAOException {
    if (account == null) {
      return;
    }
    String sql =
        "insert into account values(seq_account.nextval,?,?,?,'1',sysdate,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Connection conn = DBUtil.getConnection();
    try {
      conn.setAutoCommit(false);
      PreparedStatement pstmt = conn.prepareStatement(sql);
      pstmt.setObject(1, account.getRecommenderId());
      pstmt.setObject(2, account.getLoginName());
      pstmt.setObject(3, account.getLoginPassword());
      pstmt.setObject(4, account.getPauseDate());
      pstmt.setObject(5, account.getCloseDate());
      pstmt.setObject(6, account.getRealName());
      pstmt.setObject(7, account.getIdcardNo());
      pstmt.setObject(8, account.getBirthdate());
      pstmt.setObject(9, account.getGender());
      pstmt.setObject(10, account.getOccupation());
      pstmt.setObject(11, account.getTelephone());
      pstmt.setObject(12, account.getEmail());
      pstmt.setObject(13, account.getMailaddress());
      pstmt.setObject(14, account.getZipcode());
      pstmt.setObject(15, account.getQq());
      pstmt.setObject(16, account.getLastLoginTime());
      pstmt.setObject(17, account.getLastLoginIp());
      pstmt.executeUpdate();
      conn.commit();

    } catch (SQLException e) {
      try {
        conn.rollback();
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
      e.printStackTrace();
      throw new DAOException("账务账号插入失败!", e);
    }
  }