Posts Tagged “hql”

Follow

Happy new year, here comes SQL

   ,  , 

One of the things occupying me is my work on my last seminar for the university, so I’d finally get my Bachelor’s degree.

The project is building an application for managing, uhm, a university. You reckon they figured out a way to prototype a new system for free … ?

Anyway, the seminar name is “Database Management Systems Workshop”, so one of the things I have to do is to present a full E/R-D even before I start coding. And UI design sheets. So no agile here :(

My aim is to be able to demonstrate some cool stuff though, like the usage of an advanced O/R mapper (NH), static and dynamic object-based querying (hql and Criteria API), some advanced T-SQL stuff (recursive set-based queries using CTE, and some fancy integrity enforcing triggers), smart caching with SqlCacheDependency, and more.

To make things interesting I decided to create a large-ish mount of fake data. Well, at least in terms of a non-production university-demo app. My DB currently holds 600K students and over 500 possible modules.

I’ll blog a bit on the ways I used to populate that data, and other data as well (such as the ModulePrerequisites table which is an adjacency table).

So, beware, some SQL might follow

Get unique results from joined queries in NHibernate

   , 

I’ve just added a page to NHibernate’s new community site, about Getting unique results from joined queries.

Comments (and edits) are welcome

Using nhibernate's named queries with ActiveRecord

   ,  ,  , 

One of the methods of querying the DB when using NHibernate, is to issue HQL queries. HQL stands for “Hibernate Query Language”. It has a SQL-like syntax and is very intuitive for people with SQL background.

The way this works is that NHibernate ‘compiles’ the HQL query into SQL, and then issues the SQL query (using ADO.NET’s facilities) to the DB.

Sounds pricey?

enter Named Queries.

now these are HQL (or SQL) queries, each has a name (obviously), that are been supplied to NH through the mapping. The queries are being translated and cached as IDbCommand objects as part of the framework initialisation, which mean that you get rid of the HQL->SQL overhead throughout the life of the process.

One other major benefit, is that the mechanism to actually execute these named queries, does not differentiate between HQL and SQL queries (for the simple fact that these queries have already been transferred to SQL at runtime). That gives you the possibility to replace HQL queries into tighter SQL queries (with the same parameters and which returns the same resultset) should your DBA figure out a better one.

But if you’re using ActiveRecord, you usually do not have direct access into the mapping (.hbm) files. So how would you use named queries with AR?

enter HqlNamedQueryAttribute (not such a great name, as I did state that it would also work for SQL queries).

So, for an example, on this blog’s source code, within PostRepository.cs you’d see this code:


...



#region queries[assembly: HqlNamedQuery(Queries.FindPostsInArchive, Queries.FindPostsInArchive)][assembly: HqlNamedQuery(Queries.FindByUrlFriendlyTagName, Queries.FindByUrlFriendlyTagName)]namespace KenEgozi.Com.Domain{ internal partial class Queries { internal const string FindPostsInArchive = @" from Post p where  year(p.Lifecycle.CreationDate) = :year and month(p.Lifecycle.CreationDate) = :month  order by p.Lifecycle.CreationDate desc"; internal const string FindByUrlFriendlyTagName = @" select p  from  Post p  join p.Tags t where t.UrlFriendlyName like :urlFriendlyTagName order by p.Lifecycle.CreationDate desc;"; }}#endregion

...




 public ICollection<Post> FindInArchive(int year, int month) { 

 return session .GetNamedQuery(Queries.FindPostsInArchive) .SetParameter("year", year) .SetParameter("month", month) .List<Post>(); }

...

Queries class is marked as partial, as other queries might be presented on other repositories or services that would need to add more named queries. I considered grouping of queries into groups by the using repository, or by aggregate roots, but the thing is - having all of the queries under the same namespace helps discoverability, and helps with preventing duplications

Summing up the last two and a half years - Bye bye SQLink

   ,  ,  ,  , 

It was absolutely brilliant.

I started to work for SQLink on late December 2005 as a Team Leader in the Web Projects Department.

Colleaged by Oren Ellenbogen, it has been a pleasure. Our department head, Moti, was doing the best he can to create a very likeable working environment, and all our developers were enjoing a workplace that enabled them to learn a lot. Oren and I were directing all kinds of sessions with the developers, showing them stuff about .NET and the CLR, from “what are nullables”, through “What does the ‘using’ reserved word mean”, to “how the GC is actually working”.

We have built this great website called GotFriends, that gave the company a great new source for recruiting new employees, and actually is ground breaking in the Israeli HR world. Building that site, we’ve used many technologies to make it work smoothly with the company’s inner legacy HR system, and with the aid of the SQL master Moran Benisty, it even worked in an efficient way, and coded in a maintainable manner.

Mixing WebForms and Monorail, CodeSmith based BLL with ActiveRecord/NH, ASP.NET WebServices (asmx) and POX/JSON services, it was a very fun thing to work on, in addition to the business benefit to the company.

However, a few months ago the Web Project Department was closed, and the company started a new R&D team, leadedby Elad, the company’s VP of Business Development. We were two developers (Moran and I), and we worked on several initiatives that the CEO Tamir, and Elad, were cooking all the time. Those projects wereall Community-Driven-So-Called-Web-2.0-Kinda-Websites. It was a real delight, and I got the chance to learn a lot about the business side of running an Internet related initiative, as both Tamir and Elad are experienced and intelligent, and the process of refining ideas, with those two, was a real treat.

They also gave me the freedom to make all the technology decisions, and they’ve had enough faith in me to allow me run the projects using MonoRail and AspView, and running Castle ActiveRecord for DB access. Actually, most of the drive behind creating AspView was actually driven by Elad and Tamir, as I’ve promised to do the best I can to make sure that future additions to the team won’t need to learn Boo / Velocity in addition to learn the Monorail MVC and hql.

Which actually worked great. Moran has left the team about two months ago, and we’ve brought three new guys along (Ili, Ofir and last but not least, Itay), and they have seam to easily get control of all the “funky” technologies I’ve put in use in our projects.

Sadly enough, one of the initiatives has stalled just before airing, due to some business decisions. Then we started a new one, and in about 3 weeks we’ve had a working proof-of-concept, and I really hope that the site will air during August. I give the credit to the team, and to the use of MonoRail/ActiveRecord, as it’s such agile and suits highly-changing-environment, as most web initiatives are.

A point of interest: This very blog’s engine was actually a beta testing for some of the stuff we were using on our last project.

That’s it folks. I wish the SQLink family all the best, and I’m going to keep an eye on the cool stuff the R&D team is doing, and hopefully I’ll report on their success (which would be an AspView success too …) right here on my blog.

NHibernate ' Could not find constructor for: ' in ' select new ' projection query

   ,  ,  , 

There are two facts here:1. I love NHibernate.2. I hate NHibernate’s exception messages.

And here’s my story.

On a project I’m working on, I need to show a projection of “top 10” from the database. let’s show this on the good old Blog scenario:

So I want to show the posts with the longest comments measured by the comment’s length. Stupid, huh? but it’s a demo only (I cannot expose the actual ERD). Let’s say I want the top 5.

I am using Castle ActiveRecord. There is a Post and a Comment classes. However, I do not wish to load Posts objects, since It will load the Comments, too, and maybe other stuff that the Post class is related to. So I have defined a PostProjection class:

   1:  publicclass PostProjection

   2:  {

   3:  publicstring Title;

   4:  publicint Length;

   5:  public PostProjection(string title, int length)

   6:      {

   7:          Title = title;

   8:          Length = length;

   9:      }

  10:  }

I have also added an [Import] attribute on the Post. The actual querying is done using the next hql:

   1:  publicstatic PostProjection[] GetTopPosts(int postsToGet)

   2:  {

   3:      SimpleQuery<PostProjection> q =

   4:  new SimpleQuery<PostProjection>(typeof(Post), @"

   5:          select new PostProjection(p.Title, sum(c.LineCount)) 

   6:          from 

   7:              Post p inner join 

   8:              p.Comments c 

   9:          order by sum(o.LineCount) desc

  10:          group by p.Title");

  11:      q.SetQueryRange(postsToGet);

  12:  return q.Execute();

  13:  }

It worked great.

Yesterday I’ve upgraded my Castle dll’s to the ones from build 229. It includes NHibernate 1.2.0.2002

Now the “select new” started to fail, and NHibernate started to claim than “Could not find constructor for: PostProjection”.

I’ve been scratching my head, trying various approaches, and even was keen to skip the “new” and use an object[ ] and populate the Projection Array by hand, but then I tried changing the “length” parameter of the constructor from “int” to “long”. Magically it solved the problem.

Now, if only NHibernate would have said :

Could not find constructor for: PostProjection.Looking for: PostProjection(string, long)

I would’ve known what the problem was, and what should I change.

So what have we learned today?

  1. NHibernate expects sum() to return “long” rather than “int”2. NHibernate’s error messages suck.

I’ve svn-ed the NHibernate trunk, added some code so this message would be more developer friendly, and I’m going to send the patch to NHibernate’s JIRA.

Follow @kenegozi