Hibernate Annotation Query confusion

Question

i have 4 tables in which purchaseOrder has relation ship with sullpier ,item,and ordertype. reference key of all table to purchaseOrder is supplierIdfk,itemIdfk,orderTypeIdfk.

so my question how can i get record of purchaseOrder record who has suplliername="XXX" , suplliername is one of the column of supplier table.

i am using hibernate annotation and for backhand i am using mysql.

i have mapped all the entity with annotation. Thanks For your Time.

My table looks like this

CREATE TABLE `purchaseorder` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`discount` double NOT NULL,
`finalAmount` double NOT NULL,
`remark` varchar(255) DEFAULT NULL,
`shipDate` datetime DEFAULT NULL,
`unitPrice` double NOT NULL,
`itemIdfk` int(11) DEFAULT NULL,
`orderIdfk` int(11) DEFAULT NULL,
`supplierIdfk` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `indexPurchaseOrderShipDate` (`shipDate`),
KEY `FKDFD4BAAD36B52348` (`itemIdfk`),
KEY `FKDFD4BAADFD3C54D4` (`orderIdfk`),
KEY `FKDFD4BAAD79028B3A` (`supplierIdfk`),
CONSTRAINT `FKDFD4BAAD36B52348` FOREIGN KEY (`itemIdfk`) REFERENCES `item` (`id`),
CONSTRAINT `FKDFD4BAAD79028B3A` FOREIGN KEY (`supplierIdfk`) REFERENCES `supplier`    (`id`),
CONSTRAINT `FKDFD4BAADFD3C54D4` FOREIGN KEY (`orderIdfk`) REFERENCES `ordertype` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

my Entity code for purchaseOrder is like this.

 package org.chillies.database;
 import java.util.Date;
 import javax.persistence.Entity;
 import javax.persistence.GeneratedValue;
 import javax.persistence.GenerationType;
 import javax.persistence.Id;
 import javax.persistence.JoinColumn;
 import javax.persistence.ManyToOne;
 import org.hibernate.annotations.Index;
 @Entity
 public class PurchaseOrder {
private int id;
private Date shipDate;
private double unitPrice;
private double discount;
private double finalAmount;
private String remark;
private OrderType orderType;
private Supplier supplier;
private Item item;

private static final String KeyOrderId = "orderIdfk";
private static final String KeySupplierId = "supplierIdfk";
private static final String KeyItemId = "itemIdfk";


@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}
@Index(name = "indexPurchaseOrderShipDate")
public Date getShipDate() {
    return shipDate;
}

public void setShipDate(Date shipDate) {
    this.shipDate = shipDate;
}

public double getUnitPrice() {
    return unitPrice;
}

public void setUnitPrice(double unitPrice) {
    this.unitPrice = unitPrice;
}

public double getDiscount() {
    return discount;
}

public void setDiscount(double discount) {
    this.discount = discount;
}

public double getFinalAmount() {
    return finalAmount;
}

public void setFinalAmount(double finalAmount) {
    this.finalAmount = finalAmount;
}

public String getRemark() {
    return remark;
}

public void setRemark(String remark) {
    this.remark = remark;
}


@ManyToOne
@JoinColumn(name = KeyOrderId)
public OrderType getOrderType() {
    return orderType;
}

public void setOrderType(OrderType orderType) {
    this.orderType = orderType;
}

@ManyToOne
@JoinColumn(name = KeySupplierId)
public Supplier getSupplier() {
    return supplier;
}

public void setSupplier(Supplier supplier) {
    this.supplier = supplier;
}

@ManyToOne
@JoinColumn(name = KeyItemId)
public Item getItem() {
    return item;
}

public void setItem(Item item) {
    this.item = item;
}
 }

actually i am using query code like this but it gives me error ...

public PurchaseOrder getPurchaseOrder(String supplierName) {
    Session session = null;
    PurchaseOrder purchaseOrder = new PurchaseOrder();

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        Query query=session.createQuery("FROM PurchaseOrder WHERE PurchaseOrder.supplierIdfk=Supplier.id AND Supplier.name=?");
        purchaseOrder = (PurchaseOrder)query.uniqueResult();
        query.setString("Supplier.name",supplierName);
        session.getTransaction().commit();
    } catch (HibernateException e) {
        if (session != null) {
            session.getTransaction().rollback();
            e.printStackTrace();
        }
    } finally {
        if (session != null) {
            session.close();
        }
    }
    return purchaseOrder;
}

and my error code is like this

 org.hibernate.QueryException: Unable to resolve path [PurchaseOrder.supplierIdfk], unexpected token [PurchaseOrder] [FROM org.chillies.database.PurchaseOrder WHERE PurchaseOrder.supplierIdfk=Supplier.id AND Supplier.name=?]
