Hibernate 查询语言指南

select thing.interval.`from` from Thing thing

updateStatement
    : "UPDATE" "VERSIONED"? targetEntity setClause whereClause?

targetEntity
	: entityName variable?

setClause
	: "SET" assignment ("," assignment)*

assignment
    : simplePath "=" expression

update Person set nickName = 'Nacho' where name = 'Ignacio'

// JPA API
int updatedEntities = entityManager.createQuery(
        "update Person p set p.name = :newName where p.name = :oldName")
            .setParameter("oldName", oldName)
            .setParameter("newName", newName)
            .executeUpdate();

// Hibernate native API
int updatedEntities = session.createMutationQuery(
        "update Person set name = :newName where name = :oldName")
            .setParameter("oldName", oldName)
            .setParameter("newName", newName)
            .executeUpdate();

update versioned Book set title = :newTitle where ssn = :ssn

deleteStatement
    : "DELETE" "FROM"? targetEntity whereClause?

delete Author author where is empty author.books

insertStatement
    : "INSERT" "INTO"? targetEntity targetFields
      (queryExpression | valuesList)
      conflictClause?

targetEntity
	: entityName variable?

targetFields
	: "(" simplePath ("," simplePath)* ")"

valuesList
	: "VALUES" values ("," values)*

values
	: "(" expression ("," expression)* ")"

insert Person (id, name)
    values (100L, 'Jane Doe'), (200L, 'John Roe')

insert into Author (id, name, bio)
    select id, name, name || ' is a newcomer for ' || str(year(local date))
    from Person
    where id = :pid

conflictClause
	: ON CONFLICT conflictTarget? "DO" conflictAction

conflictTarget
	: ON CONSTRAINT identifier
	| "(" simplePath ("," simplePath)* ")"

conflictAction
	: "NOTHING"
	| "UPDATE" setClause whereClause?

insert Person (ssn, name, phone)
    values ('116-76-1234', 'Jane Doe', '404 888 4319')
    on conflict (ssn) do update
        set phone = excluded.phone

selectStatement
	: queryExpression

queryExpression
	: withClause? orderedQuery (setOperator orderedQuery)*

orderedQuery
	: (query | "(" queryExpression ")") queryOrder?

query
	: selectClause fromClause? whereClause? (groupByClause havingClause?)?
	| fromClause whereClause? (groupByClause havingClause?)? selectClause?
	| whereClause

queryOrder
	: orderByClause limitClause? offsetClause? fetchClause?

fromClause
	: "FROM" entityWithJoins ("," entityWithJoins)*

entityWithJoins
	: fromRoot (join | crossJoin | jpaCollectionJoin)*

fromRoot
	: entityName variable?
	| "LATERAL"? "(" subquery ")" variable?

join
	: joinType "JOIN" "FETCH"? joinTarget joinRestriction?

joinTarget
	: path variable?
	| "LATERAL"? "(" subquery ")" variable?

withClause
	: "WITH" cte ("," cte)*
	;

from Book

select book from Book book

// result type Book, only the Book selected
List<Book> books =
        session.createQuery("from Book join authors", Book.class)
            .getResultList();
for (Book book: books) {
    ...
}

// result type Object[], both Book and Author selected
List<Object[]> booksWithAuthors =
       session.createQuery("from Book join authors", Book.class, Object[].class)
            .getResultList();
for (var bookWithAuthor: booksWithAuthors) {
    Book book = (Book) bookWithAuthor[0];
    Author author = (Author) bookWithAuthor[1];
    ...
}

select local datetime

List<String> results =
        entityManager.createQuery("select title from Book", String.class)
            .getResultList();

List<Object[]> results =
        entityManager.createQuery("select title, left(book.text, 200) from Book",
                                  Object[].class)
            .getResultList();
for (var result : results) {
	String title = (String) result[0];
	String preamble = (String) result[1];
}

List<Tuple> tuples =
        entityManager.createQuery("select title as title, left(book.text, 200) as preamble from Book",
                                  Tuple.class)
            .getResultList();
for (Tuple tuple : tuples) {
    String title = tuple.get("title", String.class);
    String preamble = tuple.get("preamble", String.class);
}

var results =
        entityManager.createQuery("select title as title, left(book.text, 200) as preamble from Book",
                                  Map.class)
            .getResultList();
