Is it possible to use QueryDslPredicateExecutor interface with sub queries for Inheritance.JOINED?

Frank Castro

unread,

Apr 30, 2016, 10:20:50 AM4/30/16

to Querydsl

I have a domain that uses InheritanceType.JOINED so I have a master class with 2 sub classes using the table per class strategy with a discriminator column.

Master Class Item:

@Entity
@Table(name = "item")
@Inheritance(strategy = Inheritance.JOINED)
@DiscriminatorColumn(name = "TYPE")
public abstract class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long id;

@Column(name = "type")
private String type;

// other properties, getters, and setters
}

Sub Class CD:

@Entity
@Table(name = "cd")
@DiscriminatorValue("CD")
public class CD extends Item {
@Enumerated(EnumType.STRING)
private Genre genre

public static enum Genre {
CLASSICAL, ROCK, BLUES, POP;
}

// other properties, getters and setters
}

Sub Class Book

@Entity
@Table(name = "book")
@DiscriminatorValue("BOOK")
public class Book extends Item {
@Enumerated(EnumType.STRING)
private Genre genre

public static enum Genre {
HISTORY, MYSTERY, NOVEL, COMPUTER_SCIENCE;
}

// other properties, getters and setters
}

I would like to be able to use the QueryDSL QueryDslPredicateExecutor because I need to return a Page result of the Item class but by performing a search of each of the sub class tables on their respective genre property.

I know that in with Spring JPA using JPQL I can do the following and it works:

@Query(value = "SELECT DISTINCT(item) FROM Item item"
+ " WHERE item.id IN(SELECT cd.id FROM CD cd WHERE cd.genre IN('ROCK','BLUES'))"
+ " OR item.id IN(SELECT book.id FROM Book book WHERE book.genre IN('HISTORY'))"

I have tried to do this using QueryDSL, but I am not having any success. This was my last attempt:

I first created a BooleanBuilder like this:

BooleanBuilder builder = new BooleanBuilder();

builder.or(QItem.item.as(QCD.class).genre.in(CD.Genre.ROCK, CD.Genre.BLUES));

builder.or(QItem.item.as(QBook.class).genre.in(Book.Genre.HISTORY));

My ItemRepository interface looks like this:

public interface ItemRepository extends JpaRepository<Item, Long>, QueryDslPredicateExecutor<Item> {

}

But when I attempt to perform the query with:

Page<Item> itemPage = itemRepository.findAll(builder, new PageRequest(0, 10));

I get an error and I have included the whole stackTrace below. Basically, it isn't trying to search the Book.Genre genre, only the CD.Genre genre property.

Is it possible to perform a repository query with multiple ORs combining queries for each of the sub classes?

I would be grateful for any help with this.

By the way here are the versions that I am using:

Spring Boot 1.3.2.RELEASE

querydsl-jpa:

<groupId>com.mysema.querydsl</groupId>

<artifactId>querydsl-jpa</artifactId>

</dependency>

build plugin:

<groupId>com.mysema.maven</groupId>

<artifactId>apt-maven-plugin</artifactId>

<goals>

<goal>process</goal>

</goals>

<outputDirectory>target/generated-sources/apt</outputDirectory>

<processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>

</configuration>

</execution>

</executions>

<groupId>com.mysema.querydsl</groupId>

<artifactId>querydsl-apt</artifactId>

</dependency>

</dependencies>

</plugin>

Sincerely,

Frank Castro

stack trace:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [HISTORY] did not match expected type [com.facm.demo.domain.CD$Genre (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [HISTORY] did not match expected type [com.facm.demo.domain.CD$Genre (n/a)]

at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:227)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)

at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)

at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:131)

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.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)

at com.sun.proxy.$Proxy174.findAll(Unknown Source)

at com.facm.demo.repository.ItemRepositoryTest.testQueryDsl(ItemRepositoryTest.java:274)

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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)

at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)

at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)

at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)

at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)

at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)

at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)

at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)

at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)

at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)

at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)

at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)

at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)

at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)

at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)

at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)

at org.junit.runners.ParentRunner.run(ParentRunner.java:363)

at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)

at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)

at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)

at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Caused by: java.lang.IllegalArgumentException: Parameter value [HISTORY] did not match expected type [com.facm.demo.domain.CD$Genre (n/a)]

at org.hibernate.jpa.spi.BaseQueryImpl.validateBinding(BaseQueryImpl.java:874)

at org.hibernate.jpa.internal.QueryImpl.access$000(QueryImpl.java:80)

at org.hibernate.jpa.internal.QueryImpl$ParameterRegistrationImpl.bindValue(QueryImpl.java:248)

at org.hibernate.jpa.internal.QueryImpl$JpaPositionalParameterRegistrationImpl.bindValue(QueryImpl.java:337)

at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:674)

at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:198)

at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:49)

