SQLGrammarException: could not execute query: Column not found?

Question

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
    @Transient
    private 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?

The \r\n are completely unnecessary and might be causing issues — RiggsFolly
– RiggsFolly, Commented Aug 17, 2020 at 15:49
Your CorrespondingStatus entity does not appear to have relationships to any other entities. Why are you trying to do a join here? — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 17, 2020 at 15:56

César Alves · Accepted Answer · 2020-08-17 16:00:18Z

2

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

edited Aug 17, 2020 at 16:00

answered Aug 17, 2020 at 15:52

César Alves

4723 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

HQL joins don't have an ON clause.

hd1 · Accepted Answer · 2020-08-17 15:58:54Z

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

Collectives™ on Stack Overflow

SQLGrammarException: could not execute query: Column not found?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related