2

At runtime an application (based on Java/Spring/Hibernate) throws the following exception:

07:18:38.511 ERROR Error for batch element #1: The insert or update value of the FOREIGN KEY "SOME_FIELD.SQL_11410213222" is not equal to any value of the parent key of the parent table.. SQLCODE=-530

The stack trace only partially helps in finding the root of the issue (here a foreign key constraint violation).

Is there a way to also print the sql which caused this exception (or at least the parameters used in the statement)? I'm aware this can be achieved by tweaking the logging configuration but then both the statements which run fine and the statements which cause the errors will be logged. I only want to output info relevant to the erroneous statements... Is this doable ?

Bhesh Gurung
  • 50,430
  • 22
  • 93
  • 142
Eleco
  • 3,194
  • 9
  • 31
  • 40
  • 1
    yes you can http://stackoverflow.com/questions/2536829/hibernate-show-real-sql – osdamv Nov 23 '11 at 22:05
  • @osdamv: that will show me all of the sql statements while I'm only interested in the sql statements which end up throwing an Hibernate exception. – Eleco Nov 24 '11 at 07:54
  • 2
    Why do you care about logging "only for errors". Once you fix the error, turn detailed logging off again. – Bohemian Nov 26 '11 at 19:14
  • @elec: How Hibernate knows, which exceptional situation is really erroneous? For example, if SQL insert caused `DataIntegrityViolationException` then application may wish to change the data and re-try. – dma_k Nov 27 '11 at 13:31

2 Answers2

3

Log4Jdbc is the JDBC proxy driver that can intercept JDBC calls and log the information such as the exact SQL executed in the DB and the SQL timing information.

What is nice is that Log4Jdbc can log the actaul values of the binding parameters in the same line .It greatly increase readability IMO compared to the built-in hibenrate logging which the binding paramters are shown as "?" and the actual values of the binding paramters are shown in different lines.

By setting the logging level of the logger jdbc.sqlonly to ERROR , you can restrict that SQL will only be logged when the exception is thrown.

Here is the brief points to setup the logging with log4jdbc and log4j. For the detailed configuration , you can refer to the official site

1.Change to use Log4Jdbc 's JDBC driver:

 <property name="connection.driver_class">net.sf.log4jdbc.DriverSpy</property>

2.For the jdbc url that you are using , replace jdbc with jdbc:log4jdbc , for example:

<property name="connection.url">jdbc:log4jdbc:postgresql://localhost:5432/Test?protocolVersion=2</property>

3.Configure the logging level for different loggers in log4j.properties. For example ,to log the SQL only when the exception is thrown:

log4j.logger.jdbc.sqlonly=ERROR
Ken Chan
  • 84,777
  • 26
  • 143
  • 172
0

You can also enable Hibernate Logging by setting org.hibernate.SQL to debug and org.hibernate.type to trace in your log configuration

Guenter
  • 16
  • 2