at com.mysema.query.jpa.impl.JPAUtil.setConstants(JPAUtil.java:55)

at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:130)

at com.mysema.query.jpa.impl.AbstractJPAQuery.count(AbstractJPAQuery.java:81)

at org.springframework.data.jpa.repository.support.QueryDslJpaRepository.findAll(QueryDslJpaRepository.java:141)

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.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:483)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:468)

at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440)

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

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)

... 38 more

Ruben Dijkstra

unread,

Apr 30, 2016, 11:02:57 AM4/30/16

to Querydsl

This seems to be a Spring Data issue.

Could you maybe log the JPQL that we generate?

Also, could you also maybe rename the Genre inner classes (and the properties) to see if one maybe clashes with the other?

Anyway, I think this is a Spring Data issue, have you already opened up an issue on their side?

Br,

Ruben

Frank Castro

unread,

Apr 30, 2016, 1:08:06 PM4/30/16

to Querydsl

Hi Ruben,

Thank you very much for your reply. I will try renaming the Genre inner classes, and see what happens. I set logging level for com.mysema to debug

and get this:

2016-04-30 13:04:25.239 DEBUG 88286 --- [ main] com.mysema.query.jpa.impl.JPAQuery : select count(item) from Item item where item.genre = ?1 or item.genre = ?2

Again thank you for your help, and I will start searching for a possible issue with Spring Data.

Frank

Frank Castro

unread,

Apr 30, 2016, 1:11:43 PM4/30/16

to Querydsl

Hi Ruben,

I forgot to include the logging from just using the @Query. Again this is the @Query in the ItemRepository:

public interface ItemRepository extends JpaRepository<Item, Long>, QueryDslPredicateExecutor<Item> {

@Query(" SELECT item From Item item"

+ " WHERE item.id IN(SELECT book.id FROM Book book WHERE book.genre IN('SCIENCE'))"

+ " OR item.id IN(SELECT cd.id FROM CD cd WHERE cd.genre IN('BLUES'))")

Page<Item> query(Pageable pageable);

}

And this is the logging when I use the @Query, and this does work:

Hibernate: select item0_.id as id2_3_, item0_.item_type as item_typ1_3_, item0_1_.genre as genre1_2_, item0_1_.title as title2_2_, item0_2_.genre as genre1_1_, item0_2_.title as title2_1_ from item item0_ left outer join cd item0_1_ on item0_.id=item0_1_.id left outer join book item0_2_ on item0_.id=item0_2_.id where item0_.id in (select book1_.id from book book1_ inner join item book1_1_ on book1_.id=book1_1_.id where book1_.genre in ('SCIENCE')) or item0_.id in (select cd2_.id from cd cd2_ inner join item cd2_1_ on cd2_.id=cd2_1_.id where cd2_.genre in ('BLUES')) limit ?

Thank you again!

Frank

On Saturday, April 30, 2016 at 11:02:57 AM UTC-4, Ruben Dijkstra wrote:

Frank Castro

unread,

Apr 30, 2016, 1:21:45 PM4/30/16

to Querydsl

Ruben,

I won't bother you anymore, but I was able to fix it like this:

QItem qitem = QItem.item;
QBook qbook = qitem.as(QBook.class);
QCD qcd = qitem.as(QCD.class);

BooleanBuilder builder = new BooleanBuilder();

qitem.equals(qbook.genre.eq(Book.BookGenre.SCIENCE)
.or(qcd.genre.eq(CD.CDGenre.BLUES)));

Page<Item> itemPage = itemRepository.findAll(builder, new PageRequest(0, 10));

Thanks!

Frank

On Saturday, April 30, 2016 at 11:02:57 AM UTC-4, Ruben Dijkstra wrote:

Frank Castro

unread,

May 1, 2016, 7:21:21 PM5/1/16

to Querydsl

I was wrong when I thought I had it working, it simply was returning all the rows in the table.

You were absolutely correct to try and change the property name in the subtypes. When I changed the CD genre property to cdGenre, and the Book genre property to bookGenre, the query worked. And you were right that the issue is probably Spring Data, at least it seems that way.

So what is happening when the sub classes have the same property name although they're really in separate tables (Inheritance.JOINED), Spring Data is always using one of the sub classes to determine search values that I can apply. So if the Book and CD sub classes each have a property named genre, Book's genre uses the BookGenre enum type and CD uses the CDGenre enum type, but Spring Data only allows CD genre search values of BookGenre type:

org.springframework.dao.InvalidDataAccessApiUsageException: Parameter value [SCIENCE] did not match expected type [org.porthos.concepts.domain.CD$CDGenre (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [SCIENCE] did not match expected type [org.porthos.concepts.domain.CD$CDGenre (n/a)]

at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:227)

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:436)

at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)

at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)

I decided to just use JPQL for the project, but I think querydsl is great, and will continue to learn it.