Why do we require native SQL in hibernate when we have HQL

can anyone explain with example

CodePudding user response：

There are different dialects for each database. If you use native sql you can query using the dialect of a specific database. Sometime this is not possible using hql.

As an example you can use JSONB type in postgres that is a data type storing jsons. You can create queries accessing the content of this jsonb field that is not accessible with standard hql.

As an example the following query:

SELECT info -> 'customer' AS customer FROM orders;

select the property customer in the json stored in the field info. This has not equivalent in hql.

CodePudding user response：

HQL is internally converted to SQL. SQL is the standard way to communicate with SQL Database.

HQL was created to help Java developers in writing SQL. Hibernate internally take cares of this conversion.

And each database has some specific functions which can not be used with HQL (for example Postgres database uses some extensions like PostGIS which has specific methods) and some complex queries are also very tough to write with HQL.

Writing below query with HQL is almost impossible (although with some external JARs that can done in workaround way, but not as simple as this one)

SELECT ST_AsText(ST_TRANSFORM(ST_GeomFromText(:geometry), :transformation, :toSrid))

CodePudding user response：

HQL implements just a small subset of SQL. This small subset is very useful for CRUD-type operations, but little more than that.

SQL on the other hand has a full feature set of strategies and operators that are of no interest to HQL. When a query requires a more-than-trivial solution, the typical workaround is to avoid HQL and use "native SQL" instead.