Hibernate / Spring Data - get executed sql queries count

Question

I'm working on presentation in which I would like to show difference in number of executed sql queries between deleteByPost() method with and without custom query. I'm expecting method without custom query to execute 10001 delete queries and with it just 2.

I'm aware of Hibernate Statistics object and it's methods. I was expecting one of them, named getQueryExecutionCount(), to return the number of sql queries executed against db, but what I'm getting is always a 0.

If anyone wonders hibernate statistics are enabled for sure because I'm getting correct numbers on other properties like the count of deleted entities.

Below there is a complete example showing what I am trying to accomplish.

Is there a way to get the number of generated and executed queries using Statistics or any other mechanism? Currently I'm looking at logs (hibernate.show_sql) and counting printed queries but it just seems wrong to me.

package example5

import org.hibernate.SessionFactory
import org.junit.jupiter.api.AfterEach
import org.junit.jupiter.api.Assertions.assertEquals
import org.junit.jupiter.api.BeforeEach
import org.junit.jupiter.api.Test
import org.junit.jupiter.api.assertAll
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.data.jpa.repository.Modifying
import org.springframework.data.jpa.repository.Query
import org.springframework.data.jpa.repository.config.EnableJpaRepositories
import org.springframework.data.repository.PagingAndSortingRepository
import org.springframework.data.repository.query.Param
import org.springframework.stereotype.Repository
import org.springframework.stereotype.Service
import org.springframework.test.context.junit.jupiter.SpringJUnitJupiterConfig
import org.springframework.transaction.annotation.EnableTransactionManagement
import org.springframework.transaction.annotation.Transactional
import javax.persistence.*

// ENTITIES

@Entity
@Table(name = "posts")
class Post(
        @Id
        @Column(name = "id")
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        var id: Long? = null,

        @Version
        @Column(name = "version")
        var version: Long? = null
)

@Entity
@Table(name = "comments")
class Comment(
        @Id
        @Column(name = "id")
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        var id: Long? = null,

        @Version
        @Column(name = "version")
        var version: Long? = null,

        @JoinColumn(name = "post_id")
        @ManyToOne(fetch = FetchType.LAZY)
        var post: Post? = null
)


// REPOSITORIES

@Repository
interface PostRepository : PagingAndSortingRepository<Post, Long>

@Repository
interface CommentRepository : PagingAndSortingRepository<Comment, Long> {

    @Modifying
    @Query("delete from Comment c where c.post = :post")
    fun deleteByPost(@Param("post") post: Post)
}


// SERVICES

interface PostService {

    fun delete(post: Post)
}

@Service
open class PostServiceImpl(
        @Autowired
        val postRepository: PostRepository,

        @Autowired
        val commentRepository: CommentRepository
) : PostService {
    @Transactional
    override fun delete(post: Post) {
        commentRepository.deleteByPost(post)
        postRepository.delete(post)
    }

}

// CONFIGURATION

@EnableJpaRepositories(basePackages = ["example5"])
@EnableTransactionManagement
@SpringBootApplication(scanBasePackages = ["example5"])
open class FrameworkApplication


// TESTS

@SpringJUnitJupiterConfig(classes = [FrameworkApplication::class])
class Example5(
        @Autowired
        val postService: PostService,

        @Autowired
        val postRepository: PostRepository,

        @Autowired
        val commentRepository: CommentRepository,

        @Autowired
        val emFactory: EntityManagerFactory
) {

    @AfterEach
    fun cleanUp() {
        commentRepository.deleteAll()
        postRepository.deleteAll()
    }

    @Test
    fun testDelete() {
        //given
        var post = Post()
        post = postRepository.save(post)
        val comments = mutableListOf<Comment>()
        for (i in 1..10000) {
            val comment = Comment()
            comment.post = post
            comments.add(comment)
        }
        commentRepository.save(comments)

        val sessionFactory = emFactory.unwrap(SessionFactory::class.java)
        val statistics = sessionFactory.statistics

        //then
        statistics.clear()
        postService.delete(post)

        val executedQueryCount = statistics.queryExecutionCount


        //then
        assertAll(
                { assertEquals(0, postRepository.count()) },
                { assertEquals(0, commentRepository.count()) },
                { assertEquals(2, executedQueryCount) }
        )
    }

}

Answer 1

The library Spring Hibernate Query Utils (https://github.com/yannbriancon/spring-hibernate-query-utils) provides a query counter that you can use to check the number of queries generated.

If you prefer to do it yourself, Hibernate provides a class EmptyInterceptor that contains a hook named onPrepareStatement. You can extend this class and add logic in the onPrepareStatement hook to count the queries.

Take a look at the library code to see how to configure the iterator.

Answer 2

The method onPrepareStatement is now deprecated and is removed in the new Hibernate 6 version. The new way to inspect SQL is to implement a StatementInspector .

I've written a little library (https://github.com/Lemick/hibernate-query-asserts) that can assert the count of SQL queries by type (SELECT, INSERT, ..) generated by Hibernate in your Spring tests, this way, you can be warned whenever the SQL statements change in your tests, and prevent N+1 selects. You can take a look here at the project if you want to know how this is implemented.

A test example that demonstrates the purpose:

@Test
@Transactional
@AssertHibernateSQLCount(inserts = 3)
void create_two_blog_posts() {
    BlogPost post_1 = new BlogPost("Blog post 1");
    post_1.addComment(new PostComment("Good article"));
    post_1.addComment(new PostComment("Very interesting"));
    blogPostRepository.save(post_1);
}