SQL

// get QueryManager
QueryManager queryManager = workspace.getQueryManager(); 
// make SQL query
Query query = queryManager.createQuery("SELECT * FROM nt:base ", Query.SQL);
// execute query
QueryResult result = query.execute();

XPath

// get QueryManager
QueryManager queryManager = workspace.getQueryManager(); 
// make XPath query
Query query = queryManager.createQuery("//element(*,nt:base)", Query.XPATH);
// execute query
QueryResult result = query.execute();

// fetch query result
QueryResult result = query.execute();

Now we can get result in an iterator of nodes:

NodeIterator it = result.getNodes();

or we get the result in a table:

// get column names
String[] columnNames = result.getColumnNames();
// get column rows
RowIterator rowIterator = result.getRows();
while(rowIterator.hasNext()){
   // get next row
   Row row = rowIterator.nextRow();
   // get all values of row
   Value[] values = row.getValues();
}

The result returns a score for each row in the result set. The score contains a value that indicates a rating of how well the result node matches the query. A high value means a better matching than a low value. This score can be used for ordering the result.

eXo JCR Scoring is a mapping of Lucene scoring. For a more in-depth understanding, please study Lucene documentation.

jcr:score counted in next way - (lucene score)*1000f.

Score may be increased for specified nodes, see Index Boost Value

Also, see an example Order by Score

Select all nodes with primary type 'nt:unstructured' and returns only 3 nodes starting with the second node in the list.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:unstructured";
QueryImpl query = (QueryImpl)queryManager.createQuery(sqlStatement, Query.SQL);
//return starting with second result
query.setOffset(1);
// return 3 results
query.setLimit(3);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

