How to get JPA generated SQL query?

Question

I use the JPA and Hibernate specification as my provider. I need to somehow take the generated SQL query, which is sent to the database (printed in sysout) and saves it as a simple string.

Is there any way to do this?

EDIT

Let me make this clearer: I don't need a sleep log. I need to execute the same query in another DB. Therefore, I need to get the SQL query as is and hold it in a regular String variable.

Edit 2

Is there a utility that I can provide a bean for it and it will automatically generate an Insert request? can i somehow use hibernate beans here? I know this is a complex complex.

Thanks,

Idob

Answer 1

Create a bean like this.

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

If you are using Spring Boot, add it somewhere in @Configuration.

Logs created from this are executed in MySQL Workbench. You stated that you are using JPA and Hibernate. There is no other way than if the database you support is supported by JPA. In this case, you can implement AbstractJpaVendorAdapter.

Answer 2

You need to enable log4j logging and add a Hibernate application to display requests.

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

Answer 3

If I understand you correctly, you want to receive an insert request that Hibernate runs in one database, and using code to run it in another database using entityManager#executeUpdate or similar.

Hibernate does not provide a generated query because it is specific to the dialect of the target database. So even if you received an insert request, it might be pointless.

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

Change: By query I mean my own query here, the HQL query will be the same for both databases. Hope this helps.

Answer 4

A simple answer to your question: None. What you want to do is what many developers would also like to do, but that was not part of the JPA specification, and therefore the ability to get the generated SQL will depend on what the provider decided to do. Using Hibernate, the only way to get SQL is through a log.

Answer 5

Try adding properties, for example, LocalContainerEntityManagerFactoryBean, for me it works: -

 @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; } } 

Answer 6

I don’t know what you mean by the generated query, but if you use Hibernate and you have javax.persistence.Query query , you can very easily get the HQL string (for EclipseLink it looks like). And if you have HQL, you can convert it to SQL using 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();