Using properties in WHERE clause of a SQLQuery

patriks · **Joined:** Wed Feb 18, 2009 8:17 am **Posts:** 3

Hi,

I have trouble using properties (instead of column names) in the WHERE clause of a SQLQuery.
For example:

Code:

SQLQuery q = session.createSQLQuery("SELECT {insect.*} FROM insect {insect} WHERE {insect}.numberOfLegs=6").addEntity("insect", Insect.class);

This results in "Unknown column 'insect.numberOfLegs' in 'where clause'".
The column name in the database is number_of_legs.

I tried to inject the alias name using "WHERE {insect.numberOfLegs}=6" but then the database also complains with something like: "Unknown column 'number6_1093_0_' in 'where clause'". I guess this is because MySQL does not allow aliases in the WHERE clause (http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html).

Is it at all possible to use properties instead of column names in the WHERE clause?

Hibernate version: 3.3.1 GA

mmerder · **Posted:** Wed Feb 18, 2009 9:41 am

Why don't you just use a HQL-Query like:
"select i from Insect i where i.numberOfLegs=6"?

mmerder · **Posted:** Wed Feb 18, 2009 9:43 am

double post

mmerder · **Posted:** Wed Feb 18, 2009 9:45 am

double post

patriks · **Joined:** Wed Feb 18, 2009 8:17 am **Posts:** 3

mmerder wrote:

Why don't you just use a HQL-Query like:
"select i from Insect i where i.numberOfLegs=6"?

The reason I went for an SQLQuery instead of HQL in the first place was that I could not use RAND(<seed>) from HQL. (The query in my post was just an example)

mmerder · **Posted:** Wed Feb 18, 2009 10:26 am

As far as I know using properties in native SQL is not possible.

If you want HQL-Parser to recognize SQL-functions, you have to subclass your dialect and call registerFunction().

That may look like this:

Code:

public class YourDialect extends MySQLDialect {
  public YourDialect {
    registerFunction("yourFunction", new StandardSQLFunction("yourFunction", Hibernate.INTEGER) );
  }
}

patriks · **Joined:** Wed Feb 18, 2009 8:17 am **Posts:** 3

mmerder wrote:

As far as I know using properties in native SQL is not possible.

If you want HQL-Parser to recognize SQL-functions, you have to subclass your dialect and call registerFunction().

That may look like this:

Code:

public class YourDialect extends MySQLDialect {
  public YourDialect {
    registerFunction("yourFunction", new StandardSQLFunction("yourFunction", Hibernate.INTEGER) );
  }
}

Thanks! Extending MySQLDialect and using HQL worked for me in this case.

But I still think that property names in the where clause of SQLQueries would be nice to have in other cases. I found this phrase in the documentation:
"Notice that we may even use the property aliases in the where clause if we like. "
But it does not work (at least not with MySQL). Maybe it's a bug?

pkleindl · **Joined:** Thu Jan 08, 2009 3:48 pm **Posts:** 168

http://www.hibernate.org/hib_docs/v3/re ... references

I think the correct syntax for your example should be

Code:

FROM insect i where {i.numberOfLegs}=6

or

Code:

FROM insect i where {insect.numberOfLegs}=6

if alias references the "addEntity" statement.

rating is welcome