EJB QUERY LANGUAGE (EJB QL)

Ashish Bhatnagar
2d
204
0
1

Article

Introduction

EJB QL is a query language similar to SQL. There are, however, specific differences while SQL is used to query tables, rows and columns, EJB QL is used to query enterprise beans, their persistent states and their relationship while the result of an SQL query is a row-set of the same sequence of columns, the result of an EJB QL query is either a single object, a collection of entity objects of a single type, or a collection of values retrieved from container –managed persistence fields.

Entity beans are usually stored in relational databases, and EJB QL is therefore designed to map EJB QL queries directly to SQL queries for efficient execution. The translated SQL queries then retrieve data about the entity bean from the database.
EJB QL utilizes the object model to represent relationships, unlike the relational model, which employs foreign keys and joins to represent relationships. The object model utilizes a named relationship between two objects. EJB QL uses the dot(. ) operator and container-managed persistence fields.

EJB QL Built-in Functions

EJB QL provides some built–in functions to help manipulate strings and numbers. These are standard SQL operations that all JDBC 2.0 and higher drivers are required to support.

String Functions

CONCAT (String first_string, String second_string) concatenates first_string and second_string and returns the result as a String data type.

Example

SELECT CONCAT(c.firstName, ' ', c.lastName) AS fullName
FROM Customer c;

Output

CONCAT

SUBSTRING(String source_string, int sub_start, int sub_length)returns a substring from the source_string starting at index position sub_start and of length sub_length.

Example

SELECT SUBSTRING(c.firstName, 1, 3)
FROM Customer c;

Output

SUBSTRING

LOCATE(String source_string, String pattern_string) returns an integer value that gives the index location of an occurrence of the pattern_string inside the source_string.

Example

SELECT LOCATE(c.lastName, 'Sharma')
FROM Customer c;

Output

LOCATE

LENGTH(String source_string) returns an integer value specifying the number of characters in source_string.

SELECT LENGTH(c.firstName)
FROM Customer c;

Output

LENGTH

Arithmetic Functions

The arithmetic functions that EJB QL supports are.

ABS(int number) returns the absolute value of an int type argument as an int data type.
ABS(float number ) returns the absolute value of a float type argument as a float data type.
ABS(double number) returns the absolute value of a double-type argument as a double data type.
SQRT(double number) returns the square root of a number as a double-precision floating-point value.

EJB QL Syntax in BNF Notation

The Backus-Naur Form (BNF) was developed by John Backus and Peter Naur in 1960. Lists a few meta–symbols that the BNF uses to define the structure of a programming language.

BNF Notation

A list of BNF schemas created using the BNF symbols is given in Code Snippet 1. You can use these schemas to declare queries in EJB QL.

Source code Syntax

<EJB_QL> ::= <select_clause> <from_clause> [<where_clause>]

<from_clause> ::= 
    FROM <identification_variable_declaration>
    [, <identification_variable_declaration>]*

<identification_variable_declaration> ::= 
    <collection_member_declaration> | <range_variable_declaration>

<collection_member_declaration> ::= 
    IN(<collection_valued_path_expression>) [AS] <identifier>

<range_variable_declaration> ::= 
    <abstract_schema_name> [AS] <identifier>

<single_valued_path_expression> ::= 
    {<single_valued_navigation> | <identification_variable>} . <cmp_field> 
    | <single_valued_navigation>

<single_valued_navigation> ::= 
    <identification_variable> . [<single_valued_cmr_field>.]* <single_valued_cmr_field>

<collection_valued_path_expression> ::= 
    <identification_variable> . [<single_valued_cmr_field>.]* <collection_valued_cmr_field>

<select_clause> ::= 
    SELECT [DISTINCT] {<single_valued_path_expression> | OBJECT(<identification_variable>)}

<where_clause> ::= 
    WHERE <conditional_expression>

<conditional_expression> ::= 
    <conditional_term> | <conditional_expression> OR <conditional_term>

<conditional_term> ::= 
    <conditional_factor> | <conditional_term> AND <conditional_factor>

<conditional_factor> ::= 
    [NOT] <conditional_test>

<conditional_test> ::= 
    <conditional_primary>

<conditional_primary> ::= 
    <simple_cond_expression> | (<conditional_expression>)

