Join 用法，HQL的方法，Hibernate中的fetch （转载）

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 = trueYou may test the size of a collection with the special property size, or the special size() function.

from Cat cat where cat.kittens.size > 0from Cat cat where size(cat.kittens) > 0For 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_datefrom Order order where maxindex(order.items) > 100from Order order where minelement(order.items) > 10000The 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 = 1234select person from Person person, Calendar calendarwhere calendar.holidays['national day'] = person.birthDay    and person.nationality.calendar = calendarselect item from Item item, Order orderwhere order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11select item from Item item, Order orderwhere order.items[ maxindex(order.items) ] = item and order.id = 11The expression inside [] may even be an arithmetic expression.

select item from Item item, Order orderwhere order.items[ size(order.items) - 1 ] = itemHQL 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) < 5Scalar SQL functions supported by the underlying database may be used

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