Support HQL Subquery Insert Clause

[paradoxfm]

//JPQLQueryFactory query;
final QShift shs = new QShift("sh");
final JPQLQuery<Tuple> jpqlQuery = JPAExpressions.select(shs.dateTimeInterval, shs.employeePosition, shs.positionType, shs.positionIndex, shs.lunch, shs.outstaff,
                                            shs.availableForAssignment, shs.status, shs.edited, shs.ftePositionGroup, shs.plannedAbsence, Expressions.constant(newPcr.getId()))
        .from(shs).where(shs.positionCategoryRosterId.eq(oldPcr.getId()));
final QShift sh = QShift.shift;
final InsertClause<?> insertClause = query.insert(sh).columns(sh.dateTimeInterval, sh.employeePosition, sh.positionType, sh.positionIndex, sh.lunch, sh.outstaff,
                                                              sh.availableForAssignment, sh.status, sh.edited, sh.ftePositionGroup, sh.plannedAbsence, sh.positionCategoryRoster)
        .select(jpqlQuery);
return insertClause.execute();

ok. im make on JPA and remove querydsl-sql.
after clean rebuild
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting OPEN, found 'shift' near line 1, column 19 [insert into Shift shift (shift.dateTimeInterval, shift.employeePosition, shift.positionType, shift.positionIndex, shift.lunch, shift.outstaff, shift.availableForAssignment, shift.status, shift.edited, shift.ftePositionGroup, shift.plannedAbsence, shift.positionCategoryRoster) select sh.dateTimeInterval, sh.employeePosition, sh.positionType, sh.positionIndex, sh.lunch, sh.outstaff, sh.availableForAssignment, sh.status, sh.edited, sh.ftePositionGroup, sh.plannedAbsence from ru.abcconsulting.domain.entity.roster.Shift sh where sh.positionCategoryRosterId = ?1]

[jwgmeligmeyling]

Can you try to exchange sh and shs?

[paradoxfm]

final QShift shs = QShift.shift;
final JPQLQuery<Tuple> jpqlQuery = JPAExpressions.select(shs.dateTimeInterval, shs.employeePosition, shs.positionType, shs.positionIndex, shs.lunch, shs.outstaff,
                                    shs.availableForAssignment, shs.status, shs.edited, shs.ftePositionGroup, shs.plannedAbsence, Expressions.constant(newPcr.getId()))
.from(shs).where(shs.positionCategoryRosterId.eq(oldPcr.getId()));
final QShift sh = new QShift("sh");
final InsertClause<?> insertClause = query.insert(sh).columns(sh.dateTimeInterval, sh.employeePosition, sh.positionType, sh.positionIndex, sh.lunch, sh.outstaff,
                                                      sh.availableForAssignment, sh.status, sh.edited, sh.ftePositionGroup, sh.plannedAbsence, sh.positionCategoryRoster)