<simple_cond_expression> ::= 
    <comparison_expression> | <between_expression> | <like_expression> | 
    <in_expression> | <null_comparison_expression> | 
    <empty_collection_comparison_expression> | <collection_member_expression>

<between_expression> ::= 
    <arithmetic_expression>

<in_expression> ::= 
    <single_valued_path_expression> [NOT] IN (<string_literal> [, <string_literal>]*)

<like_expression> ::= 
    <single_valued_path_expression> [NOT] LIKE <pattern_value> [ESCAPE <escape_character>]

<null_comparison_expression> ::= 
    <single_valued_path_expression> IS [NOT] NULL

<empty_collection_comparison_expression> ::= 
    <collection_valued_path_expression> IS [NOT] EMPTY

<collection_member_expression> ::= 
    {<single_valued_navigation> | <identification_variable> | <input_parameter>} 
    [NOT] MEMBER [OF] <collection_valued_path_expression>

<comparison_expression> ::= 
    <string_value> {= | <>} <string_expression> |
    <boolean_value> {= | <>} <Boolean_expression> |
    <datetime_value> {= | <> | <} <datetime_expression> |
    <entity_bean_value> {= | <>} <entity_bean_expression> |
    <arithmetic_value> <comparison_operator> <single_value_designator>

<arithmetic_value> ::= 
    <single_valued_path_expression> | <functions_returning_numerics>

<single_value_designator> ::= 
    <scalar_expression>

<comparison_operator> ::= 
    = | > | >= | < | <= | <>

<scalar_expression> ::= 
    <arithmetic_expression>

<arithmetic_expression> ::= 
    <arithmetic_term> | <arithmetic_expression> {+ | -} <arithmetic_term>

<arithmetic_term> ::= 
    <arithmetic_factor> | <arithmetic_term> {* | /} <arithmetic_factor>

<arithmetic_factor> ::= 
    {+ | -} <arithmetic_primary>

<arithmetic_primary> ::= 
    <single_valued_path_expression> | <literal> | (<arithmetic_expression>) | 
    <input_parameter> | <functions_returning_numerics>

<string_value> ::= 
    <single_valued_path_expression> | <functions_returning_strings>

<string_expression> ::= 
    <string_primary> | <input_expression>

<string_primary> ::= 
    <single_valued_path_expression> | <literal> | (<string_expression>) | 
    <functions_returning_strings>

<datetime_value> ::= 
    <single_valued_path_expression>

<datetime_expression> ::= 
    <datetime_value> | <input_parameter>

<boolean_value> ::= 
    <single_valued_path_expression>

<boolean_expression> ::= 
    <single_valued_path_expression> | <literal> | <input_parameter>

<entity_bean_value> ::= 
    <single_valued_navigation> | <identification_variable>

<entity_bean_expression> ::= 
    <entity_bean_value> | <input_parameter>

<functions_returning_strings> ::= 
    CONCAT(<string_expression>, <string_expression>) |
    SUBSTRING(<string_expression>, <arithmetic_expression>, <arithmetic_expression>)

<functions_returning_numerics> ::= 
    LENGTH(<string_expression>) |
    LOCATE(<string_expression>, <string_expression> [, <arithmetic_expression>]) |
    ABS(<arithmetic_expression>) |
    SQRT(<arithmetic_expression>)

The code below illustrates a select query in the BNF schema that returns all Branch Manager objects where the manager's Name is specified as the parameter.

SELECT OBJECT(b)
FROM BranchManager AS b
WHERE b.managerName = ?1

The code below illustrates a select query in the BNF schema that returns all the customer objects from the Customer table.

SELECT OBJECT(o)
FROM Customer o

Summary

EJB QL is a query language similar to SQL but specifically designed for use with EJB components. Unlike SQL, which operates on tables and rows, EJB QL works with enterprise beans and their relationships. It supports both string and arithmetic functions, enabling the effective manipulation of character and numeric data within queries. The syntax of EJB QL follows the Backus-Naur Form (BNF), a formal notation used to describe the structure of programming languages, and has been part of the EJB specification since version 2.0. BNF uses a set of standard symbols to define the grammar and structure, making the language both precise and structured.