由以下原因引起:org.hibernate.hql.internal.ast.QuerySyntaxException:

Question

我想写下面查询的JPQL。我正在使用SpringBoot v.2.2.2.RELEASE和Spring Data JPA。

错误：

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 
productCode, 
productName, 
textDescription
FROM
products t1
INNER JOIN productlines t2 
ON t1.productline = t2.productline;

存储 库

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

产品.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;
}
}

产品线.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

由于此行p.productline=pl.productline，您收到错误

Productline实体没有产品线。

有了p.productline所有的映射都会得到照顾。此外，INNER 是可选的。

您的最终查询应如下所示

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

有关更多详细信息，请参阅文档

Answer 2

提供的代码片段存在一些问题。

1. 默认情况下，您应该将JPQL查询传递给@Query注释。这意味着您不应该编写手动 JOIN 运算符。例如SELECT p FROM Product p join p.productline将产生正确的 SQL。但是，您不应该这样做。

2. JpaRepository提供了所有常用方法。因此，您可以使用findAll()，并获取所有产品。在这种情况下，将自动为每个产品加载productLine。这是因为toOne关系的默认提取类型是EAGER。这意味着每次加载实体时，JPA提供程序(在本例中为Hibernate(都会加载其toOne关系。

3. 如果你想强制Spring Data生成一个INNER JOIN你应该告诉它这个关系不是可选的。你可以通过把@ManyToOne(optional=false)放在你身上来做到这一点productLine.

您的问题的答案：该错误是由语法错误引起的，ON p.productline=pl.productline.如您所见，pl没有productLine字段。为了获得所需的SQL查询INNER JOIN，请遵循上述3个建议。