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 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
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();
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:
Find all nodes with the 'mix:title' mixin type and where the 'jcr:title' property starts with 'P'.
See also the article about "Find all mix:title nodes where jcr:title does NOT start with 'P'".
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"
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();
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:title | jcr:description | jcr:path | jcr:score |
---|---|---|---|
Prison break | Run, Forest, run )) | /document2 | 4713 |
Panopticum | It's imagine film | /document3 | 5150 |
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 (' \').
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"
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();
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:
Find all nodes with a 'mix:title' mixin type and where the 'jcr:title' property does NOT start with a 'P' symbol.
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"
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();
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:
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.
See also Multivalue property comparison.
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
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();
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:
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".
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"
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();
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:title | jcr:description | jcr:path | jcr:score |
---|---|---|---|
War and peace | novel | /document1 | 3806 |
Cinderella | fairytale | /document2 | 3806 |
Find all nodes with the 'mix:title' mixin type where the 'jcr:description' property does not exist (is null).
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.
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();
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:
Find all nodes with the 'mix:title' mixin type and where the 'jcr:title' property equals 'casesensitive' in lower or upper case.
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"
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();
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:
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 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"
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();
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:
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()".
"fn:name()" can be used ONLY with an equal('=') comparison.
The repository contains nt:file nodes with different names.
root
document1 (nt:file)
file (nt:file)
somename (nt:file)
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();
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:
Find all nodes with the 'nt:unstructured' primary type whose property 'multiprop' contains both values "one" and "two".
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" ]
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();
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: