Query update using hibernate runs into QueryException : Unable to resolve path

Question

I have following entities, and need to update a field that is in a specific field and its removedDate is null. But the following code returns exception.

@Entity
public class Cart implements Serializable {

    @Id
    @GeneratedValue
    private Long id;

    @OneToMany(cascade = CascadeType.ALL)
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<CartItem> items;

    public Cart() {
    }

     getters and setters

}

@Entity
public class CartItem {

    @Id
    @GeneratedValue
    private long id;

    @ManyToOne
    private Product pro;

    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date addedDate;

    @Temporal(javax.persistence.TemporalType.TIMESTAMP)
    private Date removedDate;

    getters and setters

}

Hibernate Code 1

Query query = session.createQuery("UPDATE CartItem SET removedDate = :currentDateTime "
                        + " WHERE CartItem.id IN (Select Cart.items.id From Cart"
                        + " WHERE Cart.id = :cartId"
                        + " AND Cart.items.pro.id = :pro"
                        + " AND Cart.items.removedDate is null)");
query.setParameter("currentDateTime", dt.getCurrentDateTime());
query.setParameter("cartId", cartId);
query.setParameter("pro", proId);

int result = query.executeUpdate();

Exception of Code 1

SEVERE:   org.hibernate.QueryException: Unable to resolve path [CartItem.id], unexpected 
          token [CartItem] [UPDATE com.myproject.CartItem SET removedDate = 
           :currentDateTime  WHERE CartItem.id IN (Select Cart.items.id From 
           com.myproject.Cart WHERE Cart.id = :cartId AND cart.items.pro.id = :proId 
           AND Cart.items.removedDate is null))]
    at org.hibernate.hql.internal.ast.tree.IdentNode.resolveAsNakedComponentPropertyRefLHS(IdentNode.java:245)
    at org.hibernate.hql.internal.ast.tree.IdentNode.resolve(IdentNode.java:110)
    at org.hibernate.hql.internal.ast.tree.DotNode.resolveFirstChild(DotNode.java:177)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.lookupProperty(HqlSqlWalker.java:577)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.addrExpr(HqlSqlBaseWalker.java:4719)

Hibernate Code 2

  Query query = session.createQuery("UPDATE CartItem SET removedDate = :currentDateTime "
                        + " WHERE id IN (Select items.id From Cart"
                        + " WHERE id = :CartId"
                        + " AND items.pro.id = :pro"
                        + " AND items.removedDate is null)");

Exception of Code 2

SEVERE:   org.hibernate.QueryException: illegal attempt to dereference collection 
          [{synthetic-alias}{non-qualified-property-ref}items] with element property 
          reference [id] [UPDATE com.myproject.CartItem SET removedDate = 
          :currentDateTime  WHERE id IN (Select items.id From com.myproject.Cart WHERE 
          id = :cartId AND items.pro.id = :pro AND items.removedDate is null)]
    at org.hibernate.hql.internal.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:68)
at org.hibernate.hql.internal.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:550)
at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:246)
at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:118)
at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:114)

Try this by adding an alias in the inner select query.. `Query query = session.createQuery("UPDATE CartItem SET removedDate = :currentDateTime " + " WHERE id IN (Select cart.items.id From Cart cart" + " WHERE cart.id = :CartId" + " AND cart.items.pro.id = :pro" + " AND cart.items.removedDate is null)");` — Hrishikesh, Mar 10 '14 at 05:28

score 1 · Answer 1 · answered Mar 12 '14 at 12:46

1

Why don't you make your association bidirectional?
Add this to your CartItem entity:

@ManyToOne
private Cart cart;

Set the mappedBy on your cartItem fied in Cart:

@OneToMany(cascade = CascadeType.ALL, mappedBy="cart")
@LazyCollection(LazyCollectionOption.FALSE)
private List<CartItem> items;

The resulting HQL would be much simpler (and should work):

