eXo Platform 3.5

## Chapter 30. JCR Query Usecases

JCR supports two query languages - JCR and XPath. A query, whether XPath or SQL, specifies a subset of nodes within a workspace, called the result set. The result set constitutes all the nodes in the workspace that meet the constraints stated in the query.

## Query Lifecycle

### Query Creation and Execution

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();

### Query Result Processing

    // 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();

    }

### Scoring

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

## Query result settings

- Set Offset And Limit

## Type Constraints

- Find All Nodes

- Find Nodes by Primary Type

- Find Nodes by Mixin Type

## Property Constraints

- Property Comparison

- LIKE Constraint

- Escaping in LIKE Statements

- NOT Constraint

- AND Constraint

- OR Constraint

- Property Existence Constraint

- Upper and Lower Case Constraints

- Date Property Comparison

- Node Name Constraint

- Multivalue Property Comparison

## Path Constraint

- Exact Path Constraint

- Child Node Constraint

- Find All Descendant Nodes

## Ordering specifing

- Order by Property

- Order by Descendant Node Property

- Order by Score

- Order by Path or Name

## Fulltext Search

- Fulltext Search by Property

- Fulltext Search by All Properties

- Find nt:file document by content of child jcr:content node

- How to set new Analyzer. Accent symblos ignoring

## Indexing rules and additional features

- Aggregation rule

- Search Result Highlighting

- Index Boost Value

- Exclusion from the Node Scope Index

- Regular expressions as property name in indexing rule

- Synonim Provider

- Spell Checking

- Find Similar Nodes

## Query Examples

### SetOffset and SetLimit

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

#### Common info

QueryImpl class has two methods: one to indicate how many results shall be returned at most, and another to fix the starting position.

- setOffset(long offset) - Sets the start offset of the result set.

- setLimit(long position) - Sets the maximum size of the result set.

#### Repository structure

Repository contains mix:title nodes, where jcr:title has different values.

- root

  - node1 (nt:unstructured)

  - node2 (nt:unstructured)

  - node3 (nt:unstructured)

  - node4 (nt:unstructured)

  - node5 (nt:unstructured)

  - node6 (nt:unstructured)

#### Query execution

SQL

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

In usual case (without using setOffset and setLimit methods), Node iterator returns all nodes (node1...node6). But in our case NodeIterator will return "node2","node3" and "node4".

\[node1 node2 node3 node4 node5 node6\]

### Finding All Nodes

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

#### Repository structure:

Repository contains many different nodes.

- root

  - folder1 (nt:folder)

    - document1 (nt:file)

    - folder2 (nt:folder)

      - document2 (nt:unstructured)

      - document3 (nt:folder)

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "folder1", "folder2","document1","document2","document3", and each other nodes in workspace if they are here.

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is

jcr:path | jcr:score
--- | ---
/folder1 | 1000
/folder1/document1 | 1000
/folder1/folder2 | 1000
/folder1/folder2/document2 | 1000
/folder1/folder2/document3 | 1000
... | ...

### Finding Nodes by Primary Type

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

#### Repository structure:

The repository contains nodes with different primary types and mixin types.

- root

  - document1 primarytype = "nt:unstructured" mixintype = "mix:title"

  - document2 primarytype = "nt:file" mixintype = "mix:lockable"

  - document3 primarytype = "nt:file" mixintype = "mix:title"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document2" and "document3".

We can also get a table:

    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 table content is

jcr:path | jcr:score
--- | ---
/document2 | 2674
/document3 | 2674

### Finding Nodes by Mixin Type

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

#### Repository structure:

The repository contains nodes with different primary types and mixin types.

- root

  - document1 primarytype = "nt:unstructured" mixintype = "mix:title"

  - document2 primarytype = "nt:file" mixintype = "mix:lockable"

  - document3 primarytype = "nt:file" mixintype = "mix:title"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return "document1" and "document3".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is

jcr:title | ... | jcr:path | jcr:score
--- | --- | --- | ---
First document | ... | /document1 | 2674
Second document | ... | /document3 | 2674

### Property Comparison

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.

#### Repository structure:

Repository contains several mix:title nodes, where each prop_pagecount contains a different value.

- root

  - document1 (mix:title) jcr:title="War and peace" prop_pagecount=1000

  - document2 (mix:title) jcr:title="Cinderella" prop_pagecount=100

  - document3 (mix:title) jcr:title="Puss in Boots" prop_pagecount=60

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return "document3".

