SQLGrammarException:无法执行查询:找不到列

Question

我有以下hibernate查询字符串：

String queryString = "select rn" +
"cms.my_status_id as 'myStatusId',rn" +
"cms.status_label as 'statusLabel',rn" +
"csl.status_id as 'companyStatusLabel'rn" +
"from "+client+".corresponding_status cms rn" +
"join "+client+".company_status_label csl on csl.status_id = cms.my_status_id";

我的对应实体是：

@Entity(name = "corresponding_status")
@Table(name = "corresponding_status")
public class CorrespondingStatus implements Serializable {
@Id
@JsonProperty
@Column(name = "my_status_id")
private Integer myStatusId;
// varchar(255)
@JsonProperty
@Column(name = "status_label")
private String statusLabel;
@JsonProperty
@Transient
private String companyStatusLabel;

然而，当我运行查询时，我得到：

Column 'my_status_id' not found

即使它肯定在数据库中。

这里的问题是什么？

Answer 1

在HQL中，必须使用属性而不是数据库列名。将您的HQL更改为

String queryString = "select rn" +
"cms.myStatusId as 'myStatusId',rn" +
"cms.statusLabel as 'statusLabel',rn" +
"csl.status_id as 'companyStatusLabel'rn" +
"from "+client+".corresponding_status cms rn" +
"join "+client+".company_status_label csl with csl.status_id = cms.myStatusId";

编辑：您可能需要相应地更改company_status_label实体

第2版：更改为WITH

Answer 2

我建议使用标准API，而不是手工构建JPA查询。您上面的查询将从以下内容更改：

String queryString = "select rn" +
"cms.my_status_id as 'myStatusId',rn" +
"cms.status_label as 'statusLabel',rn" +
"csl.status_id as 'companyStatusLabel'rn" +
"from "+client+".corresponding_status cms rn" +
"join "+client+".company_status_label csl on csl.status_id = cms.my_status_id";

类似于

Session session = HibernateUtil.getHibernateSession();
CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Entity> cq = cb.createQuery(Entity.class);
Root<Entity> root = cq.from(Entity.class);
cq.select(root);
Query<Entity> query = session.createQuery(cq);
List<Entity> results = query.getResultList();