Running CMIS Queries via Alfresco Java SDK

[Mark_Davidson]

Hi there

I've got several web scripts that take some parameters, perform a search & return the results as JSON. The Java code looks like this at a high level:

1. Construct AFTS query from params

2. Perform search using SearchService to return a collection of node refs

3. Iterate search results, calling NodeService#getProperties for each node ref, and build up the response data.

This sppears to be very inefficient as you need to make multiple calls to the search engine. I was thinking that surely there's got to be a better way. i.e. can we retrieve the metadata in a single query to the DB? e.g. SELECT col1, col2, col3 FROM table1 WHERE...

I then remembered that Alfresco supports CMIS. So it should be possible to run CMIS SQL queries? I ran some queries in Alfresco Admin's Node Browser and while they worked, it didn't render the individual columns. However I've read that this behaviour is possible using Chemistry's workbench. Is it possible use the SearchService to return a ResultSet that contains metadata/columns rather than node refs. If so, could you point me towards a code example as I've not been able to find one.

Cheers

Mark

[cesarista]

Hi Mark:

In my experience CMIS is slower endpoint than REST, and as a standard it does not provide the full functionality that Alfresco provides with the REST API.  Although you interact with Alfresco CMIS Server endpoint, the typical actions are going to require a lot of CMIS connections.

CMIS SQL has a lot of limitations , it is not standard SQL and some many queries are NOT possible. But you can check syntax and example using CMIS Workbench with Alfresco.

Regarding to the question, you can query with CMIS SQL, get a resultset with properties.

https://www.zylk.net/en/web-2-0/blog/-/blogs/how-to-check-alfresco-cmis-queries

Kind regards.

--C.

[sherwinf]

We use CMIS queries for some of what we do, both via straight HTTP calls and using the SearchService.

Here is a sample of some code we use with the SearchService:

String query = String.format("SELECT * FROM cmis:folder WHERE cmis:name = '%s'", folderName);
ResultSet results = searchService.query(
    StoreRef.STORE_REF_WORKSPACE_SPACESSTORE, SearchService.LANGUAGE_CMIS_ALFRESCO, query
);

for (ResultSetRow result : results) {
    Map<String, Serializable> values = result.getValues();
    ....
}

If you haven't already I would download the CMIS Workbench and confirm you can build a query that provides what you want.