跳转至内容

Java 持久化/JPQL BNF

Add links
来自维基教科书，开放世界中的开放书籍

JPQL BNF

[编辑 | 编辑源代码]

以下定义了 JPQL 查询语言的结构。有关更多示例和用法，请参阅 查询。

 | = or
 [] = optional
 * = repeatable (zero or more times)
 {} = mandatory

QL_statement ::= select_statement | update_statement | delete_statement

选择

[编辑 | 编辑源代码]
Select employee from Employee employee join employee.address address 
where address.city = :city and employee.firstName like :name order by employee.firstName

select_statement ::= select_clause from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]

从

[编辑 | 编辑源代码]
FROM Employee employee JOIN FETCH employee.address 
LEFT OUTER JOIN FETCH employee.phones JOIN employee.manager manager, Employee ceo

from_clause ::= FROM identification_variable_declaration {, {identification_variable_declaration | collection_member_declaration}}*

identification_variable_declaration ::= range_variable_declaration { join | fetch_join }*

range_variable_declaration ::= abstract_schema_name [AS] identification_variable

join ::= join_spec join_association_path_expression [AS] identification_variable

fetch_join ::= join_spec FETCH join_association_path_expression

association_path_expression ::= collection_valued_path_expression | single_valued_association_path_expression

join_spec::= [ LEFT [OUTER] | INNER ] JOIN

join_association_path_expression ::= join_collection_valued_path_expression | join_single_valued_association_path_expression

join_collection_valued_path_expression::= identification_variable.collection_valued_association_field

join_single_valued_association_path_expression::= identification_variable.single_valued_association_field

collection_member_declaration ::= IN (collection_valued_path_expression) [AS] identification_variable

single_valued_path_expression ::= state_field_path_expression | single_valued_association_path_expression

state_field_path_expression ::= {identification_variable | single_valued_association_path_expression}.state_field

single_valued_association_path_expression ::= identification_variable.{single_valued_association_field.}* single_valued_association_field

collection_valued_path_expression ::= identification_variable.{single_valued_association_field.}*collection_valued_association_field

state_field ::= {embedded_class_state_field.}*simple_state_field

选择子句

[编辑 | 编辑源代码]
SELECT employee.id, employee.phones

SELECT DISTINCT employee.address.city, NEW com.acme.EmployeeInfo(AVG(employee.salary), MAX(employee.salary))

select_clause ::= SELECT [DISTINCT] select_expression {, select_expression}*

select_expression ::= single_valued_path_expression | aggregate_expression | identification_variable | OBJECT(identification_variable) | constructor_expression

constructor_expression ::= NEW constructor_name ( constructor_item {, constructor_item}* )

constructor_item ::= single_valued_path_expression | aggregate_expression

aggregate_expression ::= { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression | single_valued_association_path_expression)

哪里

[编辑 | 编辑源代码]
WHERE employee.firstName = :name AND employee.address.city LIKE 'Ott%' ESCAPE '/' 
OR employee.id IN (1, 2, 3) AND (employee.salary * 2) > 40000

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

between_expression ::= arithmetic_expression [NOT] BETWEEN arithmetic_expression AND arithmetic_expression | string_expression [NOT] BETWEEN string_expression AND string_expression | datetime_expression [NOT] BETWEEN datetime_expression AND datetime_expression

in_expression ::= state_field_path_expression [NOT] IN ( in_item {, in_item}* | subquery)

in_item ::= literal | input_parameter

like_expression ::= string_expression [NOT] LIKE pattern_value [ESCAPE escape_character]

null_comparison_expression ::= {single_valued_path_expression | input_parameter} IS [NOT] NULL

empty_collection_comparison_expression ::= collection_valued_path_expression IS [NOT] EMPTY

collection_member_expression ::= entity_expression [NOT] MEMBER [OF] collection_valued_path_expression

exists_expression::= [NOT] EXISTS (subquery)

all_or_any_expression ::= { ALL | ANY | SOME} (subquery)

comparison_expression ::= string_expression comparison_operator {string_expression | all_or_any_expression} | boolean_expression { =|<>} {boolean_expression | all_or_any_expression} | enum_expression { =|<>} {enum_expression | all_or_any_expression} | datetime_expression comparison_operator {datetime_expression | all_or_any_expression} | entity_expression { = | <> } {entity_expression | all_or_any_expression} | arithmetic_expression comparison_operator {arithmetic_expression | all_or_any_expression}

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

arithmetic_expression ::= simple_arithmetic_expression | (subquery)

