vladmihalcea/db-util

If you are using JPA and Hibernate, this tool can auto-detect N+1 query issues during testing.

Merged into Hypersistence Utils

The db-utils project was discontinued because it was merged into Hypersistence Utils.

Introduction

This library contains all sorts of DB utilities like:

• Optimistic Concurrency Retry
• JPA SQL statement count validator

Are you struggling with application performance issues?

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. No more performance issues, no more having to spend countless hours trying to figure out why your application is barely crawling.

Imagine discovering early during the development cycle that you are using suboptimal mappings and entity relationships or that you are missing performance-related settings.

More, with Hypersistence Optimizer, you can detect all such issues during testing and make sure you don't deploy to production a change that will affect data access layer performance.

Hypersistence Optimizer is the tool you've been long waiting for!

Training

If you are interested in on-site training, I can offer you my High-Performance Java Persistence training which can be adapted to one, two or three days of sessions. For more details, check out my website.

Consulting

If you want me to review your application and provide insight into how you can optimize it to run faster, then check out my consulting page.

High-Performance Java Persistence Video Courses

If you want the fastest way to learn how to speed up a Java database application, then you should definitely enroll in my High-Performance Java Persistence video courses.

High-Performance Java Persistence Book

Or, if you prefer reading books, you are going to love my High-Performance Java Persistence book as well.

Installation

You can get the framework from Maven Central by using the following dependency:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>db-util</artifactId>
    <version>1.0.7</version>
</dependency>

User guide

You can find usage examples on my blog:

• How to detect the Hibernate N+1 query problem during testing
• Optimistic Locking Retry with JPA

db-util's Issues

Null argument in function with @Retry causes NullPointerException

How to generate an exception:

1. create method:
   @Retry(on = OptimisticLockException.class, times = 3)
public void testRetry(String x, String y)

2. execute: testRetry("TEST", null)

Exception:

java.lang.NullPointerException at com.vladmihalcea.util.ReflectionUtils.getAnnotation(ReflectionUtils.java:44) at com.vladmihalcea.concurrent.aop.OptimisticConcurrencyControlAspect.retry(OptimisticConcurrencyControlAspec t.java:44) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) ...

I think fix can be apply by changing:
Class[] argClasses = new Class[pjp.getArgs().length];
for (int i = 0; i < pjp.getArgs().length; i++) {
argClasses[i] = pjp.getArgs()[i].getClass();
}
method = pjp.getTarget().getClass().getMethod(pjp.getSignature().getName(), argClasses);

to:
method = pjp.getTarget().getClass().getMethod(pjp.getSignature().getName(), signature.getParameterTypes());

Logback vulnerabilities in 1.0.6 release

Hi! Do you have plans to publish version 1.0.7 on maven? For now, the latest version has logback vulnerabilities

Remove dependency on Logback logging framework

Is it possible to remove the dependency on the Logback logging framework?

Release a new version within latest datasource-proxy migration

The last update of db-util on maven central was 28-Jan-2014:
https://search.maven.org/#search%7Cga%7C1%7Ca%3A%22db-util%22

That is pretty old one. Today I've faced that I need both db-util in test scope and datasource-proxy in compile. The issue is that db-utils was failing on 1.4.7 datasource-proxy..

I made a temporary workaround for the datasource-proxy which db-util is using, but I would like to get both datasource-proxy 1.4.7 and db-util without any additional effort to make them live with each other.

Thanks

Setting up query counter with Spring Boot

Hi Vlad,

TL;DR To make this work with Spring Boot I had to add as a dependency the library https://github.com/gavlyukovskiy/spring-boot-data-source-decorator and add the property decorator.datasource.datasource-proxy.count-query=true to application.properties.

First let me thank you for your fantastic work. Coming to Java/Spring Boot from other platforms has not been a pleasant experience due to the lack of community resources and your contribution is a shining exception.

My latest challenge has been into finding the equivalent functionality to Django's assertNumQueries for testing, since we have been finding hard to keep the Hibernate N+1 situation under control. Your library fills the bill.

However, It was challenging to make it work on our Spring Boot 2 configuration, so I'd like to contribute with the (certainly clumsy) solution I eventually found, and have your opinion if there's a better one.

Just adding the db-util dependency to the pom.xml and calling SQLStatementCountValidators .reset() and .assertSelectCount() was not enough. The counter was not incrementing. I am using the @Transactional and @SpringBootTest annotations in the test class. After much fiddling I ended up coming up with a solution:

