Show HN: A library for finding origin and context of JPA issued SQL statements

Creator here. As a developer, while working on performance optimisations on microservice with complex domain modeled in JPA, I struggled a lot with investigating why there are dozens of various SQL triggered by each service call, and what exact place in code is responsible for triggering them. I needed those information to choose and apply optimisation strategy i.e. detect N+1 SELECT problem and extend other query with one more JOIN. It was cumbersome task since there was no easy way to do that, only SQL logs generated by Hibernate with no context, just timestamps. Moreover each new feature can silently break those optimisations.

That's why I decided to write JPlusOne open surce library which would help with easy finding origin and context of JPA issued SQL statements. JPlusOne can generate report for each integration test containing complete tree of JPA persistence related activity or just situations when i.e. lazy loading is occurring, potentially leading to N+1 SELECT problem. Moreover JPlusOne provides API which allows to write tests checking how effectively, from performance point of view, your application is using JPA in context of some business operation (i.e. assert amount of lazy loading operations, or SQL statements in general). I hope you find it useful. Any feedback will be appreciated. Thanks.

I've only had a cursory glance at it but it looks interesting so far. I've had multiple teams struggle with N+1 and inadvertently caused queries with JPA in the past (although it was often related to their poor understanding of JPA itself). In any case I'm interested in any kind of tool which can help debug and prevent these kinds of issues.

So far I was investigating the use of Sniffy ( https://github.com/sniffy/sniffy ) which provides assertion capabilities on the number of queries during the runtime of a JUnit integration test. I'm pairing this with the rest of my JUnit 5 integration test stack (Test Containers, DBUnit, Database Rider and Flyway) which looks promising.

Unfortunately your tool seems to be closely coupled with Spring, correct? Is there a possibility of extracting some of the functionality to work independently of it? I'm thinking of JEE/MicroProfile/CDI based environments, e.g. Quarkus.

This looks really helpful, I'll definitely check it when I run into problems with persistence. Which will be probably very soon - I have to admit I've been using JPA for almost a decade and I still find it so confusing that I'd rather use native SQL for any non-trivial scenario(i.e. everything above plain CRUD). N+1 select problem is far from the hardest issue with JPA, for me it's either mixing managed and detached objects or following weird cascading rules especially during updates.

Looks amazing. I will definitely try this out the next time I am fighting with JPA performance optimizations.

Cool tool.

There’s also Hypersistence Optimizer. It does a lot more than just identifying N+1 issues. It’s paid but I think it also has a free option. The author is one of the creator of Hibernate and puts out amazing material on JPA and Hibernate. Strongly suggest to check out his stuff

Pardon my ignorance, but in this case JPA stands for Java Persistence API.

Hi, how does it compare to https://github.com/braisdom/ObjectiveSql