JPQL 语句

1.WHERE子句示例

以下语句查询 Id 介于 100 至 200 之间的订单。 select o from Orders o where o.id between 100 and 200 以下语句查询国籍为的 'US'、'CN'或'JP' 的客户。 select c from Customers c where c.county in ('US','CN','JP') 以下语句查询手机号以139开头的客户。%表示任意多个字符序列，包括0个。 select c from Customers c where c.phone like '139%' 以下语句查询名字包含4个字符，且234位为ose的客户。_表示任意单个字符。 select c from Customers c where c.lname like '_ose' 以下语句查询电话号码未知的客户。Nul l用于测试单值是否为空。 select c from Customers c where c.phone is null 以下语句查询尚未输入订单项的订单。empty用于测试集合是否为空。 select o from Orders o where o.orderItems is empty

2.ORDER BY 子句

order by子句用于对查询结果集进行排序。和SQL的用法类似，可以用 “asc“ 和 "desc“ 指定升降序。如果不显式注明，默认为升序。 select o from Orders o order by o.id select o from Orders o order by o.address.streetNumber desc select o from Orders o order by o.customer asc, o.id desc

3.聚合查询

group by 子句用于对查询结果分组统计，通常需要使用聚合函数。常用的聚合函数主要有 AVG、SUM、COUNT、MAX、MIN 等，它们的含义与SQL相同。例如：select max(o.id) from Orders o

没有 group by 子句的查询是基于整个实体类的，使用聚合函数将返回单个结果值，可以使用Query.getSingleResult()得到查询结果。例如：

Query query = entityManager.createQuery("select max(o.id) from Orders o");
Object result = query.getSingleResult();
Long max = (Long)result;
复制代码

4.having子句

Having 子句用于对 group by 分组设置约束条件，用法与where 子句基本相同，不同是 where 子句作用于基表或视图，以便从中选择满足条件的记录；having 子句则作用于分组，用于选择满足条件的组，其条件表达式中通常会使用聚合函数。

例如，以下语句用于查询订购总数大于100的商家所售商品及数量：

select o.seller, o.goodId, sum(o.amount) from V_Orders o group by o.seller, o.goodId having sum(o.amount) > 100

having子句与where子句一样都可以使用参数

关联查询

在JPQL中，很多时候都是通过在实体类中配置实体关联的类属性来实现隐含的关联(join)查询。

例如：select o from Orders o where o.address.streetNumber=2000 上述JPQL语句编译成以下SQL时就会自动包含关联，默认为左关联。 在某些情况下可能仍然需要对关联做精确的控制。为此，JPQL 也支持和 SQL 中类似的关联语法。如： left out join / left join inner join left join / inner join fetch 其中，left join和left out join等义，都是允许符合条件的右边表达式中的实体为空。

例如，以下外关联查询可以找出所有客户实体记录，即使它未曾订货：

select c from Customers c left join c.orders o

以下内关联查询只找出所有曾订过商品的客户实体记录：

select c from Customers c inner join c.orders o

如果001号客户下过5次订单的话，以下fetch关联查询将得到 5个客户实体的引用，并且执行了 5 个订单的查询：

select c from Customers c left join fetch c.orders o where c.id=001

5.子查询

JPQL也支持子查询，在 where 或 having 子句中可以包含另一个查询。当子查询返回多于 1 个结果集时，它常出现在 any、all、exist s表达式中用于集合匹配查询。它们的用法与SQL语句基本相同。

6.UPDATE和DELETE语句

update语句用于执行数据更新操作。

主要用于针对单个实体类的批量更新 以下语句将帐户余额不足万元的客户状态设置为未偿付：update Customers c set c.status = '未偿付' where c.balance < 10000

delete语句用于执行数据更新操作。 以下语句删除不活跃的、没有订单的客户：delete from Customers c where c.status = 'inactive' and c.orders is empty

7.函数

JPQL提供了以下一些内建函数，包括字符串处理函数、算术函数和日期函数。 字符串处理函数主要有：

• concat(String s1, String s2)：字符串合并/连接函数。
• substring(String s, int start, int length)：取字串函数。
• trim([leading|trailing|both,] [char c,] String s)：从字符串中去掉首/尾指定的字符或空格。
• lower(String s)：将字符串转换成小写形式。
• upper(String s)：将字符串转换成大写形式。
• length(String s)：求字符串的长度。
• locate(String s1, String s2[, int start])：从第一个字符串中查找第二个字符串(子串)出现的位置。若未找到则返回0。

算术函数主要有 abs、mod、sqrt、size 等。Size 用于求集合的元素个数。 日期函数主要为三个，即 current_date、current_time、current_timestamp，它们不需要参数，返回服务器上的当前日期、时间和时戳。

附录：

单类测试：书籍类

package com.os.model;

import javax.persistence.*;
import java.util.Date;

@Entity
@Table(name = "jpa_book")
public class Book {

    private Integer bookId;
    private String bookName;
    private Double price;
    private String author;
    private Date createDate;

    public Book() {
    }

    public Book(String bookName, Double price, String author, Date createDate) {
        this.bookName = bookName;
        this.price = price;
        this.author = author;
        this.createDate = createDate;
    }

    @Id
    @Column(name = "book_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Integer getBookId() {
        return bookId;
    }

    public void setBookId(Integer bookId) {
        this.bookId = bookId;
    }
    @Column(name = "book_name")
    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }
    @Column(name = "create_date")
    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }
}
复制代码

测试类：充填数据

package com.os.test;

import com.os.model.Book;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import java.util.Date;

public class InitData {

    private EntityManagerFactory factory ;
    private EntityManager entityManager ;
    private EntityTransaction tx;
    @Before
    public void init(){
        factory = Persistence.createEntityManagerFactory("jpa04");
        entityManager = factory.createEntityManager();
        tx = entityManager.getTransaction();
        tx.begin();
    }
    @After
    public void close(){
        tx.commit();
        entityManager.close();
        factory.close();
    }

    @Test
    public void test01(){
        Book[] books = {
            new Book("红楼梦",88.8,"曹雪芹",new Date()),
            new Book("石头记",88.8,"曹雪芹",new Date()),
            new Book("西游记",77.7,"吴承恩",new Date()),
            new Book("水浒传",66.6,"施耐庵",new Date()),
            new Book("三国演义",55.5,"罗贯中",new Date()),
            new Book("海贼王",44.4,"小日本",new Date()),
            new Book("死神",88.8,"哈哈",new Date()),
            new Book("跑男",88.8,"呵呵",new Date())
        };

        for (Book book : books) {
            entityManager.persist(book);
        }

    }
}
复制代码