分享

Join用法,HQL的方法,Hibernate中的fetch

 apollyon0728 2010-12-09

Join用法,HQL的方法,Hibernate中的fetch

默认分类 2009-11-23 18:14:58 阅读104 评论0   字号: 订阅

Join用法:

主要有Inner Join 及 Outer Join:

最常用的(默认是Inner):

Select <要选择的字段> From <主要资料表>

  <Join 方式> <次要资料表> [On <Join 规则>]

Inner Join 的主要精神就是 exclusive , 叫它做排他性吧! 就是讲 Join 规则不相符的资料就会被排除掉, 譬如讲在 Product 中有一项产品的供货商代码 (SupplierId), 没有出现在 Suppliers 资料表中, 那么这笔记录便会被排除掉

Outer Join:

Select <要查询的字段> From <Left 资料表>

  <Left | Right> [Outer] Join <Right 资料表> On <Join 规则>

语法中的 Outer 是可以省略的, 例如你可以用 Left Join 或是 Right Join, 在本质上, Outer Join 是 inclusive, 叫它做包容性吧! 不同于 Inner Join 的排他性, 因此在 Left Outer Join 的查询结果会包含所有 Left 资料表的资料, 颠倒过来讲, Right Outer Join 的查询就会包含所有 Right 资料表的资料

另外,还有全外联:

FULL JOIN 或 FULL OUTER JOIN

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。

以及,

交叉联接

交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。也就是说在没有 WHERE 子句的情况下,若表 A 有 3 行记录,表 B 有 6 行记录 : :

SELECT A.*,B.* FROM 表A CROSS JOIN 表B

那以上语句会返回 18 行记录。

Fetch:

在我们查询Parent对象的时候,默认只有Parent的内容,并不包含childs的信息,如果在Parent.hbm.xml里设置lazy="false"的话才同时取出关联的所有childs内容.

问题是我既想要hibernate默认的性能又想要临时的灵活性该怎么办? 这就是fetch的功能。我们可以把fetch与lazy="true"的关系类比为事务当中的编程式事务与声明式事务,不太准确,但是大概是这个意思。

总值,fetch就是在代码这一层给你一个主动抓取得机会.

Parent parent = (Parent)hibernateTemplate.execute(new HibernateCallback() {

public Object doInHibernate(Session session) throws HibernateException, SQLException {

                 Query q = session.createQuery(

"from Parent as parent "+

" left outer join fetch parent.childs " +

" where parent.id = :id"

                 );

                 q.setParameter("id",new Long(15));

return (Parent)q.uniqueResult();

             }

         });

         Assert.assertTrue(parent.getChilds().size() > 0);

你可以在lazy="true"的情况下把fetch去掉,就会报异常. 当然,如果lazy="false"就不需要fetch了

HQL一些特色方法:

in and between may be used as follows:

from DomesticCat cat where cat.name between 'A' and 'B'

from DomesticCat cat where cat.name in ( 'Foo', 'Bar', 'Baz' )

and the negated forms may be written

from DomesticCat cat where cat.name not between 'A' and 'B'

from DomesticCat cat where cat.name not in ( 'Foo', 'Bar', 'Baz' )

Likewise, is null and is not null may be used to test for null values.

Booleans may be easily used in expressions by declaring HQL query substitutions in Hibernate configuration:

<property name="hibernate.query.substitutions">true 1, false 0</property>

This will replace the keywords true and false with the literals 1 and 0 in the translated SQL from this HQL:

from Cat cat where cat.alive = true

You may test the size of a collection with the special property size, or the special size() function.

from Cat cat where cat.kittens.size > 0

from Cat cat where size(cat.kittens) > 0

For indexed collections, you may refer to the minimum and maximum indices using minindex and maxindex functions. Similarly, you may refer to the minimum and maximum elements of a collection of basic type using the minelement and maxelement functions.

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

The SQL functions any, some, all, exists, in are supported when passed the element or index set of a collection (elements and indices functions) or the result of a subquery (see below).

select mother from Cat as mother, Cat as kitwhere kit in elements(foo.kittens)

select p from NameList list, Person pwhere 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)

Note that these constructs - size, elements, indices, minindex, maxindex, minelement, maxelement - may only be used in the where clause in Hibernate3.

Elements of indexed collections (arrays, lists, maps) may be referred to by index (in a where clause only):

from Order order where order.items[0].id = 1234

select person from Person person, Calendar calendarwhere calendar.holidays['national day'] = person.birthDay and person.nationality.calendar = calendar

select item from Item item, Order orderwhere order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11

select item from Item item, Order orderwhere order.items[ maxindex(order.items) ] = item and order.id = 11

The expression inside [] may even be an arithmetic expression.

select item from Item item, Order orderwhere order.items[ size(order.items) - 1 ] = item

HQL also provides the built-in index() function, for elements of a one-to-many association or collection of values.

select item, index(item) from Order order join order.items itemwhere index(item) < 5

Scalar SQL functions supported by the underlying database may be used

from DomesticCat cat where upper(cat.name) like 'FRI%'

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多