How can I count the number of query results without actually returning them?
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult();
How can I find the size of a collection without initializing it?
Integer size = (Integer) s.createFilter( collection, "select count(*)" ).uniqueResult();
How can I order by the size of a collection?
Use a
left join, together with
group by
select user
from User user
left join user.messages msg
group by user
order by count(msg)
How can I place a condition upon a collection size?
If your database supports subselects:
from User user where size(user.messages) >= 1
or:
from User user where exists elements(user.messages)
If not, and in the case of a one-to-many or many-to-many association:
select user
from User user
join user.messages msg
group by user
having count(msg) >= 1
Because of the inner join, this form can't be used to return a User with zero messages, so the following form is also useful
select user
from User as user
left join user.messages as msg
group by user
having count(msg) = 0
How can I query for entities with empty collections?
from Box box
where box.balls is empty
Or, try this:
select box
from Box box
left join box.balls ball
where ball is null
How can I sort / order collection elements?
There are three different approaches:
- Use a SortedSet or SortedMap, specifying a comparator class in the sort attribute or <set> or <map>. This solution does a sort in memory.
- Specify an order-by attribute of <set>, <map> or <bag>, naming a list of table columns to sort by. This solution works only in JDK 1.4+.
- Use a filter session.createFilter( collection, "order by ...." ).list()
Are collections pageable?
Query q = s.createFilter( collection, "" ); // the trivial filter
q.setMaxResults(PAGE_SIZE);
q.setFirstResult(PAGE_SIZE * pageNumber);
List page = q.list();
I have a one-to-one association between two classes. Ensuring that associated objects have matching identifiers is bugprone. Is there a better way?
<generator class="foreign">
<param name="property">parent</param>
</generator>
I have a many-to-many association between two tables, but the association table has some extra columns (apart from the foreign keys). What kind of mapping should I use?
Use a composite-element to model the association table. For example, given the following association table:
create table relationship (
fk_of_foo bigint not null,
fk_of_bar bigint not null,
multiplicity smallint,
created date )
you could use this collection mapping (inside the mapping for class Foo):
<set name="relationship">
<key column="fk_of_foo"/>
<composite-element class="Relationship">
<property name="multiplicity" type="short" not-null="true"/>
<property name="created" type="date" not-null="true"/>
<many-to-one name="bar" class="Bar" not-null="true"/>
</composite-element>
</set>
You may also use an
<idbag> with a surrogate key column for the collection table. This would allow you to have nullable columns.
An alternative approach is to simply map the association table as a normal entity class with two bidirectional one-to-many associations.
In an MVC application, how can we ensure that all proxies and lazy collections will be initialized when the view tries to access them?
One possible approach is to leave the session open (and transaction uncommitted) when forwarding to the view. The session/transaction would be closed/committed after the view is rendered in, for example, a servlet filter (another example would by to use the ModelLifetime.discard() callback in Maverick). One difficulty with this approach is making sure the session/transaction is closed/rolled back if an exception occurs rendering the view.
Another approach is to simply force initialization of all needed objects using Hibernate.initialize(). This is often more straightforward than it sounds.Another approach is to simply force initialization of all needed objects using Hibernate.initialize(). This is often more straightforward than it sounds.
How can I bind a dynamic list of values into an in query expression?
Query q = s.createQuery("from foo in class Foo where foo.id in (:id_list)");
q.setParameterList("id_list", fooIdList);
List foos = q.list();
How can I bind properties of a JavaBean to named query parameters?
Query q = s.createQuery("from foo in class Foo where foo.name=:name and foo.size=:size");
q.setProperties(fooBean); // fooBean has getName() and getSize()
List foos = q.list();
Can I map an inner class?
You may persist any static inner class. You should specify the class name using the standard form ie. eg.
Foo$Bar
How can I assign a default value to a property when the database column is null?
Use a
UserType.
How can I trucate String data?
Use a
UserType.
How can I trim spaces from String data persisted to a CHAR column?
Use a
UserType.
How can I convert the type of a property to/from the database column type?
Use a
UserType.
This information is available via the
Configuration object. For example, entity mappings may be obtained using
Configuration.getClassMapping(). It is even possible to manipulate this metamodel at runtime and then build a new
SessionFactory.
How can I create an association to an entity without fetching that entity from the database (if I know the identifier)?
If the entity is proxyable (
lazy="true"), simply use
load(). The following code does not result in any
SELECT statement:
Item itemProxy = (Item) session.load(Item.class, itemId);
Bid bid = new Bid(user, amount, itemProxy);
session.save(bid);
How can I retrieve the identifier of an associated object, without fetching the association?
Just do it. The following code does not result in any
SELECT statement, even if the
item association is lazy.
Long itemId = bid.getItem().getId();
This works if
getItem() returns a proxy and if you mapped the identifier property with regular accessor methods. If you enabled direct field access for the
id of an
Item, the Item proxy will be initialized if you call
getId(). This method is then treated like any other business method of the proxy, initialization is required if it is called.
How can I manipulate mappings at runtime?
You can access (and modify) the Hibernate metamodel via the
Configuration object, using
getClassMapping(),
getCollectionMapping(), etc.
Note that the
SessionFactory is immutable and does not retain any reference to the
Configuration instance, so you must re-build it if you wish to activate the modified mappings.
How can I avoid n+1 SQL SELECT queries when running a Hibernate query?
Follow the best practices guide! Ensure that all
<class> and
<collection> mappings specify
lazy="true" in Hibernate2 (this is the new default in Hibernate3). Use HQL
LEFT JOIN FETCH to specify which associations you need to be retrieved in the initial SQL
SELECT.
A second way to avoid the n+1 selects problem is to use
fetch="subselect" in Hibernate3.
If you are still unsure, refer to the Hibernate documentation and
Hibernate in Action.
I have a collection with second-level cache enabled, and Hibernate retrieves the collection elements one at a time with a SQL query per element!
Enable second-level cache for the associated entity class. Don't cache collections of uncached entity types.
How can I insert XML data into Oracle using the xmltype() function?
Specify custom SQL
INSERT (and
UPDATE) statements using
<sql-insert> and
<sql-update> in Hibernate3, or using a custom persister in Hibernate 2.1.
You will also need to write a
UserType to perform binding to/from the
PreparedStatement.
How can I execute arbitrary SQL using Hibernate?
PreparedStatement ps = session.connection().prepareStatement(sqlString);
Or, if you wish to retrieve managed entity objects, use
session.createSQLQuery().
Or, in Hibernate3, override generated SQL using
<sql-insert>,
<sql-update>,
<sql-delete> and
<loader> in the mapping document.
I want to call an SQL function from HQL, but the HQL parser does not recognize it!
Subclass your
Dialect, and call
registerFunction() from the constructor.
