Thiết kế website giá rẻ

Question

I’m trying to write an UPDATE query in Hibernate HQL and use a subquery in it that crashes with an error. Why is this SQL query not working?

    @Query("""
            UPDATE #{#entityName} p
            SET
            p.read = (
                SELECT (pg.readByDefault = true or pg.portalAdmin = true)
                FROM PermissionGroup pg
                WHERE pg.id = p.permissionGroup.id
            ),
            p.write = (
                SELECT pg.writeByDefault
                FROM PermissionGroup pg
                WHERE pg.id = p.permissionGroup.id
            ),
            p.control = (
                SELECT pg.controlByDefault
                FROM PermissionGroup pg
                WHERE pg.id = p.permissionGroup.id
            )
            WHERE p.permissionObject.id = :objectId
            """)
    void updateAllPermissionsToDefault(@Param("objectId") Long objectId);

Below is a stacktrace that indicates an error in the word “or”, but it is not entirely clear why. If you put such a subquery into a regular query, the syntax will be completely correct and there will be no error.

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract void ru.inrecolan.inportal.repository.permissions.FieldPermissionRepository.updateAllPermissionsToDefaultByParentId(java.lang.Long)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:63)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:76)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:56)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:140)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:207)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:78)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lookupQuery(RepositoryFactorySupport.java:574)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$mapMethodsToQuery$1(RepositoryFactorySupport.java:567)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.Iterator.forEachRemaining(Iterator.java:133)
    at java.base/java.util.Collections$UnmodifiableCollection$1.forEachRemaining(Collections.java:1054)
    at java.base/java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.mapMethodsToQuery(RepositoryFactorySupport.java:569)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$new$0(RepositoryFactorySupport.java:559)
    at java.base/java.util.Optional.map(Optional.java:265)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:559)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:332)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.lambda$afterPropertiesSet$5(RepositoryFactoryBeanSupport.java:297)
    at org.springframework.data.util.Lazy.getNullable(Lazy.java:212)
    at org.springframework.data.util.Lazy.get(Lazy.java:94)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:300)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:121)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
    ... 75 common frames omitted
Caused by: java.lang.IllegalStateException: Unexpected AST: org.hibernate.hql.internal.ast.tree.SqlNode 
 -[OR] SqlNode: 'or'

    at org.hibernate.hql.internal.ast.tree.SelectExpressionList.collectSelectExpressions(SelectExpressionList.java:48)
    at org.hibernate.hql.internal.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:127)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:1026)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:794)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:689)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.newValue(HqlSqlBaseWalker.java:1256)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:1068)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:777)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:393)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:281)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:155)
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)
    at jdk.internal.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:366)
    at com.sun.proxy.$Proxy223.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87)
    ... 104 common frames omitted

The problem was solved by replacing the SQL query with the following:

    @Query("""
            UPDATE #{#entityName} p
            SET
            p.read = (
                SELECT (CASE pg.readByDefault WHEN TRUE THEN TRUE ELSE pg.portalAdmin END)
                FROM PermissionGroup pg
                WHERE pg.id = p.permissionGroup.id
            ),
            p.write = (
                SELECT pg.writeByDefault
                FROM PermissionGroup pg
                WHERE pg.id = p.permissionGroup.id
            ),
            p.control = (
                SELECT pg.controlByDefault
                FROM PermissionGroup pg
                WHERE pg.id = p.permissionGroup.id
            )
            WHERE p.permissionObject.id = :objectId
            """)
    void updateAllPermissionsToDefault(@Param("objectId") Long objectId);

, but I don’t understand why the first option doesn’t work. Is there something wrong with the subqueries in Hibernate?

Thiết kế website giá rẻ

Danh mục

Not work logical operator in subquery in select section. Hibernate HQL, Spring, Java