CODEWITHSUNDEEP
javamysqlhibernatespring-data-jpa
Faqeer
Faqeer

Reputation: 120

org.hibernate.hql.internal.ast.ErrorCounter - line 1:199: unexpected token: JOIN

I have two Entities 

@Entity
public class Video {    

    @Id
    @GeneratedValue
    private Integer id;     
    @Size(min = 5,max = 50, message = "Valid title is 5-50 chars")
    private String title;   
@ManyToMany(cascade = {CascadeType.MERGE})
@JoinTable(name = "vids_customers",
            joinColumns = @JoinColumn(name = "vids_id"),
            inverseJoinColumns = @JoinColumn(name = "customers_id"))
private List<Customer> customers;
// getters and setters goes here
}

and 

    @Entity
    public class Customer {    

        @Id
        @GeneratedValue
        private Integer id;

        @Size(min = 5,max = 15, message = "Valid name is 5-15 chars")
        private String name;

        @ManyToMany(mappedBy = "customers", cascade = {CascadeType.MERGE})  
        private List<Videos> videos;
        // getters and setters goes here
}

I have created a record as

Video title: Stand Up for the the Champs Customers 1-John, 2-James

I want to update the video record and want to add or remove customers

Video title: Stand Up for the ... Customers 1-John, 2-James, 3-Ria

so I have a query for updating Video as 

@Modifying
    @Query("UPDATE Video v SET v.title = :title WHERE v.id = :id")
    @Transactional
    void update(@Param("id") Integer id, @Param("title") String title);

the query above updates video record but does not and or remove customers, I wanted to get done that by making change in above query as 

@Modifying
    @Query("UPDATE Video v SET v.title = :title JOIN v.customers c WHERE v.id = :id")
    @Transactional
    void update(@Param("id") Integer id, @Param("title") String title);

I get 

ERROR: org.hibernate.hql.internal.ast.ErrorCounter - line 1:199: unexpected token: JOIN
ERROR: org.hibernate.hql.internal.ast.ErrorCounter - line 1:199: unexpected token: JOIN
line 1:199: unexpected token: JOIN
    at org.hibernate.hql.internal.antlr.HqlBaseParser.updateStatement(HqlBaseParser.java:254)
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:162)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:295)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
    at com.sun.proxy.$Proxy48.createQuery(Unknown Source)

How can I write a query so that I can update the video record and want to add or remove customers.

Upvotes: 0

Views: 2342

Answers (1)

Ramesh Kotha
Ramesh Kotha

Reputation: 8322

You can not update two or more entities by using HQL. First get the Video object through Hibernate session and add/remove customers to it

Video video = (Video)session.get(Video.class, id);
video.getCustomers().clear();//removes all the existing customers
Customer customer1 = new Customer();
customer1.getVideos().add(video)//adding video to the customer
video.getCustomers().add(customer1);
session.update(video)// this will insert row into video table, customers table and join table.

Upvotes: 1

Related Questions