ART#212 - How to query ATG repository from Java Code using Repository API?



Now this is some really important stuff. We now understand RQLs, and we know how to read/manipulate/create data in ATG repositories from dyn/admin.
Now, its time to understand, how you can achieve the same thing using JAVA code.

There are quite a few ways by which this can be achieved, but before we start on that, we must know the basic repository API. 

Now, there must be a component, in which we'd need to do this task. In this component, you need to inject your required repository component. 
Let us see the process of injecting. Repository can be injected just like any other component. Below process describes this.

In your component's java class, create a variable of type Repository, and create its setter-getters. Repository interface is used when you just need to read the data. You have to use MutableRepository instance, if you need to create/update/delete the data.

Next, we inject the repository component in our properties file.
This step is same for both; Repository and MutableRepository interfaces.

We will know about the API on-the-go in this article, we will first see what a RepositoryView is.

We know that a Repository has many item-descriptors. A RepositoryView represents an item-descriptor on which we query.
The syntax for RepositoryView goes like this:-

Just use the getView() method of Repository interface and pass the item-descriptor name. This method throws RepositoryException, which you can handle or throw on.

We can operate on the data in two ways.

1. RQL statements [reading the data]
In ART#210, we learnt the process to create RQL queries. We can use the same queries in our java code using Repository API to fetch data.
Let us see how.
a. We create a RQLStatement Object.
RqlStatement statement = RqlStatement.parseRqlStatement("middleName is ?0");

b. Next, we pass parameters, if required. In above statement, "?0" stands for a parameter. For multiple parameters, we can replace the value-expressions with "?" followed by parameter index. Here we are passing only one parameter, so the index is zero (0).

c. Create an array of parameters. If your array has three values, they will correspond to ?0, ?1 and ?2 in the RQL respectively.
Object params[] = new Object[1];
params[0] = new String("anderson");
In above code snippet, we create an array of parameters. The index-0 of the array contains a String with value "anderson".

d. Next, we pass this array of parameters into the statement and execute the query.
ATG itself does the task of mapping the parameters in array to the RQL statement. When this query is executed, an array of "RepositoryItem" is returned. This will be empty if no matching RepositoryItem is found.
RepositoryItem [] items = statement.executeQuery (view, params);

Finally, our query is executed and all the repositoryItems of "user" item-descriptor with middleName "anderson" are returned.

See below screenshot for more details:-

2. QueryBuilder [reading the data]
This process does not involve RQLs, but still, it is much easier to use. I always choose QueryBuilder over executing RQL statements, as it is easier to figure out what went wrong in your code.

a. Fetch the RepositoryView
b. Fetch QueryBuilder from RepositoryView
c. Create a QueryExpression, with property-name using method "createPropertyQueryExpression".
d. Create a QueryExpression, with with property-value using method "createConstantQueryExpression".
e. Create a query (a comparison query), path both the QueryExpression objects,  and pass the comparison parameter. In our case, it is EQUALS.
f. Next, execute the query created above and fetch the repository items.
g. Iterate through repository items and print the "firstName" of each item.

See below screenshot for more details.

You can user the getPropertyValue() method on any RepositoryItem to fetch the property value by passing the property-name of that item.
Also, you can fetch the repository-id of the RepositoryItem by using: item.getRepositoryId() which return the repository-id in String format.

3. Create/Update the data
Data update can be done easily using the getItemForUpdate() method of class "MutableRepository", followed by the updateItem() method of "MutableRepository".

All we have to do is, instead of a normal repository, we put our repository instance in "MutableRepository" class.
All your properties files remain same as before.

See below screenshot for more details.

1. Inject the repository into a MutableRepository type of variable, and create respective setters and getters.

2.  For this step, you require the repository-id of the item you are updating.
If you do not have the repository-id of the item you want to update, you can use QueryBuilder to fetch the repository-item and use getRepositoryId() method on that repository-item to fetch the repository-id and then go ahead with the steps mentioned here.

