3.1.4.1.3. Property constraints

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

Property comparison

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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

Warning

You are looking at documentation for an older release. Not what you want? See the current release documentation.

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