Fetching data from database into JTable with Hibernate HQL

Hibernate ORM
1

I am trying to fetch data from a database, see its entity-relationship diagram below: https://i.stack.imgur.com/7zpni.png

My goal is to represent the following data in a JTabla as such:
image

As you can see multiple joins are needed to calculate the actual quantity of an item in the inventory because it’s not stored explicitly in the database. First I have to multiply the Flow from Transactions (flow refers to the flow of items into and out from the inventory, thus it refers to an integer quantity) and In_Or_Out from Advicenote table, then I have to sum the values grouped according to the Item_IDs . By the way, I am using Hibernate with Apache Derby in NetBeans…here is where I am right now:

I have a FillData() method with a DefaultTableModel and a HQL that’s not working:

  private void FillData(){

DefaultTableModel dtm = new DefaultTableModel();
dtm.addColumn("Partner or Project");
dtm.addColumn("Category");
dtm.addColumn("Item");
dtm.addColumn("Quantity (in/or)");
dtm.addColumn("Date");

Font f = new Font("Georgia", Font.BOLD, 16);
JTableHeader header = ledgerTable.getTableHeader();
header.setFont(f);
ledgerTable.setRowHeight(25);

List<TableModel> result = new ArrayList<TableModel>();

try {
        session = sessionFactory.openSession();
        transaction = session.beginTransaction();
        Query query = session.createQuery("select c.name from Advicenote a join Transactions as t join items as i join categories as c group by a.advicenoteId");
        result = query.list();
        transaction.commit();
    } 
catch (HibernateException e) {
    if (transaction != null) {
        transaction.rollback();
    }
}
    for (int i = 0; i < result.size(); i++) {
        dtm.addRow(new Object[]{result.get(i).getPartnername(), result.get(i).getCategoryname(), result.get(i).getItemname(), result.get(i).getQuantity(), result.get(i).getDate()});
    }

this.ledgerTable.setModel(dtm);}

And I would use another TableModel class for adding the appropriate data in FillData() method (See List result = new ArrayList() above):

class TableModel {
        private String partnername;
        private String categoryname;
        private String itemname;
        private int quantity;
        private Date date;

    public TableModel(String partnername, String categoryname, String itemname, int quantity, Date date) {
        this.partnername = partnername;
        this.categoryname = categoryname;
        this.itemname = itemname;
        this.quantity = quantity;
        this.date = date;
    }

    public String getPartnername() {
        return partnername;
    }

    public void setPartnername(String partnername) {
        this.partnername = partnername;
    }

    public String getCategoryname() {
        return categoryname;
    }

    public void setCategoryname(String categoryname) {
        this.categoryname = categoryname;
    }

    public String getItemname() {
        return itemname;
    }

    public void setItemname(String itemname) {
        this.itemname = itemname;
    }

    public int getQuantity() {
        return quantity;
    }

    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

I don’t know if I am on the right track, but I would definitely need help with writing the appropriate HQL that would satisfy my needs, because I get a littlebit confused when multiple joins and group by and other concepts have to be included. Also, the data should be sortable by time period or a specific Partner/Project (see GUI at top), which could be done by further HQL queries, couldn’t it?. Any help would be greatly appreciated!

2

You don’t need a JPQL or HQL query to build a report. Use native SQL instead.