Find all nodes in the repository. Only those nodes are found to which the session has READ permission. See also Access Control.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:base";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:base)";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes whose primary type is "nt:file".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:file";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:file)";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes in repository, that contain a mixin type "mix:title".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with mixin type 'mix:title' where the prop_pagecount property contains a value less than 90. Only select the title of each node.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT jcr:title FROM mix:title WHERE prop_pagecount < 90";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[@prop_pagecount < 90]/@jcr:title";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with mixin type 'mix:title' and where the property 'jcr:title' starts with 'P'.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE jcr:title LIKE 'P%'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[jcr:like(@jcr:title, 'P%')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with a mixin type 'mix:title' and whose property 'jcr:title' starts with 'P%ri'.

As you see "P%rison break" contains the symbol '%'. This symbol is reserved for LIKE comparisons. So what can we do?

Within the LIKE pattern, literal instances of percent ("%") or underscore ("_") must be escaped. The SQL ESCAPE clause allows the definition of an arbitrary escape character within the context of a single LIKE statement. The following example defines the backslash ' \' as escape character:

SELECT * FROM mytype WHERE a LIKE 'foo\%' ESCAPE '\'

XPath does not have any specification for defining escape symbols, so we must use the default escape character (' \').

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE jcr:title LIKE 'P#%ri%' ESCAPE '#'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[jcr:like(@jcr:title, 'P\\%ri%')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with a mixin type 'mix:title' and where the property 'jcr:title' does NOT start with a 'P' symbol

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE NOT jcr:title LIKE 'P%'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[not(jcr:like(@jcr:title, 'P%'))]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all fairytales with a page count more than 90 pages.

How does it sound in jcr terms - Find all nodes with mixin type 'mix:title' where the property 'jcr:description' equals "fairytale" and whose "prop_pagecount" property value is less than 90.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE jcr:description = 'fairytale' AND prop_pagecount > 90";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[@jcr:description='fairytale' and @prop_pagecount > 90]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all documents whose title is 'Cinderella' or whose description is 'novel'.

How does it sound in jcr terms? - Find all nodes with a mixin type 'mix:title' whose property 'jcr:title' equals "Cinderella" or whose "jcr:description" property value is "novel".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE jcr:title = 'Cinderella' OR jcr:description = 'novel'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[@jcr:title='Cinderella' or @jcr:description = 'novel']";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with a mixin type 'mix:title' where the property 'jcr:description' does not exist (is null).

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE jcr:description IS NULL";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = ""//element(*,mix:title)[not(@jcr:description)]"";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with a mixin type 'mix:title' and where the property 'jcr:title' equals 'casesensitive' in lower or upper case.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE UPPER(jcr:title) = 'CASESENSITIVE'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[fn:upper-case(@jcr:title)='CASESENSITIVE']";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE LOWER(jcr:title) = 'casesensitive'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[fn:lower-case(@jcr:title)='casesensitive']";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes of primary type "nt:resource" whose jcr:lastModified property value is greater than 2006-06-04 and less than 2008-06-04.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
StringBuffer sb = new StringBuffer();
sb.append("select * from nt:resource where ");
sb.append("( jcr:lastModified >= TIMESTAMP '");
sb.append("2006-06-04T15:34:15.917+02:00");
sb.append("' )");
sb.append(" and ");
sb.append("( jcr:lastModified <= TIMESTAMP '");
sb.append("2008-06-04T15:34:15.917+02:00");
sb.append("' )");
String sqlStatement = sb.toString();
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
StringBuffer sb = new StringBuffer();
sb.append("//element(*,nt:resource)");
sb.append("[");
sb.append("@jcr:lastModified >= xs:dateTime('2006-08-19T10:11:38.281+02:00')");
sb.append(" and ");
sb.append("@jcr:lastModified <= xs:dateTime('2008-06-04T15:34:15.917+02:00')");
sb.append("]");
String xpathStatement = sb.toString();
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node foundNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with primary type 'nt:file' whose node name is 'document'. The node name is accessible by a function called "fn:name()".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:file WHERE fn:name() = 'document'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:file)[fn:name() = 'document']";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with the primary type 'nt:unstructured' whose property 'multiprop' contains both values "one" and "two".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:unstructured WHERE multiprop = 'one' AND multiprop = 'two'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:unstructured)[@multiprop = 'one' and @multiprop = 'two']";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find a node with the primary type 'nt:file' that is located on the exact path "/folder1/folder2/document1".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// we want find 'document1'
String sqlStatement = "SELECT * FROM nt:file WHERE jcr:path = '/folder1/folder2/document1'";
// create query
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// we want to find 'document1'
String xpathStatement = "/jcr:root/folder1[1]/folder2[1]/element(document1,nt:file)[1]";
// create query
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with the primary type 'nt:folder' that are children of node by path "/root1/root2". Only find children, do not find further descendants.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:folder WHERE jcr:path LIKE '/folder1/folder2/%' AND NOT jcr:path LIKE '/folder1/folder2/%/%'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "/jcr:root/folder1[1]/folder2[1]/element(*,nt:folder)";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with the primary type 'nt:folder' that are descendants of the node "/folder1/folder2".

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:folder WHERE jcr:path LIKE '/folder1/folder2/%'";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "/jcr:root/folder1[1]/folder2[1]//element(*,nt:folder)";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Select all nodes with the mixin type ''mix:title' and order them by the 'prop_pagecount' property.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title ORDER BY prop_pagecount ASC";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title) order by @prop_pagecount ascending";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find all nodes with the primary type 'nt:unstructured' and sort them by the property value of descendant nodes with the relative path '/a/b'.

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "/jcr:root/* order by a/b/c/@prop descending;
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Select all nodes with the mixin type 'mix:title' containing any word from the set {'brown','fox','jumps'}. Then, sort result by the score in ascending node. This way nodes that match better the query statement are ordered at the last positions in the result list.

SELECT * FROM nt:base ORDER BY jcr:score [ASC|DESC]
SELECT * FROM nt:base ORDER BY jcr:score()[ASC|DESC]

//element(*,nt:base) order by jcr:score() [descending]
//element(*,nt:base) order by @jcr:score [descending]

... order by jcr:score() ascending

... order by jcr:score()

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE CONTAINS(*, 'brown OR fox OR jumps') ORDER BY jcr:score() ASC";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[jcr:contains(., 'brown OR fox OR jumps')] order by jcr:score()";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

There is two ways to order results, when path may be used as criteria:

If no order specification is supplied in the query statement, implementations may support document order on the result nodes (see jsr-170 / 6.6.4.2 Document Order). And it's sorted by order number.

By default, (if query do not contains any ordering statements) result nodes is sorted by document order.

SELECT * FROM nt:unstructured WHERE jcr:path LIKE 'testRoot/%'

Find all nodes containing a mixin type 'mix:title' and whose 'jcr:description' contains "forest" string.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// we want find document which contains "forest" word
String sqlStatement = "SELECT \* FROM mix:title WHERE CONTAINS(jcr:description, 'forest')";
// create query
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// we want find document which contains "forest" word
String xpathStatement = "//element(*,mix:title)[jcr:contains(@jcr:description, 'forest')]";
// create query
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

Find nodes with mixin type 'mix:title' where any property contains 'break' string.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
String sqlStatement = "SELECT * FROM mix:title WHERE CONTAINS(*,'break')";
// create query
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// we want find 'document1'
String xpathStatement = "//element(*,mix:title)[jcr:contains(.,'break')]";
// create query
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

while(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

In this example, we will create new Analyzer, set it in QueryHandler configuration, and make query to check it.

Standard analyzer does not normalize accents like é,è,à. So, a word like 'tréma' will be stored to index as 'tréma'. But if we want to normalize such symbols or not? We want to store 'tréma' word as 'trema'.

There is two ways of setting up new Analyzer (no matter standarts or our):

There is only one way - create new Analyzer (if there is no previously created and accepted for our needs) and set it in Search index.

We will use the last one:

public class MyAnalyzer extends Analyzer
{
   @Override
   public TokenStream tokenStream(String fieldName, Reader reader)
   {
      StandardTokenizer tokenStream = new StandardTokenizer(reader);
      // process all text with standard filter
      // removes 's (as 's in "Peter's") from the end of words and removes dots from acronyms.
      TokenStream result = new StandardFilter(tokenStream);
      // this filter normalizes token text to lower case
      result = new LowerCaseFilter(result);
      // this one replaces accented characters in the ISO Latin 1 character set (ISO-8859-1) by their unaccented equivalents
      result = new ISOLatin1AccentFilter(result);
      // and finally return token stream
      return result;
   }
}

<workspace name="ws">
   ...
   <query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex">
      <properties>
         <property name="analyzer" value="org.exoplatform.services.jcr.impl.core.MyAnalyzer"/>
         ...
      </properties>
   </query-handler>
   ...
</workspace>

After that, check it with query:

Find node with mixin type 'mix:title' where 'jcr:title' contains "tréma" and "naïve" strings.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE CONTAINS(jcr:title, 'tr\u00E8ma na\u00EFve')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[jcr:contains(@jcr:title, 'tr\u00E8ma na\u00EFve')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

The node type nt:file represents a file. It requires a single child node, called jcr:content. This node type represents images and other binary content in a JCRWiki entry. The node type of jcr:conent is nt:resource which represents the actual content of a file.

Find node with the primary type is 'nt:file' and which whose 'jcr:content' child node contains "cats".

Normally, we can't find nodes (in our case) using just JCR SQL or XPath queries. But we can configure indexing so that nt:file aggregates jcr:content child node.

So, change indexing-configuration.xml:

<?xml version="1.0"?>
<!DOCTYPE configuration SYSTEM "http://www.exoplatform.org/dtd/indexing-configuration-1.2.dtd">
<configuration xmlns:jcr="http://www.jcp.org/jcr/1.0"
               xmlns:nt="http://www.jcp.org/jcr/nt/1.0">
    <aggregate primaryType="nt:file">
        <include>jcr:content</include>
        <include>jcr:content/*</include>
        <include-property>jcr:content/jcr:lastModified</include-property>
    </aggregate>
</configuration>

Now the content of 'nt:file' and 'jcr:content' ('nt:resource') nodes are concatenated in a single Lucene document. Then, we can make a fulltext search query by content of 'nt:file'; this search includes the content of child 'jcr:content' node.

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:file WHERE CONTAINS(*,'cats')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:file)[jcr:contains(.,'cats')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

In this example, we will set different boost values for predefined nodes, and will check effect by selecting those nodes and order them by jcr:score.

The default boost value is 1.0. Higher boost values (a reasonable range is 1.0 - 5.0) will yield a higher score value and appear as more relevant.

<!-- 
This rule actualy do nothing. 'text' property has default boost value.
-->
<index-rule nodeType="nt:unstructured" condition="@rule='boost1'">
   <!-- default boost: 1.0 -->
   <property>text</property>
</index-rule>

<!-- 
Set boost value as 2.0 for 'text' property in nt:unstructured nodes where property 'rule' equal to 'boost2'
-->
<index-rule nodeType="nt:unstructured" condition="@rule='boost2'">
   <!-- boost: 2.0 -->
   <property boost="2.0">text</property>
</index-rule>

<!-- 
Set boost value as 3.0 for 'text' property in nt:unstructured nodes where property 'rule' equal to 'boost3'
-->
<index-rule nodeType="nt:unstructured" condition="@rule='boost3'">
   <!-- boost: 3.0 -->
   <property boost="3.0">text</property>
</index-rule>

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:unstructured WHERE CONTAINS(text, 'quick') ORDER BY jcr:score() DESC";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:unstructured)[jcr:contains(@text, 'quick')] order by @jcr:score descending";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

In this example, we will exclude some 'text' property of nt:unstructured node from indexind. And, therefore, node will not be found by the content of this property, even if it accepts all constraints.

First of all, add rules to indexing-configuration.xml:

<index-rule nodeType="nt:unstructured" condition="@rule='nsiTrue'">
    <!-- default value for nodeScopeIndex is true -->
    <property>text</property>
</index-rule>

<index-rule nodeType="nt:unstructured" condition="@rule='nsiFalse'">
    <!-- do not include text in node scope index -->
    <property nodeScopeIndex="false">text</property>
</index-rule>

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:unstructured WHERE CONTAINS(*,'quick')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:unstructured)[jcr:contains(., 'quick')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

In this example, we want to configure indexind in the next way. All properties of nt:unstructured nodes must be excluded from search, except properties whoes names ends with 'Text' string. First of all, add rules to indexing-configuration.xml:

<index-rule nodeType="nt:unstructured"">
   <property isRegexp="true">.*Text</property>
</index-rule>

Now, let's check this rule with simple query - select all nodes with primary type 'nt:unstructured' and containing 'quick' string (fulltext search by full node).

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:unstructured WHERE CONTAINS(*,'quick')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:unstructured)[jcr:contains(., 'quick')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

It's also called excerption (see Excerpt configuration in Search Configuration and in Searching Repository article).

The goal of this query is to find words "eXo" and "implementation" with fulltext search and high-light this words in result value.

<query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex">
   <properties>
      ...
      <property name="support-highlighting" value="true" />
      <property name="excerptprovider-class" value="org.exoplatform.services.jcr.impl.core.query.lucene.WeightedHTMLExcerpt"/>
      ...
   <properties>
</query-handler>

<index-rule nodeType="nt:unstructured" condition="@rule='excerpt'">
   <property useInExcerpt="false">title</property>
   <property>text</property>
</index-rule>

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT rep:excerpt() FROM nt:unstructured WHERE CONTAINS(*, 'eXo implementation')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,nt:unstructured)[jcr:contains(., 'eXo implementation')]/rep:excerpt(.)";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

RowIterator rows = result.getRows();
Value excerpt = rows.nextRow().getValue("rep:excerpt(.)");
// excerpt will be equal to "<div><span\><strong>eXo</strong> is a JCR <strong>implementation</strong></span></div>"

Find all mix:title nodes where title contains synonims to 'fast' word.

Synonim provider must be configured in indexing-configuration.xml :

<query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex">
   <properties>
      ...
      <property name="synonymprovider-class" value="org.exoplatform.services.jcr.impl.core.query.lucene.PropertiesSynonymProvider" />
      <property name="synonymprovider-config-path" value="../../synonyms.properties" />
      ...
   </properties>
</query-handler>

File synonim.properties contains next synonims list:

ASF=Apache Software Foundation
quick=fast
sluggish=lazy

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM mix:title WHERE CONTAINS(jcr:title, '~fast')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*,mix:title)[jcr:contains(@jcr:title, '~fast')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

Check the correct spelling of phrase 'quik OR (-foo bar)' according to data already stored in index.

SpellChecker must be settled in query-handler config.

test-jcr-config.xml:

<query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex">
   <properties>
      ...
   <property name="spellchecker-class" value="org.exoplatform.services.jcr.impl.core.query.lucene.spell.LuceneSpellChecker$FiveSecondsRefreshInterval" />
      ...
   </properties>
</query-handler>

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT rep:spellcheck() FROM nt:base WHERE jcr:path = '/' AND SPELLCHECK('quik OR (-foo bar)')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "/jcr:root[rep:spellcheck('quik OR (-foo bar)')]/(rep:spellcheck())";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

RowIterator it = result.getRows();
Row r = rows.nextRow();
Value v = r.getValue("rep:spellcheck()");
String correctPhrase = v.getString();

Find similar nodes to node by path '/baseFile/jcr:content'.

In our example, baseFile will contain text where "terms" word happens many times. That's a reason why the existanse of this word will be used as a criteria of node similarity (for node baseFile).

Higlighting support must be added to configuration. test-jcr-config.xml:

<query-handler class="org.exoplatform.services.jcr.impl.core.query.lucene.SearchIndex">
   <properties>
      ...
      <property name="support-highlighting" value="true" />
      ...
   </properties>
</query-handler>

// make SQL query
QueryManager queryManager = workspace.getQueryManager();
// create query
String sqlStatement = "SELECT * FROM nt:resource WHERE SIMILAR(.,'/baseFile/jcr:content')";
Query query = queryManager.createQuery(sqlStatement, Query.SQL);
// execute query and fetch result
QueryResult result = query.execute();

// make XPath query
QueryManager queryManager = workspace.getQueryManager();
// create query
String xpathStatement = "//element(*, nt:resource)[rep:similar(., '/testroot/baseFile/jcr:content')]";
Query query = queryManager.createQuery(xpathStatement, Query.XPATH);
// execute query and fetch result
QueryResult result = query.execute();

NodeIterator it = result.getNodes();

if(it.hasNext())
{
   Node findedNode = it.nextNode();
}

String[] columnNames = result.getColumnNames();
RowIterator rit = result.getRows();
while (rit.hasNext())
{
   Row row = rit.nextRow();
   // get values of the row
   Value[] values = row.getValues();
}

If you execute an XPath request like this:

XPath

// get QueryManager
QueryManager queryManager = workspace.getQueryManager(); 
// make XPath query
Query query = queryManager.createQuery("/jcr:root/Documents/Publie/2010//element(*, exo:article)", Query.XPATH);

You will have an error : "Invalid request". This happens because XML does not allow names starting with a number - and XPath is part of XML: http://www.w3.org/TR/REC-xml/#NT-Name

Therefore, you cannot do XPath requests using a node name that starts with a number.

Easy workarounds:

XPath

// get QueryManager
QueryManager queryManager = workspace.getQueryManager(); 
// make XPath query
Query query = queryManager.createQuery("/jcr:root/Documents/Publie/_x0032_010//element(*, exo:article)", Query.XPATH);