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: https://i.stack.imgur.com/R04OK.png
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_ID s 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 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!