for (var map : results) {
    String title = (String) map.get("title");
    String preamble = (String) map.get("preamble");
}

var results =
        entityManager.createQuery("select title, left(book.text, 200) from Book",
                                  List.class)
            .getResultList();
for (var list : results) {
    String title = (String) list.get(0);
    String preamble = (String) list.get(1);
}

record BookSummary(String title, String summary) {}

List<BookSummary> results =
        entityManager.createQuery("select title, left(book.text, 200) from Book",
                                  BookSummary.class)
            .getResultList();
for (var result : results) {
    String title = result.title();
    String preamble = result.summary();
}

record BookSummary(String title, String summary) {}

List<BookSummary> results =
        entityManager.createQuery("select new BookSummary(title, left(book.text, 200)) from Book",
                                  BookSummary.class)
            .getResultList();
for (var result : results) {
    String title = result.title();
    String preamble = result.summary();
}

selection
    : (expression | instantiation) alias?

instantiation
    : "NEW" instantiationTarget "(" selection ("," selection)* ")"

alias
    : "AS"? identifier

select 'hello world'

from Book where title like 'Ender''s'

select "hello\tworld"

from Book where price < 100.0

select author, count(book)
from Author as author
    join author.books as book
group by author
having count(book) > 10

select start, end, start - 30 minute from Event

from Book where status <> OUT_OF_PRINT

from Book where type in (BOOK, MAGAZINE)

update Book set status = OUT_OF_PRINT

from Book
order by
    case type
    when BOOK then 1
    when MAGAZINE then 2
    when JOURNAL then 3
    else 4
    end

select java.lang.Math.PI

from Payment as payment
where type(payment) = CreditCardPayment

select book.title || ' by ' || listagg(author.name, ' & ')
from Book as book
    join book.authors as author
group by book

select (1.0 + :taxRate) * sum(item.book.price * item.quantity)
from Order as ord
    join ord.items as item
where ord.id = :oid

select payment
from Payment as payment
where type(payment) = CreditCardPayment

select payment
from Payment as payment
where length(treat(payment as CreditCardPayment).cardNumber)
        between 16 and 20

select cast(id as String) from Order

select str(id) from Order

select coalesce(author.nomDePlume, person.name)
from Author as author
    join author.person as person

select ifnull(author.nomDePlume, person.name)
from Author as author
    join author.person as person

select ifnull(nullif(author.nomDePlume, person.name), 'Real name')
from Author as author
    join author.person as person

from Order where extract(date from created) = local date

select extract(year from created), extract(month from created) from Order

select year(created), month(created) from Order

select format(local datetime as 'yyyy-MM-dd HH:mm:ss')

select trunc(local datetime, hour)

select concat(book.title, ' by ', listagg(author.name, ' & '))
from Book as book
    join book.authors as author
group by book

select locate('Hibernate', title) from Book

select position('Hibernate' in title) from Book

select substring(title, 1, position(' for Dummies' in title)) from Book         /* JPQL-style */

select substring(title from 1 for position(' for Dummies' in title)) from Book  /* ANSI SQL-style */

select trim(title) from Book

select trim(trailing ' ' from text) from Book

"TRIM" "(" (("LEADING" | "TRAILING" | "BOTH")? trimCharacter? "FROM")? expression ")" ;

select concat(pad(b.title with 40 trailing '.'),
              pad(a.firstName with 10 leading),
              pad(a.lastName with 10 leading))
from Book as b
    join b.authors as a

"PAD" "(" expression "WITH" expression ("LEADING" | "TRAILING") padCharacter? ")"

select title, element(tags) from Book

select title from Book where 'hibernate' in elements(tags)

select name, size(books) from Author

select id(book), index(ed), element(ed)
from Book book as book
    join book.editions as ed

select key(entry), value(entry)
from Thing as thing
    join thing.entries as entry

from Book where price < 1.0

from Author as author where author.nomDePlume <> author.person.name

select id, total
from (
    select ord.id as id, sum(item.book.price * item.quantity) as total
    from Order as ord
        join Item as item
    group by ord
)
where total > 100.0

from Book where price between 1.0 and 100.0

from Author where nomDePlume is not null

from Book where discontinued is not true

from Author where books is empty

select author, book
from Author as author, Book as book
where author member of book.authors

expression "NOT"? ("LIKE" | "ILIKE") expression ("ESCAPE" character)?

from Book where title not like '% for Dummies'

