Querying ATG Repositories

[] allUsers = userView.executeQuery(allUsersQuery);

Steps:

1. Get a handle on the repository.

2. Get the item descriptor you’d like to query using getItemDescriptor().

3. Get a view on the item.

4. Instanciate the QueryBuilder.

Steps 1-4 are required for any QueryBuilder query.  The creation of the Query object(s) is/are the bread and butter of repository querying.  Take a look at the QueryBuilder API specification (Interface Query Builder documentation) to get an idea of what it can do.  Here, we call its createUnconstrainedQuery method without any parameters, which returns a Query object.

5. Pass the Query object to the item view’s executeQuery method, which will always return an array of RepositoryItem.

Please note that createUnconstrainedQuery is DANGEROUS and should not be used unless in a special circumstance.  A repository can have millions of items, which will tank an application’s performance if the query runs.

2. Finding all users in the ProfileAdapterRepository that have an email equal to “[email protected]”.

Stepping it up a bit, let’s add a constraint.  Pretend we’d like to find all users in the ProfileAdapterRepository that have an email equal to “[email protected]”.  Like I said earlier, the process of getting to the QueryBuilder will be the same for all repository queries, so we’ll leave that code out going forward.

// get handle on QueryBuilder

QueryExpression userEmail = userQueryBuilder.createPropertyQueryExpression(“email”);

QueryExpression requestedEmail = userQueryBuilder.createConstantQueryExpression(“[email protected]”);

Query matchingEmails = userQueryBuilder.createComparisonQuery(userEmail, requestedEmail,QueryBuilder.EQUALS);

RepositoryItem[] queryResult = userView.executeQuery(matchingEmails);

In this example, we introduce QueryExpression, which can be thought of as a sub-component of a Query.  Multiple QueryExpression objects can be ‘connected’ to create a final Query.  We create two expressions, the first one getting a handle on the ‘email’ property of the ‘user’ item, and the second passes in the constant value that we’re looking for.  We then use the QueryBuilder’s createComparisonQuery to setup the relationship that we’re looking for.

At this point, almost all parts of the API have been exposed.  The only difficult parts come with more advanced data types, and stringing multiple queries together.  In the next example, let’s look at adding a time element to the query.  Pretend we have to find all users that have a registration date within the last day.  To do this:

3. Finding all users that have a registration date within the last day.

// get handle on QueryBuilder

CurrentDate date =(CurrentDate) resolveName(“/atg/dynamo/service/CurrentDate”);

Calendar calendar =newGregorianCalendar();

calendar.setTime(date.getDateAsDate());

calendar.add(Calendar.DAY_OF_YEAR,-1);

Timestamp oneDayEarlier =newTimestamp(calendar.getTimeInMillis()); 

QueryExpression registrationDate = userQueryBuilder.createPropertyQueryExpression(“registrationDate”);

QueryExpression registrationMaxDate = userQueryBuilder.createConstantQueryExpression(oneDayEarlier);

Query dateQuery = userQueryBuilder.createComparisonQuery(registrationDate, registrationMaxDate,QueryBuilder.GREATER_THAN);

RepositoryItem[] results = userView.executeQuery(dateQuery);

The only different part in this example is that we have to do some haggling with the date to get it within the right bounds.  Also note that when creating the expression, we use QueryBuilder.GREATER_THAN to indicate a registration date greater than the date one day ago.  There are many comparison constants available for use from the QueryBuilder class such as STARTS_WITH and CONTAINS.  Sometimes when building date queries, it helps to think of them in unix time.

Note: CurrentDate is the ATG best practice for getting the current date.

4.  Finding all users with a null billing address assigned to their profile.

Another tricky thing when querying is to find items with properties that are null or not. Pretend we have to find all users with a null billing address assigned to their profile.

To do this:

// get handle on QueryBuilder

QueryExpression billingAddress = userQueryBuilder.createPropertyQueryExpression(“billingAddress”);

Query nullBillingAddress = userQueryBuilder.createIsNullQuery(billingAddress);

RepositoryItem[] queryResult = userView.executeQuery(nullBillingAddress);

We simply use the QueryBuilder’s createIsNullQuery method to take care of the null constraint.  Note, you can also use createNotQuery to flip any query.  We could create a query to find users that do not have a null billing address simply by adding:

Query notNullBilling = userQueryBuilder.createNotQuery(nullBillingAddress);

The queries so far have been relatively simple.  Inevitably, you’re going to have to add multiple constraitns into queries to get the desired data.  This is very easy, and builds on what we’ve already learned.

5. Querying for all users with a first name of “Bill” or “Jeff”.

Pretend for a second we want to query for all users with a first name of “Bill” or “Jeff”.