"UPDATE CartItem c SET c.removedDate = :currentDateTime "
            + " WHERE c.cart.id = :cartId" 
            + " AND c.pro.id = :pro"
            + " AND c.removedDate is null"

answered Mar 12 '14 at 12:46

Julien

1,087
7
15

it returns org.hibernate.AnnotationException: mappedBy reference an unknown target entity property – AlexCartio1 Mar 17 '14 at 02:02
Did you use the same name for your ManyToOne property "cart" and in your mappedBy attribute? – Julien Mar 17 '14 at 08:54
+1 for bidirectional association. This really is the best way to 'fix' this query and make the structure simpler for future use. All our 160-odd `OneToMany` associations use `mappedBy`. – DuncanKinnear Mar 17 '14 at 22:00

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

Try this by adding an alias in the inner select query.

  Query query = session.createQuery("UPDATE CartItem SET removedDate = :currentDateTime "
                        + " WHERE id IN (Select cart.items.id From Cart cart"
                        + " WHERE cart.id = :CartId"
                        + " AND cart.items.pro.id = :pro"
                        + " AND cart.items.removedDate is null)");

EDIT 2 I did a bit of reading and found out that the object.collection.id works only for a 1:1 or an N:1 relation, not for a 1:N relation, which is what you have. Try this.

SELECT items.id
FROM Cart cart
LEFT JOIN cart.items items
WHERE cart.id = :CartId AND items.pro.id = :pro AND items.removedDate is null

Here is more info, info, info

edited May 23 '17 at 11:57

Community

1
1

answered Mar 10 '14 at 05:39

Hrishikesh

2,033
1
16
26

it throws SEVERE: org.hibernate.QueryException: illegal attempt to dereference collection [id.items] with element property reference [id] – AlexCartio1 Mar 10 '14 at 05:43
I changed Cart cart to Cart but same exception – AlexCartio1 Mar 10 '14 at 22:44
1

when I copy the query in parenthesis into my database manager it throws error, when I change LEFT Join to Join it works but using hibernate neither works – AlexCartio1 Mar 10 '14 at 23:10
1

@AlexCartio1 Use SQLQuery instead of createQuery() – Prabha Mar 17 '14 at 08:36

score 0 · Answer 3 · answered Mar 18 '14 at 02:25

Thanks to Hrishikesh's comment, I found the answer by providing the exact SQLQuery.

UPDATE cartItem SET removedDate = :currentDateTime"
                        + " WHERE pro = :pro"
                        + " AND removedDate IS NULL"
                        + " AND id IN 
                            ( SELECT items_id from Cart_CartItem WHERE Cart_id = :CartId)

Florent Bayle · Answer 4 · 2014-03-10T06:40:12.203

-1

Try that:

    Query query = session.createQuery("UPDATE CartItem SET removedDate = :currentDateTime "
                    + " WHERE id IN (Select ci.id From Cart c inner join c.items ci"
                    + " WHERE c.id = :cartId"
                    + " AND ci.pro.id = :pro"
                    + " AND ci.removedDate is null)");

edited Mar 10 '14 at 06:40

answered Mar 10 '14 at 03:31

Florent Bayle

11,520
4
34
47

@AlexCartio1 Can you try to use `id IN` instead of `CartItem.id IN` ? – Florent Bayle Mar 10 '14 at 05:06
1

@AlexCartio1 Updated the answer (after I took some time to set-up hibernate on my test environment). – Florent Bayle Mar 10 '14 at 06:40
it returns org.hibernate.exception.GenericJDBCException: could not execute statement – AlexCartio1 Mar 17 '14 at 01:39
@AlexCartio1 Are you using MySql ? Because it doesn't support to have the same table in the `UPDATE` and the sub-query. If not, can you paste more of the stack trace ? – Florent Bayle Mar 18 '14 at 05:25

Query update using hibernate runs into QueryException : Unable to resolve path

4 Answers4