CODEWITHSUNDEEP
javahibernatejpa
Sergey Vasilyevic
Sergey Vasilyevic

Reputation: 21

convert subquery to a join in jpql

I have two classes ClassA and ClassB. Many ClassB's can have association with a single ClassA, hence a many-to-one relationship from ClassB to ClassA. Relationship is like:

ClassA {
    @ManyToOne(optional = false)
    @JoinColumn(name = "ClassB_ID", referencedColumnName = "ID", nullable = false)
    private ClassB classB = new ClassB();
}

I have a query like this: 

delete from ClassA as a 
where a.classB in (select b from ClassB as b where b.attr1=?1 and b.attr2=?2)
and a.attr3=?3

And i converted this query like:

delete from ClassA as a join a.classb as b where b.attr1=?1 and b.attr2=?2
and a.attr3=?3"

When i run this i get below exception :

ERROR PARSER    - line 1:28: unexpected token: join

org.springframework.dao.InvalidDataAccessApiUsageException:

node to traverse cannot be null!; nested exception is java.lang.IllegalArgumentException: node to traverse cannot be null!

Can anybody help ?

Thanks.

Upvotes: 2

Views: 2056

Answers (2)

Pascal Thivent
Pascal Thivent

Reputation: 570595

I would phrase it like this: no JOIN can be used in a bulk query. From the official documentation:

13.4. DML-style operations

As already discussed, automatic and transparent object/relational mapping is concerned with the management of the object state. The object state is available in memory. This means that manipulating data directly in the database (using the SQL Data Manipulation Language (DML) the statements: INSERT, UPDATE, DELETE) will not affect in-memory state. However, Hibernate provides methods for bulk SQL-style DML statement execution that is performed through the Hibernate Query Language (HQL).

The pseudo-syntax for UPDATE and DELETE statements is: ( UPDATE | DELETE ) FROM? EntityName (WHERE where_conditions)?.

Some points to note:

  • In the from-clause, the FROM keyword is optional
  • There can only be a single entity named in the from-clause. It can, however, be aliased. If the entity name is aliased, then any property references must be qualified using that alias. If the entity name is not aliased, then it is illegal for any property references to be qualified.
  • No joins, either implicit or explicit, can be specified in a bulk HQL query. Sub-queries can be used in the where-clause, where the subqueries themselves may contain joins.
  • The where clause is also optional.

So what you currently have (using a sub-query) is optimal and there is no reason to rewrite it.

References

Upvotes: 1

Gustav Barkefors
Gustav Barkefors

Reputation: 5086

You cannot DELETE from a JOIN. Why did you want to rewrite the DELETE statement anyway?

Upvotes: 1

Related Questions