Simple JPAUpdateClause execution throws SQL error

[Michael Tontchev]

I have a City entity that has a name and a Country object in it, which in turn also has a name.

So I have the following:

QCity city = QCity.city;

 

JPAUpdateClause updateClause = new JPAUpdateClause(em, city);

updateClause = updateClause.set(city.name, "Americaville").where(city.country.name.eq("Zimbabwe"));

updateClause.execute();

When I try to run this, I get

Apr 16, 2015 3:13:34 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions

WARN: SQL Error: 42001, SQLState: 42001

Apr 16, 2015 3:13:34 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions

ERROR: Syntax error in SQL statement "UPDATE CITY CROSS[*] JOIN  SET NAME=? WHERE NAME=? "; expected "., AS, SET"; SQL statement:

update CITY cross join  set NAME=? where NAME=? [42001-170]

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)

at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771)

at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:87)

at com.mysema.query.jpa.impl.JPAUpdateClause.execute(JPAUpdateClause.java:69)

When I run the update clause without a where(), it works just fine. It's just when I add the where() that I start getting problems.

Any idea why this is happening?

Thanks!

[timowest]

Deep paths are not supported in JPA Update clauses, you will need to rewrite them using sub queries

[Michael Tontchev]

Looking at the JPQL specification (http://docs.oracle.com/cd/E12839_01/apirefs.1111/e13946/ejb3_langref.html#ejb3_langref_bulk_ops), I see

single_valued_association_path_expression ::= identification_variable.{single_valued_association_field.}*single_valued_association_field

 

Doesn't this allow me to use deep paths? It seems like a rule that allows me to write entity.property1.other.more.field .

Also, if things are like you say, shouldn't I get a compile-time error from QueryDSL? Why does QueryDSL let me do path traversal in Update clauses if JPA doesn't?

Thanks!

[Ruben Dijkstra]

Hello Michael,

I recommend looking only at the BNF rules of '10.2.9. JPQL Bulk Update and Delete'.

Those are the syntax rules that apply to all dml clauses, where the deep paths you want to use are the 'update_item's.

It mandates you can optionally scope the 'field' by prefixing it with the entity (e.g. 'city.name' instead of just 'name') but since an update clause only operates on one entity we omit the prefixing.

However, I see that we could probably verify that what is passed as argument is valid.

Besides that, the current logic is what is supported by JPQL.

Best regards,

Ruben

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Michael Tontchev]

Thanks!

Also, at this point, it would be better for me if you don't, in fact, check that the arguments are correct. I decided to override the JPAUpdateClause where() method to do take the predicate passed in and to instead run it in a subquery, which is then attached to the current JPAUpdateClause.

On Monday, April 20, 2015 at 3:34:51 PM UTC-4, Ruben Dijkstra wrote:

Hello Michael,

I recommend looking only at the BNF rules of '10.2.9. JPQL Bulk Update and Delete'.

Those are the syntax rules that apply to all dml clauses, where the deep paths you want to use are the 'update_item's.

It mandates you can optionally scope the 'field' by prefixing it with the entity (e.g. 'city.name' instead of just 'name') but since an update clause only operates on one entity we omit the prefixing.

However, I see that we could probably verify that what is passed as argument is valid.

Besides that, the current logic is what is supported by JPQL.

Best regards,

Ruben

[Michael Tontchev]

Well, a little smarter than that for a little optimization, but basically that.