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