GeeCON Prague 2014*
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
Author

calling DB functions from Hibernate

Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

If I have a SQL statement like
SELECT TRUNC(sale_date,'DAY') AS WEEK FROM SALES

how can I translate that to a hibernate query?
I would expect to have to write my own method in my Sales.java 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
Greenhorn

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) )
.list();
---------------------

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);
sqlQuery.setMaxResults(50);
List cats = sqlQuery.list();
-------------------

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

http://www.hibernate.org/hib_docs/reference/en/html_single/

hernan
Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

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

Joined: Apr 28, 2006
Posts: 2
Hi,

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!!!
 
GeeCON Prague 2014
 
subject: calling DB functions from Hibernate