We can also get a table:

    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 table content is

jcr:title | jcr:path | jcr:score
--- | --- | ---
Puss in Boots | /document3 | 1725

### LIKE Constraint

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

### Note

See also the article about "Find all mix:title nodes where jcr:title does NOT start with 'P'"

#### Repository structure:

The repository contains 3 mix:title nodes, where each jcr:title has a different value.

- root

  - document1 (mix:title) jcr:title="Star wars" jcr:description="Dart rules!!"

  - document2 (mix:title) jcr:title="Prison break" jcr:description="Run, Forest, run ))"

  - document3 (mix:title) jcr:title="Panopticum" jcr:description="It's imagine film"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return "document2" and "document3".

We can also get a table:

    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 table content is

jcr:title | jcr:description | jcr:path | jcr:score
--- | --- | --- | ---
Prison break | Run, Forest, run )) | /document2 | 4713
Panopticum | It's imagine film | /document3 | 5150

### Escaping in LIKE Statements

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 (' \').

#### Repository structure

The repository contains mix:title nodes, where jcr:title can have different values.

- root

  - document1 (mix:title) jcr:title="Star wars" jcr:description="Dart rules!!"

  - document2 (mix:title) jcr:title="P%rison break" jcr:description="Run, Forest, run ))"

  - document3 (mix:title) jcr:title="Panopticum" jcr:description="It's imagine film"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document2".

We can also get a table:

    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 table content is

jcr:title | jcr:description | jcr:path | jcr:score
--- | --- | --- | ---
P%rison break | Run, Forest, run )) | /document2 | 7452

### NOT Constraint

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

#### Repository Structure

The repository contains a mix:title nodes, where the jcr:title has different values.

- root

  - document1 (mix:title) jcr:title="Star wars" jcr:description="Dart rules!!"

  - document2 (mix:title) jcr:title="Prison break" jcr:description="Run, Forest, run ))"

  - document3 (mix:title) jcr:title="Panopticum" jcr:description="It's imagine film"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the result

Let's get the nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document1".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is

jcr:title | jcr:description | jcr:path | jcr:score
--- | --- | --- | ---
Star wars | Dart rules!! | /document1 | 4713

### AND Constraint

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.

### Note

See also Multivalue Property Comparison.

#### Repository Structure:

The repository contains mix:title nodes, where prop_pagecount has different values.

- root

  - document1 (mix:title) jcr:title="War and peace" jcr:description="novel" prop_pagecount=1000

  - document2 (mix:title) jcr:title="Cinderella" jcr:description="fairytale" prop_pagecount=100

  - document3 (mix:title) jcr:title="Puss in Boots" jcr:description="fairytale" prop_pagecount=60

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document2".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:title | jcr:description | prop_pagecount | jcr:path | jcr:score
--- | --- | --- | --- | ---
Cinderella | fairytale | 100 | /document2 | 7086

### OR Constraint

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".

#### Repository Structure

The repository contains mix:title nodes, where jcr:title and jcr:description have different values.

- root

  - document1 (mix:title) jcr:title="War and peace" jcr:description="novel"

  - document2 (mix:title) jcr:title="Cinderella" jcr:description="fairytale"

  - document3 (mix:title) jcr:title="Puss in Boots" jcr:description="fairytale"

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document1" and "document2".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:title | jcr:description | jcr:path | jcr:score
--- | --- | --- | ---
War and peace | novel | /document1 | 3806
Cinderella | fairytale | /document2 | 3806

### Property Existence Constraint

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

#### Repository Structure

The repository contains mix:title nodes, in one of these nodes the jcr:description property is null.

- root

  - document1 (mix:title) jcr:title="Star wars" jcr:description="Dart rules!!"

  - document2 (mix:title) jcr:title="Prison break" jcr:description="Run, Forest, run ))"

  - document3 (mix:title) jcr:title="Titanic" // The description property does not exist. This is the node we wish to find.

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document3".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:title | jcr:description | jcr:path | jcr:score
--- | --- | --- | ---
Titanic | null | /document3 | 1947

### Finding Nodes in a Case-Insensitive Way

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

#### Repository Structure

The repository contains mix:title nodes, whose jcr:title properties have different values.

