HQL

haha1949

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了 [1] [2] 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 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)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 calendar where calendar.holidays['national day'] = person.birthDay and person.nationality.calendar = calendarselect item from Item item, Order order where order.items[ order.deliveredItemIndices[0] ] = item and order.id = 11select item from Item item, Order order where order.items[ maxindex(order.items) ] = item and order.id = 11The expression inside [] may even be an arithmetic expression. select item from Item item, Order order where 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 item where index(item) < 5Scalar SQL functions supported by the underlying database may be used from DomesticCat cat where upper(cat.name) like 'FRI%' 上一页 [1] [2]