Show SQL from Spring Data JPA/hibernate in Spring Boot
Let’s learn how to configure spring boot to show SQL statements and parameters generated by Spring data JPA and hibernate via various approaches.
Overview
If you are using Spring JPA or hibernate for JDBC abstraction, then you may already find it difficult to debug your SQL statements. For this reason, you can configure your application to print SQL statements using the following methods.
Show SQL from Spring data JPA
The easiest way to show the hibernate-generated SQL into the console is to use the following configuration.
spring.jpa.show-sql=trueCode language: Properties (properties)note that the above configuration is equivalent to spring.jpa.properties.hibernate.show_sql=true. You can also format the printed SQL from JPA by setting up the following hibernate property.
spring.jpa.properties.hibernate.format_sql=trueCode language: Properties (properties)Even though this is way easier compared to other methods, writing logs into stdout is a bad idea. First, it is difficult to manage console logs. Second, writing to stdout directly will reduce your application performance.
Also, with this approach, you can’t see the parameters of the JPA SQL statements.
Show SQL with JPA Parameters
Hibernate prints the SQL and their parameter bindings all along. However, they are in DEBUG and TRACE levels. We can instruct the logging framework to show these SQL logs using the following configuration.
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACECode language: Properties (properties)
You can even fine-grain the logs by setting the trace to only BasicBinder class.
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACECode language: Properties (properties)Using XML based logging configuration
If your application is using a logback.xml or similar, then you can write the above configuration as shown here.
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<include resource="org/springframework/boot/logging/logback/default.xml"/>
<include resource="org/springframework/boot/logging/logback/console-appender.xml" />
<root level="INFO">
<appender-ref ref="CONSOLE" />
</root>
<logger name="org.hibernate.SQL" level="DEBUG"/>
<logger name="org.hibernate.type" level="TRACE"/>
</configuration>Code language: HTML, XML (xml)Summary
To conclude, we learned how to show spring data JPA generated SQL and their parameters in console output as well as in log files.
If you liked this short article, you may also like the following titles related to spring boot.
You can try out these configurations at this GitHub project.
If you want to print exact SQL/HQL with parameter, then create the following class in you Spring boot project.
import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;
@Aspect
@Component
public class ICMSPointcut {
//@Around(“execution(* getMtMsgparameterFromCache(..))”) // not working because AspectJ and Caching is not working together.
/*public Object aroundAccessingAllStationsCache(ProceedingJoinPoint joinPoint) throws Throwable {
System.out.println(“joinPoint- ” + joinPoint.toLongString());
Object o = joinPoint.proceed();
if(o != null) {
ICMSCacheItem c = (ICMSCacheItem)o;
if(c.expiryTime < System.currentTimeMillis()) {
System.out.println("Expired value " + c.o);
return null;
}
}
return o;
}*/
@Around("execution(public * *(..)) && @annotation(org.springframework.data.jpa.repository.Query)")
/*@Around("execution(* findAllReceivedMessagesByRoleAndLocNewerSQLSearch(..)) || execution(* findAllReceivedMessagesByRoleAndLocOlderSQLSearch(..))")*/
public Object findAllReceivedMessagesByRoleAndLocNewerSQLSearch(ProceedingJoinPoint joinPoint) throws Throwable {
//System.out.println("joinPoint- " + joinPoint.toLongString());
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
Method method = signature.getMethod();
Query query = method.getAnnotation(Query.class);
final StringBuilder sql = new StringBuilder(query.value());
if(query.nativeQuery()) {
Object[] params = joinPoint.getArgs();
for(int i = 0; i < params.length; i++) {
Object param = params[i];
for(int j = 1; j < sql.length(); j++) {
char c = sql.charAt(j);
if(c == '?') {
if(sql.charAt(j-1) == '\\') {
continue;
}
sql.deleteCharAt(j);
if(param instanceof Number) {
sql.insert(j, param != null ? param.toString() : "null");
}
else if(param instanceof java.sql.Date) {
Timestamp ts = (Timestamp)param;
sql.insert(j, param != null ? "to_date('DD-MM-YYYY HH24:MI:SS','"+new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss").format(ts)+"')" : "null");
}
else {
sql.insert(j, param != null ? "'" + param.toString() + "'" : "null");
}
break;
}
}
}
}
else {
Map paramNameIndexMap = new HashMap();
Object[] params = joinPoint.getArgs();
Annotation[][] anotss = method.getParameterAnnotations();
for(int i = 0; i < anotss.length; i++) {
Annotation[] anots = anotss[i];
org.springframework.data.repository.query.Param paramAnnot = null;
for(int j = 0; j < anots.length; j++) {
if(anots[j].annotationType().getName().equals("org.springframework.data.repository.query.Param")) {
paramAnnot = (org.springframework.data.repository.query.Param)anots[j];
break;
}
}
if(paramAnnot == null) {
continue;
}
paramNameIndexMap.put(i, paramAnnot.value());
}
for(int i = 0; i < params.length; i++) {
String paramName = paramNameIndexMap.get(i);
Object param = params[i];
String paramNameWithColon = ":" + paramName;
int indx = sql.indexOf(paramNameWithColon);
if(indx != -1) {
final String paramConstantVal;
if(param instanceof Number) {
paramConstantVal = param != null ? param.toString() : "null";
}
else if(param instanceof java.sql.Date) {
Timestamp ts = (Timestamp)param;
paramConstantVal = param != null ? "to_date('DD-MM-YYYY HH24:MI:SS','"+new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss").format(ts)+"')" : "null";
}
else {
paramConstantVal = param != null ? "'" + param.toString() + "'" : "null";
}
sql.replace(indx, indx+paramNameWithColon.length(), paramConstantVal);
}
}
}
System.out.println(method.getName()+ " Exact "+(query.nativeQuery()?"SQL":"HQL")+" from Pointcut:\n" + sql + "\n——————————————————————-");
return joinPoint.proceed();
}
}
Ain’t nobody got time for that I’m afraid…