Example #1
0
  /*
   * (non-Javadoc)
   *
   * @see com.votingcentral.model.db.dao.IVCUserDAO#updateUser(com.votingcentral.model.db.dao.to.VCUserTO,
   *      com.votingcentral.model.db.dao.to.PersonalConfigTO)
   */
  public boolean updateUser(VCUserTO vto, PersonalConfigTO pto) throws SQLException {
    String sql1 = SQLResources.getSQLResource("update.vc.user");
    String sql2 = SQLResources.getSQLResource("update.personal.config");

    //
    // How many times do you want to retry the transaction
    // (or at least _getting_ a connection)?
    //
    int retryCount = 5;
    boolean transactionCompleted = false;
    boolean vcUserUpdate = false;
    boolean pcUpdate = false;

    Connection conn = null;
    PreparedStatement pps1 = null;
    PreparedStatement pps2 = null;
    int rows = 0;
    do {
      try {
        retryCount = 0;
        conn = VCDAOFactory.getConnection();
        conn.setAutoCommit(false);

        pps1 = conn.prepareStatement(sql1);

        if (vto.getFirstName() != null && vto.getFirstName().length() > 0) {
          pps1.setString(1, vto.getFirstName());
        } else {
          pps1.setNull(1, Types.VARCHAR);
        }

        if (vto.getLastName() != null && vto.getLastName().length() > 0) {
          pps1.setString(2, vto.getLastName());
        } else {
          pps1.setNull(2, Types.VARCHAR);
        }

        if (vto.getMiddleInitial() != null && vto.getMiddleInitial().length() > 0) {
          pps1.setString(3, vto.getMiddleInitial());
        } else {
          pps1.setNull(3, Types.VARCHAR);
        }

        if (vto.getMiddleName() != null && vto.getMiddleName().length() > 0) {
          pps1.setString(4, vto.getMiddleName());
        } else {
          pps1.setNull(4, Types.VARCHAR);
        }

        pps1.setString(5, vto.getEmailAddress());

        if (vto.getBirthDay() != null && vto.getBirthDay().trim().length() > 0) {
          pps1.setInt(6, new Integer(vto.getBirthDay()).intValue());
        } else {
          pps1.setNull(6, Types.INTEGER);
        }

        if (vto.getBirthMonth() != null && vto.getBirthMonth().trim().length() > 0) {
          pps1.setInt(7, new Integer(vto.getBirthMonth()).intValue());
        } else {
          pps1.setNull(7, Types.INTEGER);
        }

        pps1.setInt(8, new Integer(vto.getBirthYear()).intValue());

        pps1.setString(9, vto.getGender());

        pps1.setString(10, vto.getUserName());
        pps1.setString(11, vto.getDisplayUserName());
        pps1.setString(12, vto.getMailingAddress1());
        pps1.setString(13, vto.getMailingAddress2());
        pps1.setString(14, vto.getCity());
        pps1.setInt(15, vto.getStateId());
        pps1.setString(16, vto.getZipCode1());
        pps1.setString(17, vto.getZipCode2());
        pps1.setInt(18, vto.getCountryId());
        pps1.setString(19, vto.getPhoneCountryCode());
        pps1.setString(20, vto.getPhoneAreaCode());
        pps1.setString(21, vto.getPhoneNum1());
        pps1.setString(22, vto.getPhoneNum2());
        pps1.setString(23, vto.getAccountStatus());
        // for the where clause.
        pps1.setLong(24, vto.getUserId());
        rows = pps1.executeUpdate();
        if (rows == 1) {
          vcUserUpdate = true;
        }
        pps2 = conn.prepareStatement(sql2);

        pps2.setString(1, pto.getSecurityQuestion());
        pps2.setString(2, pto.getSecurityAnswer());
        pps2.setString(3, pto.getEncryptedPassword());
        // for the where clause
        pps2.setLong(4, pto.getUserId());
        rows = pps2.executeUpdate();
        if (rows == 1) {
          pcUpdate = true;
        }
        transactionCompleted = true;
        conn.commit();
        conn = null;
      } catch (SQLException e) {
        //
        // The two SQL states that are 'retry-able' are 08S01
        // for a communications error, and 41000 for deadlock.
        //
        // Only retry if the error was due to a stale connection,
        // communications problem or deadlock
        //
        log.fatal("SQLException: " + e.getMessage());
        log.fatal("SQLState: " + e.getSQLState());
        log.fatal("VendorError: " + e.getErrorCode());
        String sqlState = e.getSQLState();

        if ("08S01".equals(sqlState) || "41000".equals(sqlState)) {
          retryCount--;
        } else {
          retryCount = 0;
          throw e;
        }
      } finally {
        try {
          if (pps1 != null) {
            pps1.close();
            pps1 = null;
          }

        } catch (SQLException e) {
          log.fatal("Problem closing the prepared statements", e);
          throw e;
        }
        if (conn != null) {
          try {
            //
            // If we got here, and conn is not null, the
            // transaction should be rolled back, as not
            // all work has been done
            try {
              conn.rollback();
            } finally {
              conn.close();
            }
          } catch (SQLException sqlEx) {
            //
            // If we got an exception here, something
            // pretty serious is going on, so we better
            // pass it up the stack, rather than just
            // logging it. . .

            throw sqlEx;
          }
        }
      }
    } while (!transactionCompleted && (retryCount > 0));

    return transactionCompleted;
  }
