如何将具有70个字段的Java对象发送到具有相同字段数的对象的pl sql过程 [英] How to send a java object with 70 fields to pl sql procedure having an object with the same number of fields

问题描述

我有一个具有70个字段的Java类.我的表中还有70个字段，需要执行CRUD操作.我有一个过程，其中有一个带有70个字段的对象.我需要调用此过程并执行操作.任何人都可以提出任何可能的解决方案.

I am having a java class with 70 fields. I also have 70 fields in a table where I need to perform CRUD operations. I have a procedure which has an object with 70 fields as object. I need to call this procedure and perform the operations. Could anyone suggest any possible solution.

推荐答案

首先，我建议创建一个数据库OBJECT和该对象的构造函数，以表示过程的输入参数.

First I would suggest creating a database OBJECT and a constructor for that object to represent the input parameters for your procedure.

/* set this up with your 70 values needed */
CREATE OR REPLACE TYPE Myproc_Parameters AS OBJECT(
                                       Number_Parameter   NUMBER(10)
                                      ,Varchar_Parameter1 VARCHAR2(20) 
                                      ,Varchar_Parameter2 VARCHAR2(30) 
                                      ,Varchar_Etc        VARCHAR2(100) 
                                      ,CONSTRUCTOR FUNCTION Myproc_Parameters 
                                         RETURN SELF AS RESULT);
/

CREATE OR REPLACE TYPE BODY Myproc_Parameters AS
  CONSTRUCTOR FUNCTION Myproc_Parameters RETURN SELF AS RESULT AS
  BEGIN
    RETURN;
  END;
END;
/

您将把该对象传递给将用于CRUD操作的任何过程.

You will pass this object to whatever procedure you will be using for your CRUD operations.

CREATE OR REPLACE PROCEDURE Myproc_Crud (
  p_My_Params   IN  Myproc_Parameters)
IS
BEGIN
  NULL; /* CRUD logic here */
END;
/

一旦有了参数对象&程序设置，您可以从Java调用它.

Once you have the parameter object & procedure set up, you can call it from Java.

public class TestCallDatabaseProcedure {
    public static void main(String[] args) {    
        try {
            // set up an Oracle database connection 
            OracleConnection connection = getOracleConnection().unwrap(OracleConnection.class);

            System.out.println("Got Connection.");

            // create an object array based on the database object 
            // add your 70 values for the procedure here
            Object[] procParameters = new Object[] {1, "param2", "param3", "param4"};

            // use the object array to create a struct based on the database object
            Struct structParameters = connection.createStruct("MYPROC_PARAMETERS", procParameters);

            OracleCallableStatement statement = (OracleCallableStatement) connection.prepareCall(
                    "begin " +
                    "  Myproc_Crud(?); " +
                    "end;");

            // pass the struct to the callable statement executing your procedure
            statement.setObject(1, structParameters);           

            statement.execute();

            System.out.println("Statement executed.");

            statement.close();
            connection.close();
        } catch (SQLException se) {
            System.out.println("SQL exception: " + se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } 
    }

这是我用来获取Oracle数据库连接的方法.

This is the method I use to get the Oracle database connection.

    private static Connection getOracleConnection() throws Exception {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@myhost.com:1521:mydb";
        String username = "myuser";
        String password = "mypasswd";

        Class.forName(driver); 

        Connection connection = DriverManager.getConnection(url, username, password);

        return connection;
    }

这篇关于如何将具有70个字段的Java对象发送到具有相同字段数的对象的pl sql过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！