How to get the JPA generated SQL query?

Question

I use JPA specification and Hibernate as my vendor. I need somehow to take the generated SQL Query which is sent to the the DB (printed to the sysout) and save it as a simple string.

Is there a way to do this?

EDIT

Let me make it a beat clearer: I don't need hibernate log. I need to be able to execute the same query on a different DB. Therefore, I need to get the SQL query as is, and hold it in a normal String variable.

Edit 2

Is there a util which I can provide it a bean and it will automatically generate an Insert query? can I somehow use Hibernate beans here? I know it's a beat complex.

Thanks,

Idob

Edit you question, it's not quite clear that you ask about spring data jpa module, spring can use jpa without spring data — Alexandr, Nov 09 '13 at 03:19
It doesn't look like there is a nice clean solution. You might be able to pass the EntityManager into the Specification implementation via a parameter and then use the code found here https://antoniogoncalves.org/2012/05/24/how-to-get-the-jpqlsql-string-from-a-criteriaquery-in-jpa/ — bytor99999, Feb 20 '18 at 22:44
@ido-barash Did you got the solution for "I need to get the SQL query as is, and hold it in a normal String variable." ? If yes, please let me know. — Krish, Mar 29 '18 at 18:05
Check this https://stackoverflow.com/questions/554481/how-to-get-sql-from-hibernate-criteria-api-not-for-logging . It is possible to get SQL in String using Hibernate — David, Aug 13 '18 at 06:04

Jade Devin Nocum Cabatlao · Answer 1 · 2016-11-01T04:44:52.233

17

Create a bean like this.

@Bean
public JpaVendorAdapter jpaVendorAdapter(){
    HibernateJpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
    jpaVendorAdapter.setGenerateDdl(true);
    jpaVendorAdapter.setShowSql(true);

    return jpaVendorAdapter;
}

If you're using Spring Boot add it somewhere to your @Configuration.

The logs created from this are executable in MySQL workbench. You stated that you are using JPA and Hibernate. There's no other way except if the database you support are supported by JPA. In that case there is an AbstractJpaVendorAdapter that you can implement.

edited Nov 01 '16 at 04:44

answered Sep 30 '16 at 06:02

Jade Devin Nocum Cabatlao

398
3
9

1

Cool. Able to see the sql statement in my console :) – Arundev Jul 31 '17 at 15:57
And if I also want to see the parameter values? – Fabien Jul 16 '18 at 12:46

score 2 · Answer 2 · answered Jul 11 '14 at 19:34

2

The simple answer to your question is No. What you want to do is something that many developers would also like to do however it was not part of the JPA specification and thus the ability to get the generated SQL will depend upon what the vendor decided to do. Using Hibernate the only way to obtain the SQL is via the log.

answered Jul 11 '14 at 19:34

Scott Gustafson

157
1
10

Found this with example for Hibernate. https://stackoverflow.com/questions/554481/how-to-get-sql-from-hibernate-criteria-api-not-for-logging . It is possible to get SQL in String using Hibernate – David Aug 13 '18 at 06:01

score 1 · Answer 3 · edited May 23 '17 at 12:18

1

You have to enable the log4j logging and add an appender for Hibernate to show the queries.

This has already been described here: How to print a query string with parameter values when using Hibernate

edited May 23 '17 at 12:18

Community

1
1

answered Jan 16 '13 at 12:54

ioan

489
2
4

2

This is not what I asked!. – Ido Barash Jan 16 '13 at 12:56
1

I need to get the query as a string for later use. not view it in a log. – Ido Barash Jan 16 '13 at 12:57

score 1 · Answer 4 · answered Mar 30 '18 at 06:35

If I understand you correctly, you want to get the insert query which Hibernate is executed on one database, and via code, run it on a different database via entityManager#executeUpdate or similar.

Hibernate does not expose the generated query as it is specific for the dialect of target database. So even if were to get the insert query, it could be pointless.

However in your case, you can create two database connections (via two DataSource or EntityManagerFactory whatever in your case) and call dao.persist(entity) two times for both databases, and let Hibernate handle the query construction part.

Edit: By query I mean native query here, HQL query would be same for both databases. Hope it helps.

score 1 · Answer 5 · answered Oct 13 '19 at 15:16

I don't know what you mean by a generated query but if you use Hibernate and you have javax.persistence.Query query you can get HQL string very easy (for EclipseLink it is similar). And if you have HQL you can translete it to SQL with QueryTranslator.

// Get HQL
String hqlQueryString = query.unwrap(org.hibernate.query.Query.class).getQueryString();

// Translate HQL to SQL
ASTQueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
SessionImplementor hibernateSession = em.unwrap(SessionImplementor.class);
QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator("", hqlQueryString, Collections.emptyMap(), hibernateSession.getFactory(), null);
queryTranslator.compile(Collections.emptyMap(), false);
String sqlQueryString = queryTranslator.getSQLString();

score 0 · Answer 6 · answered Mar 31 '17 at 04:46

Try to add properties in instance of LocalContainerEntityManagerFactoryBean ,Its working for me :-

@EnableJpaRepositories(basePackages = "org.common.persistence.dao")
public class PersistenceJPAConfig {

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() {
        final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource());
        em.setPackagesToScan(new String[] { "org.common.persistence.model" });
        final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(additionalProperties());
        return em;
    }

    final Properties additionalProperties() {
        final Properties hibernateProperties = new Properties();
        hibernateProperties.setProperty("showSql", "true");
        hibernateProperties.setProperty("hibernate.show_sql", "true");
        hibernateProperties.setProperty("hibernate.format_sql", "true");
        hibernateProperties.setProperty("hibernate.query.substitutions", "false");
        return hibernateProperties;
    }
}

How to get the JPA generated SQL query?

6 Answers6

Linked