eXo Platform 4.0 Documentation > Reference Guide / eXo JCR > Developer References > Basic usage > Searching for repository content > Query usecases
3.1.4.1.3. Property constraints
Property comparison

Find all nodes with the 'mix:title' mixin type 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".

You 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:titlejcr:pathjcr:score
Puss in Boots/document31725


LIKE constraint

Find all nodes with the 'mix:title' mixin type and where the 'jcr:title' property starts 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".

You 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:titlejcr:descriptionjcr:pathjcr:score
Prison breakRun, Forest, run ))/document24713
PanopticumIt's imagine film/document35150


Escaping in LIKE statements

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

As you see "P%rison break" contains the symbol '%'. This symbol is reserved for LIKE comparisons.

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

You 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:titlejcr:descriptionjcr:pathjcr:score
P%rison breakRun, Forest, run ))/document27452


NOT constraint

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

Repository structure

The repository contains a mix:title node 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 result

Let's get the nodes:

NodeIterator it = result.getNodes();


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

NodeIterator will return "document1".

You 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:titlejcr:descriptionjcr:pathjcr:score
Star wars Dart rules!!/document14713


AND constraint

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

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

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 result

Let's get nodes:

NodeIterator it = result.getNodes();


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

NodeIterator will return "document2".

You 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:titlejcr:descriptionprop_pagecountjcr:pathjcr:score
Cinderellafairytale100/document27086


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 the 'mix:title' mixin type whose 'jcr:title' property 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 result

Let's get nodes:

NodeIterator it = result.getNodes();


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

NodeIterator will return "document1" and "document2".

You 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:titlejcr:descriptionjcr:pathjcr:score
War and peacenovel/document13806
Cinderellafairytale/document23806


Property existence constraint

Find all nodes with the 'mix:title' mixin type where the 'jcr:description' property 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 result

Let's get nodes:

NodeIterator it = result.getNodes();


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

NodeIterator will return "document3".

You 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:titlejcr:descriptionjcr:pathjcr:score
Titanicnull/document31947


Finding nodes in a case-insensitive way

Find all nodes with the 'mix:title' mixin type and where the 'jcr:title' property 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 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).

You 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 the "nt:resource" primary type 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 the "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 result

Let's get nodes:

NodeIterator it = result.getNodes();


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

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

You 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 the 'nt:file' primary type 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 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, you 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:pathjcr:score
/document13575


Multivalue property comparison

Find all nodes with the 'nt:unstructured' primary type 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 result

Let's get nodes:

NodeIterator it = result.getNodes();


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

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

You 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:primarytypjcr:pathjcr:score
nt:unstructured/node1 3806
nt:unstructured/node2 3806


Copyright ©. All rights reserved. eXo Platform SAS
blog comments powered byDisqus