• 1. 第30章 JPA QL教材:Java EE实用教程
  • 2. 主要内容30.1语句的类型 30.2 简单无条件查询 30.3 具有简单查询条件的查询 30.4 带有关系的查询 30.5 使用子查询 30.6 查询中使用函数 30.7 ORDER BY子句 30.8 GROUP BY和HAVING 30.9 SELECT子句完整语法
  • 3. 30.1语句的类型分3种类型: select语句 select statement ::= select_clause from_clause [where_clause][groupby_clause][having_clause][orderby_clause] update语句 update_statement :: = update_clause [where_clause] delete语句 delete_statement :: = delete_clause [where_clause]
  • 4. 30.2 简单无条件查询30.2.1 查询所有的对象 select b from Book b from指出查询的类型,这里要查询的是Book实体,Book称为实体的抽象模式类型。
  • 5. 30.2 简单无条件查询30.2.2 查询所有对象的某个属性 如果要查询实体的属性,需要在select语句中指出属性的名字。 例1:查询图书名字。 select book.bname from Book book 例2:查询书号和书名。 select book.bid,book.bname from Book book
  • 6. 30.2 简单无条件查询30.2.3 使用distinct 如果要去掉查询中的重复信息,可以使用distinct,基本格式如下: select distinct *.属性 例:查询所有图书的名字,重复的书名只显示一次。 select distinct book.bname from Book book
  • 7. 30.2 简单无条件查询30.2.4 在查询中使用聚集函数 可以用于SELECT子句的聚集函数: AVG、COUNT、MAX、MIN和SUM。 对于所有的聚集函数,除了COUNT,参数必须是某个属性,COUNT的参数可以是实体、实体的属性或者关联实体。SUM和AVG函数的参数必须是数字。MAX和MIN函数的参数必须是可排序的状态域类型
  • 8. 30.2 简单无条件查询30.2.4 在查询中使用聚集函数 使用聚集函数的查询的结果所包含的类型如下: COUNT返回Long类型。 MAX和MIN返回它所应用的状态域的类型。 AVG返回Double类型。 对于SUM,如果用于整数类型(不包括BigInteger),返回Long;如果用于浮点型,返回Double;如果用于BigInteger类型,返回BigInteger;如果用于BigDecimal,返回BigDecimal。 如果使用SUM、AVG、MAX或者MIN,并且聚集函数没有用于任何值,聚集函数的结果为null。 如果使用COUNT,并且COUNT没有用于任何值,聚集函数的结果为0。
  • 9. 30.2 简单无条件查询30.2.4 在查询中使用聚集函数 例1:查询图书的数量。 String ql = "select count(book) from Book book"; Query query = em.createQuery(ql); Long count = (Long) query.getSingleResult(); out.println("共有" + count + "本书!"); 例2:查询最贵的书的价格。 ql = "select max(book.price) from Book book"; query = em.createQuery(ql); Float price = (Float) query.getSingleResult(); out.println("最贵的书的价格:" + price);
  • 10. 30.2 简单无条件查询30.2.5 把查询结果封装成对象 查询的多个属性封装成对象,可以通过select语句中new符号完成。 例:把查询出来的书号和书名封装成图书对象。 String ql = "select new javaeebook.jpa.basic.entity.Book(book.bid, book.bname) from Book book"; 注意:在使用的时候要保证有相应的构造方法。
  • 11. 30.3 具有简单查询条件的查询30.3.1 查询条件中的常量 字符串常量使用单引号括起来:‘Duke’。如果字符串常量包含了单引号,使用两个单引号表示一个单引号:'Duke''s'。 数字符号:精确的和大约的,例如30,30.3 逻辑符号是TRUE或者FALSE,不区分大小写。 例1:查询男用户。 SELECT user FROM User user where user.sex=’男’ 例2:查询年龄小于20岁的用户。 SELECT user FROM User user where user.age<20
  • 12. 30.3 具有简单查询条件的查询30.3.2 各种运算法的使用 算术操作符包括: +,- 单目运算符; *,/ 乘除; +,- 加减。 比较运算符包括:==、>、 >=、<、<=、<>、[NOT] BETWEEN,[NOT] LIKE,[NOT] IN,IS [NOT] NULL,IS [NOT] EMPTY,[NOT] MEMBER [OF]。 逻辑操作符包括:NOT、AND和OR。
  • 13. 30.3 具有简单查询条件的查询30.3.3 使用关系运算符查询 例1:查询价格小于30的书。 select book from Book book where book.price<30 例2:查询价格大于40的书。 select book from Book book where book.price>40
  • 14. 30.3 具有简单查询条件的查询30.3.4 使用BETWEEN操作符查询 例:查询价格位于30和40之间的书。 select book from Book book where book.price between 30 and 40
  • 15. 30.3 具有简单查询条件的查询30.3.5 使用Like查询 string_expression [NOT] LIKE pattern_value [ESCAPE escape_character] string_expression的值必须是字符串。 pattern_value是一个字符串或者值为字符串的输入参数,在这个字符串中下划线(“_”)表示任意单个字符,百分号(“%”)表示任意长度的字符串(包含空字符串),其他的字符表示他们自己。 escape_character是一个单字符的字符串常量或者值为字符的输入参数,用于转义特殊意义的下划线和百分号。
  • 16. 30.3 具有简单查询条件的查询30.3.5 使用Like查询 例1:查询号码以12开头以3结尾的地址。 select address from Address address where address.phone LIKE ‘12%3’ 如果号码是“123”“12993”结果为true,“1234”结果为false。
  • 17. 30.3 具有简单查询条件的查询30.3.6 使用IN查询 IN操作符判断某个属性值是否在某个集合中,在条件表达式中使用比较操作符[NOT]IN的语法如下: in_expression ::= state_field_path_expression [NOT ]IN ( in_item {, in_item}* | subquery) in_item ::= literal | input_parameter state_field_path_expression的值必须是一个字符串、数字或者枚举类型。 常量和输出参数的类型必须与state_field_path_expression的抽象模式类型一致。 子查询的结果的类型也必须与state_field_path_expression的抽象模式类型一致。 在IN后面的括号中至少要有一个元素。
  • 18. 30.3 具有简单查询条件的查询30.3.6 使用IN查询 例:查找清华大学出版社出版的或者电子工业出版社出版的图书。 select book from Book book where book.press in ('清华大学出版社','电子工业出版社')
  • 19. 30.3 具有简单查询条件的查询30.3.7 使用IS[NOT] NULL查询 例:查询作者为空的图书。 select book from Book book where book.author is null
  • 20. 30.3 具有简单查询条件的查询30.3.8 使用ALL和ANY操作符 ALL条件表达式,如果对于子查询的结果中的所有值,比较操作的值为true,或者子查询是空的,ALL表达式的值为true。其他情况为false。 ANY条件表达式,如果对于子查询的结果中的某些值,比较操作的值为true,ANY表达式的值为true。其他情况结果都为false。
  • 21. 30.3 具有简单查询条件的查询30.3.8 使用ALL和ANY操作符 例1:查找价格最高的书。 select book from Book book where book.price>=all( select book2.price from Book book2 ) 例2:查找价格不是最低的书 select book from Book book where book.price>any( select book2.price from Book book2 )
  • 22. 30.3 具有简单查询条件的查询30.3.9 空集合比较表达式 在条件表达式中IS EMPTY比较操作符语法如下: collection_valued_path_expressionIS [NOT ]EMPTY 这个表达式用于测试表达式表示的集合是否为空。 例如: SELECT o FROM Order o WHERE o.lineItems IS EMPTY
  • 23. 30.3 具有简单查询条件的查询30.3.10 集合成员表达式 使用MEMBER OF比较操作符; 基本语法如下: entity_expression [NOT] MEMBER [OF] collection_valued_path_expression 如果member后面是一个空集合,MEMBER OF表达式的结果为FALSE,NOT MEMBER OF表达式的结果为TRUE。
  • 24. 30.3 具有简单查询条件的查询30.3.11 Exists表达式 Exists是一个断言,只有当子查询包含一个或者多个值的时候,结果为true,否则为false。 例,查询配偶也是雇员的雇员。 SELECT DISTINCT emp FROM Employee emp WHERE EXISTS ( SELECT spouseEmp FROM Employee spouseEmp WHERE spouseEmp = emp.spouse)
  • 25. 30.4 带有关系的查询30.4.1 查询关联实体或关联实体的属性 查找关联实体使用“.”操作符,与查询实体的属性相同。 例:查询所有订单所属的州。 SELECT DISTINCT o.shippingAddress.state FROM Order o
  • 26. 30.4 带有关系的查询30.4.2 在查询条件中使用关联实体或者关联实体的属性 例1:查询所有需要运送到California的订单: SELECT o FROM Order o WHERE o.shippingAddress.state = ‘CA’ 例2:查询所有有订单项的订单: SELECT o FROM Order o WHERE o.lineItems IS NOT EMPTY
  • 27. 30.4 带有关系的查询30.4.3 集合成员声明 当关联的实体是集合的时候就不能在SELECT中或者WHERE中使用“.”操作符继续访问关联实体的属性, 例如: SELECT o FROM Order o WHERE o.lineItems.product.productType = ‘office_supplies’ 上面的语句中o关联的lineItems是集合,所以后面的.product就是非法的,这时候需要声明集合成员。
  • 28. 30.4 带有关系的查询30.4.3 集合成员声明 集合成员声明的标识变量使用保留字IN来声明。 上面的例子可以改成: SELECT DISTINCT o FROM Order o, IN(o.lineItems) l WHERE l.product.productType = ‘office_supplies’
  • 29. 30.4 带有关系的查询30.4.4 Joins 内连接(关系连接) 内连接操作的语法如下: [INNER ]JOIN join_association_path_expression [AS ] identification_variable 例如,通过顾客和订单的关系进行连接。这种类型的连接典型的等效于通过数据库中的外键进行的连接。 SELECT c FROM Customer c JOIN c.orders o WHERE c.status = 1 查询的结果是至少有一个订单的顾客。
  • 30. 30.4 带有关系的查询30.4.4 Joins 内连接(关系连接) 查询包含“Java EE 实用教程”的所有订单: SELECT DISTINCT o FROM Order o JOIN o.lineItems l WHERE l.product.type = ‘book’ AND l.product.name = ‘Java EE 实用教程’
  • 31. 30.4 带有关系的查询30.4.4 Joins 外连接:LEFT JOIN或者LEFT OUTER JOIN。 基本语法: LEFT [OUTER ]JOIN join_association_path_expression [AS ] identification_variable 例如: SELECT c FROM Customer c LEFT JOIN c.orders o WHERE c.status = 1
  • 32. 30.4 带有关系的查询30.4.4 Joins Fetch Joins FETCH JOIN能够在执行的查询时候得到关联实体。 语法如下: fetch_join ::= [LEFT [OUTER ] |INNER ]JOIN FETCH join_association_path_expression 例:查询所有部门和员工 SELECT d FROM Department d LEFT JOIN FETCH d.employees WHERE d.deptno = 1
  • 33. 30.5 使用子查询子查询可以用于WHERE或者HAVING子句。 例:查询订单数大于10的客户信息。 SELECT c FROM Customer c WHERE (SELECT COUNT(o) FROM c.orders o) > 10
  • 34. 30.6 查询中使用函数30.6.1 字符串函数 字符串函数包括: CONCAT 函数把两个字符串合并成一个。 SUBSTRING 函数返回第一个参数所指定的字符串的子串。 TRIM 函数从字符串的开头和(或)结尾处去掉特定的字符。可以指定字符,默认空格和空白。可以只删除字符串前面的字符。也可以删除字符串后面的字符。默认值是都删除。 LOWER 和UPPER 函数把字符串转换成相应的小写或者大写。 LENGTH 函数返回字符串的长度,结果为整数。 LOCATE 函数返回给定字符串在另一个字符串中的位置。
  • 35. 30.6 查询中使用函数30.6.2 算术函数 算术函数包括: ABS函数计算一个数字表达式,返回一个和参数一致的数字。 MOD 函数返回第一个参数被第二个参数除的余数。 SQRT函数返回一个数字的平方根。 SIZE函数返回给定集合中元素的个数。
  • 36. 30.6 查询中使用函数30.6.3 日期函数 日期函数返回数据库服务器上的当前日期、时间和时间戳。 日期函数的语法如下: CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP
  • 37. 30.7 ORDER BY子句ORDER BY子句允许对查询返回的对象或者值进行排序。 ORDER BY子句中出现的属性应该出现在SELECT字句中; 关键字ASC指定按照升序排列;关键字DESC指定按照降序进行排列。默认值是按照升序。 使用SQL规则对null值进行排序:所有的null值出现在所有的非null值的前面,或者所有的null值出现在所有的非null值的后面。
  • 38. 30.7 ORDER BY子句例: SELECT o FROM Customer c JOIN c.orders o JOIN c.address a WHERE a.state = ‘CA’ ORDER BY o.quantity, o.totalcost 错误用法(p.price没有出现在select中) SELECT p.product_name FROM Order o JOIN o.lineItems l JOIN l.product p JOIN o.customer c WHERE c.lastname = ‘Smith’ AND c.firstname = ‘John’ ORDER BY p.price
  • 39. 30.8 GROUP BY和HAVINGGROUP BY结构能够根据一组属性对结果进行聚集。 HAVING结构能够指定条件进一步限定查询的结果。 当使用GROUP BY的时候,任何出现在SELECT中的项必须出现在GROUP BY中。 HAVING子句只能使用GROUP BY中的项或之上的聚集函数。
  • 40. 30.8 GROUP BY和HAVING例: SELECT c.status, avg(c.filledOrderCount), count(c) FROM Customer c GROUP BY c.status HAVING c.status IN (1, 2)
  • 41. 30.9 SELECT子句完整语法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)
  • 42. Java EE实用教程(第二版)——电子工业出版社——李绪成——http://blog.csdn.net/javaeeteacher小结30.1语句的类型 30.2 简单无条件查询 30.3 具有简单查询条件的查询 30.4 带有关系的查询 30.5 使用子查询 30.6 查询中使用函数 30.7 ORDER BY子句 30.8 GROUP BY和HAVING 30.9 SELECT子句完整语法
  • 43. 谢谢!