非法参数异常：类型不能为空

java mysql hibernate spring jpa

2022-09-03 15:44:02

我面对一个问题，似乎很多人都遇到过，可能解决不了。

我有以下MYSQL存储过程。这只是一个示例，看看是否一切正常，然后我会开始添加参数，如.inactive

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_resource_types`()
BEGIN
    SELECT *
    FROM resource_types
    WHERE inactive = 0;
END

映射表和命名存储过程查询的实体。resource_types

@NamedStoredProcedureQuery(
        name="getResourceTypes",
        procedureName="get_resource_types",
        resultClasses = ResourceType.class,
        parameters = {}
)
@Entity
@Table(name = "resource_types")
public class ResourceType {
    ... fields with annotations used for validation + getters and setters ...
}

这是我从中调用存储过程的JpaRepository

@Repository
public interface ResourceTypeRepository extends JpaRepository<ResourceType, Long> {
    @Procedure("ResourceType.getResourceTypes")
    List<ResourceType> getResourceTypes();

}

驻留在getAll()@Service

public List<ResourceType> getAll(){
    final List<ResourceType> resourceTypes = resourceTypeRepository.getResourceTypes();
    return resourceTypes;
}

当我尝试运行此命令时，我得到以下堆栈跟踪：

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:857)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:618)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:725)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:167)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:134)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:144)
    at com.test.ihbs.controller.ResourceTypeControllerTest.test_getAll(ResourceTypeControllerTest.java:111)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.runTestClass(JUnitTestClassExecuter.java:86)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassExecuter.execute(JUnitTestClassExecuter.java:49)
    at org.gradle.api.internal.tasks.testing.junit.JUnitTestClassProcessor.processTestClass(JUnitTestClassProcessor.java:64)
    at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.processTestClass(SuiteTestClassProcessor.java:50)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.messaging.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:32)
    at org.gradle.messaging.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:93)
    at com.sun.proxy.$Proxy2.processTestClass(Unknown Source)
    at org.gradle.api.internal.tasks.testing.worker.TestWorker.processTestClass(TestWorker.java:106)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:35)
    at org.gradle.messaging.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
    at org.gradle.messaging.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:360)
    at org.gradle.internal.concurrent.DefaultExecutorFactory$StoppableExecutorImpl$1.run(DefaultExecutorFactory.java:64)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Type cannot be null; nested exception is java.lang.IllegalArgumentException: Type cannot be null
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:381)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:223)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:122)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy87.getResourceTypes(Unknown Source)
    at com.ihbs.service.ResourceTypeService.getAll(ResourceTypeService.java:34)
    at com.ihbs.controller.ResourceTypeController.getAllResourceTypes(ResourceTypeController.java:44)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966)
    ... 58 more
Caused by: java.lang.IllegalArgumentException: Type cannot be null
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.setHibernateType(AbstractParameterRegistrationImpl.java:182)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:131)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:140)
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.<init>(AbstractParameterRegistrationImpl.java:77)
    at org.hibernate.procedure.internal.PositionalParameterRegistration.<init>(PositionalParameterRegistration.java:41)
    at org.hibernate.procedure.internal.ProcedureCallImpl.registerParameter(ProcedureCallImpl.java:275)
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.registerStoredProcedureParameter(StoredProcedureQueryImpl.java:128)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.newAdhocStoredProcedureQuery(StoredProcedureJpaQuery.java:147)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createStoredProcedure(StoredProcedureJpaQuery.java:110)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.doCreateQuery(StoredProcedureJpaQuery.java:68)
    at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createQuery(StoredProcedureJpaQuery.java:58)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:295)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:486)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 80 more

任何想法为什么会发生这种情况以及如何解决它？

的文档说：InvalidDataAccessApiUsageException

由于 API 使用不当而引发异常，例如无法“编译”执行前需要编译的查询对象。这代表了我们的 Java 数据访问框架中存在的问题，而不是底层数据访问基础结构。

更新 1

如果我将存储库更改为以下代码，它将起作用，这意味着名称过程查询存在问题。我仍然需要使存储过程的工作方式。

@Repository
public interface ResourceTypeRepository extends JpaRepository<ResourceType, Long> {

    @Query("SELECT rt FROM ResourceType rt WHERE rt.inactive = 0")
    List<ResourceType> getResourceTypes();

}

更新 2

我没有使用EclipseLink（或者至少我不知道他的存在）

更新 3

即使我通过代码，调试，我也找不到此问题的原因。但是我发现了一些可能有助于找到问题的东西。

我在中找到了这段代码，如果你可能注意到在最后一行有一个对函数的调用，它获取参数的总数并添加一个额外的参数。这可能是问题所在，额外的参数。不过，这可能不是问题所在，因为我认为在不同情况下它可以遵循另一条道路。org/springframework/data/jpa/repository/query/StoredProcedureJpaQuery.java

/**
 * Extracts the output value from the given {@link StoredProcedureQuery}.
 * 
 * @param storedProcedureQuery must not be {@literal null}.
 * @return
 */
Object extractOutputValue(StoredProcedureQuery storedProcedureQuery) {

    Assert.notNull(storedProcedureQuery, "StoredProcedureQuery must not be null!");

    if (!procedureAttributes.hasReturnValue()) {
        return null;
    }

    if (StringUtils.hasText(procedureAttributes.getOutputParameterName())) {
        return storedProcedureQuery.getOutputParameterValue(procedureAttributes.getOutputParameterName());
    }

    return storedProcedureQuery.getOutputParameterValue(getQueryMethod().getParameters().getNumberOfParameters() + 1);
}

答案 1

自 1.11.1 起，Spring Data JPA 不支持 SP 返回结果集。我向Spring Data提交了相应的缺陷。

解决方案是降低 API 级别，只使用 JPA。这是我编写的一个与 MS SQL SP 一起使用的泛型类。

import com.google.common.base.Strings;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.Query;
import javax.persistence.StoredProcedureQuery;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
public class StoredProcRepository {

  //region Injected beans (via a RequiredArgsConstructor)
  private final EntityManager em;
  //endregion 

  /**
   * Calls a stored procedure via JPA and retrieves a single implicit result set (in DBs that
   * support them e.g. MS SQL or MySQL). The call is not dependent on a DB dialect. Be
   * aware that large result sets should be paginated and not entirely read to memory. Recreates
   * StoredProcedureQuery instance and its parameters on each call.
   * To execute MS SQL SPs performing multiple queries, SET NOCOUNT ON.
   *
   * @param procedureName stored procedure name, optionally qualified per DB syntax
   * @param resultClass converts (maps) each result set row into instances of resultClass via JPA
   * @param spArgs stored procedure arguments, supplied positionally (optional SP arguments at the
   * end of the list could be omitted)
   * @param <T> class of row instances converted per JPA
   * @return the entire result set
   */
  public <T> List<T> queryViaStoredProc(String procedureName, Class<T> resultClass,
      Object... spArgs) {
    StoredProcedureQuery spq = em.createStoredProcedureQuery(procedureName, resultClass);
    int pos = 0;
    for (Object arg : spArgs) {
      spq.registerStoredProcedureParameter(++pos, arg.getClass(), ParameterMode.IN);
      spq.setParameter(pos, arg);
    }
    return spq.getResultList();
  }

  /**
   * Calls a stored procedure via JPA and retrieves only the top row of a single implicit result
   * set (in DBs that support them e.g. MS SQL or MySQL).
   * Assumes that result set has at least one row.
   * The call is not dependent on a DB dialect.
   * Be aware that large result sets should be paginated and not entirely read to memory.
   * Recreates StoredProcedureQuery instance and its parameters on each call.
   * To execute MS SQL SPs performing multiple queries, SET NOCOUNT ON.
   *
   * @param procedureName stored procedure name, optionally qualified per DB syntax
   * @param resultClass converts (maps) each result set row into instances of resultClass via JPA
   * @param spArgs stored procedure arguments, supplied positionally (optional SP arguments at the
   * end of the list could be omitted)
   * @param <T> class of row instances converted per JPA
   * @return the entire result set
   */
  public <T> T queryTopRowViaStoredProc(String procedureName, Class<T> resultClass,
      Object... spArgs) {
    return queryViaStoredProc(procedureName, resultClass, spArgs).get(0);
  }
}

对于 MS SQL SP，附加要求是所有 SP 执行多个查询。这可以通过至少三种方式之一进行设置：SET NOCOUNT ON

在使用 JPA 的通用 Java 包装器中（请参阅下面的代码）。此方法仅适用于 jTDS JDBC 驱动程序。已向 MS JDBC 驱动程序项目提交了相应的问题。
在每个 SP 的开头。
全局存储在数据库中。

她是 #1 的代码：同一类的相应方法。StoredProcRepository

  /**
   * Calls an MS SQL stored procedure via JPA and retrieves a single implicit result set.
   * Protects against lack of SET NOCOUNT in stored procedures.
   * This works with jTDS JDBC driver, but not with MS JDBC driver.
   * Be aware that large result sets should be paginated and not entirely read to memory.
   *
   * @param procedureName stored procedure name, optionally qualified per DB syntax
   * @param resultClass converts (maps) each result set row into instances of resultClass via JPA
   * @param spArgs stored procedure arguments, supplied positionally (optional SP arguments at the
   * end of the list could be omitted)
   * @param <T> class of row instances converted per JPA
   * @return the entire result set
   */
  public <T> List<T> queryViaMsSqlStoredProc(String procedureName, Class<T> resultClass,
      Object... spArgs) {
    String spBindParams = (spArgs.length == 0) ? "" : "?" + Strings.repeat(",?", spArgs.length - 1);

    // The following works with jTDS driver, but not with MS driver
    String spQuery = String.format("EXEC %s %s", procedureName, spBindParams);

    // The following works with jTDS driver, but not with MS driver
    /*
    String spQuery = String.format("{call %s(%s)}", procedureName, spBindParams);
    Query q = em.createNativeQuery("SET NOCOUNT ON; " + spQuery, resultClass)
        .setHint("org.hibernate.readOnly", true);
    */

    Query q = em.createNativeQuery(spQuery, resultClass);
    int pos = 0;
    for (Object arg : spArgs) {
      q.setParameter(++pos, arg);
    }
    return q.getResultList();
  }    

  /**
   * Calls an MS SQL stored procedure via JPA and retrieves only the top row of a single implicit
   * result set.
   * Assumes that result set has at least one row.
   * The call sets the "NOCOUNT ON" MS SQL batch option.
   * Be aware that large result sets should be paginated and not entirely read to memory.
   *
   * @param procedureName stored procedure name, optionally qualified per DB syntax
   * @param resultClass converts (maps) each result set row into instances of resultClass via JPA
   * @param spArgs stored procedure arguments, supplied positionally (optional SP arguments at the
   * end of the list could be omitted)
   * @param <T> class of row instances converted per JPA
   * @return the entire result set
   */
  public <T> T queryTopRowViaMsSqlStoredProc(String procedureName, Class<T> resultClass,
      Object... spArgs) {
    return queryViaMsSqlStoredProc(procedureName, resultClass, spArgs).get(0);
  }

答案 2

问题似乎出在映射结果时（无法键入生成的实体）。

尝试更改：

@Procedure("ResourceType.getResourceTypes")
List<ResourceType> getResourceTypes();

自

@Procedure("ResourceType.getResourceTypes")
Object[] getResourceTypes();

并删除 NamedStoredProcedureQuery 中的结果类。

希望有所帮助。