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

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

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.

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