<document>
  <header>
    <product>resin-ee</product>
    <resin-2.0>$ee/ejb-ref/ejbql.xtp</resin-2.0>
    <title>EJB-QL Syntax</title>
  </header>

  <body>
    <summary/>
<def>
SELECT <var/expr/>
FROM <var/schema/> AS <var/var/>, IN(<var/path/>) AS <var/var1/>, ...
[WHERE <var/expr/>]
[ORDER BY <var/expr/>]
[OFFSET <var/integer/>]
[LIMIT <var/integer/>]
</def>

<p>The WHERE and ORDER BY clauses are optional.</p>


<section name='sections' title='Query Sections'>

<defun title='SELECT'>
<p>Specifies the query's values.</p>

<def>
SELECT DISTINCT? <var/expr/>
</def>

</defun>

<defun title='FROM'>
<p>Specifies the query's schema and variables.</p>  

<def>
FROM <var/schema/> AS <var/var/>, IN(<var/path/>) AS <var/var1/>, ...
</def>

</defun>

<defun title='WHERE'>
<p>Restricts the selected values to those matching
a boolean expression.</p>

<def>
WHERE <var/boolean-expr/>
</def>

</defun>

<defun title='ORDER BY' version='Resin-CMP 1.0'>
<p>Sorts the returned values.  The sorts the results of a query
by an expression.  Because the database sorts the results, ORDER BY
can often be more efficient than sorting in Java in addition to
being clearer.</p>

<p>The ORDER BY expression may use a string argument, like ?3.  This allows
the ORDER BY field to be selected by the argument.</p>

<def>
ORDER BY? <var/expr/>
</def>

</defun>

<defun title='OFFSET' version='Resin-CMP 2.1.1'>
<p>For a collection-valued query, returns items starting from the
offset value.  Only integer constants and integer arguments like ?3
are allowed.</p>

<p>OFFSET is generally used in combination with LIMIT to select a
slice of a large database query.</p>

<p>Depending on the database, this may require an ORDER BY
to make the results consistent.  Unless you specify ORDER BY, databases
are allowed to return query results in any order.  So using
OFFSET without ORDER BY name return different results even when
called with the same query.</p>

<example>
SELECT o FROM items o ORDER BY o.id OFFSET ?1 LIMIT ?2
</example>

</defun>

<defun title='LIMIT' version='Resin-CMP 2.1.1'>
<p>For a collection-valued query, limits the number of items returned.
Only integer constants and integer arguments like ?3
are allowed.</p>

<p>LIMIT is generally used with OFFSET to handle large database queries
which may have multiple return pages.</p>

<p>Depending on the database, LIMIT may require an ORDER BY
to make the results consistent.  Unless you specify ORDER BY, databases
are allowed to return query results in any order.  So using
OFFSET without ORDER BY name return different results even when
called with the same query.</p>

<example>
SELECT o FROM items o ORDER BY o.id OFFSET ?1 LIMIT ?2
</example>

</defun>

</section>

<section title='Expressions'>

<defun name='cmp-path' title="bean-expr . cmp-field">
<p>Selects a field value from an entity bean.  The type of the
expression is the type of the cmp-field.</p>

<example>
SELECT c
FROM courses c
WHERE c.room = 'Leaky Dungeon'
</example>

</defun>

<defun name='cmr-path' title="bean-expr . cmr-field">
<p>Selects relation bean from an entity bean.  The type of the
expression is the bean result.  Because the value of the expression is
an entity bean, the expression can be used in a further path
expression.</p>

<example>
SELECT c
FROM courses c
WHERE c.teacher.name = 'Severus Snape'
</example>

</defun>

<defun name='arg' title="?n">
<p>A find or select method argument.  The value of the
argument is inserted into the expression.  The count is 1-based, so
a method with a single argument will use <var/?1/>.  The type of the
expression is the type of the method argument.</p>

<example>
SELECT c
FROM courses c
WHERE c.room = ?1
</example>

</defun>

<defun name='add' title="expr + expr">
<p>Arithmetic expressions.  Adds, subtracts, multiplies or
divides two expressions, returning a numeric expression.  The
sub-expressions must have numeric values.</p>

