oracle 如何使用以下详细信息将java对象传递给oracle存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10810563/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to pass a java object to oracle stored procedure with following details
提问by User 4.5.5
I have my ORACLE table with structure as
我的 ORACLE 表的结构为
desc extraction_log1
Name Null
Type
------------------------------ -------- ------------------------------------------------------------ ---------------------------------------------------------------------------------------------------- -----------------------------
ROW_NUM NOT NULL NUMBER
DATE_TIME TIMESTAMP(8)
USER_NAME VARCHAR2(32)
PLATFORM_NAME VARCHAR2(20)
R_OBJECT_ID VARCHAR2(16)
Then I created an object type in oracle as
然后我在 oracle 中创建了一个对象类型作为
create or replace type EXTRACTION_LOG_TYPE as object (
USER_NAME VARCHAR2(32),
R_OBJECT_ID VARCHAR2(16),
);
Then I created procedure in a package as
然后我在一个包中创建了程序作为
create or replace package body PAC_BEAN is
--The insert procedure will receive EXTRACTION_LOG_TYPE and put it into table EXTRACTION_LOG1.
procedure PRO_INSERT_LOG(ELT in EXTRACTION_LOG_TYPE) is
begin
insert into EXTRACTION_LOG1 ( R_OBJECT_ID, USER_NAME)
values (ELT.R_OBJECT_ID, ELT.USER_NAME);
commit;
exception
when others then
rollback;
end PRO_INSERT_LOG;
end PAC_BEAN;
and coming to my java side I have declared a bean with
来到我的 java 端，我已经声明了一个 bean
public class ExtractionLogType {
//Name declared in Oracle
public static final String ORACLE_OBJECT_NAME = "EXTRACTION_LOG_TYPE";
//The attributes
private String R_OBJECT_ID;
private String USER_NAME;
//setters and getters
public String getR_OBJECT_ID() {
return R_OBJECT_ID;
}
public void setR_OBJECT_ID(String rOBJECTID) {
R_OBJECT_ID = rOBJECTID;
}
public String getUSER_NAME() {
return USER_NAME;
}
public void setUSER_NAME(String uSERNAME) {
USER_NAME = uSERNAME;
}
}
in my Class containing main
在我的班级中包含主要
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBLogger{
String dbUrl;
Connection con;
//constructor for creation of connection object
as and when an object of DBLogger is instantiated
public DBLogger(){
dbUrl = "jdbc:oracle:thin@my url";
try {
//load Oracle Driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.err.println("Oracle driver class not found");
}
try {
//instantiate connection object
con = DriverManager.getConnectio (dbUrl,"userId","pwd");
} catch (SQLException e) {
e.printStackTrace();
System.err.println("Connection object to oracle cant be established");
}
}
public static void main(String args[]){
try{
DBLogger db=new DBLogger();
CallableStatement cs = null;
ExtractionLogType elt=new ExtractionLogType();
elt.setR_OBJECT_ID("79479479A900");
elt.setUSER_NAME("Jeevan");
cs = db.con.prepareCall("{call PAC_BEAN.PRO_INSERT_LOG(?)}");
/*
* *code to insert the above object into our Database
*
*/
cs.execute();
System.out.println("insert procedure executed successfully");
db.con.close();
} //end try
catch (SQLException e) {
e.printStackTrace(); }
catch(Exception e) { e.printStackTrace();
}
}
}
I can't figure out the code to make the object get inserted into my database.
can anyone suggest me regarding this.
我无法弄清楚使对象插入到我的数据库中的代码。
任何人都可以就此向我建议。
Thank You.
谢谢你。
回答by sebastian
You will have to define a array descriptor for your database type, this example could help you:
你必须为你的数据库类型定义一个数组描述符，这个例子可以帮助你：
final ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("EXTRACTION_LOG_TYPE", con);
// create an Object Array
Object[] data = new Object[2];
// set the values in order of appearance
data[0] = elt.getUSER_NAME();
data[1] = elt.getR_OBJECT_ID();
// Create the Array
ARRAY array = new ARRAY(descriptor, con, data);
// put it on your statement
cs.setArray(1, array);
// execute ...
回答by smoczyna
This is terrible idea to create any objects in SYSTEM schema of the database. It is the same bad idea to connect your app straight to this scheme either. This looks like a lack of privileges disallowing you to get what you want. Create new schema, dedicated user of this schema and then create all required object using this new user (it will be the owner of your objects). This way you can avoid "issue" where you cannot access something you supposed to have an access to.
在数据库的 SYSTEM 模式中创建任何对象是一个糟糕的主意。将您的应用程序直接连接到这个方案也是同样的坏主意。这看起来像是缺乏特权，使您无法获得想要的东西。创建新模式，此模式的专用用户，然后使用此新用户创建所有必需的对象（它将是您的对象的所有者）。通过这种方式，您可以避免“问题”，即您无法访问您应该访问的内容。