- root

  - document1 (mix:title) jcr:title="CaseSensitive"

  - document2 (mix:title) jcr:title="casesensitive"

  - document3 (mix:title) jcr:title="caseSENSITIVE"

#### Query Execution

- UPPER case

SQL

    // 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();

XPath

    // 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();

- LOWER case

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document1", "document2" and "document3" (in all examples).

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:title | ... | jcr:path
--- | --- | ---
CaseSensitive | ... | /document1
casesensitive | ... | /document2
caseSENSITIVE | ... | /document3

### Date Property Comparison

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.

#### Repository Structure

Repository contains nt:resource nodes with different values of jcr:lastModified property

- root

  - document1 (nt:file)

    - jcr:content (nt:resource) jcr:lastModified="2006-01-19T15:34:15.917+02:00"

  - document2 (nt:file)

    - jcr:content (nt:resource) jcr:lastModified="2005-01-19T15:34:15.917+02:00"

  - document3 (nt:file)

    - jcr:content (nt:resource) jcr:lastModified="2007-01-19T15:34:15.917+02:00"

#### Query Execution

SQL

In SQL you have to use the keyword TIMESTAMP for date comparisons. Otherwise, the date would be interpreted as a string. The date has to be surrounded by single quotes (TIMESTAMP 'datetime') and in the ISO standard format: YYYY-MM-DDThh:mm:ss.sTZD ( http://en.wikipedia.org/wiki/ISO_8601 and well explained in a W3C note http://www.w3.org/TR/NOTE-datetime).

You will see that it can be a date only (YYYY-MM-DD) but also a complete date and time with a timezone designator (TZD).

    // 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();

XPath

Compared to the SQL format, you have to use the keyword xs:dateTime and surround the datetime by extra brackets: xs:dateTime('datetime'). The actual format of the datetime also conforms with the ISO date standard.

    // 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();

#### Fetching the result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node foundNode = it.nextNode();

    }

NodeIterator will return "/document3/jcr:content".

We can also get a table:

    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 table content is:

jcr:lastModified | ... | jcr:path
--- | --- | ---
2007-01-19T15:34:15.917+02:00 | ... | /document3/jcr:content

### Node Name Constraint

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

### Note

fn:name() can be used ONLY with an equal('=') comparison.

#### Repository Structure

The repository contains nt:file nodes with different names.

- root

  - document1 (nt:file)

  - file (nt:file)

  - somename (nt:file)

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return the node whose fn:name equals "document".

Also we can get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:path | jcr:score
--- | ---
/document1 | 3575

### Multivalue Property Comparison

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

#### Repository Structure

The repository contains nt:unstructured nodes with different 'multiprop' properties.

- root

  - node1 (nt:unstructured) multiprop = [ "one","two" ]

  - node1 (nt:unstructured) multiprop = [ "one","two","three" ]

  - node1 (nt:unstructured) multiprop = [ "one","five" ]

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return "node1" and "node2".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:primarytyp | jcr:path | jcr:score
--- | --- | ---
nt:unstructured | /node1 | 3806
nt:unstructured | /node2 | 3806

### Exact Path Constraint

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

#### Repository Structure

Repository filled by different nodes. There are several folders which contain other folders and files.

- root

  - folder1 (nt:folder)

    - folder2 (nt:folder)

      - document1 (nt:file) // This document we want to find

      - folder3 (nt:folder)

        - document1 (nt:file)

#### Query Execution

SQL

    // 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();

XPath

    // 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();

Remark: The indexes [1] are used in order to get the same result as the SQL statement. SQL by default only returns the first node, whereas XPath fetches by default all nodes.

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return expected "document1".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:path | jcr:score
--- | ---
/folder1/folder2/document1 | 1030

### Child Node Constraint

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.

#### Repository Structure

The repository is filled by "nt:folder" nodes. The nodes are placed in a multilayer tree.

- root

  - folder1 (nt:folder)

    - folder2 (nt:folder)

      - folder3 (nt:folder) // This node we want to find

        - folder4 (nt:folder) // This node is not child but a descendant of '/folder1/folder2/'.

      - folder5 (nt:folder) // This node we want to find

#### Query Execution

SQL

The use of "%" in the LIKE statement includes any string, therefore there is a second LIKE statement that excludes that the string contains "/". This way child nodes are included but descendant nodes are excluded.

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return "folder3" and "folder5".

We can also get a table:

    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 table content is:

