java - 带条件生成器的 string_agg

Question

我尝试在条件生成器中创建一个函数，但执行时得到以下结果。

CriteriaBuilder builder = manager.getCriteriaBuilder();

    CriteriaQuery<RetrospectiveSearchVO> criteriaQuery = builder.createQuery(RetrospectiveSearchVO.class);
    Root<Authorization> root = criteriaQuery.from(Authorization.class);
    Join<Authorization, MedicalDiagProcedure> medicalDiagProcedureJoin = root.join(Authorization_.medicalDiagProcedures,JoinType.LEFT);
    Join<MedicalDiagProcedure, MedicalDiagnosis> medicalDiagJoin = medicalDiagProcedureJoin.join(MedicalDiagProcedure_.medicalDiagnosis,JoinType.LEFT);
    Join<MedicalDiagnosis, ReferenceData> medicalDiagRefDataJoin = medicalDiagJoin.join(MedicalDiagnosis_.diagnosis,JoinType.LEFT);

    ArrayList<Predicate> conditions = new ArrayList<>();
    conditions.add(builder.like(medicalDiagRefDataJoin.get(ReferenceData_.referenceTypeValue), dignosisValue));

    Expression<String> functionStringAgg = builder.function( "string_agg", String.class,
            medicalDiagRefDataJoin.<String>get("referenceTypeValue"),
            builder.parameter(String.class, "delimiter"));

    Expression<String> parentExpression = root.get(Authorization_.authorizationNbr);
    Predicate parentPredicate = parentExpression.in(claimNbr);
    conditions.add(builder.equal(medicalDiagJoin.get(MedicalDiagnosis_.diagType),"F"));
    criteriaQuery.where(parentPredicate,builder.and(conditions.toArray(new Predicate[] {})));
    criteriaQuery.multiselect(
            root.get(Authorization_.authorizationNbr), functionStringAgg);
    criteriaQuery.groupBy(root.get(Authorization_.authorizationNbr));
    Query query = manager.createQuery(criteriaQuery);
    /*Query query = manager.createNativeQuery("select  authorizat0_.authorization_nbr as dignosisnbr, STRING_AGG(referenced3_.reference_value,',') as finaldiagnosis from  claims.authorization authorizat0_ left outer join claims.medical_diag_procedure medicaldia1_ on " + 
            " authorizat0_.authorization_id=medicaldia1_.authorization_id left outer join claims.medical_diagnosis medicaldia2_ on " + 
            " medicaldia1_.medical_code_id=medicaldia2_.medical_code_id left outer join ref_data.reference_data_master referenced3_ on  " + 
            " medicaldia2_.diagnosis_id=referenced3_.reference_data_id where authorizat0_.authorization_nbr in :authorizationNbr and medicaldia2_.diag_type='F' and referenced3_.reference_value like group by  authorizat0_.authorization_nbr");
    query.setParameter("authorizationNbr", claimNbr);   
    query.unwrap(SQLQuery.class)
            .setResultTransformer( Transformers.aliasToBean( RetrospectiveSearchVO.class ));*/
    List<RetrospectiveSearchVO> retrospectiveSearchList = query.getResultList();
    return retrospectiveSearchList;


public class PGDialect extends PostgreSQL82Dialect {

public PGDialect() {
    super();
    registerFunction("string_agg", new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ', ')"));
}

}

异常(exception):

java.lang.NullPointerException\r\n\tat org.hibernate.internal.util.ReflectHelper.getConstructor(ReflectHelper.java:354)\r\n\tat org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor(ConstructorNode.java:185)\r\n\tat org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare(ConstructorNode.java:158)\r\n\tat org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:1095)\r\n\tat org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2328)\r\n\tat org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2194)\r\n\tat org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1476)\r\n\tat org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:573)\r\n\tat org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301)\r\n\tat org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:249)\r\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:278)\r\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)\r\n\tat org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)\r\n\tat org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)\r\n\tat org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)\r\n\tat org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)\r\n\tat org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)\r\n\tat org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)\r\n\tat org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)\r\n\tat org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:568)\r\n\tat org.hibernate.jpa.criteria.CriteriaQueryImpl$1.buildCompiledQuery(CriteriaQueryImpl.java:336)\r\n\tat org.hibernate.jpa.criteria.compile.CriteriaCompiler.compile(CriteriaCompiler.java:147)\r\n\tat org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:736)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:294)\r\n\tat com.sun.proxy.$Proxy402.createQuery(Unknown Source)\r\n\tat com.amhi.care.claims.dao.FamRetrospectiveDaoImpl.filterWithDiagnosisForAuth(FamRetrospectiveDaoImpl.java:272)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)\r\n\tat org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:52)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)\r\n\tat org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)\r\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)\r\n\tat com.sun.proxy.$Proxy474.filterWithDiagnosisForAuth(Unknown Source)\r\n\tat com.amhi.care.claims.service.FamRetrospectiveServiceImpl.getAdvancedRetrospectiveSearch(FamRetrospectiveServiceImpl.java:89)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)\r\n\tat org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)\r\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)\r\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)\r\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)\r\n\tat com.sun.proxy.$Proxy475.getAdvancedRetrospectiveSearch(Unknown Source)\r\n\tat com.amhi.care.claims.controller.FamRetrospectiveController.retrospectiveAdvancedSearch(FamRetrospectiveController.java:49)\r\n\tat com.amhi.care.claims.controller.FamRetrospectiveController$$FastClassBySpringCGLIB$$e66accb.invoke(<generated>)\r\n\tat org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)\r\n\tat org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)\r\n\tat org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)\r\n\tat org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke(AfterReturningAdviceInterceptor.java:52)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)\r\n\tat org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)\r\n\tat org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)\r\n\tat com.amhi.care.claims.controller.FamRetrospectiveController$$EnhancerBySpringCGLIB$$e5779e76.retrospectiveAdvancedSearch(<generated>)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:498)\r\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:222)\r\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:814)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:737)\r\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)\r\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)\r\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)\r\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)\r\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:860)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:635)\r\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:742)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)\r\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)\r\n\tat org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:92)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)\r\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)\r\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)\r\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493)\r\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)\r\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)\r\n\tat org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)\r\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)\r\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)\r\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800)\r\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)\r\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:806)\r\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1498)\r\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)\r\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\r\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\r\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\r\n\tat java.lang.Thread.run(Thread.java:748)\r\n

Answer 1

对于字符串分隔符作为函数使用的参数 literal function

criteriaBuilder.literal(",")