How to use FXML controller to retrieve data from database and populate a TableView (defined in FXML)?

Question

I am new to JavaFX and FXML so forgive me if this is a very simple problem. I want to retrieve data from a database (MySQL for instance) and put it in a TableView which is defined in a FXML file. I also want to make this TableView editable and update the database when the user makes some changes.

I've read Mastering FXML

I've found this post but it is not very clear as there are no code examples.

I understand it is possible using DataFX. I don't want to do that.

I understand it is possible to use the controller to retrieve data and populate the table. I don't know how to do the last step. Does anyone have code examples?

Let's say I have a database table people containing first_name, last_name.

My FXML code looks like this:

<GridPane fx:controller="sample.Controller" xmlns:fx="http://javafx.com/fxml" alignment="center" hgap="10" vgap="10">

    <padding>
        <Insets bottom="10.0" left="10.0" right="10.0" top="10.0" />
    </padding>

    <Label style="-fx-font: NORMAL 20 Tahoma;" text="Address Book" GridPane.columnIndex="0" GridPane.rowIndex="0">
    </Label>

    <TableView fx:id="tableView" GridPane.columnIndex="0" GridPane.rowIndex="1">
        <columns>
            <TableColumn text="First Name"></TableColumn>
            <TableColumn text="Last Name"></TableColumn>
        </columns>
    </TableView>

</GridPane>

See [How to populate a TableView that is defined in an fxml file](http://stackoverflow.com/questions/11180884/how-to-populate-a-tableview-that-is-defined-in-an-fxml-file-that-is-designed-in) for the part "populate the table" of your question. "retrieve data" part is a core Java. Search for tutorials about it. But be a ware on updating JavaFX GUI with retrieved data. Namely the DB fetching must be in different thread other than JavaFX Main thread. For that purposes use JavaFX services and tasks. Again search for "JavaFX: background periodic sql quering", "JavaFX: GUI freeze on DB query" etc.GL. — Uluk Biy, Jul 18 '14 at 06:01
Thanks. And sorry, I didn't see that thread. Will research better next time :) — rfmind, Jul 19 '14 at 15:25
No problem. While searching please refer to the "Advanced Search Tips". It is life saver you can believe me. — Uluk Biy, Jul 19 '14 at 18:50

score 0 · Answer 1 · answered Apr 25 '16 at 20:39

You can define a Model for the people table like this:

public class People {

  public People(String firstName, String lastName){
      this.firstName = new SimpleStringProperty(firstName);
      this.lastName = new SimpleStringProperty(lastName);
  }

  private StringProperty firstName;
  public void setFirstName(String value) { firstNameProperty().set(value); }
  public String getFirstName() { return firstNameProperty().get(); }
  public StringProperty firstNameProperty() {
    return firstName;
  }
  private StringProperty lastName;
  public void setLastName(String value) { lastNameProperty().set(value); }
  public String getLastName() { return lastNameProperty().get(); }
  public StringProperty lastNameProperty() {
    return lastName;
  }
}

Then define a method where you retrieve the data:

private ObservableList<People> fetchPeople(Connection con) throws SQLException {
    logger.info("Fetching people from database");
    ObservableList<People> people = FXCollections.observableArrayList();

    Statement st = con.createStatement();      
    ResultSet rs = st.executeQuery("select * from people");
    while (rs.next()) {
      people.add(new People(rs.getString("first_name"), rs.getString("last_name")));
    }

    return people;
  }

This method return an ObservableList<People> that you can use to populate the tableView e.g:

Connection conn = getConnection();
ObservableList<People> people = fetchPeople(conn);
ObservableList<TableColumn> columns = tableView.getColumns();
columns.get(0).setCellValueFactory(new PropertyValueFactory("firstName")); // asumming this column is the corresponding to firstName, you can also set an id to the column an refer to it with @FXML property
columns.get(1).setCellValueFactory(new PropertyValueFactory("lastName")); // asumming this column is the corresponding to lastName, you can also set an id to the column an refer to it with @FXML property
tableView.setItems(people);

With this method: setCellValueFactory(new PropertyValueFactory("firstName")); we are binding the property firstNameProperty in the model to the corresponding column in the TableView.

Also, you can use a function to create the value that you want in the cell. If you want a third column to show the full name, this is how you can get it:

columns.get(2).setCellValueFactory(param -> {
    People p = (People)((CellDataFeatures) param).getValue();
    return new SimpleStringProperty(p.getFirstName() + " " + p.getLastName());
});

How to use FXML controller to retrieve data from database and populate a TableView (defined in FXML)?

1 Answers1