Java服务端调用Oracle存储过程，带结构体和数组对象入参

Oracle存储过程及结构体参数

入参有PAYMENTS_MONEY结构体，T_ORDERITEM对象数组；

示例代码

如果有中文乱码问题，只能用动态转换字符编码处理

import com.alibaba.fastjson2.JSON;
import lombok.extern.slf4j.Slf4j;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class CallOracle {
    static final String driverClass = "oracle.jdbc.driver.OracleDriver";
    static final String connectionURL = "jdbc:oracle:thin:@//172.*.*.*:1521/orcl";
    static final String userID = "*";
    static final String userPassword = "*";

    /*缴费回写*/
    public static ResultTBody<SaveOutpOrdersInsurOutputDTO> saveOutpOrdersInsur(SaveOutpOrdersInsurInputDTO in) {
        Connection conn = null;
        CallableStatement statement = null;
        try {
            Class.forName(driverClass).newInstance();
            conn = DriverManager.getConnection(connectionURL, userID, userPassword);
            //参数:payments
            StructDescriptor desPayments = StructDescriptor.createDescriptor("PAYMENTS_MONEY", conn);
            Object[] payments = new Object[4];
            payments[0] = in.getPayments().getYbjj();
            payments[1] = in.getPayments().getZhj();
            payments[2] = in.getPayments().getXj();
            payments[3] = in.getPayments().getYydz();
            STRUCT structPayments = new STRUCT(desPayments, conn, payments);
            statement = conn.prepareCall("{call SWIFT.SAVE_OUTPORDERS_INSUR(?,?,?,?,?,?,?,?,?)}");
            statement.setString(1, EncodingT.g2i(in.getPatientId()));
            //参数:o_list
            STRUCT[] structOrderItems = new STRUCT[in.getOrderItems().size()];
            for (int i = 0; i < in.getOrderItems().size(); i++) {
                StructDescriptor desOrderItem = StructDescriptor.createDescriptor("T_ORDERITEM", conn);
                Object[] orderItem = new Object[5];
                orderItem[0] = in.getOrderItems().get(i).getVisitDate();
                orderItem[1] = in.getOrderItems().get(i).getVisitNo();
                orderItem[2] = in.getOrderItems().get(i).getSerialNo();
                orderItem[3] = in.getOrderItems().get(i).getPrescNo();
                orderItem[4] = in.getOrderItems().get(i).getOrderNo();
                STRUCT structOrderItem = new STRUCT(desOrderItem, conn, orderItem);
                structOrderItems[i] = structOrderItem;
            }
            ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("ARRY_ORDERLIST", conn);
            ARRAY arrayOrderItems = new ARRAY(arrayDescriptor, conn, structOrderItems);
            statement.setArray(2, arrayOrderItems);
            statement.setString(3, EncodingT.g2i(in.getOperNo()));
            statement.setString(4, EncodingT.g2i(in.getChannel()));
            statement.setObject(5, structPayments);
            statement.registerOutParameter(6, oracle.jdbc.OracleTypes.VARCHAR);
            statement.registerOutParameter(7, oracle.jdbc.OracleTypes.VARCHAR);
            statement.registerOutParameter(8, OracleTypes.INTEGER);
            statement.registerOutParameter(9, oracle.jdbc.OracleTypes.VARCHAR);
            statement.execute();
            String rcptNo = EncodingT.i2g(statement.getString(6));
            String prercptNo = EncodingT.i2g(statement.getString(7));
            int resultNo = statement.getInt(8);
            String errText = EncodingT.i2g(statement.getString(9));
            log.info("(saveOutpOrdersInsur)rcptNo:" + rcptNo + ", prercptNo:" + prercptNo + ", resultNo:" + resultNo + ", errText:" + errText);
            if (resultNo != 1) {
                return ResultTBody.error(errText);
            }
            SaveOutpOrdersInsurOutputDTO out = new SaveOutpOrdersInsurOutputDTO();
            out.setRcptNo(rcptNo);
            out.setPrercptNo(prercptNo);
            return ResultTBody.success(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ResultTBody.error(Const.ShowMgs.INTERNAL_HANDLER_ERROR);
    }

    /**
     * 测试
     */
    private static void testSaveOutpOrdersInsur() {
        SaveOutpOrdersInsurInputDTO in = new SaveOutpOrdersInsurInputDTO();
        in.setPatientId("a01");
        in.setChannel("Z");
        in.setOperNo("9527");
        PaymentsInputDTO payments = new PaymentsInputDTO();
        payments.setYbjj(0);
        payments.setZhj(0);
        payments.setXj(5.55);
        payments.setYydz(0);
        in.setPayments(payments);
        List<SaveOutpOrdersInsurInputDTO.OrderItem> orderItems = new ArrayList<>();
        SaveOutpOrdersInsurInputDTO.OrderItem item = new SaveOutpOrdersInsurInputDTO.OrderItem();
        orderItems.add(item);
        in.setOrderItems(orderItems);
        ResultTBody<SaveOutpOrdersInsurOutputDTO> r = CallOracle.saveOutpOrdersInsur(in);
        System.out.println(JSON.toJSONString(r));
    }

    public static void main(String[] args) {
        testSaveOutpOrdersInsur();
    }
}

---

public class EncodingT {
    @SneakyThrows
    public static String i2g(String text) {
        if (text == null) return "";
        return new String(text.getBytes(StandardCharsets.ISO_8859_1), "GBK");
    }

    @SneakyThrows
    public static String g2i(String text) {
        if (text == null) return "";
        return new String(text.getBytes("GBK"), StandardCharsets.ISO_8859_1);
    }
}

注意

1. call存储过程中的参数数量和类型要对应正确；
2. 入参和出参的中文乱码要用new String()转换；
3. 入参中的结构体对应STRUCT类型，对象数组对应ARRAY类型；
4. createDescriptor方法的第一个参数是存储过程中的变量名；