Good morning. I have the following problem, I need to get the sql executed by hibernate and save it to a string. But I have no idea how to do this, could anyone help?
Good morning. I have the following problem, I need to get the sql executed by hibernate and save it to a string. But I have no idea how to do this, could anyone help?
Using Hibernate org.hibernate.Query (which is the most common practice when someone develops queries using Hibernate) you can retrieve the query string using the getQueryString () method:
org.hibernate.Query query = session.createQuery("from Entidade where id= :id ");
query.setParameter("id", "1");
String queryString = query.getQueryString();
You will only need to persist the queryString variable in your database.
Reference: link
You can create a specific appender
for the log of SQLs generated by Hibernate and cause this log to save the SQLs to a specific file. A different file will be generated per day, thanks to DailyRollingFileAppender
:
log4j.properties
log4j.logger.org.hibernate=INFO, hb
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.logger.org.hibernate.tool.hbm2ddl=warn
log4j.logger.org.hibernate.hql=debug
log4j.logger.org.hibernate.jdbc=debug
log4j.appender.hb=org.apache.log4j.DailyRollingFileAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout
log4j.appender.hb.Threshold=TRACE
log4j.appender.hb.RollingPolicy=org.apache.log4j.rolling.TimeBasedRollingPolicy
log4j.appender.hb.RollingPolicy.FileNamePattern=log/sql-gerado-pelo-hibernate-%d{yyyy-MM-dd}.log
In your persistence.xml or hibernate.cfg.xml
<property name="show_sql">true</property>
<property name="format_sql">true</property>
<property name="use_sql_comments">true</property>
Change log/sql-gerado-pelo-hibernate.log
to the directory and file you want. If you want to save the /tmp
folder (if you are on Linux), you can use /tmp/sql-gerado-pelo-hibernate.log
.
You need to test the above code (I've done this and in this ).
Because a different file is generated per day, you can then read the file from the previous day, retrieve all its contents, and save to the database. In this example, you will not be reading the file at the same time that Log4j is recording. You can even try this if you want, but you will need to control in the reading of the file what you have already read and have not read yet, before saving to the database.