public

  • Download Gist
StoredProcedure with advanced Oracle types
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268
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);
}
}
 
}

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.