关于Join请参考
http://silentjesse.iteye.com/admin/blogs/852058
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 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 = 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
The expression inside [] may even be an arithmetic expression.
select item from Item item, Order order
where 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 item
where index(item) < 5
Scalar SQL functions supported by the underlying database may be used
from DomesticCat cat where upper(cat.name) like 'FRI%'
分享到:
相关推荐
WHERE t.cjr = u.id AND u.orgid = o.orgcode AND t.gx = 1 ORDER BY t.ID ) WHERE rn > 10 AND rn ; 方法二:使用 INLINE VIEW 使用 INLINE VIEW 也可以解决排序问题。INLINE VIEW 是 Oracle 中的一种视图,它...
需要注意的是,`fetch`连接不适用于滚动查询或迭代查询,且`full join fetch`和`right join fetch`通常没有实际意义。 4. **关联的处理**:HQL支持处理一对一、一对多、多对一和多对多的关联。例如,`from Cat as ...
String hql = "FROM UserInfo u LEFT JOIN FETCH u.role r WHERE r.description = :roleDesc AND u.id IS NOT NULL"; Query query = session.createQuery(hql); query.setString("roleDesc", "role15"); List...
- 在查询语句中使用`Fetch Join`,可以在一次查询中同时加载主实体及其关联实体,这种方式能够有效地减少数据库的访问次数。 **3. 手动控制加载行为:** - 在某些场景下,可以手动控制加载行为,例如在查询时显式...
JOIN Student b ON a.sStudentId = b.sStudentId JOIN Class d ON b.iClassNo = d.iClassNO WHERE DATEPART(MONTH, a.tBorrowTime) = 2 AND d.iClassNO = '307' GROUP BY b.sStudentId; ``` #### 3. 查询某班所有...
- **级联加载**:`String hql = "from Menu m join fetch m.roles r join fetch r.users u where u.userLoginName=?";` 通过`join fetch`一次性加载多个关联表的数据。 ### 小结 HQL提供了丰富的查询语法,能够...
3. 分页查询:使用TOP和OFFSET/FETCH或 ROW_NUMBER() OVER() 函数实现数据的分页展示。 4. 聚合函数:SUM、COUNT、AVG、MAX、MIN等,用于对一组值进行计算。 5. 分区函数:在大数据量的表中,使用分区可以提高查询...
Lesson 1: Concepts – Locks and Lock Manager 3 Lesson 2: Concepts – Batch and Transaction 31 Lesson 3: Concepts – Locks and Applications 51 Lesson 4: Information Collection and Analysis 63 Lesson 5:...
* AND:组合两个或两个以上的布尔表达式 * OR:组合两个或两个以上的布尔表达式 * FETCH:使用行限制子句限制查询返回的行数 * IN:确定值是否与列表或子查询中的任何值相匹配 * BETWEEN:基于一系列值(区间值...
FETCH JOIN不同于普通的JOIN,它会立即加载关联的对象,而不是在需要时懒加载。这有助于减少额外的数据库查询并提高性能。 ### WHERE, GROUP BY, HAVING - **WHERE**: 定义查询的过滤条件。 - **GROUP BY**: 将结果...
4. **结果处理**: `mysqli_fetch_assoc()`或`PDO::fetch()`获取查询结果并转化为关联数组。 5. **事务处理**: 在多条SQL语句之间确保数据的一致性,通过`START TRANSACTION`, `COMMIT`和`ROLLBACK`实现。 ### 安全...
在SQL Server中,可以使用`JOIN`语句来实现组合查询,包括`INNER JOIN`(内连接)、`LEFT JOIN`(左连接)、`RIGHT JOIN`(右连接)和`FULL JOIN`(全连接)。这些连接方式可以结合多张表的数据,根据指定的关联条件...
19.1.2. Tuning fetch strategies 19.1.3. Single-ended association proxies 19.1.4. Initializing collections and proxies 19.1.5. Using batch fetching 19.1.6. Using subselect fetching 19.1.7. Using lazy ...
在“VUE and nodejs Demo”这个项目中,Vue.js 被用来构建用户界面,提供数据绑定、指令系统、组件系统等功能,使得前端开发更为高效和模块化。 Node.js 是一个基于Chrome V8引擎的JavaScript运行环境,它让...
- `DELETE`语句可以结合`USING`操作删除多个表中的数据,如`DELETE FROM table1 USING table1, table2 WHERE table1.key = table2.key AND condition;` 8. **索引与优化**: - 索引可以显著提高查询速度,创建...
总结,Oracle 存储过程中的分页实现有很多种方式,包括ROWNUM、ROW_NUMBER()、CTE、FETCH FIRST子句,以及JOIN和游标等。选择哪种方法取决于具体的需求,如数据库版本、性能需求、查询复杂性等因素。正确使用分页...
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id WHERE A.name = 'tableName'; ``` - **解释**: - `sys.tables`: 包含数据库中所有表的信息。 - `sys.columns`:...
6. **查询优化**:在处理多对一关联时,我们需要关注HQL(Hibernate Query Language)或JPA的Criteria API,以及JOIN fetch,以避免过多的数据库查询。例如,通过`join fetch`可以一次性获取学生和其关联的教师信息...
WHERE RowNum BETWEEN @StartRow AND @EndRow ``` OFFSET-FETCH则是SQL Server 2012及以后版本引入的标准分页方式,它可以直接指定要跳过的行数(OFFSET)和要获取的行数(FETCH)。例如: ```sql SELECT * FROM ...
FETCH NEXT FROM mCursor INTO @TableName; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql += 'EXEC sp_spaceused ''' + @TableName + ''';' + CHAR(13); FETCH NEXT FROM mCursor INTO @TableName; END; CLOSE ...