英文:
SQLGrammarException: could not execute query: Column not found?
问题
我有以下的 hibernate 查询字符串：
String queryString = "select \r\n" +"cms.my_status_id as 'myStatusId',\r\n" +"cms.status_label as 'statusLabel',\r\n" +"csl.status_id as 'companyStatusLabel'\r\n" +"from "+client+".corresponding_status cms \r\n" +"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@Transientprivate String companyStatusLabel;
然而，当我运行查询时，出现了：
Column 'my_status_id' not found
尽管它在数据库中确实存在。
问题出在哪里？
英文:
I have the following hibernate query string:
String queryString = "select \r\n" +"cms.my_status_id as 'myStatusId',\r\n" +"cms.status_label as 'statusLabel',\r\n" +"csl.status_id as 'companyStatusLabel'\r\n" +"from "+client+".corresponding_status cms \r\n" +"join "+client+".company_status_label csl on csl.status_id = cms.my_status_id";
My Corresponding Entity is:
@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@Transientprivate String companyStatusLabel;
However when I run the query I get:
Column 'my_status_id' not found
even though it is definitely in the DB.
What is the issue here?
答案1
得分: 2
在HQL中，您必须使用属性而不是数据库列名。将您的HQL更改为：
String queryString = "select \r\n" +"cms.myStatusId as 'myStatusId',\r\n" +"cms.statusLabel as 'statusLabel',\r\n" +"csl.status_id as 'companyStatusLabel'\r\n" +"from "+client+".corresponding_status cms \r\n" +"join "+client+".company_status_label csl with csl.status_id = cms.myStatusId";
编辑：
您可能需要相应地更改company_status_label实体。
编辑2：更改为WITH。
英文:
In HQL you must use properties instead of database column names. Change your HQL to
String queryString = "select \r\n" +"cms.myStatusId as 'myStatusId',\r\n" +"cms.statusLabel as 'statusLabel',\r\n" +"csl.status_id as 'companyStatusLabel'\r\n" +"from "+client+".corresponding_status cms \r\n" +"join "+client+".company_status_label csl with csl.status_id = cms.myStatusId";
EDIT:
You probably need to change company_status_label entity accordingly
EDIT2: Changed to WITH
答案2
得分: 0
代替手动构建 JPA 查询，我建议使用Criteria API。你上面的查询将变为：
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();
英文:
Instead of building JPA queries by hand, I would suggest the criteria API. Your query above would change from:
String queryString = "select \r\n" +"cms.my_status_id as 'myStatusId',\r\n" +"cms.status_label as 'statusLabel',\r\n" +"csl.status_id as 'companyStatusLabel'\r\n" +"from "+client+".corresponding_status cms \r\n" +"join "+client+".company_status_label csl on csl.status_id = cms.my_status_id";
to something akin to:
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();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论