Skip to content

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
from http://www.eclipse.org/forums/index.php?t=msg&th=164573&start=0&
Suppose we have an Address 'type' -
CREATE OR REPLACE TYPE ADDR_TYPE AS OBJECT (
STREET VARCHAR2(40),
CITY VARCHAR2(40),
PROV VARCHAR2(40)
)
that is used in our Employee table -
CREATE OR REPLACE TABLE EMP_W_ADDR (
EMPNO NUMBER(7) NOT NULL,
FNAME VARCHAR2(40),
LNAME VARCHAR2(40),
ADDRESS ADDR_TYPE,
PRIMARY KEY (EMPNO)
)
This can be used in a StoredProcedure -
CREATE OR REPLACE PROCEDURE GET_EMPS_BY_PROV(X IN ADDR_TYPE, Y OUT SYS_REFCURSOR) AS
BEGIN
OPEN Y FOR SELECT * FROM EMP_W_ADDR ea WHERE ea.ADDRESS.PROV LIKE X.PROV;
END;
Populating the table -
INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (1, 'Mike', 'Norman', ADDR_TYPE('Somewhere','Nepean','Ont'));
INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (2, 'Rick', 'Barkhouse', ADDR_TYPE('Over the','Hull','Que'));
INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES (3, 'Blaise', 'Doughan', ADDR_TYPE('rainbow','Ottawa','Ont'));
To invoke the 'GET_EMPS_BY_PROV' procedure, we need some (simple) model classes
{for demo purposes, I put everything in the package 'test' but that can be changed easily ...}
package test;
public class Address {
public String street;
public String city;
public String province;
public Address() {
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append(street);
sb.append(" ");
sb.append(city);
sb.append(" ");
sb.append(province);
return sb.toString();
}
}
public class Employee {
public Integer id;
public String firstName;
public String lastName;
public Address address;
public Employee() {
}
@Override
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("[");
sb.append(id);
sb.append("] ");
sb.append(firstName);
sb.append(" ");
sb.append(lastName);
sb.append(" ");
sb.append(address);
return sb.toString();
}
}
A simple test class shows mappings for each type as well as how to build a query to execute the stored procedure.
{NB: all Oracle database artifacts - type names, table names, etc. should always be represented in
EclipseLink metadata in UPPERCASE}
//javase imports
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
//EclipseLink imports
import org.eclipse.persistence.logging.SessionLog;
import org.eclipse.persistence.mappings.DirectToFieldMapping;
import org.eclipse.persistence.mappings.structures.ObjectRelationalDataTypeDescriptor;
import org.eclipse.persistence.mappings.structures.StructureMapping;
import org.eclipse.persistence.platform.database.oracle.Oracle11Platform;
import org.eclipse.persistence.queries.ReadAllQuery;
import org.eclipse.persistence.queries.StoredProcedureCall;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.eclipse.persistence.sessions.DatabaseSession;
import org.eclipse.persistence.sessions.DatasourceLogin;
import org.eclipse.persistence.sessions.Project;
public class OracleObjectTypeTest {
static String username;
static String password;
static String url;
static final String DATABASE_USERNAME_KEY = "db.user";
static final String DATABASE_PASSWORD_KEY = "db.pwd";
static final String DATABASE_URL_KEY = "db.url";
static final String DEFAULT_DATABASE_USERNAME = "scott";
static final String DEFAULT_DATABASE_PASSWORD = "tiger";
static final String DEFAULT_DATABASE_DRIVER = "oracle.jdbc.OracleDriver";
static final String DEFAULT_DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:ORCL";
static final String ADDR_TYPE_NAME = "ADDR_TYPE";
static final String EMP_TABLE_NAME = "EMP_W_ADDR";
static final String PROCEDURE_NAME = "GET_EMPS_BY_PROV";
static final String CREATE_TYPE =
"CREATE TYPE " + ADDR_TYPE_NAME + " AS OBJECT (\n" +
"STREET VARCHAR2(40),\n" +
"CITY VARCHAR2(40),\n" +
"PROV VARCHAR2(40)\n" +
");";
static final String CREATE_EMP_TABLE =
"CREATE TABLE EMP_W_ADDR (\n" +
"EMPNO NUMBER(7) NOT NULL,\n" +
"FNAME VARCHAR2(40),\n" +
"LNAME VARCHAR2(40),\n" +
"ADDRESS ADDR_TYPE,\n" +
"PRIMARY KEY (EMPNO)\n" +
")";
static final String CREATE_PROCEDURE =
"CREATE PROCEDURE " + PROCEDURE_NAME + "(X IN " + ADDR_TYPE_NAME +
", Y OUT SYS_REFCURSOR) AS\n" +
"BEGIN\n" +
"OPEN Y FOR SELECT * FROM " + EMP_TABLE_NAME + " ea WHERE ea.ADDRESS.PROV LIKE X.PROV;\n" +
"END;\n";
static final String INSERT_ROW1 =
"INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES " +
"(1, 'Mike', 'Norman', ADDR_TYPE('Somewhere','Nepean','Ont'))";
static final String INSERT_ROW2 =
"INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES " +
"(2, 'Rick', 'Barkhouse', ADDR_TYPE('Over the','Hull','Que'))";
static final String INSERT_ROW3 =
"INSERT INTO EMP_W_ADDR (EMPNO, FNAME, LNAME, ADDRESS) VALUES " +
"(3, 'Blaise', 'Doughan', ADDR_TYPE('rainbow','Ottawa','Ont'))";
static final String DELETE_ROWS =
"DELETE FROM " + EMP_TABLE_NAME;
static final String DROP_TYPE =
"DROP TYPE " + ADDR_TYPE_NAME;
static final String DROP_EMP_TABLE =
"DROP TABLE " + EMP_TABLE_NAME;
static final String DROP_PROCEDURE =
"DROP PROCEDURE " + PROCEDURE_NAME;
static DatabaseSession ds = null;
public static void main(String...args) {
username = System.getProperty(DATABASE_USERNAME_KEY, DEFAULT_DATABASE_USERNAME);
password = System.getProperty(DATABASE_PASSWORD_KEY, DEFAULT_DATABASE_PASSWORD);
url = System.getProperty(DATABASE_URL_KEY, DEFAULT_DATABASE_URL);
DatasourceLogin login = new DatabaseLogin();
login.setUserName(username);
login.setPassword(password);
((DatabaseLogin)login).setConnectionString(url);
((DatabaseLogin)login).setDriverClassName(DEFAULT_DATABASE_DRIVER);
login.setDatasourcePlatform(new Oracle11Platform());
((DatabaseLogin)login).bindAllParameters();
Project p = new Project(login);
ObjectRelationalDataTypeDescriptor addrTypeDescriptor = new ObjectRelationalDataTypeDescriptor();
addrTypeDescriptor.descriptorIsAggregate();
addrTypeDescriptor.setJavaClass(test.Address.class);
addrTypeDescriptor.setAlias("Address");
addrTypeDescriptor.setStructureName(ADDR_TYPE_NAME);
DirectToFieldMapping streetMapping = new DirectToFieldMapping();
streetMapping.setAttributeName("street");
streetMapping.setFieldName("STREET");
addrTypeDescriptor.addMapping(streetMapping);
DirectToFieldMapping cityMapping = new DirectToFieldMapping();
cityMapping.setAttributeName("city");
cityMapping.setFieldName("CITY");
addrTypeDescriptor.addMapping(cityMapping);
DirectToFieldMapping provinceMapping = new DirectToFieldMapping();
provinceMapping.setAttributeName("province");
provinceMapping.setFieldName("PROV");
addrTypeDescriptor.addMapping(provinceMapping);
p.addDescriptor(addrTypeDescriptor);
ObjectRelationalDataTypeDescriptor empDesc = new ObjectRelationalDataTypeDescriptor();
empDesc.setJavaClass(test.Employee.class);
empDesc.setAlias("Employee");
empDesc.setTableName(EMP_TABLE_NAME);
empDesc.addPrimaryKeyFieldName("EMPNO");
DirectToFieldMapping empnoMapping = new DirectToFieldMapping();
empnoMapping.setAttributeName("id");
empnoMapping.setFieldName("EMPNO");
empDesc.addMapping(empnoMapping);
DirectToFieldMapping fnameMapping = new DirectToFieldMapping();
fnameMapping.setAttributeName("firstName");
fnameMapping.setFieldName("FNAME");
empDesc.addMapping(fnameMapping);
DirectToFieldMapping lnameMapping = new DirectToFieldMapping();
lnameMapping.setAttributeName("lastName");
lnameMapping.setFieldName("LNAME");
empDesc.addMapping(lnameMapping);
StructureMapping addrMapping = new StructureMapping();
addrMapping.setAttributeName("address");
addrMapping.setFieldName("ADDRESS");
addrMapping.setReferenceClass(test.Address.class);
empDesc.addMapping(addrMapping);
p.addDescriptor(empDesc);
ds = p.createDatabaseSession();
ds.setLogLevel(SessionLog.FINE);
ds.login();
try {
ds.executeNonSelectingSQL(CREATE_TYPE);
ds.executeNonSelectingSQL(CREATE_EMP_TABLE);
ds.executeNonSelectingSQL(CREATE_PROCEDURE);
ds.executeNonSelectingSQL(INSERT_ROW1);
ds.executeNonSelectingSQL(INSERT_ROW2);
ds.executeNonSelectingSQL(INSERT_ROW3);
}
catch (Exception e) {
// ignore
}
testProcedureExecution();
try {
ds.executeNonSelectingSQL(DELETE_ROWS);
ds.executeNonSelectingSQL(DROP_EMP_TABLE);
ds.executeNonSelectingSQL(DROP_PROCEDURE);
ds.executeNonSelectingSQL(DROP_TYPE);
}
catch (Exception e) {
// ignore
}
ds.logout();
}
@SuppressWarnings("unchecked")
public static void testProcedureExecution() {
ReadAllQuery raq = new ReadAllQuery(Employee.class);
raq.addArgument("X", String.class);
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName(PROCEDURE_NAME);
call.addNamedArgument("X", "X", Types.STRUCT, ADDR_TYPE_NAME, Address.class);
call.useNamedCursorOutputAsResultSet("Y");
raq.addCall(call);
List<Object> args = new ArrayList<Object>();
Address ontarioAddresses = new Address();
ontarioAddresses.province = "Ont";
args.add(ontarioAddresses);
Vector<Employee> ontarioEmployees = (Vector<Employee>)ds.executeQuery(raq, args);
for (Employee e : ontarioEmployees) {
System.out.println(e);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.