private void fillVCUserTO(ResultSet rs, VCUserTO vto) throws SQLException {

    vto.setUserId(rs.getLong("USER_ID"));

    String value = "";

    value = rs.getString("FIRST_NAME");
    if (value != null) {
      vto.setFirstName(value);
    }

    value = rs.getString("LAST_NAME");
    if (value != null) {
      vto.setLastName(value);
    }

    value = rs.getString("MIDDLE_INITIAL");
    if (value != null) {
      vto.setMiddleInitial(value);
    }

    value = rs.getString("MIDDLE_NAME");
    if (value != null) {
      vto.setMiddleName(value);
    }

    int dob = rs.getInt("DOB");
    vto.setBirthDay(Integer.toString(dob));

    int mob = rs.getInt("MOB");
    vto.setBirthMonth(Integer.toString(mob));

    int yob = rs.getInt("YOB");
    vto.setBirthYear(Integer.toString(yob));

    value = rs.getString("GENDER");
    if (value != null) {
      vto.setGender(value);
    }

    value = rs.getString("EMAIL_ADDRESS");
    if (value != null) {
      vto.setEmailAddress(value);
    }

    value = rs.getString("USER_NAME");
    if (value != null) {
      vto.setUserName(value);
    }

    value = rs.getString("DISPLAY_USER_NAME");
    if (value != null) {
      vto.setDisplayUserName(value);
    }

    value = rs.getString("MAILING_ADDRESS1");
    if (value != null) {
      vto.setMailingAddress1(value);
    }

    value = rs.getString("MAILING_ADDRESS2");
    if (value != null) {
      vto.setMailingAddress2(value);
    }

    value = rs.getString("CITY");
    if (value != null) {
      vto.setCity(value);
    }

    int cid = rs.getInt("STATE");
    if (value != null) {
      vto.setStateId(cid);
    }

    value = rs.getString("ZIP_CODE1");
    if (value != null) {
      vto.setZipCode1(value);
    }

    value = rs.getString("ZIP_CODE2");
    if (value != null) {
      vto.setZipCode2(value);
    }

    value = rs.getString("PHONE_COUNTRY_CODE");
    if (value != null) {
      vto.setPhoneCountryCode(value);
    }

    value = rs.getString("PHONE_AREA_CODE");
    if (value != null) {
      vto.setPhoneAreaCode(value);
    }

    value = rs.getString("PHONE_NUM_1");
    if (value != null) {
      vto.setPhoneNum1(value);
    }

    value = rs.getString("PHONE_NUM_2");
    if (value != null) {
      vto.setPhoneNum2(value);
    }

    int c = rs.getInt("COUNTRY");
    vto.setCountryId(c);

    value = rs.getString("ACCOUNT_STATUS");
    if (value != null) {
      vto.setAccountStatus(value);
    }

    Timestamp d = rs.getTimestamp("CREATE_TIMESTAMP");
    if (d != null) {
      vto.setCreateTimestamp(d);
    }

    d = rs.getTimestamp("MODIFY_TIMESTAMP");

    if (d != null) {
      vto.setModifyTimestamp(d);
    }
  }
  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;
  }