Retrieving a paginated HQL query with total records included

This is going to be more of quick hit post. Many of us have written paginated queries before. (Don't need to pull back the whole database when you only need the first 100 records right ^__^ ) and, as many have discovered, ormExecuteQuery() supports the 'maxrecords' and 'offset' parameters.

This was brought up at work a few days ago (I have a feeling a lot of entries are going to start this way...) where a coworker was trying to write a query that was going to be paginated, but wanted to include the total number of records in the entire result set so that they could display it on the page. (i.e. Records 1-20 of 375, etc...)

The issue is this: HQL doesn't support COUNT() on multiple columns. So how do we get the value?

ORM gotchas - Child Mapping Issues

This is another work use-case example. We had a need to use some of the parent/child inheritance that ORM provides using a discriminator column (adobe's docs on using a subclass with discriminator).

The reasoning, is that there were specific types of children that had specific data attributes (it all revolved around a rule engine where the results had a shared set of data points, but depending on the rule type, the actual result data would change dramatically) and so, to avoid having a bunch of null values in the parent table, we were going to create the child tables appropriately.

Here's where it started to get tricky. We needed to map a relationship from the child object to a different table because, well because that relationship existed. Unfortunately, ORM didn't like that...

Random result set with HQL

Being a night owl I was browsing stack overflow when I came across a post about how to generate a random ordered result set with entityLoad() in ColdFusion. I (as of writing this) still haven't come up with a logical reason why one would _want_ to do so, but to each their own.

So, after a bit of research and testing the short answer is: It's really difficult to do with entityLoad().

However, with HQL, it's super easy:

Being aware of ORM sessions and gotchas

So recently at work I was trying to troubleshoot an odd issue I had never seen before. I was getting an odd error from a relatively simple HQL query:

Root cause :org.hibernate.HibernateException: coldfusion.runtime.Cast$NumberConversionException: The value '' cannot be converted to a number.

Informative, right?

The query being ran was a simple fetch query along the lines of:

hql = "FROM artists WHERE id = :id";
artist = ormExecuteQuery( hql, {id = 1}, true); 

Basically, there was nothing to indicate that there was a numeric value that was being populated with a null string.

So where did the error come from?