from eg.Cat
from Cat
from Cat as cat
from Cat cat
from Formula, Parameter
from Formula as form, Parameter as param
from Cat as cat inner join cat.mate as mate left outer join cat.kittens as kitten
from Cat as cat left join cat.mate.kittens as kittens
from Formula form full join form.parameter param
-
inner join(内连接)
-
left outer join(左外连接)
-
right outer join(右外连接)
-
full join (全连接,并不常用)
from Cat as cat join cat.mate as mate left join cat.kittens as kitten
from Cat as cat inner join fetch cat.mate left join fetch cat.kittens
from Document fetch all properties order by name
from Document doc fetch all properties where lower(doc.name) like '%cats%'
select mate from Cat as cat inner join cat.mate as mate
select cat.mate from Cat cat
select cat.name from DomesticCat cat where cat.name like 'fri%'
select cust.name.firstName from Customer as cust
select mother, offspr, mate.name from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr
select new list(mother, offspr, mate.name) from DomesticCat as mother inner join mother.mate as mate left outer join mother.kittens as offspr
select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr
select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n from Cat cat
select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n ) from Cat cat
select avg(cat.weight), sum(cat.weight), max(cat.weight), count(cat) from Cat cat
-
avg(...), sum(...), min(...), max(...)
-
count(*)
-
count(...), count(distinct ...), count(all...)
select cat.weight + sum(kitten.weight) from Cat cat join cat.kittens kitten group by cat.id, cat.weight
select firstName||' '||initial||' '||upper(lastName) from Person
select distinct cat.name from Cat cat select count(distinct cat.name), count(cat) from Cat cat
from Cat as cat
from java.lang.Object o
from Named n, Named m where n.name = m.name
from Cat where name='Fritz'
from Cat as cat where cat.name='Fritz'
select foo from Foo foo, Bar bar where foo.startDate = bar.date
from Cat cat where cat.mate.name is not null
from Foo foo where foo.bar.baz.customer.address.city is not null
from Cat cat, Cat rival where cat.mate = rival.mate
select cat, mate from Cat cat, Cat mate where cat.mate = mate
from Cat as cat where cat.id = 123 from Cat as cat where cat.mate.id = 69
from bank.Person person where person.id.country = 'AU' and person.id.medicareNumber = 123456
from bank.Account account where account.owner.id.country = 'AU' and account.owner.id.medicareNumber = 123456
from Cat cat where cat.class = DomesticCat
store.owner.address.city // 正确 store.owner.address // 错误!
from AuditLog log, Payment payment where log.item.class = 'Payment' and log.item.id = payment.id
-
数学运算符+, -, *, /
-
二进制比较运算符=, >=, <=, <>, !=, like
-
逻辑运算符and, or, not
-
in, not in, between, is null, is not null, is empty, is not empty, member of and not member of
-
"简单的" case, case ... when ... then ... else ... end,和 "搜索" case, case when ... then ... else ... end
-
字符串连接符...||... or concat(...,...)
-
current_date(), current_time(), current_timestamp()
-
second(...), minute(...), hour(...), day(...), month(...), year(...),
-
EJB-QL 3.0定义的任何函数或操作:substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length()
-
coalesce() 和 nullif()
-
cast(... as ...), 其第二个参数是某Hibernate类型的名字,以及extract(... from ...),只要ANSI cast() 和 extract() 被底层数据库支持
-
任何数据库支持的SQL标量函数,比如sign(), trunc(), rtrim(), sin()
-
JDBC参数传入 ?
-
命名参数:name, :start_date, :x1
-
SQL 直接常量 'foo', 69, '1970-01-01 10:00:01.0'
-
Java public static final 类型的常量 eg.Color.TABBY
from DomesticCat cat where cat.name between 'A' and 'B'
from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )
from DomesticCat cat where cat.name not between 'A' and 'B'
from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )
<property name="hibernate.query.substitutions">true 1, false 0</property>
from Cat cat where cat.alive = true
from Cat cat where cat.kittens.size > 0
from Cat cat where size(cat.kittens) > 0
from Calendar cal where maxelement(cal.holidays) > current date
from Order order where maxindex(order.items) > 100
from Order order where minelement(order.items) > 10000
select mother from Cat as mother, Cat as kit where kit in elements(foo.kittens)
select p from NameList list, Person p where p.name = some elements(list.names)
from Cat cat where exists elements(cat.kittens)
from Player p where 3 > all elements(p.scores)
from Show show where 'fizard' in indices(show.acts)
from Order order where order.items[0].id = 1234
select person from Person person, Calendar calendar where calendar.holidays['national day'] = person.birthDay and person.nationality.calendar = calendar
select item from Item item, Order order where order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11
select item from Item item, Order order where order.items[ maxindex(order.items) ] = item and order.id = 11
select item from Item item, Order order where order.items[ size(order.items) - 1 ] = item
select item, index(item) from Order order join order.items item where index(item) < 5
from DomesticCat cat where upper(cat.name) like 'FRI%'
select cust from Product prod, Store store inner join store.customers cust where prod.name = 'widget' and store.location.name in ( 'Melbourne', 'Sydney' ) and prod = all elements(cust.currentOrder.lineItems)
SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order FROM customers cust, stores store, locations loc, store_customers sc, product prod WHERE prod.name = 'widget' AND store.loc_id = loc.id AND loc.name IN ( 'Melbourne', 'Sydney' ) AND sc.store_id = store.id AND sc.cust_id = cust.id AND prod.id = ALL( SELECT item.prod_id FROM line_items item, orders o WHERE item.order_id = o.id AND cust.current_order = o.id )
from DomesticCat cat order by cat.name asc, cat.weight desc, cat.birthdate
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color
select foo.id, avg(name), max(name) from Foo foo join foo.names name group by foo.id
select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK)
select cat from Cat cat join cat.kittens kitten group by cat having avg(kitten.weight) > 100 order by count(kitten) asc, sum(kitten.weight) desc
from Cat as fatcat where fatcat.weight > ( select avg(cat.weight) from DomesticCat cat )
from DomesticCat as cat where cat.name = some ( select name.nickName from Name as name )
from Cat as cat where not exists ( from Cat as mate where mate.mate = cat )
from DomesticCat as cat where cat.name not in ( select name.nickName from Name as name )
from Cat as cat where not ( cat.name, cat.color ) in ( select cat.name, cat.color from DomesticCat cat )
from Person where name = ('Gavin', 'A', 'King')
from Person where name.first = 'Gavin' and name.initial = 'A' and name.last = 'King')
select order.id, sum(price.amount), count(item) from Order as order join order.lineItems as item join item.product as product, Catalog as catalog join catalog.prices as price where order.paid = false and order.customer = :customer and price.product = product and catalog.effectiveDate < sysdate and catalog.effectiveDate >= all ( select cat.effectiveDate from Catalog as cat where cat.effectiveDate < sysdate ) group by order having sum(price.amount) > :minAmount order by sum(price.amount) desc
select order.id, sum(price.amount), count(item) from Order as order join order.lineItems as item join item.product as product, Catalog as catalog join catalog.prices as price where order.paid = false and order.customer = :customer and price.product = product and catalog = :currentCatalog group by order having sum(price.amount) > :minAmount order by sum(price.amount) desc
select count(payment), status.name from Payment as payment join payment.currentStatus as status join payment.statusChanges as statusChange where payment.status.name <> PaymentStatus.AWAITING_APPROVAL or ( statusChange.timeStamp = ( select max(change.timeStamp) from PaymentStatusChange change where change.payment = payment ) and statusChange.user <> :currentUser ) group by status.name, status.sortOrder order by status.sortOrder
select count(payment), status.name from Payment as payment join payment.currentStatus as status where payment.status.name <> PaymentStatus.AWAITING_APPROVAL or payment.statusChanges[ maxIndex(payment.statusChanges) ].user <> :currentUser group by status.name, status.sortOrder order by status.sortOrder
select account, payment from Account as account left outer join account.payments as payment where :currentUser in elements(account.holder.users) and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID) order by account.type.sortOrder, account.accountNumber, payment.dueDate
select account, payment from Account as account join account.holder.users as user left outer join account.payments as payment where :currentUser = user and PaymentStatus.UNPAID = isNull(payment.currentStatus.name, PaymentStatus.UNPAID) order by account.type.sortOrder, account.accountNumber, payment.dueDate
( (Integer) session.iterate("select count(*) from ....").next() ).intValue()
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name order by count(msg)
from User usr where size(usr.messages) >= 1
select usr.id, usr.name from User usr.name join usr.messages msg group by usr.id, usr.name having count(msg) >= 1
select usr.id, usr.name from User as usr left join usr.messages as msg group by usr.id, usr.name having count(msg) = 0
Query q = s.createQuery("from foo Foo as foo where foo.name=:name and foo.size=:size"); q.setProperties(fooBean); // fooBean包含方法getName()与getSize() List foos = q.list();
Query q = s.createFilter( collection, "" ); // 一个简单的过滤器 q.setMaxResults(PAGE_SIZE); q.setFirstResult(PAGE_SIZE * pageNumber); List page = q.list();
Collection orderedCollection = s.filter( collection, "order by this.amount" ); Collection counts = s.filter( collection, "select this.type, count(this) group by this.type" );
( (Integer) session.iterate("select count(*) from ....").next() ).intValue();
上一篇:XMPP开发库列表
下一篇:IBatisNet学习资料