CODEWITHSUNDEEP
hibernatecoldfusionhql
Peter Boughton
Peter Boughton

Reputation: 112150

Numeric parameters for foreign keys in HQL statement

These both work:

 OrmExecuteQuery("FROM Person WHERE Company = 1",[]);
 OrmExecuteQuery("FROM Person WHERE companyID = 1",[]);

"Company" is the property name, and references a "Company" entity, whilst "companyID" is the column name (a FK linked to an auto-inc PK).

However, what I'm attempting to do is more complex - it involves joins, and that requires using an alias.

So I update those two queries with an alias...

 OrmExecuteQuery("FROM Person AS BaseEntity WHERE BaseEntity.Company = 1",[]);
 OrmExecuteQuery("FROM Person AS BaseEntity WHERE BaseEntity.companyID = 1",[]);

The first one still works, the second throws the error:

Error while executing the Hibernate query.

org.hibernate.QueryException: could not resolve property: companyID of: Person

I have no idea why both the unaliased versions work.

Of course, if I could simply use the property name with an alias that would be fine, but when I add parameters into the mix (because a hard-coded value isn't that helpful)...

 OrmExecuteQuery("FROM Person WHERE Company = ?",[1]);
 OrmExecuteQuery("FROM Person WHERE companyID = ?",[1]);
 OrmExecuteQuery("FROM Person AS BaseEntity WHERE BaseEntity.Company = ?",[1]);
 OrmExecuteQuery("FROM Person AS BaseEntity WHERE BaseEntity.companyID = ?",[1]);

...all four of those fail.

The last one gives the same error as above.

The first and third both give error:

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.

I'm assuming this is because CF is trying to treat the numeric id as an entity.

The second one is CF/Hibernate being its typical unhelpful self: a java.lang.NullPointerException error with no details.

Is there any way to allow a numeric ID to be provided as a parameter to Hibernate?

Upvotes: 3

Views: 332

Answers (1)

Peter Boughton
Peter Boughton

Reputation: 112150

The solution seems to be to refer to the Company's companyID property instead of the Person's companyID column, i.e:

OrmExecuteQuery("FROM Person AS BaseEntity WHERE BaseEntity.Company.companyID = ?",[1]);

The same form also works without aliases:

OrmExecuteQuery("FROM Person WHERE Company.companyID = ?",[1]);

Upvotes: 2

Related Questions