1. Add the dependency to spring-boot-data-source in the pom.xml:

        <dependency>
            <groupId>com.github.gavlyukovskiy</groupId>
            <artifactId>datasource-proxy-spring-boot-starter</artifactId>
            <version>1.5.3</version>
        </dependency>

2. Add the property decorator.datasource.datasource-proxy.count-query=true to application.properties.

Thanks

SQLStatementCountMismatchException is interpreted as a test error instead of a test failure by some test runners

Hi,

the SQLStatementCountMismatchException is interpreted as a test error instead of a test failure by some test runners.

With following sample test class:

import com.vladmihalcea.sql.SQLStatementCountValidator;
import org.junit.Test;

import static org.junit.Assert.*;

public class SampleTest {

  @Test
  public void testThatAlwaysFails() {
    assertTrue(false);
  }

  @Test
  public void testWithError() {
    throw new RuntimeException();
  }

  @Test
  public void sqlCountTest() {
    SQLStatementCountValidator.assertSelectCount(10);
  }
}

IDEA displays sqlCountTest() test as having an error from the tested source code instead of a test failure (e.g. testThatAlwaysFails())

This is also applied when running tests with Maven:

$ mvn test

...
Results :

Failed tests:   testThatAlwaysFails(SampleTest)

Tests in error: 
  sqlCountTest(SampleTest): Expected 10 statements but recorded 0 instead!
  testWithError(SampleTest)

Tests run: 3, Failures: 1, Errors: 2, Skipped: 0

This happens because SQLStatementCountMismatchException extends RuntimeException instead of AssertionError.

If the SQLStatementCountMismatchException will extend the AssertionError I think it should be renamed to SQLStatementCountMismatchAssertionError and its subclasses also should contain AssertionError instead of Exception in their names.

I'd like to submit a PR if you don't mind the proposed changes

How to prevent SELECT queries from SequenceGenerator

If I use the pooled SequenceGenerator

 @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_seq")
 @SequenceGenerator(name = "my_seq", sequenceName = "my_seq", allocationSize = 10)

then my query assertions fail sometimes, because due to the pooling, the statement(s)

select nextval ('my_seq')

are sometimes called before and sometimes during the test.

It works, when I change this property to none

spring.jpa.properties.hibernate.id.optimizer.pooled.preferred=none

Is this approach recommended or is there a better way to assert query count when using pooled sequence generator?

Migrate the utilities to Hypersistence Utils and archive this project

As explained in this issue.

Create a JUnit 5 extension to for clearer tests

Currently, we need to write tests like:

@Test 
public void test() {
    SQLStatementCountValidator.reset();
    warehouseProductInfoService.findAllWithNPlusOne();
    assertSelectCount(1);
}

It would be useful if we could write

@ExtendWith(QueryCountExtension.class)
public class Tests {

    @QueryCount(select = 1)
    public void test() {
        warehouseProductInfoService.findAllWithNPlusOne();
    }
}

I can have a look at creating a pull request, but I wanted to get your feedback on whether this is something you'd be interested in and how you'd like the @QueryCount annotation to look like. I was thinking of having something like (with defaults): @QueryCount(select = 0, insert = 0, update = 0, delete = 0, total = <sum of others>)

I'd also suggest a number of methods that take a lambda:

assertSelectCount(1, () -> warehouseProductInfoService.findAllWithNPlusOne());

so that you can do multiple assertions in the same test.

DB-util incompatible with flexipool-spring boot starter

The flexipool spring boot starter 1.6.2 (https://mvnrepository.com/artifact/com.github.gavlyukovskiy/flexy-pool-spring-boot-starter) does not work with datasource-proxy 1.4.7 , which is the dependency version in the pom of db-util. It will throw a NoClassDefFounderError:

java.lang.NoClassDefFoundError: net/ttddyy/dsproxy/proxy/GlobalConnectionIdManager at com.github.gavlyukovskiy.boot.jdbc.decorator.dsproxy.DataSourceProxyConfiguration.connectionIdManagerProvider(DataSourceProxyConfiguration.java:49) ~[datasource-decorator-spring-boot-autoconfigure-1.6.2.jar:na]

This is because GlobalConnectionIdManager is added in version 1.6.0 of datasource-proxy => https://ttddyy.github.io/datasource-proxy/docs/current/api/net/ttddyy/dsproxy/proxy/GlobalConnectionIdManager.html

When I explicitly added a dependency to version 1.7 of datasource-proxy the problem was gone.

Maybe there are other considerations that make upgrading the dependency not trivial.