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