In which cases we use native query and hql query?

Question

My question is that I didn't understand how would I know that I have to use Native query instead of hibernate query language ?

Answer 1

There are at least two examples when you just can't use HQL and have to use native instead.

1) Hibernate is not generally geared to performing DDL type operations at runtime:

em.createNativeQuery("DROP TABLE IF EXISTS mytable").executeUpdate();

2) I might be wrong in this one, but afaik HQL queries usually look like "from MyClass as mc". Which means you must have a stringified name of a class there, and sometimes you have to determine class in runtime. That, in turn, would convert your HQL into something ugly like hql = "from " + obj.getSimpleName() + " sn";. For such "polymorphic" queries the following would be cleaner:

em.createNativeQuery(someSql, getConcreteClass());

Answer 2

To second other answers here, you would generally be better off relying on Hibernate queries (HQL) throughout your application. The primary reason here is Hibernate itself - it performs much better if you let it manage all the entities and data being fetched and updated. If native query is employed, however, Hibernate overlooks the entity changes performed, and therefore becomes less efficient in its job. There are ways to bridge the gap between native queries and Hibernate entity and ORM mechanism, but they require extra careful coding and are neither very flexible or portable.

The reasons to use native queries, as others have already explained, are the need to perform DDL operations (Hibernate does not feel good about structural changes), or the explicit need to rely on certain database vendor specific SQL syntax. For example, to apply table locking hints, or do basic data processing within the database - before the rows are returned to your application.

Other than the above, there are no particular reasons to go back to native queries. After all, being able to avoid writing bare SQL was most probably the reason you picked Hibernate, wasn't it?