使用Spring Boot JDBC将数组传递到存储过程

Question

我正在尝试执行一个从Spring Boot JDBC中执行Oracle存储的过程，该过程将数字作为输入。 Oracle过程在SQL Developer中执行罚款，但是我无法将数组作为输入传递给Spring的存储过程。 我正在使用Spring Data JDBC扩展程序来做到这一点，因此我在pom.xml文件中添加了这些依赖项。

pom.xml

        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>12.2.0.1</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jdbc-core</artifactId>
            <version>1.2.1.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-oracle</artifactId>
            <version>1.2.1.RELEASE</version>
        </dependency>

application.properties

spring.datasource.driverClassName: oracle.jdbc.driver.OracleDriver
spring.datasource.url: jdbc:oracle:thin:@ip_addr:port:db_name
spring.datasource.username: **
spring.datasource.password: *****

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle10gDialect

# Hibernate ddl auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto= update

现在致电存储过程，我正在使用Simplejdbccall：

Long[] ids = {1L,2L,3L,4L};
        try {
            SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
                    .withProcedureName("Procedure_name")
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters(new SqlParameter("in_actor_ids", Types.ARRAY, "ACTOR_ID_ARRAY"));

            
            Map<String, Object> in = Collections.singletonMap("in_actor_ids", new SqlArrayValue<Number>(ids));

            Map<String, Object> simpleJdbcCallResult = 
                    simpleJdbcCall.execute(in);
            System.out.println(simpleJdbcCallResult);
        }
        catch(Exception e) {
            System.out.println(e.getMessage());
        }

我遇到了许多帖子，其中为了将自定义对象作为输入参数传递给过程，在Spring Boot中，我们需要使用 sqlarrayvalue ，因此我在简单的jdbccall中使用了它，但是，应用程序也引发了这样的错误

java.lang.ClassCastException: com.zaxxer.hikari.pool.HikariProxyConnection cannot be cast to oracle.jdbc.OracleConnection
    at oracle.sql.TypeDescriptor.setPhysicalConnectionOf(TypeDescriptor.java:803)
    at oracle.sql.TypeDescriptor.<init>(TypeDescriptor.java:585)
    at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:258)
    at org.springframework.data.jdbc.support.oracle.SqlArrayValue.createTypeValue(SqlArrayValue.java:90)
    at org.springframework.jdbc.core.support.AbstractSqlTypeValue.setTypeValue(AbstractSqlTypeValue.java:60)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:293)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:147)
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:209)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1090)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1147)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:412)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:395)
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:193)
    at com.projects.demo.shopDAO.shopDAOimpl.tickets(shopDAOimpl.java:2640)
    at com.projects.demo.shopDAO.shopDAOimpl$$FastClassBySpringCGLIB$$e105c56a.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:769)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:747)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689)
    at com.projects.demo.shopDAO.shopDAOimpl$$EnhancerBySpringCGLIB$$2cd08236.tickets(<generated>)
    at com.projects.demo.controller.shopcontroller.tickets(shopcontroller.java:712)
    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:483)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:888)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    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.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    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.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    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:201)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    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:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:526)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1591)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)

，以克服此错误，我尝试创建 custom ustomparayType类。扩展AbstractsqltyPevalue 拆开oracleconnection，但没有运气。 在这里，我无法导入Oracle特定的类软件包。

import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;

public class OracleSqlArrayValue extends AbstractSqlTypeValue {
    
    private final Connection oracleCon;
    private List<String> values;
    private String defaultTypeName;
    
    public OracleSqlArrayValue(final Connection oracleCon, List<String> values, String defaultTypeName) {
        this.oracleCon = oracleCon;
        this.values = values;
        this.defaultTypeName = defaultTypeName;
    }   
    
    public Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
        if(typeName == null && defaultTypeName == null) {
            throw new InvalidDataAccessApiUsageException("No type named defined. Instantiate class with default type name.");
        }
        
/*     getting squiggly line across OracleConnection.class, which I am not able to import, I don't know why.*/

        if(con.isWrapperFor(OracleConnection.class)) {
            throw new InvalidDataAccessApiUsageException("Unable to unwrap OracleConnection. Ensure you are connecting to Oracle DB.");
        }
            
        return con.unwrap(OracleConnection.class).createOracleArray(typeName != null ? typeName : defaultTypeName, values);     
    }
}

程序

create or replace PROCEDURE delete_actors (in_actor_ids IN actor_id_array)
AS
BEGIN
  FOR i IN 1..in_actor_ids.count loop
    DELETE FROM actor WHERE id = in_actor_ids(i);
  END LOOP;
END;

type

create or replace TYPE actor_id_array 
    AS VARRAY(20) OF NUMBER;

请指导我，我在这里缺少什么。坚持这个问题，找不到解决此问题的方法。

Answer 1

我最终解决了这个问题。
问题是在我的pom.xml文件中，我从Oracle依赖项中删除了运行时的范围，这让我导入OracleConnection.class软件包。
此代码适用于数字数组，但是当我将字符串数组传递给存储过程时，值不会传递给过程。