Mix HQL and SQL in the same query

Question

I'm trying to mix HQL and SQl in the same query. Like using

"from ObjectA obj, TABLE_B tbl where obj.someProp = tbl.COLUMN"

because my client will need to modify the query, and learning HQL or mapping unmapped tables is out of the question :(

If not hibernate, does somebody knows another ORM tool that can accept this? another JPA implementation, or JDO?

I've tried this., and of course it doesn't work. It makes sense to me also: the results are not mapped to objects, so there's no way to get objects without somekind of coding. I'm mostly looking for reasons why this will not work anytime soon.

Answer 1

This indeed doesn't work and I don't think it ever will, but not because "there's no way to get objects".

Hibernate does allow you to use plain SQL queries and provides a lot of different options for mapping the results into your entities (or non-entity java objects). You could write your query as:

SELECT table_A.*
  FROM table_A JOIN table_B on table_A.some_prop_column = tableB.other_column
 WHERE ...

and execute it from Hibernate via:

List objectAs = session.createSQLQuery(yourQuerySQL).addEntity(ObjectA.class).list();

The problem with mixing SQL and HQL is that it's very easy to get into a conflict between them - SQL generated from HQL may use one table alias, raw SQL another; generated SQL may contain tables / joins conflicting with raw SQL, etc...

And it the end, all that gets you absolutely nothing - it won't even help your use case. If you want someone to modify a "mixed" HQL / SQL query they still need to know both SQL and HQL - at least at the level necessary to distinguish between the two.

All that said, if your end user needs to be able to customize that query, perhaps you should be looking at using Criteria API in order to generate the query you need based on user's input. Having end user modify an actual query within the application is a gaping security hole unless you take extreme precautions.