A. FOR UPDATE ITEMUse the method getItemForUpdate() and pass the repository-id and item-descriptor name to the method. This will return an item of type MutableRepositoryItem

B. FOR CREATE ITEMIn cases, when you want to create the item, you need to replace the method getItemForUpdate() with the method createItem()
There are two overloaded methods of create item, both of which return MutableRepositoryItem:-

  • MutableRepositoryItem createItem(String pItemDescriptorName): In this method you only pass the item-descriptor name, and the repository creates an item. The repository-id for this item is generated by ATG repository automatically.

  • MutableRepositoryItem createItem(String pId, String pItemDescriptorName): In this method, you pass the repository-id of YOUR CHOICE and the item-descriptor name. If you do not want ATG to generate a repository-id for this item, this method comes in handy so that you can provide your very own repository-id for the item you are creating.

3. Store the item-returned in above step in a MutableRepositoryItem type of variable. Here we have used the variable "profileItem" of type MutableRepositoryItem.

4. Now, use the setPropertyValue() method on the profileItem and pass the property-name and the new property-value to change the property.

5. Now, use the method updateItem() method of MutableRepository and pass the profileItem to persist the changes.
In case of adding an item, use addItem() method of MutableRepository and pass profileItem to persist the changes.

NOTE: Please note that when you are changing multiple properties of an item OR you are creating a new item and setting properties, ALWAYS enclose them in a transaction to avoid data inconsistency and partial updates.
We will study why we require transactions in these cases and how we can use them very soon.

4. Delete an Item
Deleting an item is an easier process than above methods, but it also requires a repository-id. This is a one-step process. Use the removeItem() method of MutableRepository class.
See below screenshot for details.

NOTE: Please note that you can also inject your repository as a normal Repository interface and you can typecast it into MutableRepository when needed.




  1. Very well articulated. Keep it going..

  2. Could you please tell which one is better.. RQL or QueryBuilder? Any performance impact?

    1. There is not much performance-impact.

      I prefer QueryBuilder as it is more clean.
      There is a chance that you might write a wrong RQLQuery [being a String], but using QueryBuilder, you'll get compilation errors in most of the cases.

  3. The query example is a little confusing... You're creating QueryExpression for properties named "middleName" & "anderson" meaning your code would return every item in the repository where the "middleName" property matches the "anderson" property (ie: returning rows where the "middleName" column matches the "anderson" column)

    What would be more likely is to create a QueryExpression for a property *value* of "anderson" in which case you'd want to use the createConstantQueryExpression() method instead.

    1. Yes. That is correct. It should be "createConstantQuryExpression".
      That is updated now.

  4. Hi Monis, Can please update a complete sample ATG application. thanks for your great support

    1. Hi Krishna,

      We will be starting with commerce tutorials very very soon.
      That would pretty much cover the main aspects of a complete sample ATG application.
      Thanks for following.


  5. Nice Blog Over All. Keep it going. :)

  6. Nice conceptual article Munaf.

  7. Using QueryBuilder is it possible to add item into database?

    1. No. QueryBuilder is used for retrieval of data only.

    2. Hi Monis, can we query to select all items in a particular block like for example, we have products in one category, so i wanna get all the products in that category. so Is that possible?

    3. Yes. You can query a category to fetch its child products, which will be a List of RepositoryItem, containing all childproducts of that category.

  8. yeah i got it monis! thank you so much

  9. other one...

    creating a query (using QueryBuilder/Builder) to get the products which are matched with provided ProductId List. ProductId List contains more than 2100 count.
    We are using SqlServer2012. When we use customQuery= prodRepositoryView.getQueryBuilder().createIdMatchingQuery(productIDs); or prodRepositoryView.getQueryBuilder().createIncludesQuery(queryexpression, queryexpression1), we are getting below exception.
    SOURCE:java.sql.SQLException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

    We need a query which should returns 'Query'

    Could you please suggest us to create query with above 2100 IN parameters.



Get All The Latest Updates Delivered Straight Into Your Inbox For Free!