How to get native SQL from Session or Query?

mikky · **Joined:** Thu Jul 29, 2004 8:52 am **Posts:** 7

Hibernate version: 2.1.2

I have a Hibernate Query. I need to have native SQL.
Method query.getQueryString() returns the Hibernate Query string. But I need native SQL string.
How can I get it?
I need native SQL as string to send it to other application to run.

tvh · **Joined:** Wed Aug 24, 2005 9:02 am **Posts:** 15

Hi,

add <property name="hibernate.show_sql">true</property> in your hibernate.cfg.xml configurationfile, then u'll get the generated (mostly unreadable sql) in your eclipse-console or console in general.

one way to get it...

greetings

tvh

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

or try last hibernate tools - there is view 'Hibernate Dynamic Query Translator" - it is great

mikky · **Joined:** Thu Jul 29, 2004 8:52 am **Posts:** 7

Thanks, but I think I was not quite clear in my question.
I need native SQL string in my Java application.
My application processes input Hibernate Query or Session and needs to get native SQL.
I would like to have the something like getSQLString() method to next processing of this.

albert_kam · **Posted:** Fri Aug 26, 2005 3:24 am

Hi, im not sure bout this, but i think your need is to execute native sql in hibernate, is that correct ?
If so, please refer to the reference

...hibernate-3.0/doc/reference/en/html/querysql.html

Some excerps :
************************************************
Chapter 17. Native SQL

You may also express queries in the native SQL dialect of your database. This is useful if you want to utilize database specific features such as query hints or the CONNECT keyword in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate.

Hibernate3 allows you to specify handwritten SQL (including stored procedures) for all create, update, delete, and load operations.
17.1. Creating a native SQL Query

SQL queries are controlled via the SQLQuery interface, which is obtained by calling Session.createSQLQuery().

List cats = sess.createSQLQuery("select {cat.*} from cats cat")
.addEntity("cat", Cat.class);
.setMaxResults(50);
.list();

This query specified:
* the SQL query string, with a placeholder for Hibernate to inject the column aliases
* the entity returned by the query, and its SQL table alias

************************************************

Sorry if i misunderstood,
hope that helps

Regards,
Albert Kam
[/code]

mikky · **Joined:** Thu Jul 29, 2004 8:52 am **Posts:** 7

No, I need native SQL to execute it in other application.
My application has to generate native SQL from Hibernate Query and sends it to other application.
The simple diagram :)
[java application with Hibernate]->[My java application]->[java application without Hibernate]
I have some methods to get Hibernate Query or Session. I have to extract native SQL from it and I have to send this SQL to next execute.

d · **Joined:** Mon Jul 04, 2005 5:19 pm **Posts:** 720

It sounds like mikky needs the native SQL that would/is produced from HQL. This is needed in memory at runtime, not in a console during devlopment.

If this is the case, you can accomplish this (ironically) by doing what albert_kam suggests. If you are passing native SQL to hibernate, you then have the SQL you need to pass to the other application.

Just curious, why do you need to do this?

mikky · **Joined:** Thu Jul 29, 2004 8:52 am **Posts:** 7

Yes dennisbyrne, you are right. I need it in memory at runtime.

The task is very simple, I thought.

I have the jave application (API) to create and update database. This application is based on Hibernate. And I have second application that collects statistics info from the same database. The second application does NOT use Hibernate. I call the methods of second application via SOAP (Web services).
The first application can generate Hibernate query, after that I have to get native SQL to send it to the second application.

I do not program the both applications, I can use only API. My application is like integrator of first and second java application. (I would like to notice that second application can be not java application, since it is web services only)

baliukas · **Posted:** Fri Aug 26, 2005 7:00 am

Probably you can wrapp JDBC driver to send all updates to WS.

kgignatyev · **Posted:** Fri Aug 26, 2005 11:12 am

well, one HQL actually might produce more than one SQL. You may try [sergeya] suggestion:

Code:

QueryTranslator[] queries = ((SessionFactoryImpl)sessionFactory)getQuery(hsqlString, false, null);

String sql = queries[0].getSQLString();

(russian) http://forum.hibernate.org/viewtopic.ph ... highlight=

Note you may need to loop through all the queries, not just first one.

mikky · **Joined:** Thu Jul 29, 2004 8:52 am **Posts:** 7

Thanks, but method getSQLString() in QueryTranslator class is protected. :(

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

try this :

Code:

private String getSql(String query) {
      QueryTranslator newQueryTranslator;
      QueryTranslatorFactory ast = new ASTQueryTranslatorFactory();
      newQueryTranslator = ast.createQueryTranslator(query,
            Collections.EMPTY_MAP,
            (SessionFactoryImplementor) getSessionFactory());
      newQueryTranslator.compile(null, false);
      return newQueryTranslator.getSQLString();
   }

max · **Posted:** Mon Aug 29, 2005 1:57 pm

guys - he is using "Hibernate version: 2.1.2" so not much help giving him H3 API's ;)

and the short answer: you can't get it easily in H2.

snpesnpe · **Joined:** Sat Jun 12, 2004 4:49 pm **Posts:** 915

for hibernate2 you can use :

Code:

private String getSql(String query) {
        QueryTranslator queryTranslator = new QueryTranslator(query);
        queryTranslator.compile((SessionFactoryImplementor) getSessionFactory(),null,false);
        return queryTranslator.getSQLString();

I don't test with hibernate2

mikky · **Joined:** Thu Jul 29, 2004 8:52 am **Posts:** 7

Thanks to everybody for answers. I will try. :)
Migration to Hibernate3 is the possible solution as well.