spring - 按 QueryDSL 中可为空的子对象的属性对模型对象进行排序

Question

我的要求是检索所有图像(WAHImage)对象，无论样本(SpecimenImpl)是否为空，分类单元名称(WACensusImpl)是否为空，并按样本名称或分类单元名称(如果可用)排序结果，并显示首先是具有空样本和空分类名称的图像对象。

使用下面的代码，我得到一个错误 No property coalesce(wAHimage found for type WAHImage .

很明显，我的用例是 not (yet) fully supported in QueryDSL ，即查询实体并按其可为空的子对象的属性对结果对象进行排序的能力。这是因为 QueryDSL 使用 CROSS JOIN 而不是 LEFT JOIN，并且您不能使用 CROSS JOIN 按子对象属性排序。这似乎是我沮丧的根源，我现在正在寻找其他解决方案，而不是坚持使用 QueryDSL。

Spring Data JPA 存储库:

public interface ImageRepository extends JpaRepository<WAHImage, Long>,
        QueryDslPredicateExecutor<WAHImage> {
    Page<WAHImage> findAllByIsDeleted(boolean isDeleted, Pageable page);
}

Spring Boot (1.5.1) Service，它的构造函数中 Autowiring 了 ImageRepository:

@Service
public class ImageService {
    private ImageRepository imageRepository;
    public Page<WAHImage> get(Pageable pageable) {
        return imageRepository.findAllByIsDeleted(false, ImagePredicates.orderByName(pageable));
    }

ImagePredicates.orderByName(Pageable):

static QPageRequest orderByName(Pageable page) {

    QWAHImage image = QWAHImage.wAHImage;
    QWACensusImpl name = image.census;
    QSpecimenImpl specimen = image.specimen;
    OrderSpecifier genus = name.name1.coalesce(specimen.genus).asc();
    OrderSpecifier species = name.name2.coalesce(specimen.species).asc();
    OrderSpecifier rank = name.rank4.coalesce(
            name.rank3.coalesce(specimen.rank)).asc();
    OrderSpecifier infraspecies = name.name4.coalesce(
            name.name3.coalesce(specimen.infraspecies)).asc();

    OrderSpecifier[] sort = new OrderSpecifier[] {
            genus, species, rank, infraspecies
    };

    return new QPageRequest(page.getPageNumber(), page.getPageSize(), sort);
}

Controller :

@Controller
public class ImageController {
    @GetMapping("/list")
    public String list(Pageable pageable, Model model) {
        Page<WAHImage> searchResult = imageService.get(pageable);
    }
}

错误:

org.springframework.data.mapping.PropertyReferenceException: No property coalesce(wAHImage found for type WAHImage!
at org.springframework.data.mapping.PropertyPath.<init>(PropertyPath.java:77)
at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:329)
at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:309)
at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:272)
at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:243)
at org.springframework.data.jpa.repository.query.QueryUtils.toJpaOrder(QueryUtils.java:542)
at org.springframework.data.jpa.repository.query.QueryUtils.toOrders(QueryUtils.java:496)
at org.springframework.data.jpa.repository.query.JpaQueryCreator.complete(JpaQueryCreator.java:195)
at org.springframework.data.jpa.repository.query.JpaQueryCreator.complete(JpaQueryCreator.java:143)
at org.springframework.data.jpa.repository.query.JpaQueryCreator.complete(JpaQueryCreator.java:52)
at org.springframework.data.repository.query.parser.AbstractQueryCreator.createQuery(AbstractQueryCreator.java:88)
at org.springframework.data.jpa.repository.query.PartTreeJpaQuery$QueryPreparer.createQuery(PartTreeJpaQuery.java:144)
at org.springframework.data.jpa.repository.query.PartTreeJpaQuery.doCreateQuery(PartTreeJpaQuery.java:79)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.createQuery(AbstractJpaQuery.java:190)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:184)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:85)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:483)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:461)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy143.findAllByIsDeleted(Unknown Source)
at x.y.z.image.ImageService.get(ImageService.java:120)
at x.y.z.image.ImageService$$FastClassBySpringCGLIB$$6d3a7999.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:652)
at x.y.z.image.ImageService$$EnhancerBySpringCGLIB$$21037cfe.get(<generated>)
at x.y.z.controller.image.ImageController.list(ImageController.java:132)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:116)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)

