File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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!!!
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: calling DB functions from Hibernate
 
Similar Threads
sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION
Passing specific format date to callable statement
Calling stored procedures
HQL date without time
comparing dates in SELECT statment