How to get SQL from Hibernate Criteria API (not for logging)

sql

sql-spies

criteria-api

hibernate-logging

byAnton Shumikhin·Nov 23, 2024

Ready for SQL extraction magic from Hibernate Criteria? Grab CriteriaQueryTranslator and CriteriaJoinWalker classes from your wizard hat, and swing your code-wand. Cast your Criteria to CriteriaImpl, summon the essential Hibernate internal magic runes (objects), and finally, shout out "Reveal!" to getSQLString() incantation. Here's the ritual:

//Step 1: Hey, Criteria! Why so shy? Behave like CriteriaImpl!
CriteriaImpl criteriaImpl = (CriteriaImpl) session.createCriteria(YourEntity.class);

//Step 2: Magic translator: Converts Criteria lingo to SQL talk
CriteriaQueryTranslator translator = new CriteriaQueryTranslator(
    session.getFactory(), 
    criteriaImpl, 
    YourEntity.class.getName(), 
    CriteriaQueryTranslator.ROOT_SQL_ALIAS
);
//Step 3: Drumroll... Now we walk with the SQL fetcher
CriteriaJoinWalker walker = new CriteriaJoinWalker(
    (OuterJoinLoadable) session.getFactory().getEntityPersister(YourEntity.class.getName()), 
    translator, 
    session.getFactory(), 
    session.getLoadQueryInfluencers()
);
//And Voila! The Resplendent SQL in all its glory!
String sql = walker.getSQLString();

Heads Up!: The spell used here fiddles with the internal, non-public Hibernate APIs. Be ready for backlash from future Hibernate updates.

Extracting SQL programmatically

Logging: Good when chatty isn't irritating

Logging carries performance overhead. Our approach ditches log piles, fetches SQL programmatically and elegantly flexes try/catch muscle for exception handling.

SQL Spy-craft: Spring AOP

Spring AOP is your century's SQL spy. It intercepts SQL statements stealthily, without logging rug being pulled from under your feet.

SQL Language-class: 101

'Specific database operations like 'MINUS' not supported by Criteria API?' Pshaw! Use CriteriaQueryTranslator's getWhereCondition to converse in pure SQL for some custom fun!

Customized Crieteria API: Because one-size doesn't fit all

Criteria API is a moldable clay, ready to be fashioned into custom SQL queries. Useful for schemas on a date or advanced query operations.

Tread carefully: Direct API accesses

Direct access to Hibernate's mystical internal APIs is tempting, but beware: It's a double-edged sword. Change winds of future updates might just upset your apple cart. Keep an eye out if maintenance and upgrades are on your horizon.

Strutting your code with style

Playing with CriteriaLoader and Fields

For SQL diggers out there, link arms with CriteriaLoader and twirl in the ecstasy of Field reflections. Dive deeper into Criteria SQL belly!

LoadQueryInfluencers: Because influence is bliss

LoadQueryInfluencers twinned with SessionFactoryImplementor form the power couple of Hibernate realm. Control and fine-tune your SQL generation to your heart's content!

NHibernate: Hibernate's distant cousin

Hibernate's Java implementation not spicy enough? Well, there's always NHibernate, the .NET cousin with familiar yet distinct habits. Consider ported solutions from NHibernate for a flavoursome change!

explain-codes / Sql / How to get SQL from Hibernate Criteria API (*not* for logging)

Linked

%like% Query in spring JpaRepository



How to view the SQL queries issued by JPA?



How to add custom method to Spring Data JPA



Can I use enum parameter into JpaRepository nativeQuery?



How can I log SQL statements in Spring Boot?

