springboot：mybatis-plus调用Oracle存储过程，参数为集合

oracle需要执行的语句

创建新数组类型

CREATE OR REPLACE TYPE tables_array AS VARRAY(50000) OF VARCHAR2(64);

存储过程

PROCEDURE PRO_ZRZ_IDS_BY_BDCDYHS (v_bdcdyhs IN tables_array,v_cursor OUT sys_refcursor)
AS
v_sql varchar2(9000);
BEGIN
  v_sql := 'SELECT T.ID FROM  QJDC_ZRZ T LEFT JOIN  QJDC_BDCDY DY ON DY.ID = T.BDCDYID WHERE DY.BDCDYH IN ( '''||v_bdcdyhs(1)||'''';
  for i in 2 .. v_bdcdyhs.last loop
      v_sql := v_sql || ','''||v_bdcdyhs(i)||'''';
  end loop;
  v_sql := v_sql|| ')';
  open v_cursor for  v_sql  ;
 -- SELECT T.ID FROM  QJDC_ZRZ T LEFT JOIN  QJDC_BDCDY DY ON DY.ID = T.BDCDYID WHERE DY.BDCDYH IN (v_bdcdyhs);
end PRO_ZRZ_IDS_BY_BDCDYHS;

服务端需要的操作

依赖

<!-- https://mvnrepository.com/artifact/cn.easyproject/orai18n -->
		<dependency>
            <groupId>cn.easyproject</groupId>
            <artifactId>orai18n</artifactId>
            <version>12.1.0.2.0</version>
        </dependency>

获取yml配置文件中的数据库信息

package com.tuxin.data.config;

import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * @author zfxiang [xiangzf@engine3d.com]
 * @version v1.0
 * @className test
 * @description
 * @date 2020/7/30 13:20
 */
@Component
@ConfigurationProperties(prefix = "spring.datasource")
@Data
public class DataSourceInfo  {
    private String url;
    private String username;
    private String password;
}

处理jdbc类型

package com.tuxin.data.config;

import com.tuxin.common.util.SpringUtils;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @author zfxiang [xiangzf@engine3d.com]
 * @version v1.0
 * @className ArrayTypeHandler
 * @description 自定义的typeHandler()因为我们在Oracle声明的自定义数组.是自定义类型的.所以mybatis无法自动处理
 * @date 2019/7/18 9:13
 */
//声明这是处理jdbc类型的
@MappedJdbcTypes(JdbcType.ARRAY)
public class ArrayTypeHandler extends BaseTypeHandler {

    @Override
    public Object getNullableResult(ResultSet arg0, String arg1) {
        return null;
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) {
        return null;
    }

    @Override
    public Object getNullableResult(CallableStatement arg0, int arg1) {
        return null;
    }


    /**
     * 将java中的集合类型转换成数据库中对应类型(数组类型)
     * 前提： 必须引用orai18n.jar这个jar，否则没有效果
     * @author zfxiang [xiangzf@engine3d.com]
     * @date 2020/7/31 14:28
     * @param parameterSetter :
     * @param i :
     * @param o :
     * @param jdbcType :
     * @return : void
     */
    @Override
    public void setNonNullParameter(java.sql.PreparedStatement parameterSetter, int i,
                                    Object o, JdbcType jdbcType) throws SQLException {
        Connection conn = null;
        try {
            if (null != o) {
                List<String> list = (ArrayList<String>) o;
                //该类无法直接获取yml中的信息，所以通过DataSourceInfo获取yml配置文件中的数据库信息
                DataSourceInfo bean = SpringUtils.getBean(DataSourceInfo.class);
                conn = DriverManager.getConnection(bean.getUrl(), bean.getUsername(), bean.getPassword());
                // TABLES_ARRAY：对应的是在oracle创建的type名，必须大写
                ArrayDescriptor arrayDes = ArrayDescriptor.createDescriptor("TABLES_ARRAY", conn);
                //这里是声明一个数据库的数组类型
                ARRAY array = new ARRAY(arrayDes, conn, list.toArray());
                parameterSetter.setArray(i, array);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != conn) {
                conn.close();
            }
        }

    }
}

Service层

public List<String> getZrzIdsByZrzBdcdyhs(List<String> bdcdyhList) {
        Map<String, Object> params = new HashMap<>();
        params.put("v_bdcdyhs", bdcdyhList);
        params.put("result",new ArrayList<HashMap<String,String>>());
        qjdcZrzMapper.getZrzIdsByZrzBdcdyhs(params);
        Object result = params.get("result");
        if (null == result){
            return null;
        }
        List<String> zrzIds = new ArrayList<>();
        List<Map<String, String>> mapList = (List<Map<String, String>>) result;
        for (Map<String, String> map : mapList){
            zrzIds.add(map.get("ID"));
        }
        return  zrzIds;
    }

Mapper层

    void getZrzIdsByZrzBdcdyhs(Map<String, Object> params);

xml

<select id="getZrzIdsByZrzBdcdyhs"  statementType="CALLABLE" parameterType="java.util.Map">
		{
			call swbdc_pro.PRO_ZRZ_IDS_BY_BDCDYHS(
				#{v_bdcdyhs,jdbcType=ARRAY,javaType=List,typeHandler= com.tuxin.data.config.ArrayTypeHandler,mode=IN},
				#{result,mode=OUT,jdbcType=CURSOR,javaType=ResultSet, resultMap=resultMap}
			)
		}
	</select>

注：

#{v_bdcdyhs,jdbcType=ARRAY,javaType=List,typeHandler= com.tuxin.data.config.ArrayTypeHandler,mode=IN},
jdbcType为ArrayTypeHandler 类申明的jdbc类型，typeHandler为ArrayTypeHandler 类的包名+类名