[FIXED] JPAQuery mapping correctly but JPASQLQuery not

Issue

The following QueryDSL query gives the correct response :

return new JPAQuery<>(entityManager)
                .select(QCategoryEntity.categoryEntity)
                .from(QCategoryEntity.categoryEntity)
                .where(QCategoryEntity.categoryEntity.parentCategory.id.eq(4L))
                .fetch();

The following does not :

return new JPASQLQuery<>(entityManager, new DB2Templates())
                    .select(QCategoryEntity.categoryEntity)
                    .from(QCategoryEntity.categoryEntity)
                    .where(QCategoryEntity.categoryEntity.parentCategory.id.eq(4L))
                    .fetch();

The latter returns the following error :

Column "CATEGORYENTITY.PARENTCATEGORY.C_I_IDF" not found; SQL statement: select categoryEntity.* from CATEGORY categoryEntity where categoryEntity.parentCategory.C_I_IDF = ? [42122-200]

My Hibernate class looks like this :

@Entity
@Table(name = "CATEGORY")
public class CategoryEntity {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY)
    @Column(name = "C_I_IDF")
    protected Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="C_PARENTCATEGORY")
    private CategoryEntity parentCategory;

...

}

Any idea why? I generated the entity classes both withe the Hibernate annotation processor as well as the querydsl-maven-plugin which translates my schema to entities.

Solution

My bad, I was using the Q-entities (QCategoryEntity) whereas I needed to use the one generated for usage with SQL queries (JPASQLQuery and HibernateSQLQuery), i.e. the S-entities. This is mentioned in the documentation and I am now generating them with the following Maven configuration snippet :

<plugin>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-maven-plugin</artifactId>
    <version>${querydsl.version}</version>
    <executions>
        <execution>
            <goals>
                <goal>export</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <jdbcDriver>com.ibm.db2.jcc.DB2Driver</jdbcDriver>
        <jdbcUrl>redacted</jdbcUrl>
        <jdbcUser>redacted</jdbcUser>
        <jdbcPassword>redacted</jdbcPassword>
        <schemaPattern>myschemaprefix%</schemaPattern>
        <packageName>mypackage</packageName>
        <sourceFolder>src/main/java</sourceFolder>
<targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
        <namePrefix>S</namePrefix>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>com.ibm.db2.jcc</groupId>
            <artifactId>db2jcc4</artifactId>
            <version>10.1</version>
        </dependency>
    </dependencies>
</plugin>

Code then becomes :

QCategoryEntity categoryEntity = QCategoryEntity.categoryEntity;
        SCategory category = new SCategory(categoryEntity.getMetadata().getName());
        return new JPASQLQuery<>(entityManager, templates)
                .select(category.cIIdf)
                .from(category)
                .where(category.cParentcategory.eq(id))
                .fetch();

Answered By - Bruno
Answer Checked By - Senaida (JavaFixing Volunteer)