Populating TableView from database using a hibernate join query

Question

I'm trying to make an application that gets it's data from database and displays it in JavaFX TableView control. In my database I have two tables: members and members_details. Members and members_details have one to one relationship. Table members has 'members_details_id' column which is a foreign key that points to column 'id' in members_details table.

Using the HQL query List<Object[]> members = session.createQuery("from Members m inner join m.membersDetails").getResultList(); I can get the data I want.

Is it possible to populate TableView using the data gathered with the above query? I have no problem populating the table with just members or members_data but I have no clue how to populate it with both types of data.

EDIT

I tried what user @fabian suggested but I still can't get it to work. Here is the code of my class that controls the TableView. The app crashes immediately after starting.

public class MembersScreenController {
private SessionFactory factory;
private Session session;

@FXML
private TableView<Object[]> membersTable;
@FXML
private TableColumn<Members,Integer> idCol;
@FXML
private TableColumn<Members,String> firstNameCol;
@FXML
private TableColumn<Members,String> lastNameCol;
@FXML
private TableColumn<MembersDetails, String> addressCol;
@FXML
private TableColumn<MembersDetails, String> phoneNumberCol;
@FXML
private TableColumn<MembersDetails, String> emailCol;


public void initialize() {

    factory = Factory.getSessionFactory();
    session = factory.getCurrentSession();
    session.beginTransaction();
    List<Object[]> list = session.createQuery("from Members m inner join m.membersDetails").getResultList();
    session.close();
    Factory.closeFactory();

    ObservableList<Object[]> listObs = FXCollections.observableArrayList(list);

    idCol.setCellValueFactory(cd -> new SimpleIntegerProperty(((Members)cd.getValue()).getId()).asObject());
    firstNameCol.setCellValueFactory(cd -> new SimpleStringProperty(((Members)cd.getValue()).getFirstName()));
    lastNameCol.setCellValueFactory(cd -> new SimpleStringProperty(((Members)cd.getValue()).getLastName()));
    addressCol.setCellValueFactory(cd -> new SimpleStringProperty(((MembersDetails)cd.getValue()).getAddress()));
    phoneNumberCol.setCellValueFactory(cd -> new SimpleStringProperty(((MembersDetails)cd.getValue()).getPhoneNum()));
    emailCol.setCellValueFactory(cd -> new SimpleStringProperty(((MembersDetails)cd.getValue()).getAddress()));

    membersTable.setItems(listObs);
}

And here is the part of the FXML file that defines my TableView

 <TableView fx:id="membersTable">

        <columns>

            <TableColumn fx:id="idCol" text="id"/>
            <TableColumn fx:id="firstNameCol" text="First Name"/>
            <TableColumn fx:id="lastNameCol" text="Last Name"/>
            <TableColumn fx:id="addressCol" text="Address"/>
            <TableColumn fx:id="phoneNumberCol" text="Phone Number"/>
            <TableColumn fx:id="emailCol" text="Email"/>

        </columns>
    </TableView>

Another thing that comes to mind is to have two separate querys. With one I could retrieve all from members table and with the other all from members_details table. That would leave me with two lists, one with members objects and the other with membersdetails object. But I still have no idea how to populate TableView.

What are the types in the object arrays returned by the query? Cab you include the code which defines those classes? — jewelsea, Dec 27 '19 at 15:06
@jewelsea query returns two objects 'members' and 'members_details'. Object 'members' has firstName and lastName fields. Object 'members_details' has: address, email and phone number fields. I would like to show both object's fields in my TableView. — Marko, Dec 27 '19 at 16:03
@fabian could you please elaborate yours answer? I have just started programing and have hard time understanding code that is a bit advanced. Thank you — Marko, Dec 27 '19 at 16:05
I'm not overly familiar with hibernate, but I think you can use a [select](https://stackoverflow.com/questions/44269599/hibernate-doesnt-return-proper-objects-in-inner-join) clause and [typed query](https://docs.jboss.org/hibernate/orm/6.0/javadocs/org/hibernate/Session.html#createQuery-java.lang.String-java.lang.Class). If you have your one-to-one relationship [well defined](https://www.baeldung.com/jpa-one-to-one), it should return a Members object with data from both tables. You would still need custom cell value factories to extract member details, not property value factories. — jewelsea, Dec 27 '19 at 16:11
@jewelsea I believe that query I have typed returns the data from both tables, I just don't know how to put it into TableView. Could you explain me how to make custom factories? This is very new to me and I'm having hard time finding the right solution. — Marko, Dec 27 '19 at 16:17
Fabian already did. More explanation at [cell factory in JavaFX](https://stackoverflow.com/questions/10519534/cell-factory-in-javafx). — jewelsea, Dec 27 '19 at 16:20
@Marko There's a example using a custom class here: https://code.makery.ch/library/javafx-tutorial/part2/#personoverviewcontroller-java (take a look at the code containing the `setCellValueFactory` part (as well as the code of the `Person` class). Basically the `cellValueFactory` gets a [`TableColumn.CellDataFeatures`](https://openjfx.io/javadoc/12/javafx.controls/javafx/scene/control/TableColumn.CellDataFeatures.html) object where `S` is the `TableView` item type and `T` is the cell value type and needs to return a `ObservableValue`. `Simple...Property` implements this. — fabian, Dec 27 '19 at 16:26
@fabian I expanded my original post with more code. Do you see why the app crashes? — Marko, Dec 27 '19 at 17:30
@fabian what I don't understand (ObjectType) and [objectIndex] part. Is it because I used Object[]? — Marko, Dec 27 '19 at 20:21
You've got the first type parameter of your columns wrong. It needs to match the type parameter of the `TableView` it is used with, i.e. in your case it needs to be `Object[]`. Furthermore you got the cast wrong: `CellDataFeatures.getValue` returns a `TableView` item, i.e. `Object[]` in this case. Note: you need to be careful with the type parameters for objects injected by `FXMLLoader`. `FXMLLoader` uses reflection and does not do any checks of type parameters. You could place any type in there and the loader wouldn't complain. You'll only see te issue when a cast hidden by generics happens. — fabian, Dec 27 '19 at 21:22
@fabian Could you please be more specific. I have written my first line of code two months ago so this is very confusing to me. If i put `idCol.setCellValueFactory(cd -> new SimpleIntegerProperty(((Object[])cd.getValue()).getId()).asObject());` IDE will complain and will not start the app. Is there an easier way to populate the TableView with two objects than this? I had the idea for an simple application that will help me learn how to code but I didn't imagine that this would not be so trivial. — Marko, Dec 27 '19 at 22:05

Populating TableView from database using a hibernate join query

0 Answers0