All geek questions in one place

How to write JPA 2.1 update criteria request without connections?

Im using JPA 2.1, Hibernate 4.3.6.Final and MySQL 5.5.37. I am trying to use the CriteriaBuilder API to write an update request that will update multiple rows. However, Im getting "java.lang.IllegalArgumentException: UPDATE / DELETE polling requests cannot determine joins" when I try to run 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 any other way to write my query so that I can use multiple row updates with JPA 2.1? Below is a complete trace of the exception stack ...

 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 idea of ​​a subquery 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 an 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
+6
source share
4 answers

During your question, I found some solution to this.

You can use the Hibernate Criteria interface to create criteria instead of JPQL criteria and add a 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.

myClassList will have all the objects you want to update, now iterating and updating the value you want. Hope this solves your problem.

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

0
source

If you are just looking for solutions, I recommend:

  • Write a selection of the data you need (using CriteriaQuery because you want them).
  • Scroll through the list of extracted data and update everything in the transaction.

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

Also, a slight improvement over the solution described above will be to use subqueries in the WHERE part of your criteria builder. This should work, although I have never tried (I'm a fan of always getting data that changes, in the end I want to register something, for example, for history). JPQL example:

  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();

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

0
source

Joins are only possible in SELECT in standard SQL. Not surprisingly, it does not work in JPQL / Criteria.

You should try using Subquery to make your connection and specify in in where .

To create a subquery, it will be the same as for the standard CriteriaQuery, since the subquery method is defined in CommonAbstractCriteria - this is a common classifier.

An example of a subquery is available in this exchange .

0
source
  1. An update in JPA does not allow joining.

  2. Even using a subquery like

    refresh MyClassroom is set to enabled = false, where is the identifier in (select c.id in MyClassroom c join c.roster r ... where ...)

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

  1. Also this is not possible in JPQL (due to point 2).
0
source

Source: https://habr.com/ru/post/1205449/

More articles:


All Articles