Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: could not resolve property: productline of:

Question

I want to write the JPQL of below query. I am using Spring Boot v.2.2.2.RELEASE and Spring Data JPA.

Error:

Caused by: org.hibernate.QueryException: could not resolve property: productline of: com.example.entity.Productline [SELECT p FROM com.example.entity.Product p JOIN p.productline pl WHERE p.productline = pl.productline]
    at org.hibernate.QueryException.generateQueryException(QueryException.java:120) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:155) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:600) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:709) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    ... 66 common frames omitted
Caused by: org.hibernate.QueryException: could not resolve property: productline of: com.example.entity.Productline
    at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:73) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:67) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:2015) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:407) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.tree.FromElement.getPropertyType(FromElement.java:516) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.getDataType(DotNode.java:697) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.prepareLhs(DotNode.java:275) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:215) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:1045) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1290) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4706) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4177) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2138) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:815) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:609) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final]
    ... 72 common frames omitted

Select Query

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products t1
INNER JOIN productlines t2 
    ON t1.productline = t2.productline;

Repository

public interface ProductRepository extends JpaRepository<Product, String>{

    @Query("SELECT p FROM Product p INNER JOIN p.productline pl ON p.productline=pl.productline")
    List<Product> findProductsAndProductLines(); 
}

Product.java

@Entity
@Table(name="products")
@NamedQuery(name="Product.findAll", query="SELECT p FROM Product p")
public class Product implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique=true, nullable=false, length=15)
    private String productCode;

    @Column(nullable=false, precision=10, scale=2)
    private BigDecimal buyPrice;

    @Column(nullable=false, precision=10, scale=2)
    private BigDecimal msrp;

    @Lob
    @Type(type = "text")
    @Column(nullable=false)
    private String productDescription;

    @Column(nullable=false, length=70)
    private String productName;

    @Column(nullable=false, length=10)
    private String productScale;

    @Column(nullable=false, length=50)
    private String productVendor;

    @Column(nullable=false)
    private short quantityInStock;

    //bi-directional many-to-one association to Orderdetail
    @OneToMany(mappedBy="product")
    private List<Orderdetail> orderdetails;

    //bi-directional many-to-one association to Productline
    @ManyToOne
    @JoinColumn(name="productLine", nullable=false)
    private Productline productline;

    public Product() {
    }

    public String getProductCode() {
        return this.productCode;
    }

    public void setProductCode(String productCode) {
        this.productCode = productCode;
    }

    public BigDecimal getBuyPrice() {
        return this.buyPrice;
    }

    public void setBuyPrice(BigDecimal buyPrice) {
        this.buyPrice = buyPrice;
    }

    public BigDecimal getMsrp() {
        return this.msrp;
    }

    public void setMsrp(BigDecimal msrp) {
        this.msrp = msrp;
    }

    public String getProductDescription() {
        return this.productDescription;
    }

    public void setProductDescription(String productDescription) {
        this.productDescription = productDescription;
    }

    public String getProductName() {
        return this.productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getProductScale() {
        return this.productScale;
    }

    public void setProductScale(String productScale) {
        this.productScale = productScale;
    }

    public String getProductVendor() {
        return this.productVendor;
    }

    public void setProductVendor(String productVendor) {
        this.productVendor = productVendor;
    }

    public short getQuantityInStock() {
        return this.quantityInStock;
    }

    public void setQuantityInStock(short quantityInStock) {
        this.quantityInStock = quantityInStock;
    }

    public List<Orderdetail> getOrderdetails() {
        return this.orderdetails;
    }

    public void setOrderdetails(List<Orderdetail> orderdetails) {
        this.orderdetails = orderdetails;
    }

    public Orderdetail addOrderdetail(Orderdetail orderdetail) {
        getOrderdetails().add(orderdetail);
        orderdetail.setProduct(this);

        return orderdetail;
    }

    public Orderdetail removeOrderdetail(Orderdetail orderdetail) {
        getOrderdetails().remove(orderdetail);
        orderdetail.setProduct(null);

        return orderdetail;
    }

    public Productline getProductline() {
        return this.productline;
    }

    public void setProductline(Productline productline) {
        this.productline = productline;
    }
}

Productline.java

@Entity
@Table(name="productlines")
@NamedQuery(name="Productline.findAll", query="SELECT p FROM Productline p")
public class Productline implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique=true, nullable=false, length=50)
    private String productLine;

    @Lob
    @Type(type = "text")
    private String htmlDescription;

    // Ref: https://docs.jboss.org/hibernate/orm/5.0/mappingGuide/en-US/html/ch03.html
    @Lob
    @Type(type="org.hibernate.type.ImageType")
    private byte[] image;

    @Column(length=4000)
    private String textDescription;

    //bi-directional many-to-one association to Product
    @OneToMany(mappedBy="productline")
    private List<Product> products;

    public Productline() {
    }

    public String getProductLine() {
        return this.productLine;
    }

    public void setProductLine(String productLine) {
        this.productLine = productLine;
    }

    public String getHtmlDescription() {
        return this.htmlDescription;
    }

    public void setHtmlDescription(String htmlDescription) {
        this.htmlDescription = htmlDescription;
    }

    public byte[] getImage() {
        return this.image;
    }

    public void setImage(byte[] image) {
        this.image = image;
    }

    public String getTextDescription() {
        return this.textDescription;
    }

    public void setTextDescription(String textDescription) {
        this.textDescription = textDescription;
    }

    public List<Product> getProducts() {
        return this.products;
    }

    public void setProducts(List<Product> products) {
        this.products = products;
    }

    public Product addProduct(Product product) {
        getProducts().add(product);
        product.setProductline(this);

        return product;
    }

    public Product removeProduct(Product product) {
        getProducts().remove(product);
        product.setProductline(null);

        return product;
    }
}

Answer 1

You are getting error because of this line p.productline=pl.productline

Productline entity doesn't have productline.

With p.productline all the mapping will be taking care. Also INNER is optional.

Your final query should be like this

@Query("SELECT p FROM Product p JOIN p.productline pl")
List<Product> findProductsAndProductLines(); 

Refer Doc for more Detail

Answer 2

There are a couple of issues with the provided code snippet.

1. You should pass a JPQL query to your @Query annotation by default. Which means that you should not write manual JOIN operators. E.g. SELECT p FROM Product p join p.productline will result in a proper SQL. However, you should not do that.

2. JpaRepository provides all common methods. So you can use findAll(), and fetch all products. A productLine will be loaded automatically for each product in that case. That's because a default fetch type for toOne relations is EAGER. This means that every time you load an entity, a JPA provider (in this case Hibernate) will load its toOne relations.

3. If you want to force Spring Data to generate an INNER JOIN you should tell it that this relation is not optional. You can do that by putting @ManyToOne(optional=false) on you productLine.

The answer to your question: that error is caused by syntax error, ON p.productline=pl.productline. As you can see, pl does not have a productLine field. In order to get the desired SQL query with INNER JOIN, please follow 3 recommendations above.