This can be done using:

// get handle on QueryBuilder

QueryExpression firstName = userQueryBuilder.createPropertyQueryExpression(“firstName”);

QueryExpression firstNameBill = userQueryBuilder.createConstantQueryExpression(“Bill”);

Query firstNameBillQuery = userQueryBuilder.createComparisonQuery(firstName, firstNameBill,QueryBuilder.EQUALS);

QueryExpression firstNameJeff = userQueryBuilder.createConstantQueryExpression(“Jeff”);

Query firstNameJeffQuery = userQueryBuilder.createComparisonQuery(firstName, firstNameJeff,QueryBuilder.EQUALS);

Query[] queryPieces ={firstNameBillQuery, firstNameJeffQuery};

Query firstNameQuery = userQueryBuilder.createOrQuery(queryPieces);

RepositoryItem[] results = userView.executeQuery(firstNameQuery);

(This is why I said earlier the QueryBuilder API can be cumbersome)

All that’s new here is that we’re creating two queries (firstNameBillQuery and firstNameJeffQuery) and adding them to an array of Query (queryPieces).  Then, using the array of Query, we build the final Query that’s sent to executeQuery.  I used QueryBuilder’s createOrQuery method, but there are many other methods that take an array of queries and return a single query.

At this point, you should now have the knowledge to create any query.  In the above example, we combined two queries into one, however it’s entirely possible that you might be creating a handful of Query arrays, then criss-crossing them with eachother before you get the final Query object.  The QueryBuilder API is a bit bulky, but anything is possible.

Queries Using Repository Query Language

Repository Query Language (RQL), on the other hand, is much more staight-forward than the QueryBuilder API for constructing queries.  It provides a simple SQL-like syntax that allows developers to quickly construct complex queries.

1. Finding all users named Bill or Jeff.

Repository profileRepository =(Repository) resolveName(“/atg/userprofiling/ProfileAdapterRepository”);

RepositoryItemDescriptor userItem = profileRepository.getItemDescriptor(“user”);

RepositoryView userView = userItem.getRepositoryView();

RqlStatement emailQuery =RqlStatement.parseRqlStatement(“firstName EQUALS ?0 OR firstName EQUALS ?1”);

Object[]params=newString[]{“Bill”,”Jeff”};

RepositoryItem[] results = emailQuery.executeQuery(userView,params);

It starts the same as the QueryBuilder by getting a handle on a “view” to the repository item.  The main difference is that we use RqlStatement.parseRqlStatement to pass in the query as a String.  Also note that we can use ?[0-indexed integer] to specify parameters in the query.  The query ends with calling the RqlStatement’s executeQuery method, passing in any required parameters.

As you can probably see, RQL is much more simple than the QueryBuilder API.  In addition, there are out-of-the-box droplets that can do RQL too (see Database and Repository Access Servlet Beans). I wouldn’t recommend querying the repository from a jsp since it feels like a design principle breakdown, but the option is there.

I didn’t give many RQL examples simply because they aren’t nearly as complicated as the QueryBuilder.  Read up on the RQL section in the ATG Repository Guide for info on building more complicated queries.

Best Practices

1. Before writing a query for some item, check to see if the object you’re querying is available without going to the database. 

(In any application, and especially in ATG applications, going to the database is an expensive operation.)

For example in a droplet or form handler, instead of looking up the current user’s profile or order using a repository query, pass it in.  Likewise, in a droplet or form handler connected to a product detail page, pass in the necessary info (like product, SKU and order) rather than making a query for it based on something like a product id.

2. Before writing a repository query to a standard out-of-the-box repository such as the ProfileAdapterRepository or the OrderRepository, it’s worth digging around to see if what you’re trying to do hasn’t already been written.

Here are some worthwhile classes to consider:

RepositoryProfileItemFinder – Profile item queries

OrderQueries – Order info queries

3. Many standard form handlers, *tools and *manager classes also have some degree of item querying built in.

Unfortunately for repository customizations, you’re on your own for queries.

Conclusion

Repository queries are a skill all ATG developers need to know well.  While the learning curve can be large, it really is worth learning one method inside and out.  I’d recommend learning either the QueryBuilder API or RQL, and having a working knowledge of the other method.  In the wild, you’ll see a mix of each method.  Despite the bulkiness of the QueryBuilder API, I prefer it because it has a kind of ‘wholeness’ that RQL doesn’t have.  At the end of the day, it’s a purely personal decision.
In addition to just knowing the API, it’s important to be contextually aware of the queries being performed.  Repository queries can be the main source of a bottleneck of an ATG application, so it is crucial to only write queries when what you’re looking for isn’t immediately available.