Hibernate Tips

 

1.     from Bid bid where bid.amount between 1 and 10

 

session.createCriteria(Bid.class)

.add(Restrictions.between("amount",

new BigDecimal(1),

new BigDecimal(10))

).list();

 

 

2.     from Bid bid where bid.amount > 100

 

session.createCriteria(Bid.class)

.add(Restrictions.gt("amount", new BigDecimal(100) ) )

.list();

 

 

3.     from User u where u.email in ( "[e-mail]", "[e-mail]" )

 

String[] emails = { "[e-mail]", "[e-mail]" };

session.createCriteria(User.class)

.add(Restrictions.in("email", emails) )

.list();

 

 

4.     from User u where u.email is null

 

session.createCriteria(User.class)

.add(Restrictions.isNull("email") )

.list();

 

from User u where u.email is not null

 

session.createCriteria(User.class)

.add( Restrictions.isNotNull("email") )

.list();

 

 

5.     from User u where u.firstname like "G%"

 

session.createCriteria(User.class)

.add( Restrictions.like("firstname", "G%") )

.list();

session.createCriteria(User.class)

.add( Restrictions.like("firstname", "G", MatchMode.START) )

.list();

 

The allowed MatchModes are START, END, ANYWHERE, and EXACT.

 

 

6.     from User u where lower(u.email) = '[e-mail]'

 

session.createCriteria(User.class)

.add( Restrictions.eq("email", "[e-mail]").ignoreCase() )

.list();

 

HQL支持对于数据库函数的调用；Criteria查询不支持。

 

 

7.     from User user

where user.firstname like "G%" and user.lastname like "K%"

 

session.createCriteria(User.class)

.add( Restrictions.like("firstname", "G%") )

.add( Restrictions.like("lastname", "K%") )

 

 

8.     from User user

where ( user.firstname like "G%" and user.lastname like "K%" )

or user.email in ( "[e-mail]", "[e-mail]" )

 

Criteria crit = session.createCriteria(User.class)

.add(

Restrictions.or(

Restrictions.and(

Restrictions.like("firstname", "G%"),

Restrictions.like("lastname", "K%")

),

Restrictions.in("email", emails)

)

);

 

Criteria crit = session.createCriteria(User.class)

.add( Restrictions.disjunction()

.add( Restrictions.conjunction()

.add( Restrictions.like("firstname", "G%") )

.add( Restrictions.like("lastname", "K%") )

)

.add( Restrictions.in("email", emails) )

);

 

HQL string is much easier to understand.

Complex criteria queries are useful only when they’re created programmatically; for example, in the case of a complex search screen with several optional search criteria, we might have a CriteriaBuilderthat translates user restrictions to Criteria instances.

 

 

9.     from User u order by u.lastname asc, u.firstname asc

 

List results = session.createCriteria(User.class)

.addOrder( Order.asc("lastname") )

.addOrder( Order.asc("firstname") )

.list();

 

10. from Item item

left join fetch item.bids

where item.description like '%gc%'

 

session.createCriteria(Item.class)

.setFetchMode("bids", FetchMode.EAGER)

.add( Restrictions.like("description", "gc", MatchMode.ANYWHERE) )

.list();

 

Both of these queries result in the following SQL:

select I.DESCRIPTION, I.CREATED, I.SUCCESSFUL_BID, B.BID_ID,

B.AMOUNT, B.ITEM_ID, B.CREATED

from ITEM I

left outer join BID B on I.ITEM_ID = B.ITEM_ID

where I.DESCRIPTION like '%gc%'

 

 

Appendix

Ø         外连接(out join):

内连接是关联的表的公共字段值必须相同，所有不同的值的记录都没有了。

外连接是值一个表的中的公共字段的值可以不与另一个表的公共字段值相同。一般时它是null.

 

任务：查询员工表，显示员工的项目，部门名称，部门位置，要求显示所有的员工，即使员工没有部门。

select a.ename,b.dname,b.loc

from emp a left outer join dept b

on a.deptno=b.deptno

 

注：此任务无法使用正常的内连接。因为有一个员工没有部门，它的部门编号为空。

 

 

Ø         外连接语法：

（1）左连接：取出左边的表的所有记录

select 子句

from 表1 left outer join 表2

on 表1.公共字段＝表2.公共字段

（2） 右连接: 取出右边表的所有记录

select 子句

from 表1 right outer join 表2

on 表1.公共字段＝表2.公共字段

（3） 全连接（左右连接）：左右两边的表的记录都取。

select 子句

from 表1 full outer join 表2

on 表1.公共字段＝表2.公共字段

 

select a.empno,a.deptno,b.deptno,b.dname

from emp a left outer join dept b

on a.deptno=b.deptno

 

select a.empno,a.deptno,b.deptno,b.dname

from emp a right outer join dept b

on a.deptno=b.deptno

 

select a.ename,b.dname,b.loc

from emp a full outer join dept b

on a.deptno=b.deptno

 

select a.dname, b.ename

from dept a full outer join emp b

on a.deptno=b.deptno

 

一般情况下，不使用上述的语法，而使用如下的语法：

select a.dname, b.ename

from dept a, emp b

where a.deptno(+)=b.deptno   ß--

一般情况情况下，(+)放在关联表的主键的一侧，才有实际的意义。

没有(+)的表的取所有的记录，关联的表如果有记录对应就显示关联的值，没有关联的值显示null.

 

但使用(+)的情况下，无法实现全连接。 因为无法在where 的左右同时使用(+).

select a.ename,b.dname

from emp a, dept b

where a.deptno=b.deptno(+)

下列的语句是无法通过的：

select a.ename,b.dname

from emp a, dept b

where a.deptno(+)=b.deptno(+)