java - 如何将具有 70 个字段的 java 对象发送到具有相同字段数的对象的 pl sql 过程

Question

我有一个包含 70 个字段的 java 类。我的表中还有 70 个字段，我需要在其中执行 CRUD 操作。我有一个程序，它有一个包含 70 个字段的对象作为对象。我需要调用此过程并执行操作。任何人都可以提出任何可能的解决方案。

标签： javastored-proceduresplsql

Answer 1

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

/* 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 操作的任何过程。

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

一旦设置了参数对象和过程，就可以从 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 数据库连接的方法。

    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;
    }