The Basics – Specialized Query Methods

Following the Running a DQL Query post, this post looks at some simple ways to specialize DQL queries based upon their type (e.g., select, set, create, delete, insert, drop, cached, and update) and expected return values, and the benefits such specialization provides.  The Basics library of Documentum methods containing the specialized query methods as well as all other methods discussed in this series, can be found here.

The Basics library contains 10 query methods.  The method signatures follow:

1. public static IDfCollection runSelectQuery(String query, IDfSession session) throws DfException
2. public static dmRecordSet runSelectQueryAsRecordSet(String query, IDfSession session) throws Exception
3. public static int runUpdateObjectQuery(String query, IDfSession session) throws DfException
4. public static IDfCollection runCachedQuery(String query, IDfSession session) throws DfException
5. public static String runCreateObjectQuery(String query, IDfSession session) throws DfException
6. public static int runDeleteObjectQuery(String query, IDfSession session) throws DfException
7. public static IDfCollection runExecQuery(String query, IDfSession session) throws DfException
8. public static int runInsertQuery(String query, IDfSession session) throws DfException
9. public static int runDeleteQuery(String query, IDfSession session) throws DfException
10. public static int runUpdateQuery(String query, IDfSession session) throws DfException

I will leave the exploration of the code to you, and instead discuss a few of the benefits of these specialized query methods.

1. Using specialized query methods makes your primary code much simpler to create, read, and maintain.  You don’t have to clutter the code with IDfQuery and IDfCollection objects.
2. These methods provide simpler handling of return values.  For example, if you are only interested in a single result value (e.g., number objects updated), you don’t have to clutter your code with loops, etc. to retrieve that value.
3. Using specialized query methods, you don’t have to worry about using the correct or most efficient query constants.
4. These methods provide rudimentary syntax checking, which could be improved/extended for your purposes.  This can help alleviate receiving cryptic error messages from the query engine.
5. Because all of these methods eventually call a single “uber” query method, error checking/trapping is centralized.

Here are some quick examples:

				// SELECT
				query = "select r_object_id, object_name from dm_document where folder('/Temp')";
				System.out.println("running SELECT query: " + query);
				col = DCTMBasics.runSelectQuery(query,session);
				// do something with IDfCollection
				col.close();

				// CACHED
				query = "select r_object_id, object_name from dm_document where folder('/Temp',descend)";
				System.out.println("running CACHED query: " + query);
				long start = System.currentTimeMillis();
				col = DCTMBasics.runCachedQuery(query,session);
				long stop = System.currentTimeMillis();
				long dif = stop - start;
				// do something with IDfCollection
				col.close();
				System.out.println("duration=" + dif);

				System.out.println("[running second query]");
				start = System.currentTimeMillis();
				query = "select r_object_id, object_name from dm_document where folder('/Temp',descend)";
				col = DCTMBasics.runCachedQuery(query,session);
				stop = System.currentTimeMillis();
				dif = stop - start;
				// do something with IDfCollection
				col.close();
				System.out.println("duration=" + dif);

				// OBJ CREATE
				query = "create dm_document object set object_name = 'DCTMBasics Test Object' link '/Temp'";
				System.out.println("running OBJ CREATE query: " + query);
				String objId = DCTMBasics.runCreateObjectQuery(query,session);
				System.out.println("created " + objId);

				// EXEC
				query = "execute db_stats";
				System.out.println("running EXEC query: " + query);
				col = DCTMBasics.runExecQuery(query,session);
				// do something with IDfCollection
				col.close();

				// OBJ UPDATE
				System.out.println("running OBJ UPDATE query");
				query = "update dm_document object set object_name = 'DCTMBasics updated object name' where r_object_id = '" + objId + "'";
				int cnt = DCTMBasics.runUpdateObjectQuery(query,session);
				System.out.println("update " + cnt + " objs");

				// OBJ DELETE
				System.out.println("running OBJ DELETE query");
				query = "delete dm_document object where r_object_id = '" + objId + "'";
				cnt = DCTMBasics.runDeleteObjectQuery(query,session);
				System.out.println("deleted " + cnt + " objs");

The Basics – Running a Query

Next to logging on/off, executing DQL queries is one of the most frequent tasks developed in a Documentum application.  There are three primary classes used to query the repository and process the results. These classes are:

• IDfQuery – The query class encapsulates all of the data and functionality necessary to run a DQL query against a repository.
• IDfCollection – The collection class encapsulates the results of a query in a read-once, forward-only object.  A better alternative is to use the dmRecordSet class.
• IDfTypedObject – A typed object is a non-persistent object used to model row data in the IDfCollection object.

There are potentially more classes involved depending upon circumstances, for example whether you choose to use a query builder class (IDfQueryBuilder) to construct your query, or the dmRecordSet to hold query results.

The basic structure of the query code looks like this:

public IDfCollection runQuery(String query, IDfSession session) {
  IDfCollection col = null;

  try {
    // create query object
    IDfQuery q = new DfQuery();

    // set query string
    q.setDQL(query);

    // execute query
    col = q.execute(session, DfQuery.DF_READ_QUERY);

  } catch (DfException e) {
    e.printStackTrace();
  }
  return col;
}

Note the use of the DfQuery.DF_READ_QUERY constant. The use of the proper query type constant can affect your query performance. See here for more on query type constants.

The basic structure of the code used to process the results returned in the IDfCollection object is:

// do query
IDfCollection col = runQuery("select r_object_id, object_name from dm_document where folder('/Templates')", session);

// process results
while (col.next()) {

 // get each row
 IDfTypedObject tObj = col.getTypedObject();

 // get value in each column and do something with results
 String id = tObj.getString("r_object_id");
 String name =  tObj.getString("object_name");
 System.out.println(id + "\t" + name);
}

// it is very important to close each collection after you process it
if ( (col != null) && (col.getState() != IDfCollection.DF_CLOSED_STATE) )
 col.close();

In this example, the IDfTypedObject represents a row in the IDfCollection and its getter methods are used to retrieve each column’s data.  Note that an IDfCollection object can only be read once, and only in the forward direction.  For a more robust and capable query result object, use the dmRecordSet class.

UPDATE:  I have created a DCTMBasics JAR file that contains most of the code discussed in this series.

dmRecordSet Update v1.2

I just published an update to the dmRecordSet class, a more useable and flexible alternative to the DFC’s IDfCollection class.  Here is a quick list of improvements/changes in this release:

• added getRecordSetAsList() method – returns the record set as a Java List
• added getRecordSetAsSet() method – returns the record set as a Java Set (contains only unique objects)
• added getRecordSetInfo() method – returns a String of stats about record set
• added getRowCount() – returns number of rows in record set
• added getColumnCount() – returns number of columns in record set
• added static getVersion() – returns version information for class
• changed getRow() to issue DfLogger statement if invalid row requested, instead of throwing exception
• Deprecated first(), replaced with getFirstRow()
• Deprecated last(), replaced with getLastRow()
• Deprecated next(), replaced with getNextRow()
• Deprecated getRow(), replaced with getCurrentRow()
• Deprecated previous(), replaced with getPreviousRow()
• Deprecated resetBeginning(), replaced with resetToBeginning()
• Deprecated resetEnd(), replaced with resetToEnd()
• getNextRow() and getPreviousRow() use getRow()
• updated CollectionExamples test class

I also included a short tutorial that walks through some basic uses of the class.

The latest dmRecordSet class can be downloaded here.