simple_arithmetic_expression ::= arithmetic_term | simple_arithmetic_expression { + | - } arithmetic_term

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

arithmetic_factor ::= [{ + | - }] arithmetic_primary

arithmetic_primary ::= state_field_path_expression | numeric_literal | (simple_arithmetic_expression) | input_parameter | functions_returning_numerics | aggregate_expression

string_expression ::= string_primary | (subquery)

string_primary ::= state_field_path_expression | string_literal | input_parameter | functions_returning_strings | aggregate_expression

datetime_expression ::= datetime_primary | (subquery)

datetime_primary ::= state_field_path_expression | input_parameter | functions_returning_datetime | aggregate_expression

boolean_expression ::= boolean_primary | (subquery)

boolean_primary ::= state_field_path_expression | boolean_literal | input_parameter |

enum_expression ::= enum_primary | (subquery)

enum_primary ::= state_field_path_expression | enum_literal | input_parameter |

entity_expression ::= single_valued_association_path_expression | simple_entity_expression

simple_entity_expression ::= identification_variable | input_parameter

功能

[编辑 | 编辑源代码]
LENGTH(SUBSTRING(UPPER(CONCAT('FOO', :bar)), 1, 5))

functions_returning_numerics::= LENGTH(string_primary) | LOCATE(string_primary, string_primary[, simple_arithmetic_expression]) | ABS(simple_arithmetic_expression) | SQRT(simple_arithmetic_expression) | MOD(simple_arithmetic_expression, simple_arithmetic_expression) | SIZE(collection_valued_path_expression)

functions_returning_datetime ::= CURRENT_DATE| CURRENT_TIME | CURRENT_TIMESTAMP

functions_returning_strings ::= CONCAT(string_primary, string_primary) | SUBSTRING(string_primary, simple_arithmetic_expression, simple_arithmetic_expression)| TRIM([[trim_specification] [trim_character] FROM] string_primary) | LOWER(string_primary) | UPPER(string_primary)

trim_specification ::= LEADING | TRAILING | BOTH

分组依据

[编辑 | 编辑源代码]
GROUP BY employee.address.country, employee.address.city HAVING COUNT(employee.id) > 500

groupby_clause ::= GROUP BY groupby_item {, groupby_item}*

groupby_item ::= single_valued_path_expression | identification_variable

having_clause ::= HAVING conditional_expression

排序依据

[编辑 | 编辑源代码]
ORDER BY employee.address.country, employee.address.city DESC

orderby_clause ::= ORDER BY orderby_item {, orderby_item}*

orderby_item ::= state_field_path_expression [ ASC | DESC ]

子查询

[编辑 | 编辑源代码]
WHERE employee.salary = (SELECT MAX(wellPaid.salary) FROM Employee wellPaid)

subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause]

subquery_from_clause ::= FROM subselect_identification_variable_declaration {, subselect_identification_variable_declaration}* subselect_identification_variable_declaration ::= identification_variable_declaration | association_path_expression [AS] identification_variable | collection_member_declaration

simple_select_clause ::= SELECT [DISTINCT] simple_select_expression

simple_select_expression::= single_valued_path_expression | aggregate_expression | identification_variable

更新

[编辑 | 编辑源代码]
UPDATE Employee e SET e.salary = e.salary * 2 WHERE e.address.city = :city

update_statement ::= update_clause [where_clause]

update_clause ::= UPDATE abstract_schema_name [[AS] identification_variable] SET update_item {, update_item}*

update_item ::= [identification_variable.]{state_field | single_valued_association_field} = new_value

new_value ::= simple_arithmetic_expression | string_primary | datetime_primary | boolean_primary | enum_primary | simple_entity_expression | NULL

删除

[编辑 | 编辑源代码]
DELETE FROM Employee e WHERE e.address.city = :city

delete_statement ::= delete_clause [where_clause]

delete_clause ::= DELETE FROM abstract_schema_name [[AS] identification_variable]

文字

[编辑 | 编辑源代码]

通常最好使用参数在查询中定义数据值，使用语法 :parameter 或 ?。JPQL 还允许使用文字在 JPQL 查询中内联数据值。

