File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes calling DB functions from Hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "calling DB functions from Hibernate" Watch "calling DB functions from Hibernate" New topic

calling DB functions from Hibernate

Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

If I have a SQL statement like

how can I translate that to a hibernate query?
I would expect to have to write my own method in my bean to replace "trunc"

but I can't seem to call a method of an object in my hibernate query:

SELECT sales.saleDate, sales.saleDate.getWeek() from Sales sales doesn't seem to be right. I can't see any queries in this form in the documentation.

Is the answer to just get the date with the hibernate query and perform my methods on date after I have the query results? If so, then I can't group by week as I want to.

So, my solution right now is to add a "week" column to the database.
hernan silberman

Joined: Nov 20, 2001
Posts: 27
If you're using criteria queries, you can do like the Hibernate manual says here:

There are quite a range of built-in criterion types (Expression subclasses), but one that is especially useful lets you specify SQL directly.

List cats = sess.createCriteria(Cat.class)
.add( Expression.sql("lower({alias}.name) like lower(?)", "Fritz%", Hibernate.STRING) )

Otherwise, you can use a native SQL query in Hibernate, which is really useful for cases where you have vendor-specific SQL to use, such as a rule hint for your optimizer:

13.1. Creating a SQL based Query

SQL queries are exposed through the same Query interface, just like ordinary HQL queries. The only difference is the use of Session.createSQLQuery().

Query sqlQuery = sess.createSQLQuery("select {cat.*} from cats {cat}", "cat", Cat.class);
List cats = sqlQuery.list();

Here's a link to the full Hibernate docs, I think what you're looking for is in Chapter 13:

Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

Thanks Hernan. That is exactly what I was looking for.
Margaret Schwab

Joined: Apr 28, 2006
Posts: 2

When using 'createSQLQuery', do you still need the hbm mapping file?

I am struggling with
SELECT trunc(some_date), some_field
FROM some_table
GROUP BY trunc(some_date), some_field
ORDER BY trunc(some_date), some_field

What should the hbm file look like, in particular - how to define the ID?

Thanks so much!!!
I agree. Here's the link:
subject: calling DB functions from Hibernate
It's not a secret anymore!