How to send a java object with 70 fields to pl sql

2020-07-31 05:59发布

问题:

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.

回答1:

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

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

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

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