RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query

Question

i am retrieving records from a table called PurchaseDetailContributor by using another table joins.but i am getting query error.

below is my DAOHibernate.java class inside i worte a method.

@SuppressWarnings("unchecked")
public List<PurchaseDetailContributor> getPurchaseDetailContributorByCustomerNo(Integer customer_no) {
    System.out.println("The Customer number in PurchaseDetailContributorDAOHIbernate >>>>" + customer_no);
    String sql = " SELECT * FROM purchase_detail_contributor pdc "
             + " INNER JOIN purchasedetail pd ON pdc.purchase_detail_id = pd.purchasedetailid "
             + " INNER JOIN purchase p ON pd.purchasefk = p.purchaseid " 
             + " INNER JOIN user u ON u.userid = p.userid AND u.customer_no = " +customer_no;

    List<PurchaseDetailContributor> pdcList = new ArrayList<PurchaseDetailContributor>();
    Transaction tx = null;
    Session session = getHibernateTemplate().getSessionFactory().openSession();
    try{
        tx = session.beginTransaction();
        SQLQuery sqlQuery = session.createSQLQuery(sql).addEntity(PurchaseDetailContributor.class);
        //sqlQuery.setInteger("customer_no", customer_no);
        pdcList = sqlQuery.list();
        tx.commit();
    }catch (RuntimeException e) {
        System.out.println("getPurchaseDetailContributorByCustomerNo() >>>>- RuntimeException: " + e);
        if (tx != null && tx.isActive()) {
            try {
                // Second try catch as the rollback could fail as well
                tx.rollback();
            } catch (HibernateException e1) {
                System.out.println("Error rolling back transaction: " + e1);
            }
        }
    } finally {
        session.disconnect();
    }

my error is in join query.

//error information

1:13:00,713 DEBUG [UserDAOHibernate:774] findUsersByCustomer_no() - Namequery: GetUsersByCustomer_no, 57835
11:13:00,752 DEBUG [UserDAOHibernate:774] findUsersByCustomer_no() - Namequery: GetUsersByCustomer_no, 57835
This COMPANYID is before passing from  FORM >>>>>>>>41
This COMPANYtype after passing id from FORM >>>>>>>>UnTrusted
The customer number is >>>>>>57835
User select values is Untrusted
Inside try condition
The Customer number in PurchaseDetailContributorDAOHIbernate *********57835
11:13:01,073 WARN  [JDBCExceptionReporter:233] SQL Error: 0, SQLState: S0022
11:13:01,074 ERROR [JDBCExceptionReporter:234] Column 'is_approved' not found.
getPurchaseDetailContributorByCustomerNo() ====222>>>>- RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query
java.lang.NullPointerException
at com.newscom.action.AccountAdminAction.updateTheCustomer(AccountAdminAction.java:4749)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:453)
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:292)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:255)
at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:249)
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)

You need some spaces when you add the "inner join" and "where" clauses, otherwise your sql will be something like "select * from purchase_detail_contributorinner join...". — Insac
– Insac, Commented Oct 14, 2016 at 6:57
@Insac & @ stanislavL ,thanks now i changed the sql query as you people suggested but still i am getting the following error org.hibernate.exception.SQLGrammarException: could not execute query — Fakkir mohamed Sait
– Fakkir mohamed Sait, Commented Oct 14, 2016 at 10:32
can you print your SQL before submitting it and adding it to the question? — Insac
– Insac, Commented Oct 14, 2016 at 10:40
i didn't get what your telling...can you please modify the errors in my code. — Fakkir mohamed Sait
– Fakkir mohamed Sait, Commented Oct 14, 2016 at 10:46
To help us understand what might go wrong, can you add some information? For example the complete stacktrace and/or the value of the sql variable? — Insac
– Insac, Commented Oct 14, 2016 at 10:55

StanislavL · Accepted Answer · 2016-10-14 07:38:02Z

1

You have no spaces in your sql

String sql = "select * from purchase_detail_contributor"
         + "inner join purchasedetail on (purchase_detail_contributor.purchase_detail_id = purchasedetail.purchasedetailid)"
         + "inner join purchase on (purchasedetail.purchasefk = purchase.purchaseid)" 
         + "inner join user on (user.userid = purchase.userid)"
         + "where user.customer_no = :customer_no";

So the sql is

select * from purchase_detail_contributorinner join purchasedetail...

Which is definitely syntax error

answered Oct 14, 2016 at 7:38

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

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

1 Comment

coladict Over a year ago

You have to always set the parameters like user.customer_no = :cno when creating the query, then call setParameter("cno", customer_no) to protect against SQL injection attacks. This one is safer, because it's an Integer object, but if you're doing it like this in one place, it's safe to assume you're doing it in a lot more.

Gautam · Accepted Answer · 2016-10-14 13:05:33Z

1

Did you post correct code?

As in error it shows

11:13:01,074 ERROR [JDBCExceptionReporter:234] Column 'is_approved' not found.

answered Oct 14, 2016 at 13:05

Gautam

3,3844 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query

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