Is there any way to get final SQL query from TypedQuery or CriteriaQuery

Hibernate ORM
1

I have a use case where I want to get NativeSQL query from TypedQuery or CriteriaQuery before executing that query. I tried one method which partially worked, it is giving SQL string but with placeholders(?) instead of actual values.

Here is the code I used

      String hqlQueryString=typedQuery.unwrap(org.hibernate.query.Query.class).getQueryString();
      ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
      SessionImplementor hibernateSession = entityManager.unwrap(SessionImplementor.class);
      QueryTranslator
          queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, java.util.Collections.EMPTY_MAP, hibernateSession.getFactory(), null);
      queryTranslator.compile(java.util.Collections.EMPTY_MAP, false);
      String sqlQueryString = queryTranslator.getSQLString();

Query It gave
select product0_.product_id as col_0_0_, product0_.title as col_1_0_, image1_.url as col_2_0_ from shopify_products product0_ cross join shopify_images image1_ cross join shopify_collections collection2_ where product0_.product_id=image1_.product_id and image1_.product_id=collection2_.product_id and (product0_.product_id in (?)) and collection2_.title=? order by product0_.product_id asc

Is there any way to get final SQL query as string which I can directly execute on Database without any further modification

2

No, that’s not easily possible as sometimes the parameters have to be inlined as literals. Also, the internal SPIs changed quite a bit in Hibernate 6, so code that might work today won’t work anymore in newer versions.

Why do you need this i.e. for what purpose? There are possibilities to intercept the statements before execution with org.hibernate.resource.jdbc.spi.StatementInspector, but to get access to the actual parameter values and figure out the literal values, you will have to dig deeper into the internal classes used for executing queries.

3

My use case is to generate query dynamically, for this I’m using hibernate. Once query is generated I want to execute this query in sqlite in-memory database.

4

You can execute queries directly against SQLite with the SQLiteDialect, so there is no need to extract queries and try to run them yourself.

5

This was my plan earlier, but with SQLiteDialect I didn’t find any way to configure connections for SQLite. I mean for all connection to SQLite I want to set few PRAGMA’s which I think is not possible through SQLiteDialect

6

If you can do it with the JDBC driver (GitHub - xerial/sqlite-jdbc: SQLite JDBC Driver), then you can do it through Hibernate as well. You just need to provide a JDBC URL that contains all your configs.