Pegar sql Hibernate

Asked

Viewed 812 times

4

Good morning. I have the following problem, I need to take sql executed by Hibernate and save in a string. But I have no idea how to do that, someone could help?

java hibernate

  • Do you need it to be printed on the console or do you really need to save to a variable? What do you need to do with this information?

  • I need to save in a String. Because then I have to save it in the bank, for a Log system.

  • Since it is for logs I recommend log4j. Check out the article: https://www.mkyong.com/hibernate/how-to-configure-log4j-in-hibernate-project/

  • So I used log4j and p6spy, however they do not meet my need. would have to take in running time SQL.

  • Using Query in all Hibernate queries? If you have it you can call the getQueryString() method for each query.

  • It will work yes when and a select, in my case I have to pick up(update, Insert and delete), but I am using Session to do this (Session.save(obj)).

  • Then I would not use Session, but query.executeUpdate(). That’s the same answer I already posted. Now you have to see the impact of using that way.

  • Thanks, Giuliana it worked :)

Show 3 more comments

2 answers

2

You can create a appender specific to the Sqls log generated by Hibernate and have this log 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 his 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>

Alter log/sql-gerado-pelo-hibernate.log to the directory and file you want. If you want to save to the folder /tmp (if you are on Linux), you can use /tmp/sql-gerado-pelo-hibernate.log.

Need to test the code above (I made based in this and in this).

As a different file is generated per day, you can then read the file from the previous day, recover all the contents from it and save in the database. In this my example you will not be reading the file at the same time that Log4j will be recording. You can even try this if you want, but you will need to control in reading the file what you have already read and not read yet, before saving in the database.

2


Using org.hibernate.Query from Hibernate (which is the most common practice when someone develops queries using Hibernate) it is possible to 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 log bank.

Reference: https://antoniogoncalves.org/2012/05/24/how-to-get-the-jpqlsql-string-from-a-criteriaquery-in-jpa/

  • You can add an example so that the link is just a reference?

  • Yes, I’ll add it here!

Browser other questions tagged java hibernate

You are not signed in. Login or sign up in order to post.