如何将 Java 对象传递给存放在 Oacle 程序, 并附有以下细节
原标题：how to pass a java object to oracle stored procedure with following details
I have my ORACLE table with structure as
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
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
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.
问题回答
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 ...
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.
相关问题
Spring Properties File
Hi have this j2ee web application developed using spring framework. I have a problem with rendering mnessages in nihongo characters from the properties file. I tried converting the file to ascii using ...
What do you say of chopping type-4 UUID in this manner
Check this,
List<String> list = new ArrayList<String>();
for (int i = 0; i < 10000; i++) {
String value = (""+UUID.randomUUID().getLeastSignificantBits()).substring(3, ...
combining decorator and state pattern in java - question about OO design
I am in the middle of solving a problem where I think it s best suited for a decorator and a state pattern. The high level setting is something like a sandwich maker and dispenser, where I have a set ...
Unable to execute stored Procedure using Java and JDBC on SQL server
I have been trying to execute a MS SQL Server stored procedure via JDBC today and have been unsuccessful thus far. The stored procedure has 1 input and 1 output parameter. With every combination I ...
Logging a global ID in multiple components
I have a system which contains multiple applications connected together using JMS and Spring Integration. Messages get sent along a chain of applications.
[App A] -> [App B] -> [App C]
We set a ...
Java Library Size
If I m given two Java Libraries in Jar format, 1 having no bells and whistles, and the other having lots of them that will mostly go unused.... my question is:
How will the larger, mostly unused ...
How to get the Array Class for a given Class in Java?
I have a Class variable that holds a certain type and I need to get a variable that holds the corresponding array class. The best I could come up with is this:
Class arrayOfFooClass = java.lang....
SQLite , Derby vs file system
I m working on a Java desktop application that reads and writes from/to different files. I think a better solution would be to replace the file system by a SQLite database.
How hard is it to migrate ...
