+972 52-548-6969
NHibernate Worst Practice - SQL
time to read 1 min | 114 words
The question just came up in the NHibernate dev list, and I wanted to be as clear about this as possible.
If you can at all avoid it, do so. The use of SQL in NHibernate should be strongly discouraged. It is there to give you the ability to use the proprietary features of your database, and as a way to avoid the abstraction if you really need that.
The problem is when people are using that for things that they really shouldn't, like complex queries that they know how to express in SQL but not using NHibernate, so they drop to that. This is not what it is for, and it will bite you later.
Comments
no kidding huh?
there are 1001 ways to avoid going that route. stored procs/udfs/views add a nice layer of indirection between he server and database internals
Using stored procs or views when it's not necessary (like Ayende said - for queries that the programmer doesn't know how to write in HQL) has the same disadvantages (just that the SQL code is somewhere else) ...
HQL and the Criteria API are very powerfull and they handle most scenarios, but I hit a problem recently. I wanted to join to a subquery (subquery that contained a projection and a group by).
This did not appear to be possible, so I had to use a sql query directly, but this is the second time of 3 year of nhibernate usage I had to use native sql.
http://forum.hibernate.org/viewtopic.php?t=989057&sid=918dd164816d8fba3158fd2389306660
Sometimes it is the mappin and the db model making you go down that ugly route.
If you have to use native sql, wouldn't it be easier to create and map a stored procedure to an object instead of embedding native sql into your app?
Bunter said it exactly right. We've needed to create custom stored procedures MANY times because of our legacy database. And sometimes you just need additional WITH constraint in your joins (last I checked NHibernate didn't support this).
Oops, I mean ON constraints.
The right way to proceed in my opion is: try to express the query in HQL or CRiteria, then if you cannot find a way to express your query in this way, try to see around the web if someone already solved a similar problem. Then if still you do not come to a solution try to ask on the forum, and if you still does not have a solution, maybe you really need SQL.
Quite often people are skilled with SQL and less with HQL, so they take another way, they do not have idea how to express the query in HQL, but they knows well how to create SQL query, so they simply resort to relay on the language they knows better (SQL), instead of using HQL
alk.
Comment preview