Port Data JPA Specification to Data Jdbc/R2dbc

[hantsy]

Similar to Specification support in Micronaut Data Jdbc/R2dbc, add Specification(using JPA Criteria API directly) to Jdbc/R2dbc world.

• My example of using Specification in Micronaut Data Jdbc
• My example of using Specification in Micronaut Data R2dbc

[DiegoKrupitza]

using JPA API directly

As far as I can see the Specification is inside org.springframework.data.jpa.domain (in spring-data-jpa) and there is no way to use the spring-data-JPA API directly from spring-data-JDBC. It is needed to fully "port" the Specification API to JDBC if I am not mixing up something.

[DiegoKrupitza]

If we want to use the exact API as spring-dat-JPA, there is a small obstacle in the way. Spring-data-jpa uses the EntityManager to get a CriteriaBuilder which is latter used to generate the CriteriaQuery and apply the Specification. Since spring-data-jdbc does not have an EntityManager (to be precise it uses JdbcAggregateOperations which then later uses a specific implementation of DataAccessStrategy), I am curious what the approach would be to get a CriteriaBuilder.

[DiegoKrupitza]

I assume using the Criteria API (from JPA) won't make much sense, since the goal of jdbc is not using JPA stuff. Therefore the only thing I can think of, is that we "port" the specification to JDCB by using specifications to generate SQL queries?

[hantsy]

@DiegoKrupitza I guess Micronaut Data using Specification and JPA CriteriaBuilder to generate sql queries internally.

[DiegoKrupitza]

@DiegoKrupitza I guess Micronaut Data using Specification and JPA CriteriaBuilder to generate sql queries internally.

Ok I will take a look at it how Micronaut is dealing with this problem. Maybe this will give me a better insight on how spring-data-jdbc could do it.

[hantsy]

In Micronaut Data, there are some specific RactorJpaSpecficationExecutor, CoroutineJpaSpecificationExecutor for Micronaut Data R2dbc(to return RactiveStreams types), but Jdbc uses the same JpaSpecficationExecutor as Data JPA.

[DiegoKrupitza]

In Micronaut Data, there are some specific RactorJpaSpecficationExecutor, CoroutineJpaSpecificationExecutor for Micronaut Data R2dbc(to return RactiveStreams types), but Jdbc uses the same JpaSpecficationExecutor as Data JPA.

In spring-data-jpa there is JpaSpecficationExecutor too. I actually started to port Specification from Spring-data-jpa to spring-data-Jdbc but go stuck when "generating" the (criteria) query for jdbc since I could not find a way to do this.

[DiegoKrupitza]

As far as I can see Micronaut creates custom "CriteriaQuery" that is build at runtime, so CriteriaQueries can be created although no entity manager is present in JDBC. (see RuntimeCriteriaBuilder usage in class AbstractSpecificationInterceptor).

The custom RuntimeCriteriaBuilder is then later used to generate the SQL that will be executed.

QueryResult queryResult = ((QueryResultPersistentEntityCriteriaQuery) criteriaQuery).buildQuery(sqlQueryBuilder);
String query = queryResult.getQuery();

So if I understand correctly: if we want to port JPA Specification to JDBC with the same API as JPA, we somehow need to implement our custom "CriteriaRuntime Wrapper" like micronaut. In other words have a custom implementation of jakarta.persistence.criteria.Root, jakarta.persistence.criteria.CriteriaQuery and jakarta.persistence.criteria.CriteriaBuilder from jakarta.persistence:jakarta.persistence-api

[schauder]

The JPA Criteria API is not a good model to imitate. It allows to manipulate all kinds of things: where clauses, group by, projection although the Spring Data JPA Specification really only works as long as you limit yourself to manipulating the Where clause.

Spring Data Relational has a Criteria API, which so far is considered semi internal. This could serve as a basis.

I'd like to see the opinion of @mp911de about this.

In any case It like to push this change back, because there are some changes in the working which might heavily affect this. I'm therefore marking this as blocked.

[DiegoKrupitza]

Spring Data Relational has a Criteria API, which so far is considered semi internal. This could serve as a basis.

I think this would be a better thing than JPA Criteria, since mixing JPA and JDBC may feel quite "hacky". 😅