title: Java store onaction in mysql database
body: <p>So I'm making a login system. Based on the role your account has when you log in, it displays role specific buttons. For instance if you login as an employee, you get to see two buttons called RequestStatus and RegisterMissing. If you login as a manager, you get to see the buttons RequestStatus, RegisterMissing, Reports and UserRoles. 
  After a bit of trail and error (As I am a beginner in java) I managed to make it work!</p>
  
  <p>CODE:</p>
  
  <pre><code>@FXML
  private TextField textUsername;
  
  @FXML
  private PasswordField textPassword;
  
  Stage dialogStage = new Stage();
  Scene scene;
  
  ResultSet resultSet = null;
  
  //Login for employee
  @FXML
  private void handleButtonAction(ActionEvent event) {
      Database db = new Database();
      String username = textUsername.getText();
      String password = textPassword.getText();
      int usrID = 0;
      int roleID = 0;
      //String RoleID = EmpSelect.getValue();
      String sql = String.format("SELECT * FROM Employee "
              + "WHERE username = '%s' "
              + "and password = '%s' ",
              //+ "and RoleID = '%s' ", 
              username, password);
  
      infoBox("sql [" + sql + "]", "Success", null); 
  
      try {
          resultSet = db.executeResultSetQuery(sql);
  
  
          if (!resultSet.next()) {
              infoBox("Enter Correct Username and Password", "Failed", null);
          } else {
                  //infoBox("Login Successfull", "Success", null);
              resultSet.first();
              usrID = resultSet.getInt("idEmployee");
              roleID = resultSet.getInt("RoleID");
  
              utilities.setEmployee(usrID, roleID);
  
  
  
              FXMLDocumentController controller = new FXMLDocumentController();
              utilities.newAnchorpane("EmployeeHomescreen", paneLogin);
              infoBox("Login Successfull", "Success", null);                  
          }
  
      } catch (Exception e) {
          e.printStackTrace();
      }
  }
  
  public static void infoBox(String infoMessage, String titleBar, String headerMessage) {
      Alert alert = new Alert(AlertType.INFORMATION);
      alert.setTitle(titleBar);
      alert.setHeaderText(headerMessage);
      alert.setContentText(infoMessage);
      alert.showAndWait();
  }
  </code></pre>
  
  <p>So basically what I'm doing here is that an employee of the company can login and it displays the result of the query just so that I know that it works.</p>
  
  <pre><code>import static com.mycompany.mavenproject2.LoginController.infoBox;
  import java.net.URL;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ResourceBundle;
  import javafx.event.ActionEvent;
  import javafx.fxml.FXML;
  import javafx.fxml.Initializable;
  import javafx.scene.control.Button;
  import javafx.scene.control.Label;
  import javafx.scene.layout.AnchorPane;
  import javafx.scene.layout.GridPane;
  import javafx.scene.layout.Pane;
  import javafx.scene.layout.VBox;
  
  /**
   *
   * @author John
   */
  public class WorkerHomescreenController implements Initializable {
  
  @FXML
  private AnchorPane actualmain;
  
  @FXML
  private Pane mainpage;
  
  @FXML
  private Label label1;
  
  @FXML
  private Button but1, but2, but3, but4;
  
  @FXML
  private VBox VBPane;
  
  @Override
  public void initialize(URL url, ResourceBundle rb) {
  
      //infoBox("User ID = " + utilities.userID, "Success", null);
         Database db = new Database();
      ResultSet resultSet = null;
      String sqlMenu = String.format("SELECT * FROM Menu "
              + "WHERE idMenu IN (SELECT MenuID FROM MenuRoles WHERE RoleID = " + utilities.roleID + ") ");
              //+ "AND Active = True");
      try {
          resultSet = db.executeResultSetQuery(sqlMenu);
          int i=0;
          GridPane gpnael = new GridPane();
          while (resultSet.next()) {
              Button button = new Button(resultSet.getString("Link"));
              gpnael.add (button, i++, 1 );
              button.setStyle("-fx-font: 22 arial; -fx-base: #d81e05; "
                      + "-fx-background-radius: 0; -fx-border-width: 1 0 0 0; "
                      + "-fx-border-color: white; -fx-font-weight: bold; "
                      + "-fx-font-size: 18;");
  
              button.setOnAction(new EventHandler&lt;ActionEvent&gt;() {
                  @Override
                  public void handle(ActionEvent e) {
                      try {
                          resultSet.getURL("Link");
                      } catch (SQLException ex) {
                          Logger.getLogger(WorkerHomescreenController.class.getName()).log(Level.SEVERE, null, ex);
                      }
                  }
              });
          }
  
      } catch (SQLException e) {
          e.printStackTrace();
      }
  
  }
  Utilities utilities = new Utilities();
  
  //Methods to open other pages
  @FXML
  private void openRequestStatus(ActionEvent event) {
      utilities.newPane("RequestStatus", but1, mainpage, label1);
  }
  
  @FXML
  private void openRegisterMissing(ActionEvent event) {
      utilities.newPane("RegisterMissing", but2, mainpage, label1);
  }
  
  @FXML
  private void openRequestTest(ActionEvent event) {
      utilities.newPane("RequestStatus", but3, mainpage, label1);
  }
  
  @FXML
  private void openRegistertesting(ActionEvent event) {
      utilities.newPane("RegisterMissing", but4, mainpage, label1);
  }
  }
  </code></pre>
  
  <p>This is the controller of the page where you are redirected to after you login. This page is currently set up in a manner in which based on which RoleId the account you used to login with, it will generate the buttons with the info I have stored in my database.</p>
  
  <p>Utilities controller:</p>
  
  <pre><code>   public static int userID;
     public static int roleID;
  
     @FXML
     public void setEmployee(int idEmployee, int RoleID) {
      this.userID = idEmployee;
      this.roleID = RoleID;
  }
  </code></pre>
  
  <p>The Utilities controller is used to set the current employee.</p>
  
  <p>My question is this:
  My program currently works so that it generates buttons based on your account (Which is good!). What I want to do now is that the buttons have a link saved in the database as well. So that when the buttons are generated, it has a label and a link attached to it so that when you press the button it will go to the link that is attached to the button.</p>
  
  <p>I hope this makes sense.</p>
  
  <p>I know that in a .FXML file if you make a button inside of there you can give it an onAction event which makes it so that if you press the button it takes you to the correct page.</p>
  
  <p>This is what my database looks like:</p>
  
  <pre><code>EMPLOYEE Table
  idEmployee    firstname   lastname   username   password     RoleID
  1             John        Doe        John123    Test123      1
  2             Tim         Gardener   Tim123     Welcome123   2
  
  ROLES Table
  idRole     roleName
  1          Employee
  2          Manager
  
  MENU Table
  idMenu    Name    Link     
  1         but1    RequestStatus
  2         but2    RegisterMissing
  3         but3    Reports
  4         but4    UserRoles
  
  MenuRoles Table
  MenuID      RoleID
  1           1
  1           2
  2           1
  2           2
  3           2
  4           2
  </code></pre>
  
  <p>I hope you guys can help me with this problem and thank you for your time I appreciate it!</p>
  
  <p>EDIT 1:
  After reading the comment I edit a bit of my code.
  The problem I'm having now is that the resultSet in resultSet.getURL("Link"); gives me an error saying: <strong>"Local variabel is accessed from within inner class; needs to be declared final.</strong>
  After making the resultSet final it gives me another error saying 
  final ResultSet resultSet = null;</p>
  
  <p>This part now gives me an error saying:
  resultSet = db.executeResultSetQuery(sqlMenu);
  Cannot assign value to final variabel resultSet.</p>