Example #2
0
  /*
   * (non-Javadoc)
   *
   * @see com.votingcentral.model.db.dao.IVCUserDAO#updateUser(com.votingcentral.model.db.dao.to.VCUserTO)
   */
  public boolean updateUser(VCUserTO vto) throws SQLException {
    String sql1 = SQLResources.getSQLResource("update.vc.user");
    Connection conn = null;
    PreparedStatement pps1 = null;
    int rows = 0;

    try {
      conn = VCDAOFactory.getConnection();
      pps1 = conn.prepareStatement(sql1);

      if (vto.getFirstName() != null && vto.getFirstName().length() > 0) {
        pps1.setString(1, vto.getFirstName());
      } else {
        pps1.setNull(1, Types.VARCHAR);
      }

      if (vto.getLastName() != null && vto.getLastName().length() > 0) {
        pps1.setString(2, vto.getLastName());
      } else {
        pps1.setNull(2, Types.VARCHAR);
      }

      if (vto.getMiddleInitial() != null && vto.getMiddleInitial().length() > 0) {
        pps1.setString(3, vto.getMiddleInitial());
      } else {
        pps1.setNull(3, Types.VARCHAR);
      }

      if (vto.getMiddleName() != null && vto.getMiddleName().length() > 0) {
        pps1.setString(4, vto.getMiddleName());
      } else {
        pps1.setNull(4, Types.VARCHAR);
      }

      pps1.setString(5, vto.getEmailAddress());

      if (vto.getBirthDay() != null && vto.getBirthDay().trim().length() > 0) {
        pps1.setInt(6, new Integer(vto.getBirthDay()).intValue());
      } else {
        pps1.setNull(6, Types.INTEGER);
      }

      if (vto.getBirthMonth() != null && vto.getBirthMonth().trim().length() > 0) {
        pps1.setInt(7, new Integer(vto.getBirthMonth()).intValue());
      } else {
        pps1.setNull(7, Types.INTEGER);
      }

      pps1.setInt(8, new Integer(vto.getBirthYear()).intValue());

      pps1.setString(9, vto.getGender());

      pps1.setString(10, vto.getUserName());
      pps1.setString(11, vto.getDisplayUserName());
      pps1.setString(12, vto.getMailingAddress1());
      pps1.setString(13, vto.getMailingAddress2());
      pps1.setString(14, vto.getCity());
      pps1.setInt(15, vto.getStateId());
      pps1.setString(16, vto.getZipCode1());
      pps1.setString(17, vto.getZipCode2());
      pps1.setInt(18, vto.getCountryId());
      pps1.setString(19, vto.getPhoneCountryCode());
      pps1.setString(20, vto.getPhoneAreaCode());
      pps1.setString(21, vto.getPhoneNum1());
      pps1.setString(22, vto.getPhoneNum2());
      pps1.setString(23, vto.getAccountStatus());
      // for the where clause.
      pps1.setLong(24, vto.getUserId());
      rows = pps1.executeUpdate();

    } catch (SQLException e) {
      log.fatal("SQLException: " + e.getMessage());
      log.fatal("SQLState: " + e.getSQLState());
      log.fatal("VendorError: " + e.getErrorCode());
      throw e;
    } finally {
      try {
        if (pps1 != null) {
          pps1.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        log.fatal("Connection.close", e);
        throw e;
      }
    }
    return rows > 0 ? true : false;
  }
Example #3
0
  public boolean createUser(VCUserTO vto, PersonalConfigTO pto) throws SQLException {

    String sql1 = SQLResources.getSQLResource("insert.new.vc.user");
    String sql2 = SQLResources.getSQLResource("insert.new.personal.config");
    String sql3 = SQLResources.getSQLResource("insert.new.user.roles");
    //
    // How many times do you want to retry the transaction
    // (or at least _getting_ a connection)?
    //
    int retryCount = 5;
    boolean transactionCompleted = false;
    boolean vcUserInsert = false;
    boolean pcInsert = false;

    Connection conn = null;
    PreparedStatement pps1 = null;
    PreparedStatement pps2 = null;
    int rows = 0;
    do {
      try {
        retryCount = 0;
        conn = VCDAOFactory.getConnection();
        conn.setAutoCommit(false);

        pps1 = conn.prepareStatement(sql1);

        if (vto.getFirstName() != null) {
          pps1.setString(1, vto.getFirstName());
        } else {
          pps1.setNull(1, Types.VARCHAR);
        }

        if (vto.getLastName() != null) {
          pps1.setString(2, vto.getLastName());
        } else {
          pps1.setNull(2, Types.VARCHAR);
        }

        if (vto.getMiddleInitial() != null) {
          pps1.setString(3, vto.getMiddleInitial());
        } else {
          pps1.setNull(3, Types.VARCHAR);
        }

        if (vto.getMiddleName() != null) {
          pps1.setString(4, vto.getMiddleName());
        } else {
          pps1.setNull(4, Types.VARCHAR);
        }

        pps1.setString(5, vto.getEmailAddress());

        if (vto.getBirthDay() != null && vto.getBirthDay().trim().length() > 0) {
          pps1.setInt(6, new Integer(vto.getBirthDay()).intValue());
        } else {
          pps1.setNull(6, Types.INTEGER);
        }

        if (vto.getBirthMonth() != null && vto.getBirthMonth().trim().length() > 0) {
          pps1.setInt(7, new Integer(vto.getBirthMonth()).intValue());
        } else {
          pps1.setNull(7, Types.INTEGER);
        }

        pps1.setInt(8, new Integer(vto.getBirthYear()).intValue());

        pps1.setString(9, vto.getGender());

        pps1.setString(10, vto.getUserName());
        pps1.setString(11, vto.getDisplayUserName());
        pps1.setString(12, vto.getMailingAddress1());
        pps1.setString(13, vto.getMailingAddress2());
        pps1.setString(14, vto.getCity());
        pps1.setInt(15, vto.getStateId());
        pps1.setString(16, vto.getZipCode1());
        pps1.setString(17, vto.getZipCode2());
        pps1.setInt(18, vto.getCountryId());
        pps1.setString(19, vto.getPhoneCountryCode());
        pps1.setString(20, vto.getPhoneAreaCode());
        pps1.setString(21, vto.getPhoneNum1());
        pps1.setString(22, vto.getPhoneNum2());
        pps1.setString(23, vto.getAccountStatus());
        rows = pps1.executeUpdate();
        if (rows == 1) {
          vcUserInsert = true;
        }

        Statement stmt = conn.createStatement();
        ResultSet rs = null;
        long autoIncKeyFromFunc = -1;
        rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
        if (rs.next()) {
          autoIncKeyFromFunc = rs.getLong(1);
          vto.setUserId(autoIncKeyFromFunc);
        } else {
          // throw an exception from here
        }
        log.debug("The autoincrement id inserted now is :" + autoIncKeyFromFunc);

        pps2 = conn.prepareStatement(sql2);
        pps2.setLong(1, autoIncKeyFromFunc);
        pps2.setString(2, pto.getUserName());
        pps2.setString(3, pto.getEmailConfCode());
        pps2.setString(4, pto.getSecurityQuestion());
        pps2.setString(5, pto.getSecurityAnswer());
        pps2.setString(6, pto.getEncryptedPassword());
        // the first temp pswd is TEMP.
        pps2.setString(7, "TEMP");
        rows = pps2.executeUpdate();
        if (rows == 1) {
          pcInsert = true;
        }
        // The third sql starts here
        List userRoles = vto.getUserRoles();
        if (userRoles != null) {
          for (int i = 0; i < userRoles.size(); i++) {
            DAOFactory dao = DAOFactory.getDAOFactory();
            IVCUserRolesDAO vdao = dao.getVCUserRolesDAO();
            vdao.addUserRole(vto.getDisplayUserName(), userRoles.get(i).toString());
          }
        }
        transactionCompleted = true;
        conn.commit();
        conn.setAutoCommit(true);
        conn.close();
        conn = null;
      } catch (SQLException e) {
        //
        // The two SQL states that are 'retry-able' are 08S01
        // for a communications error, and 41000 for deadlock.
        //
        // Only retry if the error was due to a stale connection,
        // communications problem or deadlock
        //
        handleSQLException(e, pps1);
        handleSQLException(e, pps2);
        String sqlState = e.getSQLState();

        if ("08S01".equals(sqlState) || "41000".equals(sqlState)) {
          retryCount--;
        } else {
          retryCount = 0;
          throw e;
        }
      } finally {
        try {
          if (pps1 != null) {
            pps1.close();
            pps1 = null;
          }
          if (pps2 != null) {
            pps2.close();
            pps2 = null;
          }

        } catch (SQLException e) {
          handleSQLException(e, pps1);
          handleSQLException(e, pps2);
          throw e;
        }
        if (conn != null) {
          try {
            //
            // If we got here, and conn is not null, the
            // transaction should be rolled back, as not
            // all work has been done
            try {
              conn.rollback();
            } finally {
              conn.close();
            }
          } catch (SQLException sqlEx) {
            //
            // If we got an exception here, something
            // pretty serious is going on, so we better
            // pass it up the stack, rather than just
            // logging it. . .

            throw sqlEx;
          }
        }
      }
    } while (!transactionCompleted && (retryCount > 0));

    return transactionCompleted;
  }