Micronaut Data JPA Specifications with Projections

[karolbryzgiel]

Issue description

I am trying to use Specification with Projection.

Following is the projection :

@Introspected
public class ProductShortDto {

    private Long id;

    private String name;
}

My repository method is:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<ProductShortDto> {

}

My Query specifications builder:

public class ProductShortSpecifications {

    public static QuerySpecification<ProductShortDto> name(String name) {
        return (root, query, cb) -> name == null ? null :
                cb.equal(root.get("name"), name);
    }
}

Micronaut Data version: 4.9.2

I tried invoking the repository using findAll:

        List<ProductShortDto> all = productRepository.findAll(productFilters.toShortPredicate());

But the select query has all the columns. How do I select only the columns mentioned in projection and its distinct values?

[dstepanov]

The generic of JpaSpecificationExecutor is supposed to be the entity root.

Use CriteriaQueryBuilder for this scenario:

      var query = cb.createQuery(ProductShortDto)
      var root = query.from(Product)

      query.where(cb.equal(root.get("name"), name))
      return query

Note this will make Hibernate create a DTO, not Micronaut.

[karolbryzgiel]

@dstepanov thanks for reply.

I am trying to use this solution, but it’s not working for me. Do you have any example of how to implement it?

I have modified the repository in the following way:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
    List<ProductShortDto> getAll(QuerySpecification<ProductShortDto> querySpecification);
}

And I have this method to return a QuerySpecification:

    public static QuerySpecification<ProductShortDto> name(String name) {
        return (root, query, cb) -> {
            CriteriaQuery<ProductShortDto> query1 = cb.createQuery(ProductShortDto.class);
            Root<Product> root1 = query1.from(Product.class);
            query1.where(cb.equal(root1.get("name"), name));
            return query1.getRestriction();
        };
    }

When fetching data, I get the following error:

org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - dev.itrust.product.Product(799810928193541)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3703)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3646)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3630)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBasicValuedPath(BaseSqmToSqlAstConverter.java:4298)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBasicValuedPath(BaseSqmToSqlAstConverter.java:445)
	at org.hibernate.query.sqm.tree.domain.SqmBasicValuedSimplePath.accept(SqmBasicValuedSimplePath.java:145)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:7549)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitComparisonPredicate(BaseSqmToSqlAstConverter.java:445)
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.accept(SqmComparisonPredicate.java:111)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2484)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:2080)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:445)
	at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:122)
	at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:244)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1934)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1619)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:445)
	at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:234)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:784)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:422)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:328)
	at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:302)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:526)
	at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:423)
	at org.hibernate.query.Query.getResultList(Query.java:120)

[dstepanov]

Please use CriteriaQueryBuilder

[dstepanov]

I will close it as it's not a bug.