at org.hibernate.hql.ast.tree.IdentNode.resolveAsNakedComponentPropertyRefLHS(IdentNode.java:219)
at org.hibernate.hql.ast.tree.IdentNode.resolve(IdentNode.java:108)
at org.hibernate.hql.ast.tree.DotNode.resolveFirstChild(DotNode.java:175)
at org.hibernate.hql.ast.HqlSqlWalker.lookupProperty(HqlSqlWalker.java:550)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.addrExpr(HqlSqlBaseWalker.java:4543)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1289)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4243)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3722)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1864)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1789)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:818)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:604)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:288)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:231)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:254)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at      org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at     org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
at org.chillies.dataaccesslayer.DataAccessLayer.getPurchaseOrder(DataAccessLayer.java:748)
at TestDataAccess.purchaseOrderDalTest(TestDataAccess.java:368)
at TestDataAccess.main(TestDataAccess.java:404)
Exception in thread "main" java.lang.NullPointerException
at TestDataAccess.purchaseOrderDalTest(TestDataAccess.java:370)
at TestDataAccess.main(TestDataAccess.java:404)

i ahve tried simply joining, left outer join ,i already wrote even whole query with all filed but it keeps giving me error like null.supplierIdfk not found — chiragchavda.ks, Mar 26 '14 at 09:42
How does your table and your entity for PurchaseOrder look like? If "supplierIdfk" is a field in your table and the entity-class "PurchaseOrder" has a different var-name, HQL will not find the field. In HQL you have to use the Object and its class-variables. — DennisH, Mar 26 '14 at 10:12

Jay · Answer 1 · 2014-03-26T10:00:16.467

0

Assuming you have done all the mappings correctly, the query would be like this,

    List<PurchaseOrder> purchaseOrders;
    Session session = HibernateUtil.getSession();
    Query query = session.createQuery("from PurchaseOrder  po where po.supplierName = ?1");
    query.setString(1, pass_your_supplier_name);
    purchaseOrders = query.list();

edited Mar 26 '14 at 10:00

answered Mar 26 '14 at 09:43

Jay

9,189
12
56
96

actually i can insert update delete reord and my mapping is done correctly but the problem is my supllier table has filed with column name "name" and item table also has field with column name "name" so how can it works if 2 parent table has same name of column – chiragchavda.ks Mar 26 '14 at 09:47
If two table has same column name, either you can refer them using alias eg supplier.name, item.name. If you get confused you can change it in your Entity to have different field like supplierName & itemName – Jay Mar 26 '14 at 09:53
i ahve updatedd my question with code now can you tell pleasse – chiragchavda.ks Mar 26 '14 at 10:02
Do you such field supplierIdfk in your PurchaseOrder entity ? Why can't you have query like "FROM PurchaseOrder po WHERE po.supplier.name=?1" – Jay Mar 26 '14 at 10:06
actually in PurchaseOrder table in database i have filed name supplierIdfk which is refarance key to purchaseOrder from supplier , in mean while i have Supplier instance in Purchaseorder Entity which is annotated with relation ship – chiragchavda.ks Mar 26 '14 at 10:11
If you used session.createQuery("") all its contents are Java related HQL/JPQL so no DB sql should be refered inside like supplierIdfk etc. If you want to use DB SQL use session.createSQLQuery("") – Jay Mar 26 '14 at 10:29
but is i use sql query then i think it will cause a proble in entity to result mapping – chiragchavda.ks Mar 26 '14 at 10:32
Please read http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html_single/#queryhql – Jay Mar 26 '14 at 10:37

DennisH · Answer 2 · 2014-03-26T10:45:39.460

Try

Query query=session.createQuery("select new fu.bar.PurchaseOrderDto(po.id, po.x, po.y, po.z) FROM PurchaseOrder po, Supplier sup WHERE po.supplier.id=sup.id AND sup.name=?");

or

Query query=session.createQuery("FROM PurchaseOrder po WHERE po.supplier.id=sup.id AND sup.name=?");

As I said in my comment: If u are using HQL, you have to use the java-objects variables, not the database-fields.

I don't know if the second one is working, because the Supplier is not mentioned after "from". If it doesn't work, create 2 constructors in your entity: empty and one with the fields you need OR create a DTO with the fields you need. Then use the first query.

EDIT: Second problem will be the "query.setString". It does not do what you want. Use setParameter instead:

    [...]
    Query query=session.createQuery("select new fu.bar.PurchaseOrderDto(po.id, po.x, po.y, po.z) FROM PurchaseOrder po, Supplier sup WHERE po.supplier.id=sup.id AND sup.name= :supName ");
    query.setParameter("supName",supplierName);
    purchaseOrder = (PurchaseOrder)query.uniqueResult();
    session.getTransaction().commit();
    [...]

DTO's are simple plain objects used to carry data http://stackoverflow.com/questions/1612334/difference-between-dto-vo-pojo-javabeans — DennisH, Mar 26 '14 at 12:20
i got my answer . "FROM PurchaseOrder po LEFT JOIN FETCH po.supplier sup WHERE sup.name='"+supplierName+"'" — chiragchavda.ks, Mar 27 '14 at 06:08

score 0 · Accepted Answer · answered Apr 01 '14 at 11:22

0

Thanks for all the reply and your precious time.

I have found my answer.

i have build my query like this.

 createQuery("FROM PurchaseOrder po LEFT JOIN FETCH po.supplier sup WHERE sup.name=:supName").setString("supName",suplierName);

answered Apr 01 '14 at 11:22

chiragchavda.ks

532
7
25

Hibernate Annotation Query confusion

3 Answers3