Remove hardcode of `querySortRewriter` from AbstractStringBasedJpaQuery (sorting in native query)

[ars-java]

I use Spring Data JPA Repository and I have snake_case naming in DB. When I create Pageable there's a problem that in JPQL queries I must use camelCase sort parameter name, but in native queries I must use snake_case sort parameter name.

For example, I have property first_name in DB and firstName in Entity. But I get my Pageable from Controller layer which is higher than Repository layer and I think it's good to use consistent REST API with snake_case sort property naming. And after it I must convert name for few repository query methods to camelCase (JPQL) but not convert for other methods (native). I can't convert it in Repository because I have no implementation, just interface (and don't wanna have it and create custom Repository... because I wanna use Spring way of doing things...) But I don't think that Service layer (between Controller and Repository) is a good place to convert it because Repository method and Pageable are abstractions and must be closed to Service (SOLID). Then I wanna have some place to convert sort property from camelCase to snake_case just for native query. I know that there's a QueryRewriter where I can rewrite query, but there're 2 problems:

• I must add QueryRewriter to every native query annotation (boilerplate)
• The query I see in QueryRewriter is already has added "order by". Then I need to extract already added part of string, remove it and add my own "order by ...". It's non-optimal way to do things.

Why add incorrect sorting to replace it later?

We have class NativeJpaQuery which processes native queries, but this class use AbstractStringBasedJpaQuery to add sorting to query

        String getSortedQueryString(Sort sort) {
		return querySortRewriter.getSorted(query, sort);
	}

this method use private final QuerySortRewriter querySortRewriter; and I thought that I can change it, but:

• Constructor of AbstractStringBasedJpaQuery has no such parameter
• Creation of instance of implementation of QuerySortRewriter is hard-coded in class AbstractStringBasedJpaQuery

		JpaParameters parameters = method.getParameters();
		if (parameters.hasPageableParameter() || parameters.hasSortParameter()) {
			this.querySortRewriter = new CachingQuerySortRewriter();
		} else {
			this.querySortRewriter = NoOpQuerySortRewriter.INSTANCE;
		}

I think it's better to add possibility to change implementation of querySortRewriter globally. For example, autowire it as a bean and add constructor parameter for autowiring (there's interface QuerySortRewriter already). It gives me possibility to create my own implementation of such bean, add it to configuration and use good optimal implementation globally.

[mp911de]

QuerySortRewriter was never intended to be a public API, rather it is an internal aspect to qualify caching of rewritten statements.

For the 3.5 development line (and earlier) you can configure a custom QueryRewriterProvider that is based on public API through providing a RepositoryFactoryCustomizer via the JpaRepositoryFactoryBean. That way, you don't need to provide your rewriter for each query, see example code:

@Bean
static BeanPostProcessor beanPostProcessor(@Lazy RepositoryFactoryCustomizer customizer) {

	return new BeanPostProcessor() {

		@Override
		public Object postProcessBeforeInitialization(Object bean, String beanName) throws BeansException {

			if (bean instanceof JpaRepositoryFactoryBean<?, ?, ?> factory) {
				factory.addRepositoryFactoryCustomizer(customizer);
			}

			return BeanPostProcessor.super.postProcessBeforeInitialization(bean, beanName);
		}
	};
}

@Bean
RepositoryFactoryCustomizer customizer(BeanFactory beanFactory) {

	return factory -> {

		if (factory instanceof JpaRepositoryFactory jpaRepositoryFactory) {

			jpaRepositoryFactory.setQueryRewriterProvider(new BeanFactoryQueryRewriterProvider(beanFactory) {

				@Override
				public QueryRewriter getQueryRewriter(JpaQueryMethod method) {

					return (query, sort) -> {
						return query; // sorted query that can be rewritten;
					};
				}
			});
		}
	};
}

With the 4.0 development line (currently on main), we have refined QueryEnhancer through QueryEnhancerSelector (@EnableJpaRepositories(queryEnhancerSelector = YourQueryEnhancerSelector.class)) allowing you to define a selector and factory for rewriting queries. Our JSqlParserQueryEnhancer implementation is public and that could serve as entrypoint for subclassing or delegating sort rewriting.

Let me know whether this helps and if you need further assistance.