.select(jpqlQuery);
return insertClause.execute();

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting OPEN, found 'sh' near line 1, column 19 [insert into Shift sh (sh.dateTimeInterval, sh.emp
after swapped

[jwgmeligmeyling]

You're not supposed to use columns and select, but instead use set:

QCat cat = QCat.cat;
long amount = insert(cat)
            .set(cat.name, "Bobby")
            .set(cat.alive, false)
            .execute();
assertEquals(1, amount);

Probably some invalid API left overs/inheritance from inserts in QueryDSL SQL when this limited support was implemented by #2421

[paradoxfm]

ok. but im wating api for this query

insert into shift (val, dat, title, order)
select val, dat, title order + 1 from shift;

[jwgmeligmeyling]

I would think that .set() besides constants also accepts expressions/paths as second argument. And that then allows to reference fields from the from clause:

QCat cat = QCat.cat;
long amount = insert(cat)
.set(cat.name, otherCat.name)
.set(cat.alive, otherCat.alive)
.from(otherCat)
.execute();
assertEquals(1, amount);

[paradoxfm]

QCat cat = QCat.cat;
long amount = insert(cat)
.set(cat.name, otherCat.name)
.set(cat.alive, otherCat.alive)
.from(otherCat)
.execute();
assertEquals(1, amount);

this not work

[jwgmeligmeyling]

Hi @paradoxfm ,

I looked a bit further into this issue.

The JPAInsertClause introduced in the latest version of QueryDSL is pretty much a starting point. It has to be considered that in JPA officially there is no support for the Insert clause at all. Hibernate supports it in its propriertairy HQL. Hibernate however does not support the values clause, so it only allows insertion from a subquery. Other vendors might support some sorts of insert clauses as well, but vendors are certainly not required to and there will be subtle differences in implementations.

I don't know against what vendor of ORM the original implementation was tested, but none of the three APIs (set, values and subquery) worked in Hibernate for me. I've implemented a small patch that fixes insert from subquery for Hibernate.

I am eager to find out if my fix actually breaks some other situations, so hopefully the CI results give some insight into that. But I am starting to believe the work was never finished/tested at all.

Please try out my branch if its possible for you 😄 The code is available at https://github.com/querydsl/querydsl/pull/2667/files

[paradoxfm]

Expressions.constant(newPcr.getId()) not includet to select from my example. this generate hibernate exception
Caused by: org.hibernate.QueryException: number of select types did not match those for insert [insert into Shift (dateTimeInterval, employeePosition, positionType, positionIndex, lunch, outstaff, availableForAssignment, status, edited, ftePositionGroup, plannedAbsence, positionCategoryRoster) select shift.dateTimeInterval, shift.employeePosition, shift.positionType, shift.positionIndex, shift.lunch, shift.outstaff, shift.availableForAssignment, shift.status, shift.edited, shift.ftePositionGroup, shift.plannedAbsence from ru.abcconsulting.domain.entity.roster.Shift shift where shift.positionCategoryRosterId = ?1]

[paradoxfm]

i need change sh.positionCategoryRoster for positionCategoryRoster last version

[paradoxfm]

but if i fix positionCategoryRosterId and set random default on database, then its worked

[jwgmeligmeyling]

From the exception it seems that your list of columns and list of expressions selected in the subquery did not match. Did you add the column for id to the columns list before adding Expressions.constant(newPcr.getId()) to the select clause?

[paradoxfm]

Did you add the column for id to the columns list before adding Expressions.constant(newPcr.getId()) to the select clause?
offcorse
if i use version 4.4.0 Expressions.constant(newPcr.getId()) added into select subquery

[jwgmeligmeyling]

So does it work now or not? Still trying to figure out the last exception. Could you share a stack trace?

[paradoxfm]

subquery

final JPQLQuery<Tuple> jpqlQuery = query.select(shs.dateTimeInterval, shs.allowConfirmExchange,
shs.employeePosition, shs.positionType, shs.positionIndex, shs.lunch, shs.outstaff,
shs.availableForAssignment, shs.status, shs.edited, shs.ftePositionGroup, shs.plannedAbsence,
Expressions.constant(newPcr.getId()))
.from(shs).where(shs.positionCategoryRosterId.eq(oldPcr.getId()));

subselect from query with losted constant

select shift.dateTimeInterval, shift.allowConfirmExchange, shift.employeePosition,
shift.positionType, shift.positionIndex, shift.lunch, shift.outstaff, shift.availableForAssignment,
shift.status, shift.edited, shift.ftePositionGroup, shift.plannedAbsence
from Shift shift
where shift.positionCategoryRosterId = ?1

exception stack trace

java.lang.IllegalArgumentException: org.hibernate.QueryException: number of select types did not match those for insert [insert into Shift (dateTimeInterval, allowConfirmExchange, employeePosition, positionType, positionIndex, lunch, outstaff, availableForAssignment, status, edited, ftePositionGroup, plannedAbsence, positionCategoryRosterId) select shift.dateTimeInterval, shift.allowConfirmExchange, shift.employeePosition, shift.positionType, shift.positionIndex, shift.lunch, shift.outstaff, shift.availableForAssignment, shift.status, shift.edited, shift.ftePositionGroup, shift.plannedAbsence from ru.abcconsulting.domain.entity.roster.Shift shift where shift.positionCategoryRosterId = ?1] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:718) at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:109) at jdk.internal.reflect.GeneratedMethodAccessor144.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:310) at com.sun.proxy.$Proxy143.createQuery(Unknown Source) at com.querydsl.jpa.impl.JPAInsertClause.execute(JPAInsertClause.java:80) at ru.abcconsulting.domain.query.roster.RosterQueryPredicateFactory.copyShifts(RosterQueryPredicateFactory.java:292) at ru.abcconsulting.application.roster.utils.RosterCopier.copyPositionCategoryRoster(RosterCopier.java:95) at ru.abcconsulting.application.roster.utils.RosterCopier.lambda$createCopy$0(RosterCopier.java:74) at java.base/java.lang.Iterable.forEach(Iterable.java:75) at ru.abcconsulting.application.roster.utils.RosterCopier.createCopy(RosterCopier.java:73) at ru.abcconsulting.application.roster.utils.RosterCopier.createCopy(RosterCopier.java:57) at ru.abcconsulting.application.roster.ShiftApplicationService$ShiftApplicationServiceProxy.resolveRoster(ShiftApplicationService.java:3252) at ru.abcconsulting.application.roster.ShiftApplicationService$ShiftApplicationServiceProxy.updateShift(ShiftApplicationService.java:1673) at ru.abcconsulting.application.roster.ShiftApplicationService$ShiftApplicationServiceProxy$$FastClassBySpringCGLIB$$aca87088.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:752) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:69) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) at ru.abcconsulting.application.roster.ShiftApplicationService$ShiftApplicationServiceProxy$$EnhancerBySpringCGLIB$$5f82080d.updateShift() at ru.abcconsulting.application.roster.ShiftApplicationService.update(ShiftApplicationService.java:348) at ru.abcconsulting.presentation.controllers.roster.ShiftController.update(ShiftController.java:208) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base/java.lang.reflect.Method.invoke(Method.java:564) at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:894) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:981) at org.springframework.web.servlet.FrameworkServlet.doPut(FrameworkServlet.java:895) at javax.servlet.http.HttpServlet.service(HttpServlet.java:655) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:858) at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.multipart.support.MultipartFilter.doFilterInternal(MultipartFilter.java:123) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.keycloak.adapters.springsecurity.filter.KeycloakAuthenticatedActionsFilter.doFilter(KeycloakAuthenticatedActionsFilter.java:74) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.keycloak.adapters.springsecurity.filter.KeycloakSecurityContextRequestFilter.doFilter(KeycloakSecurityContextRequestFilter.java:92) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at ru.abcconsulting.config.CustomConcurrentSessionFilter.doFilter(CustomConcurrentSessionFilter.java:95) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:209) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) at ru.abcconsulting.presentation.filters.openid.CustomKeycloakLogoutFilter.doFilter(CustomKeycloakLogoutFilter.java:37) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.keycloak.adapters.springsecurity.filter.KeycloakPreAuthActionsFilter.doFilter(KeycloakPreAuthActionsFilter.java:96) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:74) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:141) at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:82) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:143) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) at org.apache.tomcat.util.net.Nio2Endpoint$SocketProcessor.doRun(Nio2Endpoint.java:1679) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.net.AbstractEndpoint.processSocket(AbstractEndpoint.java:1104) at org.apache.tomcat.util.net.Nio2Endpoint$Nio2SocketWrapper$2.completed(Nio2Endpoint.java:603) at org.apache.tomcat.util.net.Nio2Endpoint$Nio2SocketWrapper$2.completed(Nio2Endpoint.java:581) at java.base/sun.nio.ch.Invoker.invokeUnchecked(Invoker.java:127) at java.base/sun.nio.ch.Invoker$2.run(Invoker.java:219) at java.base/sun.nio.ch.AsynchronousChannelGroupImpl$1.run(AsynchronousChannelGroupImpl.java:112) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.base/java.lang.Thread.run(Thread.java:832)