use of org.eclipse.persistence.queries.StoredProcedureCall in project eclipselink by eclipse-ee4j.
the class CustomSQLTestModel method build2ResultSetTest.
/**
* Test a procedure with multiple result sets
*/
public static TestCase build2ResultSetTest() {
TestCase test = new TestCase() {
@Override
public void test() {
if (!(getSession().getPlatform().isMySQL())) {
throwWarning("This test can only be run in MySQL");
}
DataReadQuery query = new DataReadQuery();
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("Read_Emp_Add");
call.setHasMultipleResultSets(true);
query.setCall(call);
List<Map> result = (List<Map>) getSession().executeQuery(query);
if (result.size() != (getSession().readAllObjects(Employee.class).size() + getSession().readAllObjects(Address.class).size())) {
throwError("Incorrect number of rows returned : " + result);
}
result = (List<Map>) getSession().executeQuery(query);
}
};
test.setName("2ResultSetTest");
return test;
}
use of org.eclipse.persistence.queries.StoredProcedureCall in project eclipselink by eclipse-ee4j.
the class EmployeeCustomSQLMWIntegrationSystem method buildUNamedStoredProcedureCallInQueryIntoDescriptor.
public void buildUNamedStoredProcedureCallInQueryIntoDescriptor() {
StoredProcedureCall unamedcall = new StoredProcedureCall();
unamedcall.setProcedureName("StoredProcedure_InOut_Out_In");
unamedcall.addUnamedInOutputArgumentValue(100, "P_INOUT_FIELD_NAME", Integer.class);
unamedcall.addUnamedOutputArgument("P_OUT_FIELD_NAME", Integer.class);
unamedcall.addUnamedArgumentValue(1000);
// Set stored procedure to Named query
DataReadQuery unameddataReadQuery = new DataReadQuery();
unameddataReadQuery.setCall(unamedcall);
employeeDescriptor.getQueryManager().addQuery("UNamedStoredProcedureCallInDataReadQuery", unameddataReadQuery);
}
use of org.eclipse.persistence.queries.StoredProcedureCall in project eclipselink by eclipse-ee4j.
the class StoredProcedureQueryTestSuite method testQueryWithMultipleResultsFromCode.
/**
* Test multiple result sets by setting the SQL results set mapping from code.
*/
public void testQueryWithMultipleResultsFromCode() throws Exception {
if (supportsStoredProcedures() && getPlatform().isMySQL()) {
// SQL result set mapping for employee.
SQLResultSetMapping employeeResultSetMapping = new SQLResultSetMapping("EmployeeResultSetMapping");
employeeResultSetMapping.addResult(new EntityResult(Employee.class));
// SQL result set mapping for address.
SQLResultSetMapping addressResultSetMapping = new SQLResultSetMapping("AddressResultSetMapping");
addressResultSetMapping.addResult(new EntityResult(Address.class));
// SQL result set mapping for project (using inheritance and more complex result)
SQLResultSetMapping projectResultSetMapping = new SQLResultSetMapping("ProjectResultSetMapping");
EntityResult projectEntityResult = new EntityResult(Project.class);
projectResultSetMapping.addResult(projectEntityResult);
projectEntityResult = new EntityResult(SmallProject.class);
projectEntityResult.addFieldResult(new FieldResult("id", "SMALL_ID"));
projectEntityResult.addFieldResult(new FieldResult("name", "SMALL_NAME"));
projectEntityResult.addFieldResult(new FieldResult("description", "SMALL_DESCRIPTION"));
projectEntityResult.addFieldResult(new FieldResult("teamLeader", "SMALL_TEAMLEAD"));
projectEntityResult.addFieldResult(new FieldResult("version", "SMALL_VERSION"));
projectEntityResult.setDiscriminatorColumn("SMALL_DESCRIM");
projectResultSetMapping.addResult(projectEntityResult);
projectResultSetMapping.addResult(new ColumnResult("BUDGET_SUM"));
// SQL result set mapping for employee using constructor results.
SQLResultSetMapping employeeConstrustorResultSetMapping = new SQLResultSetMapping("EmployeeConstructorResultSetMapping");
ConstructorResult constructorResult = new ConstructorResult(EmployeeDetails.class);
ColumnResult columnResult = new ColumnResult("EMP_ID");
columnResult.getColumn().setType(Integer.class);
constructorResult.addColumnResult(columnResult);
columnResult = new ColumnResult("F_NAME");
columnResult.getColumn().setType(String.class);
constructorResult.addColumnResult(columnResult);
columnResult = new ColumnResult("L_NAME");
columnResult.getColumn().setType(String.class);
constructorResult.addColumnResult(columnResult);
columnResult = new ColumnResult("R_COUNT");
columnResult.getColumn().setType(Integer.class);
constructorResult.addColumnResult(columnResult);
employeeConstrustorResultSetMapping.addResult(constructorResult);
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("Read_Multiple_Result_Sets");
call.setHasMultipleResultSets(true);
call.setReturnMultipleResultSetCollections(true);
ResultSetMappingQuery query = new ResultSetMappingQuery(call);
query.addSQLResultSetMapping(employeeResultSetMapping);
query.addSQLResultSetMapping(addressResultSetMapping);
query.addSQLResultSetMapping(projectResultSetMapping);
query.addSQLResultSetMapping(employeeConstrustorResultSetMapping);
List allResults = (List) getPersistenceUnitServerSession().executeQuery(query);
assertNotNull("No results returned", allResults);
assertTrue("Incorrect number of results returned", allResults.size() == 4);
// Verify first result set mapping --> Employee
List results0 = (List) allResults.get(0);
assertNotNull("No Employee results returned", results0);
assertTrue("Empty Employee results returned", results0.size() > 0);
// Verify second result set mapping --> Address
List results1 = (List) allResults.get(1);
assertNotNull("No Address results returned", results1);
assertTrue("Empty Address results returned", results1.size() > 0);
// Verify third result set mapping --> Project
List results2 = (List) allResults.get(2);
assertNotNull("No Project results returned", results2);
assertTrue("Empty Project results returned", results2.size() > 0);
for (Object result2 : results2) {
Object[] result2Element = (Object[]) result2;
assertTrue("Failed to Return 3 items", (result2Element.length == 3));
// Using Number as Different db/drivers can return different types
// e.g. Oracle with ijdbc14.jar returns BigDecimal where as Derby
// with derbyclient.jar returns Double. NOTE: the order of checking
// here is valid and as defined by the spec.
assertTrue("Failed to return LargeProject", (result2Element[0] instanceof LargeProject));
assertTrue("Failed To Return SmallProject", (result2Element[1] instanceof SmallProject));
assertTrue("Failed to return column", (result2Element[2] instanceof Number));
assertFalse("Returned same data in both result elements", ((SmallProject) result2Element[1]).getName().equals(((LargeProject) result2Element[0]).getName()));
}
// Verify fourth result set mapping --> Employee Constructor Result
List results3 = (List) allResults.get(3);
assertNotNull("No Employee constructor results returned", results3);
assertTrue("Empty Employee constructor results returned", results3.size() > 0);
}
}
use of org.eclipse.persistence.queries.StoredProcedureCall in project eclipselink by eclipse-ee4j.
the class XmlEntityMappingsGenerator method generateXmlEntityMappings.
/**
* Generate an XMLEntityMappings instance based on a given OR Project's Queries and Descriptors.
*
* @param orProject the ORM Project instance containing Queries and Descriptors to be used to generate an XMLEntityMappings
* @param complexTypes list of composite database types used to generate metadata for advanced Oracle and PL/SQL types
* @param crudOperations map of maps keyed on table name - the second map are operation name to SQL string entries
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static XMLEntityMappings generateXmlEntityMappings(Project orProject, List<CompositeDatabaseType> complexTypes, Map<String, Map<String, String>> crudOperations) {
List<ClassDescriptor> descriptors = orProject.getOrderedDescriptors();
List<DatabaseQuery> queries = orProject.getQueries();
XMLEntityMappings xmlEntityMappings = new XMLEntityMappings();
xmlEntityMappings.setEmbeddables(new ArrayList<EmbeddableAccessor>());
xmlEntityMappings.setEntities(new ArrayList<EntityAccessor>());
xmlEntityMappings.setPLSQLRecords(new ArrayList<PLSQLRecordMetadata>());
xmlEntityMappings.setPLSQLTables(new ArrayList<PLSQLTableMetadata>());
xmlEntityMappings.setOracleObjectTypes(new ArrayList<OracleObjectTypeMetadata>());
xmlEntityMappings.setOracleArrayTypes(new ArrayList<OracleArrayTypeMetadata>());
// process PL/SQL records and collections, and Oracle advanced JDBC types
List<PLSQLRecordMetadata> plsqlRecords = null;
List<PLSQLTableMetadata> plsqlTables = null;
List<OracleObjectTypeMetadata> objectTypes = null;
List<OracleArrayTypeMetadata> arrayTypes = null;
// generate metadata for each of the composite types
List<ComplexTypeMetadata> complexTypeMetadata = processCompositeTypes(complexTypes, orProject);
for (ComplexTypeMetadata cTypeMetadata : complexTypeMetadata) {
if (cTypeMetadata.isOracleComplexTypeMetadata()) {
OracleComplexTypeMetadata octMetadata = (OracleComplexTypeMetadata) cTypeMetadata;
if (octMetadata.isOracleArrayTypeMetadata()) {
if (arrayTypes == null) {
arrayTypes = new ArrayList<OracleArrayTypeMetadata>();
}
arrayTypes.add((OracleArrayTypeMetadata) octMetadata);
} else {
// assumes OracleObjectTypeMetadata
if (objectTypes == null) {
objectTypes = new ArrayList<OracleObjectTypeMetadata>();
}
objectTypes.add((OracleObjectTypeMetadata) octMetadata);
}
} else {
// assumes PL/SQL complex type metadata
PLSQLComplexTypeMetadata plsqlctMetadata = (PLSQLComplexTypeMetadata) cTypeMetadata;
if (plsqlctMetadata.isPLSQLRecordMetadata()) {
if (plsqlRecords == null) {
plsqlRecords = new ArrayList<PLSQLRecordMetadata>();
}
plsqlRecords.add((PLSQLRecordMetadata) plsqlctMetadata);
} else {
// assumes PLSQLTableMetadata
if (plsqlTables == null) {
plsqlTables = new ArrayList<PLSQLTableMetadata>();
}
plsqlTables.add((PLSQLTableMetadata) plsqlctMetadata);
}
}
}
// add generated metadata
xmlEntityMappings.setPLSQLRecords(plsqlRecords);
xmlEntityMappings.setPLSQLTables(plsqlTables);
xmlEntityMappings.setOracleObjectTypes(objectTypes);
xmlEntityMappings.setOracleArrayTypes(arrayTypes);
// process stored procedures/functions
List<NamedPLSQLStoredProcedureQueryMetadata> plsqlStoredProcs = null;
List<NamedPLSQLStoredFunctionQueryMetadata> plsqlStoredFuncs = null;
List<NamedStoredProcedureQueryMetadata> storedProcs = null;
List<NamedStoredFunctionQueryMetadata> storedFuncs = null;
List<NamedNativeQueryMetadata> namedNativeQueries = null;
// process database queries set on the descriptor
for (DatabaseQuery query : queries) {
if (query.getCall().isStoredFunctionCall()) {
if (query.getCall() instanceof PLSQLStoredFunctionCall) {
PLSQLStoredFunctionCall call = (PLSQLStoredFunctionCall) query.getCall();
NamedPLSQLStoredFunctionQueryMetadata metadata = new NamedPLSQLStoredFunctionQueryMetadata();
metadata.setName(query.getName());
metadata.setProcedureName(call.getProcedureName());
List<PLSQLParameterMetadata> params = new ArrayList<PLSQLParameterMetadata>();
if (plsqlStoredFuncs == null) {
plsqlStoredFuncs = new ArrayList<NamedPLSQLStoredFunctionQueryMetadata>();
}
PLSQLargument arg;
PLSQLParameterMetadata param;
List<PLSQLargument> types = call.getArguments();
for (int i = 0; i < types.size(); i++) {
arg = types.get(i);
param = new PLSQLParameterMetadata();
param.setName(arg.name);
String dbType = arg.databaseType.getTypeName();
if (arg.databaseType == XMLType) {
dbType = XMLType.name();
} else if (arg.databaseType == PLSQLBoolean) {
dbType = PLSQLBoolean.name();
} else {
if (!(getJDBCTypeFromTypeName(dbType) == Types.OTHER)) {
dbType = dbType.concat(_TYPE_STR);
}
}
param.setDatabaseType(dbType);
if (i == 0) {
// first arg is the return arg
metadata.setReturnParameter(param);
if (arg.cursorOutput) {
param.setDirection(CURSOR_STR);
}
} else {
param.setDirection(getDirectionAsString(arg.direction));
params.add(param);
}
}
if (params.size() > 0) {
metadata.setParameters(params);
}
plsqlStoredFuncs.add(metadata);
} else {
StoredFunctionCall call = (StoredFunctionCall) query.getCall();
NamedStoredFunctionQueryMetadata metadata = new NamedStoredFunctionQueryMetadata();
metadata.setName(query.getName());
metadata.setProcedureName(call.getProcedureName());
List<StoredProcedureParameterMetadata> params = new ArrayList<StoredProcedureParameterMetadata>();
if (storedFuncs == null) {
storedFuncs = new ArrayList<NamedStoredFunctionQueryMetadata>();
}
DatabaseField arg;
StoredProcedureParameterMetadata param;
List<DatabaseField> paramFields = call.getParameters();
List<Integer> types = call.getParameterTypes();
for (int i = 0; i < paramFields.size(); i++) {
arg = paramFields.get(i);
param = new StoredProcedureParameterMetadata();
param.setTypeName(arg.getTypeName());
if (arg.getSqlType() != DatabaseField.NULL_SQL_TYPE) {
param.setJdbcType(arg.getSqlType());
}
if (arg.isObjectRelationalDatabaseField()) {
param.setJdbcTypeName(((ObjectRelationalDatabaseField) arg).getSqlTypeName());
}
if (i == 0) {
// first arg is the return arg
metadata.setReturnParameter(param);
// handle CURSOR types - want name/value pairs returned
if (types.get(i) == 8) {
addQueryHint(metadata);
}
} else {
param.setName(arg.getName());
param.setMode(getParameterModeAsString(types.get(i)));
params.add(param);
}
}
if (params.size() > 0) {
metadata.setParameters(params);
}
storedFuncs.add(metadata);
}
} else if (query.getCall().isStoredProcedureCall()) {
if (query.getCall() instanceof PLSQLStoredProcedureCall) {
PLSQLStoredProcedureCall call = (PLSQLStoredProcedureCall) query.getCall();
if (plsqlStoredProcs == null) {
plsqlStoredProcs = new ArrayList<NamedPLSQLStoredProcedureQueryMetadata>();
}
NamedPLSQLStoredProcedureQueryMetadata metadata = new NamedPLSQLStoredProcedureQueryMetadata();
metadata.setName(query.getName());
metadata.setProcedureName(call.getProcedureName());
PLSQLParameterMetadata param;
List<PLSQLParameterMetadata> params = new ArrayList<PLSQLParameterMetadata>();
List<PLSQLargument> types = call.getArguments();
for (PLSQLargument arg : types) {
param = new PLSQLParameterMetadata();
param.setName(arg.name);
String dbType = processTypeName(arg.databaseType.getTypeName());
if (arg.cursorOutput) {
param.setDirection(CURSOR_STR);
} else {
param.setDirection(getDirectionAsString(arg.direction));
}
if (arg.databaseType == XMLType) {
param.setDatabaseType(XMLType.name());
} else if (arg.databaseType == PLSQLBoolean) {
param.setDatabaseType(PLSQLBoolean.name());
} else {
param.setDatabaseType(dbType);
}
params.add(param);
}
if (params.size() > 0) {
metadata.setParameters(params);
}
plsqlStoredProcs.add(metadata);
} else {
StoredProcedureCall call = (StoredProcedureCall) query.getCall();
NamedStoredProcedureQueryMetadata metadata = new NamedStoredProcedureQueryMetadata();
metadata.setName(query.getName());
metadata.setProcedureName(call.getProcedureName());
metadata.setReturnsResultSet(false);
List<StoredProcedureParameterMetadata> params = new ArrayList<StoredProcedureParameterMetadata>();
DatabaseField arg;
StoredProcedureParameterMetadata param;
List paramFields = call.getParameters();
List<Integer> types = call.getParameterTypes();
for (int i = 0; i < paramFields.size(); i++) {
if (types.get(i) == DatabaseCall.INOUT) {
// for INOUT we get Object[IN, OUT]
arg = (DatabaseField) ((Object[]) paramFields.get(i))[1];
} else {
arg = (DatabaseField) paramFields.get(i);
}
param = new StoredProcedureParameterMetadata();
param.setName(arg.getName());
param.setTypeName(arg.getTypeName());
if (arg.getSqlType() != DatabaseField.NULL_SQL_TYPE) {
param.setJdbcType(arg.getSqlType());
}
if (arg.isObjectRelationalDatabaseField()) {
param.setJdbcTypeName(((ObjectRelationalDatabaseField) arg).getSqlTypeName());
}
param.setMode(getParameterModeAsString(types.get(i)));
// handle CURSOR types - want name/value pairs returned
if (types.get(i) == 8) {
addQueryHint(metadata);
}
params.add(param);
}
if (params.size() > 0) {
metadata.setParameters(params);
}
if (storedProcs == null) {
storedProcs = new ArrayList<NamedStoredProcedureQueryMetadata>();
}
storedProcs.add(metadata);
}
} else {
// named native query
NamedNativeQueryMetadata namedQuery = new NamedNativeQueryMetadata();
namedQuery.setName(query.getName());
namedQuery.setQuery(query.getSQLString());
namedQuery.setResultClassName(query.getReferenceClassName());
if (namedNativeQueries == null) {
namedNativeQueries = new ArrayList<NamedNativeQueryMetadata>();
}
namedNativeQueries.add(namedQuery);
}
}
// add generated metadata
if (plsqlStoredProcs != null) {
xmlEntityMappings.setNamedPLSQLStoredProcedureQueries(plsqlStoredProcs);
}
if (plsqlStoredFuncs != null) {
xmlEntityMappings.setNamedPLSQLStoredFunctionQueries(plsqlStoredFuncs);
}
if (storedProcs != null) {
xmlEntityMappings.setNamedStoredProcedureQueries(storedProcs);
}
if (storedFuncs != null) {
xmlEntityMappings.setNamedStoredFunctionQueries(storedFuncs);
}
if (namedNativeQueries != null) {
xmlEntityMappings.setNamedNativeQueries(namedNativeQueries);
}
// generate a ClassAccessor for each Descriptor, keeping track of Embeddables
List<String> embeddables = new ArrayList<String>();
Map<String, ClassAccessor> accessors = new HashMap<String, ClassAccessor>();
for (ClassDescriptor cdesc : descriptors) {
boolean embeddable = false;
ClassAccessor classAccessor;
if (cdesc.isAggregateDescriptor()) {
embeddable = true;
classAccessor = new EmbeddableAccessor();
embeddables.add(cdesc.getJavaClassName());
} else {
classAccessor = new EntityAccessor();
}
classAccessor.setClassName(cdesc.getJavaClassName());
classAccessor.setAccess(EL_ACCESS_VIRTUAL);
// may need add STRUCT metadata to the classAccessor to ensure correct field ordering
if (cdesc.isObjectRelationalDataTypeDescriptor()) {
ObjectRelationalDataTypeDescriptor odesc = (ObjectRelationalDataTypeDescriptor) cdesc;
if (odesc.getOrderedFields().size() > 0) {
StructMetadata struct = new StructMetadata();
struct.setName(odesc.getStructureName());
struct.setFields(odesc.getOrderedFields());
classAccessor.setStruct(struct);
}
}
if (!embeddable && cdesc.getTableName() != null) {
TableMetadata table = new TableMetadata();
table.setName(cdesc.getTableName());
((EntityAccessor) classAccessor).setTable(table);
}
if (!embeddable) {
List<NamedNativeQueryMetadata> namedNatQueries = new ArrayList<NamedNativeQueryMetadata>();
NamedNativeQueryMetadata namedQuery;
DatabaseQuery dbQuery;
// process findAll and findByPk queries
for (Iterator<DatabaseQuery> queryIt = cdesc.getQueryManager().getAllQueries().iterator(); queryIt.hasNext(); ) {
dbQuery = queryIt.next();
namedQuery = new NamedNativeQueryMetadata();
namedQuery.setName(dbQuery.getName());
namedQuery.setQuery(dbQuery.getSQLString());
namedQuery.setResultClassName(dbQuery.getReferenceClassName());
namedNatQueries.add(namedQuery);
}
// now create/update/delete operations
Map<String, String> crudOps = crudOperations.get(cdesc.getTableName());
if (!crudOps.isEmpty()) {
for (String opName : crudOps.keySet()) {
String crudSql = crudOps.get(opName);
NamedNativeQueryMetadata crudQuery = new NamedNativeQueryMetadata();
crudQuery.setName(opName);
crudQuery.setQuery(crudSql);
namedNatQueries.add(crudQuery);
}
}
if (namedNatQueries.size() > 0) {
((EntityAccessor) classAccessor).setNamedNativeQueries(namedNatQueries);
}
}
classAccessor.setAttributes(new XMLAttributes());
classAccessor.getAttributes().setIds(new ArrayList<IdAccessor>());
classAccessor.getAttributes().setBasics(new ArrayList<BasicAccessor>());
classAccessor.getAttributes().setArrays(new ArrayList<ArrayAccessor>());
classAccessor.getAttributes().setStructures(new ArrayList<StructureAccessor>());
classAccessor.getAttributes().setEmbeddeds(new ArrayList<EmbeddedAccessor>());
if (embeddable) {
xmlEntityMappings.getEmbeddables().add((EmbeddableAccessor) classAccessor);
} else {
xmlEntityMappings.getEntities().add((EntityAccessor) classAccessor);
}
accessors.put(cdesc.getJavaClassName(), classAccessor);
}
// now the we know what the embeddables are, we can process mappings
for (ClassDescriptor cdesc : descriptors) {
ClassAccessor classAccessor = accessors.get(cdesc.getJavaClassName());
MappingAccessor mapAccessor;
// generate a MappingAccessor for each mapping
for (DatabaseMapping dbMapping : cdesc.getMappings()) {
mapAccessor = generateMappingAccessor(dbMapping, embeddables);
if (mapAccessor == null) {
continue;
}
if (mapAccessor.isId()) {
classAccessor.getAttributes().getIds().add((IdAccessor) mapAccessor);
} else if (mapAccessor.isBasic()) {
classAccessor.getAttributes().getBasics().add((BasicAccessor) mapAccessor);
} else if (mapAccessor instanceof ArrayAccessor) {
classAccessor.getAttributes().getArrays().add((ArrayAccessor) mapAccessor);
} else if (mapAccessor instanceof StructureAccessor) {
classAccessor.getAttributes().getStructures().add((StructureAccessor) mapAccessor);
} else {
// assumes EmbeddedAccessor
classAccessor.getAttributes().getEmbeddeds().add((EmbeddedAccessor) mapAccessor);
}
}
}
return xmlEntityMappings;
}
use of org.eclipse.persistence.queries.StoredProcedureCall in project eclipselink by eclipse-ee4j.
the class StoredProcedureQueryImpl method registerStoredProcedureParameter.
/**
* Register a named parameter. When using parameter names, all parameters
* must be registered in the order in which they occur in the parameter list
* of the stored procedure.
*
* @param parameterName name of the parameter as registered or
* specified in metadata
* @param type type of the parameter
* @param mode parameter mode
* @return the same query instance
*/
@Override
@SuppressWarnings({ "rawtypes" })
public StoredProcedureQuery registerStoredProcedureParameter(String parameterName, Class type, ParameterMode mode) {
entityManager.verifyOpenWithSetRollbackOnly();
StoredProcedureCall call = (StoredProcedureCall) getDatabaseQuery().getCall();
if (mode.equals(ParameterMode.IN)) {
call.addNamedArgument(parameterName, parameterName, type);
} else if (mode.equals(ParameterMode.OUT)) {
call.addNamedOutputArgument(parameterName, parameterName, type);
} else if (mode.equals(ParameterMode.INOUT)) {
call.addNamedInOutputArgument(parameterName, parameterName, parameterName, type);
} else if (mode.equals(ParameterMode.REF_CURSOR)) {
call.useNamedCursorOutputAsResultSet(parameterName);
}
// Force a re-calculate of the parameters.
this.parameters = null;
return this;
}
Aggregations