public VCUserTO getUserByEmailAddress(String email) throws SQLException {

    String sql = SQLResources.getSQLResource("get.vcuser.by.email.address");
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pps = null;
    VCUserTO vto = null;

    try {
      conn = VCDAOFactory.getConnection();
      pps = conn.prepareStatement(sql);
      pps.setString(1, email);
      rs = pps.executeQuery();
      while (rs.next()) {
        vto = new VCUserTO();
        fillVCUserTO(rs, vto);
      }
    } catch (SQLException e) {
      log.fatal("SQLException: " + e.getMessage());
      log.fatal("SQLState: " + e.getSQLState());
      log.fatal("VendorError: " + e.getErrorCode());
      throw e;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pps != null) {
          pps.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        log.fatal("Connection.close", e);
        throw e;
      }
    }
    return vto;
  }
 /*
  * (non-Javadoc)
  *
  * @see com.votingcentral.model.db.dao.IVCUserDAO#getMostRecentlyJoinedUsers()
  */
 public List getMostRecentlyJoinedUsers(int limit) throws SQLException {
   String sql = SQLResources.getSQLResource("get.most.recently.joined.users");
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement pps = null;
   VCUserTO vto = null;
   List users = new ArrayList();
   try {
     conn = VCDAOFactory.getConnection();
     pps = conn.prepareStatement(sql);
     pps.setLong(1, limit);
     rs = pps.executeQuery();
     while (rs.next()) {
       vto = new VCUserTO();
       fillVCUserTO(rs, vto);
       users.add(vto);
     }
   } catch (SQLException e) {
     handleSQLException(e, pps);
     throw e;
   } finally {
     try {
       if (rs != null) {
         rs.close();
       }
       if (pps != null) {
         pps.close();
       }
       if (conn != null) {
         conn.close();
       }
     } catch (SQLException e) {
       handleSQLException(e, pps);
       log.fatal("Connection.close", e);
       throw e;
     }
   }
   return users;
 }
 /*
  * (non-Javadoc)
  *
  * @see com.votingcentral.model.db.dao.IVCUserDAO#getAllPollStartedEmailableUsers()
  */
 public List getAllPollStartedEmailableUsers() throws SQLException {
   String sql = SQLResources.getSQLResource("get.all.poll.started.emailable.users");
   Connection conn = null;
   ResultSet rs = null;
   PreparedStatement pps = null;
   VCUserTO vto = null;
   List users = new ArrayList();
   try {
     conn = VCDAOFactory.getConnection();
     pps = conn.prepareStatement(sql);
     rs = pps.executeQuery();
     while (rs.next()) {
       vto = new VCUserTO();
       fillVCUserTO(rs, vto);
       users.add(vto);
     }
   } catch (SQLException e) {
     handleSQLException(e, pps);
     throw e;
   } finally {
     try {
       if (rs != null) {
         rs.close();
       }
       if (pps != null) {
         pps.close();
       }
       if (conn != null) {
         conn.close();
       }
     } catch (SQLException e) {
       handleSQLException(e, pps);
       log.fatal("Connection.close", e);
       throw e;
     }
   }
   return users;
 }
  /*
   * (non-Javadoc)
   *
   * @see com.votingcentral.model.db.dao.IVCUserDAO#getUserByEmailAddressId(long)
   */
  public VCUserTO getUserById(long userId) throws SQLException {
    String sql = SQLResources.getSQLResource("get.vcuser.by.email.address.id");
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pps = null;
    VCUserTO vto = null;

    try {
      conn = VCDAOFactory.getConnection();
      pps = conn.prepareStatement(sql);
      pps.setLong(1, userId);
      rs = pps.executeQuery();
      while (rs.next()) {
        vto = new VCUserTO();
        fillVCUserTO(rs, vto);
      }
    } catch (SQLException e) {
      handleSQLException(e, pps);
      throw e;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pps != null) {
          pps.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        handleSQLException(e, pps);
        log.fatal("Connection.close", e);
        throw e;
      }
    }
    return vto;
  }
  public void setAccountStatusByLoginName(String loginName, String status) throws SQLException {

    String sql = SQLResources.getSQLResource("set.vcuser.account.status.by.username");
    log.debug(sql);
    Connection conn = null;
    ResultSet rs = null;
    PreparedStatement pps = null;
    int numRows = 0;
    try {
      conn = VCDAOFactory.getConnection();
      pps = conn.prepareStatement(sql);
      pps.setString(1, status);
      pps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
      pps.setString(3, loginName);
      numRows = pps.executeUpdate();
    } catch (SQLException e) {
      log.fatal("setAccountStatusByLoginName :", e);
      log.fatal("SQLException: " + e.getMessage());
      log.fatal("SQLState: " + e.getSQLState());
      log.fatal("VendorError: " + e.getErrorCode());
      throw e;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (pps != null) {
          pps.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException e) {
        log.fatal("Connection.close", e);
        throw e;
      }
    }
  }
  /*
   * (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;
  }
  /*
   * (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;
  }
  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;
  }