stmt = conn.prepareStatement("SELECT id FROM regions WHERE name=?"); stmt.setString(1, regionName); rs = stmt.executeQuery(); if (rs.first()) { regionId = rs.getInt("id"); }
stmt = conn.prepareStatement("INSERT INTO users VALUES (NULL, \""+firstname+ "\", \""+lastname+"\", \""+nickname+"\", \""+ password+"\", \""+email+"\", 0, 0,\""+creationDate+"\", "+ regionId+")"); stmt.executeUpdate(); stmt.close();
  public String createUser(String firstname, String lastname, String nickname, String email, String password, String regionName) throws RemoteException  {    StringBuffer html = new StringBuffer();    Connection        conn = null;    PreparedStatement stmt = null;    ResultSet rs           = null;    int regionId           = -1;    int userId             = -1;    int rating             = 0;    float balance          = 0;    String creationDate    = null;      try      {        conn = dataSource.getConnection();        stmt = conn.prepareStatement("SELECT id FROM regions WHERE name=?");        stmt.setString(1, regionName);        rs = stmt.executeQuery();        if (rs.first())        {          regionId = rs.getInt("id");        }      }      catch (Exception e)      {        try        {          if (stmt != null) stmt.close();          if (conn != null) conn.close();        }        catch (Exception ignore)        {        }        throw new RemoteException(" Region "+regionName+" does not exist in the database!<br>(got exception: " +e+")<br>\n");      }      utx = sessionContext.getUserTransaction();      // Try to create a new user      try      {        stmt = conn.prepareStatement("SELECT nickname FROM users WHERE nickname=?");        stmt.setString(1, nickname);        rs = stmt.executeQuery();        if (rs.first())        {          html.append("The nickname you have choosen is already taken by someone else. Please choose a new nickname.<br>");          return html.toString();        }      }      catch (Exception fe)      {        try        {          if (stmt != null) stmt.close();          if (conn != null) conn.close();        }        catch (Exception ignore)        {        }        throw new RemoteException("Failed to execute Query to check the nickname: " +fe);      }      try      {        utx.begin();        try        {          creationDate = TimeManagement.currentDateToString();          stmt = conn.prepareStatement("INSERT INTO users VALUES (NULL, \""+firstname+                                       "\", \""+lastname+"\", \""+nickname+"\", \""+                                       password+"\", \""+email+"\", 0, 0,\""+creationDate+"\", "+                                        regionId+")");          stmt.executeUpdate();        }        catch (SQLException e)        {          try          {            if (stmt != null) stmt.close();            if (conn != null) conn.close();          }          catch (Exception ignore)          {          }          throw new RemoteException("RUBiS internal error: User registration failed (got exception: " +e+")<br>");        }        try        {          stmt = conn.prepareStatement("SELECT id, rating, balance FROM users WHERE nickname=?");          stmt.setString(1, nickname);          ResultSet urs = stmt.executeQuery();          if (urs.first())          {            userId = urs.getInt("id");            rating = urs.getInt("rating");            balance = urs.getFloat("balance");          }        }        catch (SQLException e)        {          try          {            if (stmt != null) stmt.close();            if (conn != null) conn.close();          }          catch (Exception ignore)          {          }          throw new RemoteException("Failed to execute Query for user: " +e);        }        if (stmt != null) stmt.close();        if (conn != null) conn.close();        utx.commit();        html.append("User id       :"+userId+"<br>\n");        html.append("Creation date :"+creationDate+"<br>\n");        html.append("Rating        :"+rating+"<br>\n");        html.append("Balance       :"+balance+"<br>\n");      }      catch (Exception e)      {        try        {          if (stmt != null) stmt.close();          if (conn != null) conn.close();        }        catch (Exception ignore)        {        }        try        {          utx.rollback();          throw new RemoteException("User registration failed (got exception: " +e+")<br>");        }        catch (Exception se)         {          throw new RemoteException("Transaction rollback failed: " + e +"<br>");        }      }      return html.toString();  }