SQLGrammar error when querying MySql view

Question

When a run a GET request i get an exception o.h.engine.jdbc.spi.SqlExceptionHelper : Unknown column 'disburseme0_.reason_type' in 'field list' in stack trace even though i have configured the field correctly in the entity class. I have a Spring Boot SOAP interface that is querying a MySql database view. I have assigned one of the unique keys from the parent tables as the view Id in JPA.

Part of my entity class has:

@Entity
@Table(name="disbursement_payload")

public class Disbursement {
@Id 
@Column(name="ID")
private long disbursementId;

@Column(name="ReasonType")  
private String reasonType;
public long getDisbursementId() {
    return disbursementId;
}
public void setDisbursementId(long disbursementId) {
    this.disbursementId = disbursementId;
public String getReasonType() {
    return reasonType;
}
public void setReasonType(String reasonType) {
    this.reasonType = reasonType;
}

I have the view as:

CREATE VIEW disbursement_payload AS (
  SELECT 
   iso_number AS Currency,
trans_desc AS ReasonType,
account_number AS ReceiverParty,
amount AS Amount
  FROM m_payment_detail, m_loan_transaction 
  WHERE m_payment_detail.`id`= m_loan_transaction.`payment_detail_id` AND 
m_payment_detail.`payment_type_id`=2
);

Is there something im missing , in the entity or view definition? I have read one of the comments here could not extract ResultSet in hibernate that i might have to explicitly define the parent schemas. Any assistance, greatly appreciated.

When you select from two tables,use `JOIN` is a better choice — flyingfox, Sep 18 '18 at 09:41
Your view doesn't appear to have a column ```reason_type``` in it. — Sloan Thrasher, Sep 18 '18 at 09:43
I wouldn't bother with the view. I can't see the point of it. — Strawberry, Sep 18 '18 at 09:53

ScaisEdge · Accepted Answer · 2018-09-18T12:23:06.950

do the mapping for db column and class var name based on camelCase conversion basded on underscore _ separated name

you could try using

    CREATE VIEW disbursement_payload AS (
      SELECT iso_number AS currency
      , trans_desc AS reason_type
      , account_number AS receiver_rarty
      , amount AS amount
      FROM m_payment_detail
      INNER JOIN m_loan_transaction 
          ON  m_payment_detail.`id`= m_loan_transaction.`payment_detail_id` 
            AND m_payment_detail.`payment_type_id`=2
    );

the view code is SQL code and hibernate see a view as a table, so the conversion of column name is base on the same rules

and a suggestion you should not use (older) implicit join based on where condition you should use (more recent) explici join sintax ..

SQLGrammar error when querying MySql view

1 Answers1