WAHImage 模型类的相关部分。

@Entity
@Table(name = "image")
public class WAHImage extends PersistentImpl {
    private WACensusImpl census = new WACensusImpl();
    private SpecimenImpl specimen = new SpecimenImpl();
    private boolean isDeleted = Boolean.FALSE;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "census_id", nullable = true)
    public WACensusImpl getCensus() {
        return census;
    }

    public void setCensus(WACensusImpl census) {
        this.census = census;
    }

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "specimen_id", nullable = true)
    public SpecimenImpl getSpecimen() {
        return specimen;
    }

    public void setSpecimen(SpecimenImpl specimen) {
        this.specimen = specimen;
    }

    @Column(name = "is_deleted")
    public boolean getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(boolean isDeleted) {
        this.isDeleted = isDeleted;
    }

}

QWAHImage QueryDSL 类的相关部分。

/**
 * QWAHImage is a Querydsl query type for WAHImage
 */
@Generated("com.querydsl.codegen.EntitySerializer")
public class QWAHImage extends EntityPathBase<WAHImage> {

    public static final QWAHImage wAHImage = new QWAHImage("wAHImage");

    public final x.y.z.db.impl.QPersistentImpl _super = new x.y.z.db.impl.QPersistentImpl(this);

    public final x.y.z.reference.census.impl.QWACensusImpl census;

    //inherited
    public final NumberPath<Long> id = _super.id;

    public final BooleanPath isDeleted = createBoolean("isDeleted");

    public final x.y.z.reference.specimen.impl.QSpecimenImpl specimen;
}

QWACensusImpl 的相关部分:

/**
 * QWACensusImpl is a Querydsl query type for WACensusImpl
 */
@Generated("com.querydsl.codegen.EntitySerializer")
public class QWACensusImpl extends EntityPathBase<WACensusImpl> {

    public static final QWACensusImpl wACensusImpl = new QWACensusImpl("wACensusImpl");

    public final x.y.z.db.impl.QPersistentWithIDImpl _super = new x.y.z.db.impl.QPersistentWithIDImpl(this);

    //inherited
    public final NumberPath<Long> id = _super.id;

    public final StringPath name1 = createString("name1");

    public final StringPath name2 = createString("name2");

    public final StringPath name3 = createString("name3");

    public final StringPath name4 = createString("name4");

    // extraneous parts omitted.
}

Answer 1

经过一些广泛的背景研究，我的用例似乎有 a long history并且仍然是 common one .

为了解决这个问题，我可以 (a) get access to the EntityManager在 Spring Data JPA并从那里使用 LEFT JOIN s 构建查询，之后我应该可以使用 COALESCE ImagePredicates.orderByName(Pageable) 中的关键字.

或者，(以及我决定使用的方法)我在 ImageRepository.findAllByIsDeleted(boolean, Pageable) 上使用 Spring Data @Query :

public interface ImageRepository extends JpaRepository<WAHImage, Long>,
        QueryDslPredicateExecutor<WAHImage> {
    @Query("select i from WAHImage i " +
            "left join i.specimen s " +
            "left join i.census c " +
            "left join i.author a " +
            "left join i.copyright co " +
            "left join i.allowedUse au " +
            "where i.isDeleted = ?1 " +
            "order by coalesce(s.genus, c.name1) asc, " +
            "coalesce(s.species, c.name2) asc, " +
            "coalesce(s.infraspecies, c.name4, c.name3) asc")
    Page<WAHImage> findAllByIsDeleted(boolean isDeleted, Pageable page);
}