Herre is the enitity-relatiosnhip diagram of my database tables
Advicenote table's In_Or_Out variable indicates whether items are going out or coming in from the company, while Transactions has a flow variable, which actually the number or amount of items that comes in or goes out from the system. I want to query the actual quantity of the items in the inventory, by multiplying Advicenote.In_Or_Out and Transactions.Flow, then I would sum then and group them by the Items.Item_ID. I would appreciate any help!
Below you can see the HQL that I've written for this purpose and the error message that I recieve:
public List<ItemAndQuantity> getQuantity()
{
List<ItemAndQuantity> quantity = new ArrayList<ItemAndQuantity>();
try {
session = sessionFactory.openSession();
transaction = session.beginTransaction();
Query query = session.createQuery("select sum(t.flow * a.inOrOut) from Advicenote a join a.Transactions t join t.Items i group by i.itemId");
quantity = query.list();
transaction.commit();
}
catch (HibernateException e) {
if (transaction != null) {
transaction.rollback();
}
}
finally {
session.close();}
return quantity;
}
Exception in thread "AWT-EventQueue-0" java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: Transactions of: classes.Advicenote [select sum(t.flow * a.inOrOut) from classes.Advicenote a join a.Transactions t join t.Items i group by i.itemId] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:713) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23) at stock.view.StockView.getQuantity(StockView.java:145) at stock.view.StockView.FillData(StockView.java:62) at stock.view.StockView.(StockView.java:38) at main.view.MainMenu.stockMenuItemActionPerformed(MainMenu.java:290) at main.view.MainMenu$FormListener.actionPerformed(MainMenu.java:240) at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967) at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308) at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405) at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262) at java.desktop/javax.swing.AbstractButton.doClick(AbstractButton.java:369) at java.desktop/javax.swing.plaf.basic.BasicMenuItemUI.doClick(BasicMenuItemUI.java:1020) at java.desktop/javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(BasicMenuItemUI.java:1064) at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:297) at java.desktop/java.awt.Component.processMouseEvent(Component.java:6632) at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342) at java.desktop/java.awt.Component.processEvent(Component.java:6397) at java.desktop/java.awt.Container.processEvent(Container.java:2263) at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5008) at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321) at java.desktop/java.awt.Component.dispatchEvent(Component.java:4840) at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4918) at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4547) at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4488) at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307) at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2772) at java.desktop/java.awt.Component.dispatchEvent(Component.java:4840) at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772) at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721) at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715) at java.base/java.security.AccessController.doPrivileged(Native Method) at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85) at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95) at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745) at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743) at java.base/java.security.AccessController.doPrivileged(Native Method) at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85) at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742) at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203) at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124) at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113) at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109) at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101) at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90) Caused by: org.hibernate.QueryException: could not resolve property: Transactions of: classes.Advicenote [select sum(t.flow * a.inOrOut) from classes.Advicenote a join a.Transactions t join t.Items i group by i.itemId] at org.hibernate.QueryException.generateQueryException(QueryException.java:120) at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:118) at org.hibernate.engine.query.spi.HQLQueryPlan.(HQLQueryPlan.java:78) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:158) at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:595) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704) ... 44 more Caused by: org.hibernate.QueryException: could not resolve property: Transactions of: classes.Advicenote at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:73) at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:67) at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1983) at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:396) at org.hibernate.hql.internal.ast.tree.FromElement.getPropertyType(FromElement.java:515) at org.hibernate.hql.internal.ast.tree.DotNode.getDataType(DotNode.java:686) at org.hibernate.hql.internal.ast.tree.DotNode.prepareLhs(DotNode.java:264) at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:204) at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:114) at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:109) at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:408) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3920) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3706) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3584) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:720) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:576) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ... 50 more
At lastly here are the affected classes and xml mappings: Advicenote
package classes;
import java.util.Date;
import java.util.Set;
public class Advicenote implements java.io.Serializable {
private int advicenoteId;
private Short inOrOut;
private Date advicedate;
private Partner partner;
private Set<Transactions> transactions;
public Advicenote() {
}
public Advicenote(int advicenoteId, Short inOrOut, Date advicedate, Partner partner, Set<Transactions> transactions) {
this.advicenoteId = advicenoteId;
this.inOrOut = inOrOut;
this.advicedate = advicedate;
this.partner = partner;
this.transactions = transactions;
}
public int getAdvicenoteId() {
return this.advicenoteId;
}
public void setAdvicenoteId(int advicenoteId) {
this.advicenoteId = advicenoteId;
}
public Date getAdvicedate() {
return advicedate;
}
public void setAdvicedate(Date advicedate) {
this.advicedate = advicedate;
}
public Short getInOrOut() {
return this.inOrOut;
}
public void setInOrOut(Short inOrOut) {
this.inOrOut = inOrOut;
}
public Partner getPartner() {
return partner;
}
public void setPartner(Partner partner) {
this.partner = partner;
}
public Set<Transactions> getTransactions() {
return transactions;
}
public void setTransactions(Set<Transactions> transactions) {
this.transactions = transactions;
}
}
Advicenote XML
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Feb 9, 2019, 3:57:52 PM by Hibernate Tools 4.3.1 -->
<hibernate-mapping>
<class name="classes.Advicenote" table="ADVICENOTE" schema="APP" optimistic-lock="version">
<id name="advicenoteId" type="int" column="ADVICENOTE_ID">
<generator class="native" />
</id>
<property name="advicedate" type="date" column="ADVICE_DATE" length="10" />
<property name="inOrOut" type="java.lang.Short" column="IN_OR_OUT" />
<many-to-one
name="partner" class="classes.Partner"/>
<set name="transactions" table="TRANSACTIONS" inverse="false" cascade="all" lazy="true" fetch="select">
<key column="ADVICENOTE_ID" not-null="true"/>
<one-to-many class="classes.Transactions"/>
</set>
</class>
</hibernate-mapping>
Transactions class:
package classes;
public class Transactions implements java.io.Serializable {
private int transactionsId;
private int flow;
private Integer netPrice;
private Advicenote advicenote;
private Items item;
public Transactions() {
}
public Transactions(int transactionsId, int flow, Integer netPrice, Advicenote advicenote, Items item) {
this.transactionsId = transactionsId;
this.flow = flow;
this.netPrice = netPrice;
this.advicenote = advicenote;
this.item = item;
}
public int getTransactionsId() {
return this.transactionsId;
}
public void setTransactionsId(int transactionsId) {
this.transactionsId = transactionsId;
}
public int getFlow() {
return this.flow;
}
public void setFlow(int flow) {
this.flow = flow;
}
public Integer getNetPrice() {
return this.netPrice;
}
public void setNetPrice(Integer netPrice) {
this.netPrice = netPrice;
}
public Advicenote getAdvicenote() {
return advicenote;
}
public void setAdvicenote(Advicenote advicenote) {
this.advicenote = advicenote;
}
public Items getItem() {
return item;
}
public void setItem(Items item) {
this.item = item;
}
}
Transactions XML
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Feb 9, 2019, 3:57:52 PM by Hibernate Tools 4.3.1 -->
<hibernate-mapping>
<class name="classes.Transactions" table="TRANSACTIONS" schema="APP" optimistic-lock="version">
<id name="transactionsId" type="int" column="TRANSACTIONS_ID">
<generator class="native" />
</id>
<property name="flow" type="int" column="FLOW" not-null="true" />
<property name="netPrice" type="java.lang.Integer" column="NET_PRICE" />
<many-to-one name="advicenote" class="classes.Advicenote"/>
<many-to-one name="item" class="classes.Items" fetch="select" cascade="save-update" column="ITEM_ID" unique="true" not-null="true"/>
</class>
</hibernate-mapping>
Items
package classes;
public class Items implements java.io.Serializable {
private int itemId;
private String name;
private Integer netPrice;
private Integer vatRate;
private Categories category;
//has-a relationship
public Items() {
}
public Items(int itemId, String name, Integer netPrice, Integer vatRate, Categories category) {
this.itemId = itemId;
this.name = name;
this.netPrice = netPrice;
this.vatRate = vatRate;
this.category = category;
}
public int getItemId() {
return this.itemId;
}
public void setItemId(int itemId) {
this.itemId = itemId;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Integer getNetPrice() {
return this.netPrice;
}
public void setNetPrice(Integer netPrice) {
this.netPrice = netPrice;
}
public Integer getVatRate() {
return this.vatRate;
}
public void setVatRate(Integer vatRate) {
this.vatRate = vatRate;
}
public Categories getCategory() {
return category;
}
public void setCategory(Categories category) {
this.category = category;
}
}
Items XML
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="classes.Items" table="ITEMS" schema="APP" optimistic-lock="version">
<id name="itemId" type="int" column="ITEM_ID">
<generator class="native" />
</id>
<property name="name" type="string" column="NAME" length="100"/>
<property name="netPrice" type="java.lang.Integer" column="NET_PRICE"/>
<property name="vatRate" type="java.lang.Integer" column="VAT_RATE"/>
<many-to-one name="category" class="classes.Categories" fetch="select" column="CATEGORY_ID" not-null="true"/>
</class>
</hibernate-mapping>
Categories XML
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Feb 9, 2019, 3:57:52 PM by Hibernate Tools 4.3.1 -->
<hibernate-mapping>
<class name="classes.Categories" table="CATEGORIES" schema="APP" optimistic-lock="version">
<id name="categoryId" type="int">
<column name="CATEGORY_ID" />
<generator class="native" />
</id>
<property name="name" type="string">
<column name="NAME" length="100" />
</property>
<set name="items" table="ITEMS" inverse="false" cascade="all" lazy="false" fetch="select">
<key>
<column name="CATEGORY_ID" not-null="true" />
</key>
<one-to-many class="classes.Items" />
</set>
</class>
</hibernate-mapping>
Your query:
select sum(t.flow * a.inOrOut) from Advicenote a join a.Transactions t join t.Items i group by i.itemId
should be:
select sum(t.flow * a.inOrOut) from Advicenote a join a.transactions t join t.item i group by i.itemId
(transactions in lowercase and item also in lowercase and without 's'). They refer to the fields, not the classes.