Validating Native Query and Entity Graph

 Validating Native Query and Entity Graph

Using native queries in Spring JPA/Hibernate can be less enjoyable, but they are occasionally indispensable, especially for enhancing performance. Similarly, EntryGraphs become necessary in specific situations to mitigate N+1 scenarios or define data boundaries retrieved from the database.
Since it's challenging to validate these scenarios without executing each query, inadequate testing can lead to significant issues. The same holds true when refactoring numerous database tables and columns, potentially missing updates in native queries, which may slip through testing and manifest in production.
You may encounter such scenarios as well. Therefore, we've invested effort into developing a solution to validate native queries against a schema. You might be interested in such thing or suggest some improvement over it.

Here's what we've accomplished:
Developed a component that conducts validation by executing each native query against the schema in a fairly generic manner.
To initiate this process, we've created the following class for gathering all native queries:

import org.springframework.context.annotation.ClassPathScanningCandidateComponentProvider;

class RepositoryScanner extends ClassPathScanningCandidateComponentProvider {

    public RepositoryScanner() {
        super(false);
        addIncludeFilter(new AnnotationTypeFilter(Repository.class));
    }

    @Override
    protected boolean isCandidateComponent(AnnotatedBeanDefinition beanDefinition) {
        return true;
    }
}
          

Then using the same class:

  
  private void scanRepositories(String basePackage) throws ClassNotFoundException {
    var candidateComponents = provider.findCandidateComponents(basePackage);

    log.warn(createWarningMessage(format("DB Validation Mode (%s)", basePackage),
        format("Native Queries and Entity Graphs (%s repositories)", candidateComponents.size())));

    for (var repositoryClass : candidateComponents) {
        Class jpaRepository = Class.forName(repositoryClass.getBeanClassName());

        for (var method : jpaRepository.getDeclaredMethods()) {
            var queryAnnotation = method.getAnnotation(Query.class);
            var entityGraph = method.getAnnotation(EntityGraph.class);

            if ((queryAnnotation != null && queryAnnotation.nativeQuery()) || entityGraph != null) {
                log.info("SQL: {}.{} - {}",  jpaRepository.getSimpleName(), method.getName(),
                        queryAnnotation != null ? queryAnnotation.value() : entityGraph.type() + ": " + Arrays.toString(entityGraph.attributePaths()));
            }
        }
    }
}
  

