SQL annotations
Recommended global annotations
Cancel the behavior of global annotations
Recommended method annotations
To check that the configuration is properly done, you can try to add an annotation on a test method in order to make it fail. For example, add @ExpectSelect(0) on a test method that is supposed to send one or several selects to the database.
You can use SQL annotations with a global scope, a class scope or a method scope.
The SQL annotations automatically detect if Hibernate or Spring frameworks are used. You don't have any configuration to do. If a SQL property is not respected, the SQL annotations can suggest you solutions to fix it with these frameworks.
For example, the following message is diplayed when a N+1 select is presumed and Spring Data JPA is detected:
* With Spring Data JPA, you may fix it by adding
@EntityGraph(attributePaths = { "..." }) on repository method.
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.entity-graph
You can take care of several things about SQL statements to promote performance and scalability at the beginning of application development.
-
JDBC roundtrips
- Detect N+1 selects by using @ExpectSelect, @ExpectMaxSelect or @DisableSameSelectTypesWithDifferentParams
- Detect JDBC batching disabled by using @ExpectJdbcBatching
- Detect exactly same selects by using @DisableExactlySameSelects
- Detect N+1 selects by using @ExpectSelect, @ExpectMaxSelect or @DisableSameSelectTypesWithDifferentParams
-
Fetched data
- Detect too many selected columns by using @ExpectSelectedColumn or @ExpectMaxSelectedColumn
Why limit the number of selected columns?
- Detect too many selected columns by using @ExpectSelectedColumn or @ExpectMaxSelectedColumn
-
SQL statements having a LIKE pattern starting with a wildcard by using @DisableLikeWithLeadingWildcard
-
...
| @ExpectInsert |
| @ExpectDelete |
| @ExpectUpdate | @ExpectMaxUpdatedColumn |
| @DisplaySql | @DisplaySqlOfTestMethodBody |
You can also use @DisplayAppliedAnnotations in debug activity.
| @ExpectJdbcBatching | @ExpectMaxQueryExecutionTime |
| @DisableLikeWithLeadingWildcard | @EnableLikeWithLeadingWildcard |
A SqlAnnotationBuilder class is available to easily implement SpecifiableGlobalAnnotations.
package org.quickperf;
import org.quickperf.config.user.SpecifiableGlobalAnnotations;
import org.quickperf.sql.annotation.SqlAnnotationBuilder;
import java.lang.annotation.Annotation;
import java.util.Arrays;
import java.util.Collection;
import static org.quickperf.sql.annotation.SqlAnnotationBuilder.*;
public class QuickPerfConfiguration implements SpecifiableGlobalAnnotations {
public Collection<Annotation> specifyAnnotationsAppliedOnEachTest() {
return Arrays.asList(
// Can reveal some N+1 selects
// https://blog.jooq.org/2017/12/18/the-cost-of-jdbc-server-roundtrips/
disableSameSelectTypesWithDifferentParams()
, // Sometimes, JDBC batching can be disabled:
// https://abramsm.wordpress.com/2008/04/23/hibernate-batch-processing-why-you-may-not-be-using-it-even-if-you-think-you-are/
// https://stackoverflow.com/questions/27697810/hibernate-disabled-insert-batching-when-using-an-identity-identifier
expectJdbcBatching()
, // https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning
disableLikeWithLeadingWildcard()
, disableExactlySameSelects()
// Not relevant with an in-memory database used for testing purpose
, expectMaxQueryExecutionTime( 30, TimeUnit.MILLISECONDS)
);
}
}The class implementing SpecifiableGlobalAnnotations has to be in org.quickperf package.
- @DisableExactlySameSelects
- @DisableSameSelectTypesWithDifferentParams
- @DisableLikeWithLeadingWildcard
- @ExpectJdbcBatching
- @ExpectMaxQueryExecutionTime