jcr:path | jcr:score
--- | ---
/folder1/folder2/folder3 | 1707
/folder1/folder2/folder5 | 1707

### Finding All Descendant Nodes

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

#### Repository Structure

The repository contains "nt:folder" nodes. The nodes are placed in a multilayer tree.

- root

  - folder1 (nt:folder)

    - folder2 (nt:folder)

      - folder3 (nt:folder) // This node we want to find

        - folder4 (nt:folder) // This node we want to find

      - folder5 (nt:folder) // This node we want to find

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return "folder3", "folder4" and "folder5" nodes.

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:path | jcr:score
--- | ---
/folder1/folder2/folder3 | 1000
/folder1/folder2/folder3/folder4 | 1000
/folder1/folder2/folder5 | 1000

### Sorting Nodes by Property

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

#### Repository Structure

The repository contains several mix:title nodes, where prop_pagecount has different values.

- root

  - document1 (mix:title) jcr:title="War and peace" jcr:description="roman" prop_pagecount=4

  - document2 (mix:title) jcr:title="Cinderella" jcr:description="fairytale" prop_pagecount=7

  - document3 (mix:title) jcr:title="Puss in Boots" jcr:description="fairytale" prop_pagecount=1

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

The NodeIterator will return nodes in the following order "document3", "document1", "document2".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:title | jcr:description | prop_pagecount | jcr:path | jcr:score
--- | --- | --- | --- | ---
Puss in Boots | fairytale | 1 | /document3 | 1405
War and peace | roman | 4 | /document1 | 1405
Cinderella | fairytale | 7 | /document2 | 1405

### Ordering by Descendant Nodes Property (XPath only)

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'.

### Note

This ORDER BY construction only works in XPath!

#### Repository structure:

- root

  - node1 (nt:unstructured)

    - a (nt:unstructured)

      - b (nt:unstructured)

  - node2 (nt:unstructured)

    - a (nt:unstructured)

      - b (nt:unstructured)

        - c (nt:unstructured) prop = "a"

  - node3 (nt:unstructured)

    - a (nt:unstructured)

      - b (nt:unstructured)

        - c (nt:unstructured) prop = "b"

#### Query Execution

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return nodes in the following order - "node3","node2" and "node1".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:primaryType | jcr:path | jcr:score
--- | --- | ---
nt:unstructured | /testroot/node3 | 1000
nt:unstructured | /testroot/node2 | 1000
nt:unstructured | /testroot/node1 | 1000

### Ordering by Score

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.

#### Info

SQL and XPath queries support both score constructions jcr:score and jcr:score()

    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]

Do not use "ascending" combined with jcr:score in XPath. The following XPath statement may throw an exception:

    ... order by jcr:score() ascending

Do not set any ordering specifier - ascending is default:

    ... order by jcr:score()

#### Repository Structure

The repository contains mix:title nodes, where the jcr:description has different values.

- root

  - document1 (mix:title) jcr:description="The quick brown fox jumps over the lazy dog."

  - document2 (mix:title) jcr:description="The brown fox lives in the forest."

  - document3 (mix:title) jcr:description="The fox is a nice animal."

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return nodes in the following order: "document3", "document2", "document1".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:description | ... | jcr:path | jcr:score
--- | --- | --- | ---
The fox is a nice animal. | ... | /document3 | 2512
The brown fox lives in the forest. | ... | /document2 | 3595
The quick brown fox jumps over the lazy dog. | ... | /document1 | 5017

### Ordering by Path or Name

### Warning

Ordering by jcr:path or jcr:name does not supported.

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

- Order by property with value type NAME or PATH (jcr supports it)

- Order by jcr:path or jcr:name - sort by exact path or name of node (jcr do not supports it)

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/%'

### Fulltext Search by Property

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

#### Repository Structure

The repository is filled with nodes of the mixin type 'mix:title' and different values of the 'jcr:description' property.

- root

  - document1 (mix:title) jcr:description = "The quick brown fox jumps over the lazy dog."

  - document2 (mix:title) jcr:description = "The brown fox lives in a forest." // This is the node we want to find

  - document3 (mix:title) jcr:description = "The fox is a nice animal."

  - document4 (nt:unstructured) jcr:description = "There is the word forest, too."

#### Query Execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the Result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document2".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:description | ... | jcr:path
--- | --- | ---
The brown fox lives in forest. | ... | /document2

