Mybatis - Passing String[] to Oracle stored procedure

Question

I have a Oracle SP with the parameters as below

create type stringArray as table of varchar2(30)
/

CREATE OR REPLACE PROCEDURE create_deliverable
(
    in_p_name varchar2,
    in_p_filename stringArray 
)AS
  ret_ID number;
BEGIN
...
END;
/

while the "file name" is a string array in Oracle. The bean is defined as below:

@Data
public class BaseEntity {
    private String name;
    private String[] filename;
}

I want to pass the entire bean to Oracle stored procedure.

In my mapper.java

@Mapper
public interface BaseMapper {


    void add(BaseEntity d);
}

In my BaseMapper.xml

<select id="add"  statementType="CALLABLE" parameterType="BaseEntity">
         call create_deliverable(
             #{name},
             #{filename,jdbcType=ARRAY, typeHandler=ArrayTypeHandler}
         )
    </select>

I attempted to write up a type handler to deal with the case. But I failed in the part to work it out.

Here's what's failed:

public class ArrayTypeHandler extends BaseTypeHandler<Object> {
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)
          throws SQLException {

      Class<?> componentType = parameter.getClass().getComponentType();
      String arrayTypeName = resolveTypeName(componentType);

      Array array = ps.getConnection().createArrayOf(arrayTypeName, (Object[]) parameter);
      ps.setArray(i, array);
      array.free();
  }
}

The failure is related to the part of "createArrayOf". It reads the arrayTypeName as VARCHAR, which is correct

Here's the error message:

Could not set parameters for mapping: ParameterMapping{property='filename', mode=IN, javaType=class java.util.ArrayList, jdbcType=ARRAY, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #2 with JdbcType ARRAY

Any input will be greatly appreciated.

Thanks

Please add the definition of stringarray to the question. It's a user-defined type, right? — ave
– ave, Commented Dec 12, 2020 at 18:30
Hello ave, yes it's a user defined type. It's created as below: create type stringarray as table of varchar2(30); — Ginger_Chacha
– Ginger_Chacha, Commented Dec 12, 2020 at 21:04

Ginger_Chacha · Accepted Answer · 2020-12-13 05:14:37Z

@ave has given the correct answer. Just because I'm using springboot I'm here to recap the solution:

I'm using the default hikari pool & I didn't touch the datasource previously. It's autowired. It needs to be overwritten otherwise below error message would pop up

Cause: java.lang.ClassCastException: class com.zaxxer.hikari.pool.HikariProxyConnection cannot be cast to class oracle.jdbc.OracleConnection (com.zaxxer.hikari.pool.HikariProxyConnection and oracle.jdbc.OracleConnection are in unnamed module of loader 'app'

Here's my application.yml

spring: application: name: tools datasource: username: myuser password: mypassword url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost) (PORT=1521))(CONNECT_DATA= (SERVICE_NAME=orclpdb))) driver-class-name: oracle.jdbc.OracleDriver

Here's my datasource bean

    import oracle.jdbc.pool.OracleDataSource;

    @Value("${spring.datasource.username}")
    String username;

    @Value("${spring.datasource.password}")
    String password;

    @Value("${spring.datasource.url}")
    String url;
    @Bean
    DataSource oracleDataSource() throws SQLException {

        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser(username);
        dataSource.setPassword(password);
        dataSource.setURL(url);
        return dataSource;
    }

2）Here's my entity bean(I created new ones to not change the previous ones):

@Data
public class TestEntity {
    private String name;
    private String[] filename;
}

3)Here's my mapper java:

@Mapper
public interface TestMapper {
    void add(TestEntity t);
}

Here's my mapper xml:

    <select id="add"  statementType="CALLABLE" parameterType="org.ssc.gss.entity.TestEntity">
        call create_deliverable_test(
            #{name,mode=IN},
            #{filename,mode=IN,jdbcType=ARRAY,javaType=ArrayList, typeHandler=OracleStringArrayTypeHandler}
            )
    </select>

please note the "filename" parameter needs to be assigned to the typeHandler as mybatis default one won't work

5)the OracleStringArrayTypeHandler => Refers to ave's answer. There are a few more methods to be implemented but the key is the set parameter & I used

import oracle.jdbc.OracleConnection;

instead of

oracle.jdbc.driver.OracleDriver;

as the 2nd one has been deprecated

The Oracle stored procedure part:

create type stringArray as table of varchar2(30)
/

CREATE OR REPLACE PROCEDURE create_deliverable_test
(
    in_p_name varchar2,
    in_p_filename stringArray 
)AS
  ret_ID number;
BEGIN
...
END;
/

Thanks for @ave again. Without the help from @ave I couldn't move forward for days. I was thinking of use delimiter seperated string instead previously but absolutely string [] is much more advanced & convinient

ave · Accepted Answer · 2020-12-13 06:14:34Z

0

Oracle's JDBC driver (as of version 19.8.0.0) does not support java.sql.Connection#createArrayOf() which is used by MyBatis' built-in ArrayTypeHandler, unfortunately.

So, you need to write a custom type handler.
I just tested and the following implementation worked.

package test;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import oracle.jdbc.OracleConnection;

public class OracleStringArrayTypeHandler extends BaseTypeHandler<String[]> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i,
      String[] parameter, JdbcType jdbcType) throws SQLException {
    OracleConnection conn = ps.getConnection().unwrap(OracleConnection.class);
    Array array = conn.createOracleArray("STRINGARRAY", parameter);
    ps.setArray(i, array);
    array.free();
  }
  ...

And specify the type handler in the parameter reference.

call create_deliverable(
  #{name},
  #{filename,jdbcType=ARRAY,typeHandler=test.OracleStringArrayTypeHandler}
)

edited Dec 13, 2020 at 6:14

answered Dec 12, 2020 at 22:56

ave

3,7242 gold badges16 silver badges33 bronze badges

2 Comments

Ginger_Chacha Over a year ago

much appreciated! My issue has been resolved now. Just add my 2 cents here - I'm using springboot as well so I need to overwrite the default hikari data source to convert the sql.connection to oracleconnection :) in addition the oracle.jdbc.driver.OracleConnection has been replaced by oracle.jdbc.OracleDriver. Your kindness and talent saved me from days of wondering and helped me to identify an issue I haven't been realized for weeks

ave Over a year ago

@Ginger_Chacha You're very welcome and thank you for the detailed follow-up :) I have updated the type handler code so that it works even when the OracleConnection is wrapped in other implementations like HikariProxyConnection.

Collectives™ on Stack Overflow

Mybatis - Passing String[] to Oracle stored procedure

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related