Tips and Tricks

--------------------------------------------------------------------------------

Tips and Tricks
How can I count the number of query results without actually returning them?
How can I find the size of a collection without initializing it?
How can I order by the size of a collection?
How can I place a condition upon a collection size?
How can I query for entities with empty collections?
How can I sort / order collection elements?
Are collections pageable?
I have a one-to-one association between two classes. Ensuring that associated objects have matching identifiers is bugprone. Is there a better way?
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?
In an MVC application, how can we ensure that all proxies and lazy collections will be initialized when the view tries to access them?
How can I bind a dynamic list of values into an in query expression?
How can I bind properties of a JavaBean to named query parameters?
Can I map an inner class?
How can I assign a default value to a property when the database column is null?
How can I trucate String data?
How can I trim spaces from String data persisted to a CHAR column?
How can I convert the type of a property to/from the database column type?
How can I get access to O/R mapping information such as table and column names at runtime?
How can I create an association to an entity without fetching that entity from the database (if I know the identifier)?
How can I retrieve the identifier of an associated object, without fetching the association?
How can I manipulate mappings at runtime?
How can I avoid n+1 SQL SELECT queries when running a Hibernate query?
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!
How can I insert XML data into Oracle using the xmltype() function?
How can I execute arbitrary SQL using Hibernate?
I want to call an SQL function from HQL, but the HQL parser does not recognize it!

--------------------------------------------------------------------------------

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:

1. Use a SortedSet or SortedMap, specifying a comparator class in the sort attribute or or < map>. This solution does a sort in memory.

2. Specify an order-by attribute of , < map> or , naming a list of table columns to sort by. This solution works only in JDK 1.4+.

3. 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?

parent

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):









You may also use an 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.

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.

How can I get access to O/R mapping information such as table and column names at runtime?
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 and 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 and 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 , , and 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.