I am trying to execute a query against a MySQL database. The query is fairly complex it has 5 inner joins, including 1 join to itself and it returns 3 pieces of information from 2 different tables. We are using hibernate and till now I have used it for simple queries only. I have written the sql query and tested it too. I am wondering how to implement this using hibernate, can I execute plain sql statements with hibernate? If so what do I need, a separate hbm.xml? If I use hibernate and execute the plain sql query can I still utilize caching later on?
Add a comment
|
2 Answers
Yes, you can execute plain SQL queries with Hibernate.
No, you don't need a separate hbm.xml mapping file (unless you WANT to separate sql queries from the rest, in which case you can do so). You can map your named SQL query the same way you do with named HQL queries.
Whether you will be able to "utilize caching" depends on what exactly you understand by "caching" and how you're going to map your SQL query; it's impossible to answer without knowing more details.
All that said, you may not need to resort to SQL query; HQL is quite powerful and it may very well be possible (assuming appropriate mappings exist) to write your query as HQL. Can you post relevant mappings / schemas and your SQL query?
2 Comments
msharma
Thanks very much for your reply. I was just wondering what would be the advantage of using HQL instead of Native SQL with Hibernate? Given the time frame I want to pick something that can be done quickly.
ChssPly76
HQL in general is less headache - you don't have to deal with mapping your results back into your objects; caching is easily configurable; you don't have to deal with session data inconsistencies, etc... But again, it really depends on what you're trying to do - for read-only query retrieving a few fields SQL may be more appropriate.
I strongly recommend criteria queries over HQL queries. They are much closer to your program code without sacrificing any expression power. They DO however depend on relations to be explicitly mapped, otherwise they get quite complicated.
To speed up development, set property hibernate.show_sql=true, and play with the system in the debugger, using the "reload modified class" and "drop stack frame" features of the IDE+jvm until the SQL emitted looks like the one you've posted.
3 Comments
ChssPly76
You have got to be joking. Even ignoring the fact that Criteria API is severely limited comparing to HQL, you can't seriously say that reading 50 lines of
.add(Property.forName("whatever").eq(value)) is easier than reading corresponding SQL? That goes double for when you're trying to convert an already working SQL query to Hibernate.
pstanton
you mean
add(Restrictions.eq("whatever", value))pstanton
and yes it is better in many cases, for eg when the restrictions are conditional to save concatenating where clauses.