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.

ChssPly76 · Accepted Answer · 2009-10-08 16:33:36Z

10

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.

answered Oct 8, 2009 at 16:33

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andreas Covidiot Over a year ago

Wow - works great. Really cool and robust feature with the SQL and auto-entity load! I had been afraid to have to map all HQL entities in order to do some simple SQL joins for retrieving entities of one type. Btw. using JPA/Hibernate 4.3.7 it was as easy as this: List< DAO > daos = em.createNativeQuery( sql , myDaoClass ).getResultList()

Collectives™ on Stack Overflow

Mix HQL and SQL in the same query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related