### Fulltext Search by All Properties in Node

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

#### Repository structure:

Repository filled with different nodes with mixin type 'mix:title' and different values of 'jcr:title' and 'jcr:description' properties.

- root

  - document1 (mix:title) jcr:title ='Star Wars' jcr:description = 'Dart rules!!'

  - document2 (mix:title) jcr:title ='Prison break' jcr:description = 'Run, Forest, run ))'

  - document3 (mix:title) jcr:title ='Titanic' jcr:description = 'An iceberg breaks a ship.'

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    while(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document1" and "document2".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:title | jcr:description | ... | jcr:path
--- | --- | --- | ---
Prison break. | Run, Forest, run )) | ... | /document2
Titanic | An iceberg breaks a ship. | ... | /document3

### Ignoring Accent Symbols. New Analyzer Setting.

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):

- The first way: Create descendant class of SearchIndex with new Analyzer (see Search Configuration);

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.

- The second way: Register new Analyzer in QueryHandler configuration (this one eccepted since 1.12 version);

We will use the last one:

- Create new MyAnalyzer

    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;

       }

    }

- Then, register new MyAnalyzer in configuration

    <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.

#### Repository structure:

Repository filled by nodes with mixin type 'mix:title' and different values of 'jcr:title' property.

- root

  - node1 (mix:title) jcr:title = "tréma blabla naïve"

  - node2 (mix:title) jcr:description = "trema come text naive"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "node1" and "node2". How is it possible? Remember that our MyAnalyzer transforms 'tréma' word to 'trema'. So node2 accepts our constraints to.

Also, we can get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is

cr:title | ... | cr:path
--- | --- | ---
trèma blabla naïve | ... | /node1
trema come text naive | ... | /node2

### Finding nt:file node by content of child jcr:content node

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.

#### Repository structure:

Repository contains different nt:file nodes.

- root

  - document1 (nt:file)

    - jcr:content (nt:resource) jcr:data = "The quick brown fox jumps over the lazy dog."

  - document2 (nt:file)

    - jcr:content (nt:resource) jcr:data = "Dogs do not like cats."

  - document3 (nt:file)

    - jcr:content (nt:resource) jcr:data = "Cats jumping high."

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching the result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "document2" and "document3".

We can also get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:path | jcr:score
--- | ---
/document2 | 1030
/document3 | 1030

### Changing Priority of Node

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.

### Note

See 4.2.2 Index Boost Value Search Configuration

#### Indexing configuration

In next configuration, we will set boost values for nt:ustructured nodes 'text' property.

indexing-config.xml:

    <!--

    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>

#### Repository structure:

Repository contains many nodes with primary type nt:unstructured. Each node contains 'text' property and 'rule' property with different values.

- root

  - node1(nt:unstructured) rule='boost1' text='The quick brown fox jump...'

  - node2(nt:unstructured) rule='boost2' text='The quick brown fox jump...'

  - node3(nt:unstructured) rule='boost3' text='The quick brown fox jump...'

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return nodes in next order "node3", "node2", "node1".

### Removing Nodes Property From Indexing Scope

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>

### Note

See Search Configuration

#### Repository structure:

Repository contains nt:unstructured nodes, with same 'text'property and different 'rule' properties (even null)

- root

  - node1 (nt:unstructured) rule="nsiTrue" text="The quick brown fox ..."

  - node2 (nt:unstructured) rule="nsiFalse" text="The quick brown fox ..."

  - node3 (nt:unstructured) text="The quick brown fox ..." // as you see this node not mentioned in indexing-coniguration

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "node1" and "node3". Node2, as you see, is not in result set.

Also, we can get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is

jcr:primarytype | jcr:path | jcr:score
--- | --- | ---
nt:unstructured | /node1 | 3806
nt:unstructured | /node3 | 3806

### Regular Expression as Property Name in Indexing Rules

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>

### Note

See Search Configuration

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).

#### Repository structure:

Repository contains nt:unstructured nodes, with different 'text'-like named properties

- root

  - node1 (nt:unstructured) Text="The quick brown fox ..."

  - node2 (nt:unstructured) OtherText="The quick brown fox ..."

  - node3 (nt:unstructured) Textle="The quick brown fox ..."

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "node1" and "node2". "node3", as you see, is not in result set.

Also, we can get a table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is:

