# Fetching data from database into JTable with Hibernate HQL

**URL:** <https://discourse.hibernate.org/t/fetching-data-from-database-into-jtable-with-hibernate-hql/2323>\
**Category:** Hibernate ORM\
**Created:** [February 20, 2019, 8:07am UTC](https://discourse.hibernate.org/t/fetching-data-from-database-into-jtable-with-hibernate-hql/2323 "2019-02-20T08:07:14Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Fluellen](https://avatars.discourse-cdn.com/v4/letter/f/f1d935/32.png) [@Fluellen](https://discourse.hibernate.org/u/Fluellen)\
**Post date:** [February 20, 2019, 8:07am UTC](https://discourse.hibernate.org/t/fetching-data-from-database-into-jtable-with-hibernate-hql/2323/1 "2019-02-20T08:07:14Z")

</div>

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

My goal is to represent the following data in a JTabla as such:  
 ![image](https://cdck-file-uploads-canada1.s3.dualstack.ca-central-1.amazonaws.com/flex035/uploads/hibernate/original/1X/0d70f9442c71136083eaf1dfd3ac5bbe1e8eb5c3.png)

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:

```auto
  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):

```auto
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!**

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 20, 2019, 2:46pm UTC](https://discourse.hibernate.org/t/fetching-data-from-database-into-jtable-with-hibernate-hql/2323/2 "2019-02-20T14:46:10Z")

</div>

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