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

Question

我的要求是检索所有image（WAHImage）对象，无论标本（标本Impl）是否为空，无论分类单元名称（WACensusImpl）是否为空，并按标本名称或分类单元名称（如果可用）对结果进行排序，首先显示具有空标本和空分类单元名称的image对象。

使用下面的代码，我得到一个错误no property coalesce(wAHimagefound for type wAHimage。

很明显，QueryDSL还不完全支持我的用例，即查询实体并根据其可为空的子对象的属性对结果对象进行排序的能力。这是因为QueryDSL使用交叉联接而不是左联接，并且不能使用交叉联接按子对象属性排序。这似乎是我沮丧的根源，我现在正在四处寻找其他解决方案，而不是坚持使用QueryDSL。

Spring数据JPA存储库：

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

Spring Boot(1.5.1)服务，该服务在其构造函数中自动连接了ImageRepository：

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

ImagePredicates.OrderByName（可分页）：

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
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)

@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 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)访问Spring Data JPA中的EntityManager，并从那里使用left join构建查询，之后我应该可以在ImagePredicates.OrderByName(Pageable)中使用Coalesce关键字。

或者，（以及我决定使用的方法）我对ImageRepository.findAllByisDeleted(boolean，Pageable)使用Spring Data@查询：

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);
}