<deftable>
<tr><th>Operator<th>Meaning
<tr><td>+<td>Add
<tr><td>-<td>Subtract
<tr><td>*<td>Multiply
<tr><td>/<td>Divide
</deftable>

</defun>

<defun name='cmp' title="expr = expr">
<p>Comparison expressions.  Compares two expressions,
returning a boolean expression.  The two expressions for an
equality comparison must have compatible types.
The expressions for less-than comparison must have numeric values.</p>

<deftable>
<tr><th>Operator<th>Meaning
<tr><td>=<td>Equals
<tr><td>&lt;&gt;<td>Not equals
<tr><td>&lt;<td>Less-than
<tr><td>&lt;=<td>Less-than or equal to
<tr><td>&gt;<td>Greater-than
<tr><td>&gt;=<td>Greater-than or equal to
</deftable>

<example>
SELECT c
FROM courses c
WHERE c.students &lt; 20
</example>

</defun>

<defun name='is-null' title="IS [NOT] NULL">
<p>Tests for a null value.  IS NULL can be used both for
SQL null values and for null relation values.</p>

<p>The following example shows the case where c.teacher is a relation
to a Teacher object.  It will select courses with no assigned teacher.</p>

<example>
SELECT c
FROM courses c
WHERE c.teacher IS NULL
</example>

</defun>

<defun name='and' title="expr AND expr">
<p>Boolean and expression.  Evaluates to true if both
expressions are true.  Both expressions must be boolean and
the result is a boolean.</p>
</defun>

<defun name='or' title="expr OR expr">
<p>Boolean or expression.  Evaluates to true if either
expression is true.  Both expressions must be boolean and
the result is a boolean.</p>
</defun>

<defun name='not' title="NOT expr">
<p>Boolean not expression.  The expression and the result are
boolean expressions.</p>
</defun>

<defun name='fun' title="fun_name(expr, ...)">
<p>Applies a function to the expression arguments.
Resin-CMP will allow the functions defined in the JDBC 2.0 spec,
but the EJB-QL spec only defines the following functions:</p>

<deftable>
<tr><th>Function<th>Description
<tr><td>CONCAT(string, string)<td>Contatenates two strings
<tr><td>SUBSTRING(string, start, len)<td>Selects a substring
<tr><td>LOCATE(string, start [, start])<td>Finds a substring
<tr><td>LENGTH(string)<td>Returns the string length
<tr><td>ABS(number)<td>Returns the absolute value
<tr><td>SQRT(double)<td>Returns the square root of a number
</deftable>

<p>Additional functions can be added using the
<a href="resin-ejb-config.xtp#query-function">query-function</a> tag in the
resin-ejb configuration file.</p>

<p>The following functions are known to Resin-CMP:</p>

<def>
int abs(int)
double abs(double)

double acos(double)
double sin(double)
double atan(double)
double cos(double)
double cot(double)
double degrees(double)
double exp(double)
double log(double)
double log10(double)
double radians(double)
double sin(double)
double sqrt(double)
double tan(double)

int ceiling(double)
int floor(double)
int sign(double)

double atan2(double, double)
double power(double, double)
double round(double, double)
double truncate(double, double)

int mod()
int rand()

int count(any)

double min(double)
double max(double)

int ascii(String)
int length(String)

String char(int)
String space(int)

String concat(String, String)

int difference(String, String)

String insert(String, int, int, String)

String lcase(String)
String ltrim(String)
String rtrim(String)
String ucase(String)
String soundex(String)

String left(String, int)
String repeat(String, int)
String right(String, int)

String locate(String, int)
String locate(String, int, int)
String replace()

String substring(String, int, int)

String database()
String user()

Date curdate()
Date curtime()
Date now()

String dayname(Date)
String monthname(Date)

int dayofmonth(Date)
int dayofweek(Date)
int dayofyear(Date)
int hour(Date)
int minute(Date)
int month(Date)
int quarter(Date)
int second(Date)
int week(Date)
int year(Date)

Date timestampadd(Date, Date)
Date timestampdiff(Date, Date)
</def>

</defun>

</section>
  </body>
</document>
