How do I write a JPA 2.1 update criteria query without joins?

Question

I’m using JPA 2.1, Hibernate 4.3.6.Final, and MySQL 5.5.37. I’m trying to use the CriteriaBuilder API to write an update query that will update multiple rows. However, I’m getting an “java.lang.IllegalArgumentException: UPDATE/DELETE criteria queries cannot define joins” when I try and run the below …

final CriteriaBuilder qb = m_entityManager.getCriteriaBuilder();
    final CriteriaUpdate<MyClassroom> q = qb.createCriteriaUpdate(MyClassroom.class);
    final Root<MyClassroom> root = q.from(MyClassroom.class);
    final Join<MyClassroom, MyClassroomUser> rosterJoin = root.join(MyClassroom_.roster);
    final Join<MyClassroomUser, User> userJoin = rosterJoin.join(MyClassroomUser_.user);

    final Calendar today = Calendar.getInstance();
    q.set(root.get(MyClassroom_.enabled), false)
     .where(qb.and(qb.equal(root.get(MyClassroom_.enabled),true),
                            qb.lessThanOrEqualTo(root.get(MyClassroom_.session).get(MyClassroomSession_.schedule).<Calendar>get(MyClassroomSchedule_.endDate), today)),
                            qb.equal(rosterJoin.get(MyClassroomUser_.classroomRole).get(ClassroomRole_.name), ClassroomRoles.TEACHER),
                            qb.equal(userJoin.get(User_.organization).get(Organization_.importDataFromSis), false));

    return m_entityManager.createQuery(q).executeUpdate();

Without using JPQL, is there another way I can write my query so that I can take advantage of updating multiple rows with JPA 2.1? Below is the complete stack trace of the exception …

java.lang.IllegalArgumentException: UPDATE/DELETE criteria queries cannot define joins
    at org.hibernate.jpa.criteria.path.RootImpl.illegalJoin(RootImpl.java:81)
    at org.hibernate.jpa.criteria.path.AbstractFromImpl.join(AbstractFromImpl.java:330)
    at org.hibernate.jpa.criteria.path.AbstractFromImpl.join(AbstractFromImpl.java:324)
    at org.mainco.subco.classroom.repo.MyClassroomDaoImpl.disabledNonCleverExpiredClasses(MyClassroomDaoImpl.java:495)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at com.sun.proxy.$Proxy66.disabledNonCleverExpiredClasses(Unknown Source)
    at com.follett.fdr.lycea.lms.classroom.test.da.MyClassroomDaoTest.testDisableNonCleverExpiredClass(MyClassroomDaoTest.java:355)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Edit: I tried the subquery idea using this code …

   final CriteriaBuilder qb = m_entityManager.getCriteriaBuilder();
    final CriteriaUpdate<Classroom> q = qb.createCriteriaUpdate(Classroom.class);
    final Root<Classroom> mainRoot = q.from(Classroom.class);

    // Subquery to select the classes we want to disable.
    final Subquery<Classroom> subquery = q.subquery(Classroom.class);
    final Root<Classroom> root = subquery.from(Classroom.class);
    final Join<Classroom, ClassroomUser> rosterJoin = root.join(Classroom_.roster);
    final Join<ClassroomUser, User> userJoin = rosterJoin.join(ClassroomUser_.user);
    final SetJoin<User, Organization> orgJoin = userJoin.join(User_.organizations);
    final Calendar today = Calendar.getInstance();
    subquery.select(root)
            .where(qb.and(qb.equal(root.get(Classroom_.enabled),true),
                          qb.lessThanOrEqualTo(root.get(Classroom_.session).get(ClassroomSession_.schedule).<Calendar>get(ClassroomSchedule_.endDate), today)),
                          qb.equal(rosterJoin.get(ClassroomUser_.classroomRole).get(ClassroomRole_.name), ClassroomRoles.TEACHER),
                          qb.equal(orgJoin.get(Organization_.importDataFromSis), false)
                          );

    // Build the update query
    q.set(mainRoot.get(Classroom_.enabled), false)
     .where(mainRoot.in(subquery));

    // execute the update query
    return m_entityManager.createQuery(q).executeUpdate();

but got the exception …

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute 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 …
Caused by: java.sql.SQLException: You can't specify target table ‘my_classroom' for update in FROM clause
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    ... 49 more

I know you are not interested in JPQL queries for updating, but would that work? — V G, Oct 28 '14 at 09:45
Ok, if there is a JPQL way to do it that can update everything in one statement, then sure, I'll take that. We have been using CriteriaBuilder everywhere in our codebase, but I've read that JPQL can do the same thing as CriteriaBuilder. — Dave, Oct 28 '14 at 15:49
I would think it's down to the SQL that the RDBMS expects (since JPQL provides the same as Criteria). Perhaps the RDBMS simply doesn't allow joins in the UPDATE/DELETE statement? (some don't) — Neil Stockton, Oct 29 '14 at 15:48

Qadir Hussain · Answer 1 · 2014-10-29T05:36:18.583

While going through your question I found some solution of that.

You can use Hibernate Criteria interface to create criteria instead of using JPQL criteria and add few Restriction to it based on your actual criteria as follows.

Session session = (Session) entityManager.getDelegate();

Criteria criteria = session.createCriteria(MyClassroom.class,"myclassroom");
criteria.createCriteria("myclassroom.roster","rosterJoin");
criteria.createCriteria("rosterJoin.user","userJoin");
criteria.createCriteria("myclassroom.session","session");
criteria.createCriteria("session.schedule","schedule");
criteria.createCriteria("rosterJoin.classroomRole","classroomRole");
criteria.createCriteria("userJoin.organization","organization");


final Calendar today = Calendar.getInstance();

criteria.add(Restrictions.eq("myclassroom.enabled",true));
criteria.add(Restrictions.le("schedule.endDate",today));
criteria.add(Restrictions.eq("classroomRole.name",ClassroomRoles.TEACHER));
criteria.add(Restrictions.eq("organization.importDataFromSis",false));

List<MyClassroom> myClassList = (List<MyClassroom>)criteria.list();

for(MyClassroom room : myClassList) {
    room.setEnabled(false);
    session.saveOrUpdate(room);
}

Creating criteria in Hibernate actually creates an INNER JOIN between two tables.

The myClassList will have all the objects that you want to update, Now iterate it and update the value you want to. Hope this will solve your problem.

NOTE: This is just a way to solve your problem. Please debug yourself if you face any exception.

Hi, Your solution selects the data but doesn't update anything. What I want to do is write a single CriteriaBuilder query that updates multiple rows based on certain conditions. — Dave, Oct 28 '14 at 13:51

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

If you simply look for solutions, I recommend:

Write a select of the data that yo need (with CriteriaQuery, because you want them).
Go through the list of fetched data and update everything in a transaction.

The solution is a bit more complicated to code, but is more readable especially if you have complex JOINs.

Also a small improvement over the above described solution would be to use subqueries in the WHERE part of your criteria builder. This should work, although I never tried it (I am a fan of always fetching the data that gets changed, eventually I will want to log something about it, like for History things). An example with JPQL:

    Query updateQuery = em.createQuery("UPDATE MyClasroom SET enabled=false WHERE enabled=true AND id IN (SELECT id FROM MyClasroom WHERE ...)");
    updateQuery.setParameter("phoneNo", "phoneNo");
    ArrayList<Long> paramList = new ArrayList<Long>();
    paramList.add(123L);
    updateQuery.setParameter("ids", paramList);
    int nrUpdated = updateQuery.executeUpdate();

This is JPA compliant (as its associated Criteria query), but some databases may not support it (for example MySql is not allowed to update the same table as the one specified in the subquery). If that is your case, your only option is to do like in my first recommendation.

edited May 23 '17 at 12:29

Community

1
1

answered Oct 28 '14 at 09:47

V G

18,822
6
51
89

Thanks, but I was under the impression taht with JPA 2.1, I could write an UPDATE CriteriaBuilder statement that would allow me to update multiple rows in one go. If there's absolutely no way on earth to do that, then I'll come back and accept your answer. – Dave Oct 28 '14 at 13:53
You can certainly update with criteria update more rows in the same query, but PROBABLY not using JOINs, which seems to be your problem. – V G Oct 28 '14 at 14:26
I'm open to a solution that updates multiple rows in one go and doesn't use JOINs. – Dave Oct 28 '14 at 15:48
Because you use different tables in you update, you need the data from them in order to take decisions. So one solution would be to take the IDs of all `MyClassroom`s with those conditions (using whatever you want: `CriteriaBuilder` or `JPQL`) and then use the JPQS (or the `CriteriaBuilder`) with something like `UPDATE MyClassroom SET enabled=false WHERE id IN :myIdList`. – V G Oct 28 '14 at 15:56
Since I want to update everything in one statement, is tehre a way to replace :myIdList with the actual query and tehn I can just execute one statement? – Dave Oct 28 '14 at 18:17
I made an addition to my answer: the part with subqueries. It seems that bdulac also posted in the meanwhile this solution. – V G Oct 29 '14 at 10:49
I tried sbuqueries, the code is given but I'm getting an error. Code samples would REALLY help close this one out. – Dave Oct 29 '14 at 15:45
I updated my answer, but as I see, you are running on MySQL. I also tried it (and linked to the description of the problem) and indeed your only solution is to use my first recommendation: fetch the ids and then use them. – V G Oct 30 '14 at 17:18
You may not be able to do "Update t where id in (select id from t)" in MySQL, but I know you can do "update t where id in (select q.* from (select id from t) q)". If I can figure out how to write that wrapper in JPA, then I will have solved my problem. – Dave Oct 30 '14 at 19:46

score 0 · Answer 3 · edited May 23 '17 at 11:44

0

Joins are only possible in SELECT in standard SQL. It is not surprising that it does not work in JPQL / Criteria.

You should try to use a Subquery to make your join and specify an in expression in your where clause.

To create the Subquery, it is the same as for a standard CriteriaQuery as the subquery method is defined at the CommonAbstractCriteria is a general classifier.

An example of Subquery is available in this exchange.

edited May 23 '17 at 11:44

Community

1
1

answered Oct 28 '14 at 20:33

bdulac

1,686
17
26

Your subquery solution is almost right, you should not specify a whole entity as a predicate but only the Id attribute. It means not "mainRoot.in(subquery)" but "mainIdAttrPredicate.in(subquery)". The other point is about selecting only the Id in the subquery. It means not ".select(root)" but ".select(idAttrPredicate)". – bdulac Oct 29 '14 at 20:44
If I try and select only the id in teh sbuquery --subquery.select(root.get(Classroom_.id)) -- the code doesn't even compile. The compilation error is "The method select(Expression) is not applicable to the arguments (Path)". – Dave Oct 29 '14 at 21:45
Path specializes Expression, that's about the generic type of the Subquery>. You should not select an Entity class, but the Id type. In the Stackoverflow exchange I gave you as a link, they use an Integer ("Subquery sq = c.subquery(Integer.class);"). – bdulac Oct 30 '14 at 13:56
Got the same exception as before, the root of which is "Caused by: java.sql.SQLException: You can't specify target table 'my_classroom' for update in FROM clause" – Dave Oct 30 '14 at 15:19
I remember that problem, it is MySQL-specific. It is detailed in [that exchange](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause). It seems the workaround specified won't help you, forget the Subquery. The inner join point could help you with your problem. I am convinced all other RDBMS don't allow joins in update (perhaps there is a trick in SQL Server). – bdulac Oct 30 '14 at 22:58
Hope you are not stuck: the hibernate message is clear: "UPDATE/DELETE criteria queries cannot define joins". That seems logical : an update or delete can't be applied on a real join between different tables. If you can't update on each row of a result list as suggesting in Andrei I's answer and have to fix the MySQL problem, have a look at the answers which are not marked right (link in my previous comment). There are interesting ideas, even if no seems to fit your problem. – bdulac Oct 30 '14 at 23:22
Hey man, I'm not getting the "UPDATE/DELETE criteria queries cannot define joins" anymore, I'm getting the error message listed two comments up. You don't have to respond if you don't know the answer. – Dave Oct 31 '14 at 13:56
Please, be polite, I tried to help: there are two problems a) MySQL doesn't support the subselect query which is the standard one b) joins are not supported in update statement (your initial problem). In the end, THE OTHER ANSWER (Andrei I's) seems to provide one right solution: iteration on result list and update on each result... I don't say I have the right answer, I DEFINITELY TRY TO HELP YOU ! – bdulac Oct 31 '14 at 20:33

score 0 · Answer 4 · answered Sep 27 '19 at 20:22

Update in JPA does not allow Join.
Even using subquery like

update MyClassroom set enabled=false where id in (select c.id from MyClassroom c join c.roster r ... where ...)

will fail because this is not allowd in MySQL - update and select from the same table (doc).

Nor it is possible in JPQL (becuase of point 2).

How do I write a JPA 2.1 update criteria query without joins?

4 Answers4