JPA printed native SQL is disgusting, how to do.

Use JPA interceptors and re’s to print slightly nicer SQL.

Print JPA’s native SQL:

# native print SQL
#spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=debug
#spring.jpa.properties.hibernate.format_sql=true
# print parameters
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=trace
Copy the code

Spring. Jpa. Show – SQL and logging.level.org.hibernate.SQL effect. Print the SQL as follows:

select user0_.id as id1_1_0_, user0_.age as age2_1_0_, user0_.name 
as name3_1_0_, user0_.version as version4_1_0_ from user user0_ where user0_.id=?
Copy the code

Spring. Jpa. Properties. Hibernate. Format_sql this is formatting SQL, the results are as follows:

select
    user0_.id as id1_1_0_,
    user0_.age as age2_1_0_,
    user0_.name as name3_1_0_,
    user0_.version as version4_1_0_ 
from
    user user0_ 
where
    user0_.id=?
Copy the code

Format a wool, did not become very beautiful…

Use custom interceptors Spring. Jpa. Properties. Hibernate. Session_factory. Statement_inspector = com. Ler. Demo. The interceptor. JpaInterceptor custom interceptors, results are as follows:

select us.id , us.age , us.name , us.version  from user us where us.id=?
Copy the code

Originally about JPA also come to an end, for JPA, really do not like it.

A magical BUG forced me to bite the bullet and optimize the interceptor again.

In JPA, save and Update are merged into one, and the code of this project is quite strange, with various inheritance, various nesting, various binding, and then no this, no super, an empty save(), writing unreadable code in various ways, and I don’t know what the hatred is…

And JPA features are also more disgusting, nesting, binding update and save is synchronous, and the database has optimistic lock, originally inexplicable good, but there is a method, inexplicable optimistic lock conflict. Looking at printed native SQL, a sickening feeling rises.

It would be nice if you could print out the call stack for SQL.

#### Specific methods:

private static void defaultTag(String startTag) {
    List<String> logList = new ArrayList<>();
    StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
    for (StackTraceElement e : stackTrace) {
        if(! e.getClassName().startsWith(startTag) || e.getClassName().endsWith("JpaInterceptor")) {
            continue;
        }
        // Get the method of the code using the utility class, the Java file name, and the line number,
        MethodName(filename.java :LineNumber);
        The MethodName in the front is the auxiliary information, followed by information that the development tool can identify to jump.
        logList.add(String.format("JpaInterceptor_class: %s.%s (%s:%s)", e.getClassName(), e.getMethodName(), e.getFileName(), e.getLineNumber()));
    }
    if (logList.size() > 5) {
        logList = logList.subList(0.5);
    }
    Collections.reverse(logList);
    logList.forEach(log::warn);
}
Copy the code

Just call this method at the inspect method entry

@Override
public String inspect(String sql) {
    defaultTag("com.ler.demo"); . }Copy the code

The parameter is the top-level package name, filtering out other stack information.

And using a format like MethodName(filename.java :LineNumber), in the console, you can jump straight to a location in the code.

The parentheses are blue. Click to jump directly to the corresponding code.

Here, because it is the test code, it is relatively simple, and there is less call chain. In that project, the call chain is basically seven or eight layers. If it’s long, you can set the depth, which I set to 5, and you can generally see the entire process from receiving the request to executing the SQL.

And I’m using the reverse method here, because it’s the reverse stack, so I have to reverse it a little bit more.

Of course, this can be used not only in JPA interceptors, but also in method entrances if you want to see a method call chain.

In fact, you can add the following code to print stack information, which can be directly located to the place where the exception is thrown:

List<StackTraceElement> stackTraceElements = Arrays.asList(ex.getStackTrace());
if (stackTraceElements.size() > MAX_DEPTH) {
    stackTraceElements = stackTraceElements.subList(0, MAX_DEPTH);
}
ex.setStackTrace(stackTraceElements.toArray(new StackTraceElement[stackTraceElements.size()]));
LOGGER.error("handleException, ex caught, uri={}, httpResult={}", request.getRequestURI(), JSON.toJSONString(httpResult), ex);
Copy the code

The effect is as follows:

Well, that’s all for today

Finally, welcome everyone to pay attention to my public number, Nanzhao Blog study together, progress together. Refueling 🤣