CODEWITHSUNDEEP
jpa
maximus
maximus

Reputation: 4302

JPA delete query giving errors

I am using the following delete query in JPA:

DELETE FROM TeamInfo team WHERE team.id IN (SELECT t.id FROM TeamInfo t JOIN Manager m ON m.teamInfo = t WHERE m.localUserId IS NULL)

But I am receiving the errors:

ERROR o.h.hql.internal.ast.ErrorCounter - line 1:173: unexpected token: ON
antlr.NoViableAltException: unexpected token: ON
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1694) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1349) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:1055) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:701) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.union(HqlBaseParser.java:990) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.subQuery(HqlBaseParser.java:3719) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.compoundExpr(HqlBaseParser.java:3089) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.inList(HqlBaseParser.java:2884) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2730) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:2456) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:2420) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:2336) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:2301) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.expression(HqlBaseParser.java:2011) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:1787) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:452) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.deleteStatement(HqlBaseParser.java:264) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.antlr.HqlBaseParser.statement(HqlBaseParser.java:146) [HqlBaseParser.class:4.2.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:268) [QueryTranslatorImpl.class:4.2.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182) [QueryTranslatorImpl.class:4.2.8.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:138) [QueryTranslatorImpl.class:4.2.8.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105) [HQLQueryPlan.class:4.2.8.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) [HQLQueryPlan.class:4.2.8.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168) [QueryPlanCache.class:4.2.8.Final]
    at org.hibernate.internal.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:1085) [SessionFactoryImpl.class:4.2.8.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:522) [SessionFactoryImpl.class:4.2.8.Final]
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1797) [Configuration.class:4.2.8.Final]
    at org.hibernate.ejb.EntityManagerFactoryImpl.<init>(EntityManagerFactoryImpl.java:96) [EntityManagerFactoryImpl.class:4.2.8.Final]
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:914) [Ejb3Configuration.class:4.2.8.Final]
    at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:899) [Ejb3Configuration.class:4.2.8.Final]
    at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:59) [HibernatePersistence.class:4.2.8.Final]
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:63) [Persistence.class:1.0.1.Final]
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:47) [Persistence.class:1.0.1.Final]
    at com.magnifi.pennantrace.dao.hibernate.HibernateUtil.getEntityManagerFactory(HibernateUtil.java:18) [HibernateUtil.class:na]
    at com.magnifi.pennantrace.dao.hibernate.HibernateUtil.currentEntityManager(HibernateUtil.java:27) [HibernateUtil.class:na]
    at com.magnifi.schedserv.threads.SchedulerThread.run(SchedulerThread.java:32) [SchedulerThread.class:na]

What is the problem there?

Upvotes: 2

Views: 900

Answers (2)

landal79
landal79

Reputation: 1520

Your query doesn't appear to be a correct JPA query, check this link.
The join attribute is already specified in the mapping.

For intance if you have a one-to-many bidirectional relationship like the following:

 class TeamInfo{

   //...

   @OneToMany(mappedBy="teamInfo")
   private List<Manager> managers;

   //...

 }

 class Manager {

   //...

   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="TEAM_INFO_ID")
   private TeamInfo teamInfo;

   //...

 }

The correct join query is the following: 

   select t.id FROM TeamInfo t join t.managers m where m.localUserId is null

Or the opposite case:

 class TeamInfo{

   //...

   @ManyToOne(fetch=FetchType.LAZY)
   @JoinColumn(name="MANAGER_ID")     
   private Manager manager;

   //...

 }

 class Manager {

   //...

   @OneToMany(mappedBy="manager")
   private List<TeamInfo> teamInfo;

   //...

 }

The correct join query is the following: 

   select t.id FROM Manager m join m.teamInfo t where m.localUserId is null

Upvotes: 1

Eelke
Eelke

Reputation: 21993

The problem is that there is no ON in jpql. The following is how that join should be expressed:

DELETE FROM TeamInfo team 
WHERE team.id IN (
  SELECT t.id
  FROM Manager m JOIN m.teamInfo t
  WHERE m.localUserId IS NULL
)

Upvotes: 2

Related Questions