Show TOC

Launching a QueryLocate this document in the navigation structure

             //  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.)