[Fluellen]

So my question is: how to manipulate database columns in order to get the quantity of items using Hibernate. I am using NetBeans IDE with Hibernate 5.4 and an Apache Derby database. I've never used Hibernate and I got a little-bit lost.

Here you can see the entity relationship diagram of my database's tables:

Here is the UI of the Java inventory application I want to create:


My goal is to calculate the quantities of the items, for this I thought that I would have an In_Our_Out column, which could be either -1 (outgoing shipment) or 1 (incoming shipment). In the transactions table I would have a Flow column, which would be the amount that is actually received or transferred. So I would have to first multiply the Flow values with either 1 or -1 which would be based on the connection between the Advicenote_IDs, am I correct? Then I would have to add the calculated negative and positive values according to the Item_IDs to get the quantity of each type of items.

So I am lost because I don't know how to breakdown this process and how to make the necessary associations with the tables. Should I indicate the relationship between the tables in their entity classes using annotations (creating a transient column could be a solution or just simply indicating one-to-one or one-to-many relationship...and what if I used foreign keys? I am not sure how these work and feel quite confused.) or should I use join statements in this case? Or maybe the best would be to revamp the whole structure of the tables to store the quantities? How can I add and multiply the table columns with each other?

I would be super grateful for any help!

Below is the code for creating the a List<> for the Flow column:

List<Transactions> list = null;
Session session = sessionFactory.getCurrentSession();
Transaction tx = session.beginTransaction()
list = session.createQuery("select t.Flow from TRANSACTIONS t").list();