Issue
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) }
)
}
}
Solution
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.
Answered By - Ybri
Answer Checked By - David Marino (JavaFixing Volunteer)
