// Aim: You want to perform simple query and retrieve the query results
// from the back end.
The Workflow
// First, get a connection.
// Then create a query object.
IQuery query = con.newQuery();
// You can set query timeout and chunk size.
// - Timeout - The amount of time a query may run before an
// exception is thrown.
// - Chunk size - Get the returning results in small chunks of N
// number of records each.
//// Generating the Query
// You can either pass the query as a String or using a series of API
// calls.Bare in mind that only the connector framework SQL format is
// supported.
// The format is as follows:
// SELECT <Attribute 1 unique name>,…,<Attribute N unique name>
// FROM <Object I> [inner join <Object II> on <join condition I,II>
// … inner join <Object M> on <join condition I,M>]
// WHERE <criteria condition using selected objects>
// Example:
// SELECT [dbo].[orders].[price], [dbo].[products].[productname]
// FROM [dbo].[orders] inner join [dbo].[products] on
// [dbo].[products].[productid] = [dbo].[orders].[productid]
// WHERE [dbo].[products].[productid] = 5
// Aliases for objects are permitted.
// Running the query passing a string:
boolean success = query.execute(MySqlString);
// Run the query calling APIs: Lets create the above example.
List ordersAttributes = new LinkedList();
OrdersAttributes.add("[dbo].[orders].[price] ");
// We must supply the selected attributes for each object in a list.
// The order of the attributes within the list determines the order in
// which they appear in the result.
query.setSelectedAttributes("[dbo].[orders] ", ordersAttributes);
List productsAttributes = new LinkedList();
productsAttributes.add("[dbo].[products].[productName] ");
query.setSelectedAttributes("[dbo].[products] ", productsAttributes);
// Add the join condition.
query.join(
"[dbo].[orders] ",
"[dbo].[orders].[productid] ",
"[dbo].[products] ",
"[dbo].[products].[productid] ");
// Add criteria condition. (Only the equal operator is supported.)
query.applyCondition(
"[dbo].[products] ",
"[dbo].[products].[productid] ",
new Integer(5));
// Run the query.
boolean success = query.execute();
// Retrieve the returned results (Execution.IRecordSet) using
// nextRecordSet() and retrieveRecordSet() methods.
// Assume we only got back one chunk:
IRecordSet result = query.retrieveResultSet();
// Now query the result using the same method used in function execution.
// From the inside:
// The generic connector contains a set of helper objects for query
// execution.The JDBC connector, for example, uses these object to hold
// assemble it into a string it that it then passes to the database.
// This is the code that sets the selected attributes for a certain
// object in the query.
public void setSelectedAttributes(String objectName, List attributeList)
throws
com.sapportals.connector.execution.objects.ObjectNotFoundException,
com
.sapportals
.connector
.execution
.objects
.AttributeNotFoundException {
try {
List attributeCopy = new ArrayList(attributeList);
// Query for the table columns.
ResultSet tableColumnsRS =
m_connection.getMetaData().getColumns(
null,
null,
objectName,
null);
// Check if the requested table exists.
if (!tableColumnsRS.next())
throw new ObjectNotFoundException(
"Object Not Found:" + objectName);
// Add only the actual table columns to the query.
do {
String attributeName = tableColumnsRS.getString("COLUMN_NAME");
if (attributeCopy.contains(attributeName))
attributeCopy.remove(attributeName);
} while (tableColumnsRS.next());
if (attributeCopy.size() > 0) {
String attributes = "|";
ListIterator iter = attributeCopy.listIterator();
while (iter.hasNext())
attributes += (String) iter.next() + "|";
throw new AttributeNotFoundException(
"Attributes Not Found:" + attributes);
}
// Add the table to the query, if it is not yet part of it.
// The query holder object is just a container for the boxes for tables,
// columns, and conditions.
try {
m_queryHolder.addObject(objectName);
} catch (ObjectAlreadyExistsException e) {
}
QueryObjectHolder queryObjectHolder =
m_queryHolder.getObject(objectName);
// Add the list of attributes to table holder object.
queryObjectHolder.setSelectAttributes(attributeList);
} catch (SQLException se) {
throw new java.lang.RuntimeException(
"SQL Exception in setObjectAttributes:" + se.getMessage());
}
}
// Here is an example of how to implement the join() method.
public void join(
String sourceObject,
String sourceAttribute,
String targetObject,
String targetAttribute)
throws
com.sapportals.connector.execution.objects.ObjectNotFoundException,
com
.sapportals
.connector
.execution
.objects
.AttributeNotFoundException {
try {
// Check if the attributes exist.
ResultSet firstTableColumnsRS =
m_connection.getMetaData().getColumns(
null,
null,
sourceObject,
null);
if (!firstTableColumnsRS.next())
throw new ObjectNotFoundException(
"Object Not Found:" + sourceObject);
boolean attributeFound = false;
do {
attributeFound =
sourceAttribute.equals(
firstTableColumnsRS.getString("COLUMN_NAME"));
if (attributeFound)
break;
} while (firstTableColumnsRS.next());
firstTableColumnsRS.close();
if (!attributeFound)
throw new AttributeNotFoundException(
"Attribute Not Found:" + sourceAttribute);
ResultSet secondTableColumnsRS =
m_connection.getMetaData().getColumns(
null,
null,
targetObject,
null);
if (!secondTableColumnsRS.next())
throw new ObjectNotFoundException(
"Object Not Found:" + targetObject);
attributeFound = false;
do {
attributeFound =
targetAttribute.equals(
secondTableColumnsRS.getString("COLUMN_NAME"));
if (attributeFound)
break;
} while (secondTableColumnsRS.next());
secondTableColumnsRS.close();
if (!attributeFound)
throw new AttributeNotFoundException(
"Attribute Not Found:" + targetAttribute);
// Add the tables to the query, if necessary.
{
m_queryHolder.addObject(sourceObject);
} catch (ObjectAlreadyExistsException e) {
}
try {
m_queryHolder.addObject(targetObject);
} catch (ObjectAlreadyExistsException e) {
}
// Add the join (relation) holder object.
m_queryHolder.addDefaultRelation(
sourceObject,
sourceAttribute,
targetObject,
targetAttribute,
true);
} catch (SQLException se) {
throw new java.lang.RuntimeException(
"Join invalid, SQL Exception:" + se.getMessage());
}
}
// When it's time to execute the query, all the data from the holder
// objects are assembled into one string using the assemble() method.
public static String assemble(QueryHolder queryHolder)
throws QueryAssemblingException {
String selectClause = "SELECT ";
String fromClause = " FROM ";
String whereClause = " WHERE ";
int totalNumOfSelectAttributes = 0;
int totalNumOfConditions = 0;
int numOfObjects = queryHolder.getObjects().size();
if (numOfObjects == 0)
throw new QueryAssemblingException("No objects in query");
Iterator objIter = queryHolder.getObjects().iterator();
for (int objCounter = 1; objIter.hasNext(); objCounter++) {
String currentObject = (String) objIter.next();
QueryObjectHolder currentObjectHolder =
queryHolder.getObject(currentObject);
if (currentObjectHolder instanceof QueryAliasHolder)
fromClause += currentObject
+ " "
+ ((QueryAliasHolder) currentObjectHolder).getObjectName();
else
fromClause += currentObject;
if (objCounter < numOfObjects)
fromClause += ",";
int numOfSelectAttributes =
currentObjectHolder.getSelectAttributes().size();
if (totalNumOfSelectAttributes > 0)
selectClause += ",";
totalNumOfSelectAttributes += numOfSelectAttributes;
Iterator attributesIter =
currentObjectHolder.getSelectAttributes().iterator();
for (int attributeCounter = 1;
attributesIter.hasNext();
attributeCounter++) {
selectClause += currentObject
+ "."
+ (String) attributesIter.next();
if (attributeCounter < numOfSelectAttributes)
selectClause += ",";
}
int numOfConditions = currentObjectHolder.getConditions().size();
if (totalNumOfConditions > 0)
whereClause += " AND ";
totalNumOfConditions += numOfConditions;
Iterator conditionsIter =
currentObjectHolder.getConditions().iterator();
for (int conditionCounter = 1;
conditionsIter.hasNext();
conditionCounter++) {
QueryConditionHolder currentCondition =
(QueryConditionHolder) conditionsIter.next();
whereClause += currentObject
+ "."
+ currentCondition.getAttribute()
+ "="
+ currentCondition.getValue().toString();
if (conditionCounter < numOfConditions)
whereClause += " AND ";
}
}
if (totalNumOfConditions > 0)
whereClause += " AND ";
int numOfRelations = queryHolder.getRelations().size();
Iterator relationsIter = queryHolder.getRelations().iterator();
for (int relationCounter = 1;
relationsIter.hasNext();
relationCounter++) {
whereClause += (String) relationsIter.next();
if (relationCounter < numOfRelations)
whereClause += " AND ";
}
return selectClause + fromClause + whereClause;
}
// Then the JDBC connector just passes the string to JDBC.
// (The string's format is derived from SQL.)