Currently, the invocation and validation aspects are in place (acknowledging that there's room for improvement, but the current setup suffices for our needs).

private Object invokeMethod(Class clazz, Method method) {
    Object[] parameters = new Object[method.getParameterCount()];

    try {
        for (int index = 0; index < parameters.length; index++) {
            Parameter param = method.getParameters()[index];
            parameters[index] = getRandomValueForType(param.getType());
        }

        Object instance = applicationContext.getBean(clazz);
        return transactionTemplate.execute(action -> invoke(instance, method, parameters));
    }
    catch (UndeclaredThrowableException e) {
        if (!(e.getCause() instanceof InvocationTargetException
            && ((InvocationTargetException) e.getCause()).getTargetException().getCause() instanceof ConstraintViolationException)) {
            throw new RuntimeException(format("With Parameters: %s", Arrays.toString(parameters)), e);
        }
    }
    catch (Exception e) {
        throw new RuntimeException(format("With Parameters: %s", Arrays.toString(parameters)), e);
    }

    return null;
}

@SneakyThrows
private Object invoke(Object instance, Method method, Object[] parameters) {
    return method.invoke(instance, parameters);
}

private Object getRandomValueForType(Class type) {
    if (type == Pageable.class) return Pageable.ofSize(1);
    if (type.isEnum()) return type.getEnumConstants()[0];
    if (type == Specification.class) return new UserSpecification(Long.MAX_VALUE, null);
    if (type == boolean.class || type == Boolean.class) return false;
    if (type == LocalDateTime.class) return LocalDateTime.now().plusYears(10);
    if (type == long.class || type == Long.class) return Long.MAX_VALUE;
    if (type == int.class || type == Integer.class) return Integer.MAX_VALUE;
    if (type == String.class) return "DatabaseSchemaValidation";
    if (type == double.class || type == Double.class) return Double.MAX_VALUE;
    if (Set.class.isAssignableFrom(type)) return new HashSet<>();
    if (Collection.class.isAssignableFrom(type) || List.class.isAssignableFrom(type)) return new ArrayList<>();

    log.warn("Type not mapped: {}", type.getName());
    return null;
}

Afterward, we can determine when to execute this validator, or perhaps opt to run it during startup or integration test, such as...

import static java.lang.String.format;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Parameter;
import java.lang.reflect.UndeclaredThrowableException;
import java.time.LocalDateTime;
import java.util.*;

import javax.annotation.PostConstruct;

import org.hibernate.exception.ConstraintViolationException;
import org.springframework.beans.factory.annotation.AnnotatedBeanDefinition;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.ClassPathScanningCandidateComponentProvider;
import org.springframework.context.annotation.Profile;
import org.springframework.core.type.filter.AnnotationTypeFilter;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionTemplate;

import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;

@ConditionalOnProperty(value = "database.schema.validation.enabled", havingValue = "true")
@Component
@Slf4j
@RequiredArgsConstructor
@Transactional
public class DatabaseSchemaValidation {
	private final RepositoryScanner provider = new RepositoryScanner();
	private final ApplicationContext applicationContext;
	private final TransactionTemplate transactionTemplate;

	@PostConstruct
	void validate() throws ClassNotFoundException {
		scanRepositories("com.example.repository");
	}

	private void scanRepositories(String basePackage) throws ClassNotFoundException {
		var candidateComponents = provider.findCandidateComponents(basePackage);

		log.warn("{} - {}", format("DB Validation Mode (%s)", basePackage), format("Native Queries and Entity Graphs (%s repositories)", candidateComponents.size())));

		for (var repositoryClass : candidateComponents) {
			Class jpaRepository = Class.forName(repositoryClass.getBeanClassName());

			for (var method : jpaRepository.getDeclaredMethods()) {
				var queryAnnotation = method.getAnnotation(Query.class);
				var entityGraph = method.getAnnotation(EntityGraph.class);

				if ((queryAnnotation != null && queryAnnotation.nativeQuery()) || entityGraph != null) {
					try {
						invokeMethod(jpaRepository, method);
					} catch (Exception e) {
						log.warn("Exception while calling: {}.{} - {} - {}", jpaRepository.getSimpleName(), method.getName(),
							queryAnnotation != null ? queryAnnotation.value() : entityGraph.type() + ": " + Arrays.toString(entityGraph.attributePaths()),
							e.getMessage());
						log.debug("{}", e.getMessage(), e);
						throw e;
					}
				}
			}
		}
	}

	private static class RepositoryScanner extends ClassPathScanningCandidateComponentProvider {

		public RepositoryScanner() {
			super(false);
			addIncludeFilter(new AnnotationTypeFilter(Repository.class));
		}

		@Override
		protected boolean isCandidateComponent(AnnotatedBeanDefinition beanDefinition) {
			return true;
		}
	}

	public Object invokeMethod(Class clazz, Method method) {
		Object[] parameters = new Object[method.getParameterCount()];

		try {
			for (int index = 0; index < parameters.length; index++) {
				Parameter param = method.getParameters()[index];
				parameters[index] = getRandomValueForType(param.getType());
			}

			Object instance = applicationContext.getBean(clazz);
			return transactionTemplate.execute(action -> invoke(instance, method, parameters));
		}
		catch (UndeclaredThrowableException e) {
			if (!(e.getCause() instanceof InvocationTargetException
				&& ((InvocationTargetException) e.getCause()).getTargetException().getCause() instanceof ConstraintViolationException)) {
				throw new RuntimeException(format("With Parameters: %s", Arrays.toString(parameters)), e);
			}
		}
		catch (Exception e) {
			throw new RuntimeException(format("With Parameters: %s", Arrays.toString(parameters)), e);
		}

		return null;
	}

	@SneakyThrows
	private Object invoke(Object instance, Method method, Object[] parameters) {
		return method.invoke(instance, parameters);
	}

	private Object getRandomValueForType(Class type) {
		if (type == Pageable.class) return Pageable.ofSize(1);
		if (type.isEnum()) return type.getEnumConstants()[0];
		if (type == boolean.class || type == Boolean.class) return false;
		if (type == LocalDateTime.class) return LocalDateTime.now().plusYears(10);
		if (type == long.class || type == Long.class) return Long.MAX_VALUE;
		if (type == int.class || type == Integer.class) return Integer.MAX_VALUE;
		if (type == String.class) return "DatabaseSchemaValidation";
		if (type == double.class || type == Double.class) return Double.MAX_VALUE;
		if (Set.class.isAssignableFrom(type)) return new HashSet<>();
		if (Collection.class.isAssignableFrom(type) || List.class.isAssignableFrom(type)) return new ArrayList<>();

		log.warn("Type not mapped: {}", type.getName());
		return null;
	}
}