expression "NOT"? "IN" inList

inList
	: collectionQuantifier "(" simplePath ")"
	| "(" (expression ("," expression)*)? ")"
	| "(" subquery ")"
	| parameter

from Payment as payment
where type(payment) in (CreditCardPayment, WireTransferPayment)

from Author as author
where author.person.name in (select name from OldAuthorData)

from Book as book
where :edition in elements(book.editions)

from Author as author
where (author.person.name, author.person.birthdate)
    in (select name, birthdate from OldAuthorData)

from Publisher pub where 100.0 < all(select price from pub.books)

from Publisher pub where :title = some(select title from pub.books)

from Author where exists elements(books)

from Author as author
where exists (
    from Order join items
    where book in elements(author.books)
)

select book from Book as book

select doc from org.hibernate.example.AbstractDocument as doc where doc.text like :pattern

select a, b
from Author a, Author b, Book book
where a in elements(book.authors)
  and b in elements(book.authors)

select a, b
from Book book
    cross join Author a
    cross join Author b
where a in elements(book.authors)
  and b in elements(book.authors)

select book.title, publisher.name
from Book book, Publisher publisher
where book.publisher = publisher
  and book.title like :titlePattern

select payment from Payment as payment

select id, total
from (
    select ord.id as id, sum(item.book.price * item.quantity) as total
    from Order as ord
        join Item as item
    group by ord
)
where total > 100.0

select stuff.id, stuff.total
from (
    select ord.id as id, sum(item.book.price * item.quantity) as total
    from Order as ord
        join Item as item
    group by ord
) as stuff
where total > 100.0

select book.title, publisher.name
from Book book
    join Publisher publisher
        on book.publisher = publisher
where book.title like :titlePattern

from Book as book
    join book.publisher as publisher
    join book.authors as author
where book.title like :titlePattern
select book.title, author.name, publisher.name

from Book as book
    left join book.publisher as publisher
    join book.authors as author
where book.title like :titlePattern
select book.title, author.name, ifnull(publisher.name, '-')

from Book as book
    left join book.publisher as publisher
        with publisher.closureDate is not null
    left join book.authors as author
        with author.type <> COLLABORATION
where book.title like :titlePattern
select book.title, author.name, publisher.name

select book
from Book as book
    left join fetch book.publisher
    join fetch book.authors

from Order as ord
    join treat(ord.payments as CreditCardPayment) as creditCardPayment
where length(creditCardPayment.cardNumber) between 16 and 20
select ord.id, creditCardPayment.cardNumber, creditCardPayment.amount

from Phone as phone
    left join (
        select call.duration as duration, call.phone.id as cid
        from Call as call
        order by call.duration desc
        limit 1
    ) as longest on cid = phone.id
where phone.number = :phoneNumber
select longest.duration

from Phone as phone
    left join lateral (
       select call.duration as duration
       from phone.calls as call
       order by call.duration desc
       limit 1
    ) as longest
where phone.number = :phoneNumber
select longest.duration

from Book as book
where book.publisher.name like :pubName

select book
from Book as book
    join book.publisher as pub
where pub.name like :pubName

select book
from Book as book
where book.publisher.name like :pubName
  and book.publisher.closureDate is null

select book
from Book as book
    join book.publisher as pub
where pub.name like :pubName
  and pub.closureDate is null

select publisher.name, author.name
from Publisher as publisher
    join publisher.books as book
    join book.authors author
where author.name like :namePattern

select book.title, author.name, index(author)
from Book as book
    join book.authors as author

select publisher.name, leadAuthor.name
from Publisher as publisher
    join publisher.books as book
    join book.authors leadAuthor
where leadAuthor.name like :namePattern
  and index(leadAuthor) == 0

select book.title, element(book.authors).name, index(book.authors)
from Book book

select publisher.name, book.authors[0].name
from Publisher as publisher
    join publisher.books as book
where book.authors[0].name like :namePattern

identifier | INTEGER_LITERAL | expression

select book.isbn,
    sum(quantity) as totalSold,
    sum(quantity * book.price) as totalBilled
from Item
where book.isbn = :isbn

select book.isbn,
    year(order.dateTime) as year,
    sum(quantity) as yearlyTotalSold,
    sum(quantity * book.price) as yearlyTotalBilled
from Item
where book.isbn = :isbn
group by year(order.dateTime)