JPQL 为文字定义了以下语法

  • 字符串 - '字符串' - "Select e from Employee e where e.name = 'Bob'"
    • 要定义字符串中的 '（引号）字符，请将引号用双引号括起来，例如：'Baie-D''Urfé'。
  • 整数 - 数字 - "Select e from Employee e where e.id = 1234"
  • 长整型 - +|-数字L - "Select e from Employee e where e.id = 1234L"
  • 浮点型 - +|-数字.小数指数F - "Select s from Stat s where s.ratio > 3.14F"
  • 双精度浮点型 - +|-数字.小数指数D - "Select s from Stat s where s.ratio > 3.14e32D"
  • 布尔型 - TRUE | FALSE - "Select e from Employee e where e.active = TRUE"
  • 日期 - {d'yyyy-mm-dd'} - "Select e from Employee e where e.startDate = {d'2012-01-03'}"
  • 时间 - {t'hh:mm:ss'} - "Select e from Employee e where e.startTime = {t'09:00:00'}"
  • 时间戳 - {ts'yyy-mm-dd hh:mm:ss.nnnnnnnnn'} - "Select e from Employee e where e.version = {ts'2012-01-03 09:00:00.000000001'}"
  • 枚举 - 包名.类名.枚举值 - "Select e from Employee e where e.gender = org.acme.Gender.MALE"
  • 空 - NULL - "Update Employee e set e.manager = NULL where e.manager = :manager"

JPA 2.0 新增功能

[编辑 | 编辑源代码]

以下语法在 JPA 2.0 中新增。


type_discriminator ::= TYPE(identification_variable | single_valued_object_path_expression | input_parameter)

允许查询对象的类型/类。
SELECT p from Project p where TYPE(p) in (LargeProject, SmallProject)


qualified_identification_variable ::= KEY(identification_variable) | VALUE(identification_variable) | ENTRY(identification_variable)

允许选择 Map 的键、值和 Map 条目。
SELECT ENTRY(e.contactInfo) from Employee e


general_identification_variable ::= identification_variable | KEY(identification_variable) | VALUE(identification_variable)

允许查询 Map 的键和值。
SELECT e from Employee e join e.contactInfo c where KEY(c) = 'Email' and VALUE(c) = '[email protected]'


in_item ::= literal | single_valued_input_parameter

允许为 IN 提供集合参数。
SELECT e from Employee e where e.id in :param


functions_returning_strings ::= CONCAT(string_primary, string_primary {, string_primary}*)

允许使用多个参数进行 CONCAT。
SELECT e from Employee e where CONCAT(e.address.street, e.address.city, e.address.province) = :address


SUBSTRING(string_primary, simple_arithmetic_expression [, simple_arithmetic_expression])

允许使用单个参数进行 SUBSTRING。
SELECT e from Employee e where SUBSTRING(e.name, 3) = 'Mac'


case_expression ::= general_case_expression | simple_case_expression |coalesce_expression | nullif_expression general_case_expression::= CASE when_clause {when_clause}* ELSE scalar_expression END when_clause::= WHEN conditional_expression THEN scalar_expression simple_case_expression::= CASE case_operand simple_when_clause {simple_when_clause}* ELSE scalar_expression END case_operand::= state_field_path_expression | type_discriminator simple_when_clause::= WHEN scalar_expression THEN scalar_expression coalesce_expression::= COALESCE(scalar_expression {, scalar_expression}+) nullif_expression::= NULLIF(scalar_expression, scalar_expression)

允许使用 CASE、COALESCE 和 NULLIF 函数。
SELECT e.name, CASE WHEN (e.salary >= 100000) THEN 1 WHEN (e.salary < 100000) THEN 2 ELSE 0 END from Employee e


functions_returning_numerics ::= ... | INDEX(identification_variable)

允许查询索引列表映射中的索引。
SELECT e from Employee e join e.phones p where INDEX(p) = 1 and p.areaCode = '613'


join_collection_valued_path_expression ::= identification_variable. {single_valued_embeddable_object_field.}* collection_valued_field join_single_valued_path_expression ::= variable.{single_valued_embeddable_object_field.}*single_valued_object_field

允许在联接上使用嵌套点表示法。
SELECT p from Employee e join e.employeeDetails.phones p where e.id = :id


select_item ::= select_expression [[AS] result_variable]

允许在 select 中使用 AS 选项。
SELECT AVG(e.salary) AS s, e.address.city from Employee e group by e.address.city order by s


literalTemporal = DATE_LITERAL | TIME_LITERAL | TIMESTAMP_LITERAL

允许使用 JDBC 日期/时间转义语法。
SELECT e from Employee e where e.startDate > {d'1990-01-01'}
从 "https://wikibooks.cn/w/index.php?title=Java_Persistence/JPQL_BNF&oldid=3677282" 检索
华夏公益教科书