jcr:primarytype | jcr:path | jcr:score
--- | --- | ---
nt:unstructured | /node1 | 3806
nt:unstructured | /node2 | 3806

### High-lighting Result of Fulltext Search

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.

#### Base info

High-lighting is not default feature so we must set it in jcr-config.xml, also excerpt provider must be defined:

    <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>

Also, remember that we can make indexing rules, as in the example below:

Let's write rule for all nodes with primary node type 'nt:unstructed' where property 'rule' equal to "excerpt" string. For those nodes, we will exclude property "title" from high-lighting and set "text" property as highlightable. Indexing-configuration.xml must containt the next rule:

    <index-rule nodeType="nt:unstructured" condition="@rule='excerpt'">

       <property useInExcerpt="false">title</property>

       <property>text</property>

    </index-rule>

#### Repository structure:

We have single node with primary type 'nt:unstructured'

- document (nt:unstructured)

  - rule = "excerpt"

  - title = "eXoJCR"

  - text = "eXo is a JCR implementation"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Now let's see on the result table:

    String[] columnNames = result.getColumnNames();

    RowIterator rit = result.getRows();

    while (rit.hasNext())

    {

       Row row = rit.nextRow();

       // get values of the row

       Value[] values = row.getValues();

    }

Table content is

rep:excerpt() | jcr:path | jcr:score
--- | --- | ---
\<div\>\<span\>\<strong\>eXo\</strong\> is a JCR \<strong\>implementation\</strong\>\</span\>\</div\> | /testroot/node1 | 335

As you see, words "eXo" and "implamentation" is highlighted.

Also, we can get exactly "rep:excerpt" value:

    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>"

### Searching By Synonim

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

### Note

See also about synonim propvider configuration - Searching Repository Content

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

#### Repository structure:

Repository contains mix:title nodes, where jcr:title has different values.

- root

  - document1 (mix:title) jcr:title="The quick brown fox jumps over the lazy dog."

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return expected document1. This is a purpose of synonim providers. Find by specified word, but return by all synonims to.

### Checking the spelling of Phrase

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

### Note

See also about SpellChecker configuration - Searching Repository Content

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>

#### Repository structure:

Repository contains node, with string property "The quick brown fox jumps over the lazy dog."

- root

  - node1 property="The quick brown fox jumps over the lazy dog."

#### Query execution

Query looks only for root node, because spell checker looks for suggestions by full index. So complicated query is redundant.

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Get suggestion of coorect spelling our phrase:

    RowIterator it = result.getRows();

    Row r = rows.nextRow();

    Value v = r.getValue("rep:spellcheck()");

    String correctPhrase = v.getString();

So, correct spelling for phrase "quik OR (-foo bar)" is "quick OR (-fox bar)".

### Finding Similar Nodes

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).

### Note

See also about Similarity and configuration - Searching Repository Content

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>

#### Repository structure:

Repository contains many nt:file nodes"

- root

  - baseFile (nt:file)

    - jcr:content (nt:resource) jcr:data="Similarity is determined by looking up terms that are common to nodes. There are some conditions that must be met for a term to be considered. This is required to limit the number possibly relevant terms. Only terms with at least 4 characters are considered. Only terms that occur at least 2 times in the source node are considered. Only terms that occur in at least 5 nodes are considered."

  - target1 (nt:file)

    - jcr:content (nt:resource) jcr:data="Similarity is determined by looking up terms that are common to nodes."

  - target2 (nt:file)

    - jcr:content (nt:resource) jcr:data="There is no you know what"

  - target3 (nt:file)

    - jcr:content (nt:resource) jcr:data=" Terms occures here"

#### Query execution

SQL

    // 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();

XPath

    // 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();

#### Fetching result

Let's get nodes:

    NodeIterator it = result.getNodes();

    if(it.hasNext())

    {

       Node findedNode = it.nextNode();

    }

NodeIterator will return "/baseFile/jcr:content","/target1/jcr:content" and "/target3/jcr:content".

As you see the base node are also in result set.

We can also get a table:

    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 table content is

jcr:path | ... | jcr:score
--- | --- | ---
/baseFile/jcr:content | ... | 2674
/target1/jcr:content | ... | 2674
/target3/jcr:content | ... | 2674

## Tips and tricks

### XPath queries containing node names starting with a number

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:

- Use an SQL request.

- Use escaping :

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);

Copyright © 2009-2012. All rights reserved. eXo Platform SAS