org.hibernate.hql.ast.QuerySyntaxException

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

I got this exception when Im trying to get table records.
The exception is:

***************************
org.hibernate.hql.ast.QuerySyntaxException: TRN_HGT_QUATA is not mapped [SELECT SALES_YEAR FROM TRN_HGT_QUATA hgt WHERE hgt.SALES_NO=:sales_no AND hgt.SALES_YEAR=:sales_year]

***************************

My Entity class is

Code:

@Entity
@Table(name="TRN_HGT_QUATA")
public class HighGrownTea implements Serializable {

    private int id;
    private int saleNumber;
    private String saleYear;
    private String quotation;
    private String bop;
    private String bopf;
    private String pekoeFbop;
    private String op;

    public HighGrownTea() {
    }

    
    
    public HighGrownTea(int saleNumber, String saleYear) {
        this.saleNumber = saleNumber;
        this.saleYear = saleYear;
    }

    public List getHGTObj(HighGrownTea hgt){
        Transaction transaction = null;
        List<HighGrownTea> hgts = null;
        Session session = null;
        
        try{
            session = HibernateUtil.getInstance().getCurrentSession(); 
            
            transaction = session.beginTransaction();
            hgts = (List<HighGrownTea>)session.createQuery("SELECT SALES_YEAR FROM TRN_HGT_QUATA hgt WHERE hgt.SALES_NO=:sales_no AND hgt.SALES_YEAR=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", saleYear).list();
            transaction.commit();

            //return hgts;
        }
        catch(Exception ex){
            System.out.println("*******************"+ex.toString()+"*******************");
        }
        finally{
            //session.flush();
            session.close();
            
        }
        
        return hgts;
        
    }
    
    @Column(name="BOP")
    public String getBop() {
        return bop;
    }

    public void setBop(String bop) {
        this.bop = bop;
    }

    @Column(name="BOPF")
    public String getBopf() {
        return bopf;
    }

    public void setBopf(String bopf) {
        this.bopf = bopf;
    }

    @Id
    @GenericGenerator(name="generator", strategy="increment")
    @GeneratedValue(generator="generator")
    @Column(name="ID")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Column(name="OP")
    public String getOp() {
        return op;
    }

    public void setOp(String op) {
        this.op = op;
    }

    @Column(name="PEKOE/FBOP")
    public String getPekoeFbop() {
        return pekoeFbop;
    }

    public void setPekoeFbop(String pekoeFbop) {
        this.pekoeFbop = pekoeFbop;
    }

    @Column(name="QUATATIONS IN (RS/KGS)")
    public String getQuotation() {
        return quotation;
    }

    public void setQuotation(String quotation) {
        this.quotation = quotation;
    }

    @Column(name="SALES_NO")
    public int getSaleNumber() {
        return saleNumber;
    }

    public void setSaleNumber(int saleNumber) {
        this.saleNumber = saleNumber;
    }

    @Column(name="SALES_YEAR")
    public String getSaleYear() {
        return saleYear;
    }

    public void setSaleYear(String saleYear) {
        this.saleYear = saleYear;
    }
    
    
}

Please I need help.

Everything is ok in hibernate.cfg.xml file.

regards,
Dilan.

littypreethkr · **Posted:** Tue Feb 24, 2009 4:57 am

If this is an HQL ie you are doing session.createQuery() then the from clause should be having the class name and not the table name. Also note that in HQL the class name, property names etc are case sensitive. So ur query string should be like:

Code:

SELECT saleYear FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

I did as you said..
But it shows exception again.

***************************************
org.hibernate.QueryException: could not resolve property: saleNumber of: com.aura.data.trn.HighGrownTea [FROM com.aura.data.trn.HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year]
***************************************

Code:

public ArrayList getHGTObj(HighGrownTea hgt){
         Transaction transaction = null;
        ArrayList<HighGrownTea> hgts = null;
        Session session = null;
        
        try{
            session = HibernateUtil.getInstance().getCurrentSession(); 
            
            transaction = session.beginTransaction();
            hgts = (ArrayList<HighGrownTea>)session.createQuery("FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", hgt.getSaleYear()).list();
            transaction.commit();
            
            System.out.println(hgts.size());
            //return hgts;
        }
        catch(Exception ex){
            System.out.println("*******************"+ex.toString()+"*******************");
        }
        
        
        return hgts;
        
    }

I changed return type to to 'ArrayList'.
Help me.

thanks in advance,
Dilan.

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

I did as you said..
But it shows exception again.

***************************************
org.hibernate.QueryException: could not resolve property: saleNumber of: com.aura.data.trn.HighGrownTea [FROM com.aura.data.trn.HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year]
***************************************

Code:

public ArrayList getHGTObj(HighGrownTea hgt){
         Transaction transaction = null;
        ArrayList<HighGrownTea> hgts = null;
        Session session = null;
        
        try{
            session = HibernateUtil.getInstance().getCurrentSession(); 
            
            transaction = session.beginTransaction();
            hgts = (ArrayList<HighGrownTea>)session.createQuery("FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", hgt.getSaleYear()).list();
            transaction.commit();
            
            System.out.println(hgts.size());
            //return hgts;
        }
        catch(Exception ex){
            System.out.println("*******************"+ex.toString()+"*******************");
        }
        
        
        return hgts;
        
    }

I changed return type to to 'ArrayList'.
Help me.

thanks in advance,
Dilan.

littypreethkr · **Posted:** Tue Feb 24, 2009 5:56 am

This is some issue with your HighGrownTea mapping only. Can you now post the new code (whole code) for the HighGrownTea class. Also post the stack trace.

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

ok this is my Entity class again

Code:

package com.aura.data.trn;

import com.aura.util.HibernateUtil;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.annotations.GenericGenerator;


//To represent TRN_HGT_QUATA
@Entity
@Table(name="TRN_HGT_QUATA")
public class HighGrownTea implements Serializable {

    private int id;
    private int saleNumber;
    private String saleYear;
    private String quotation;
    private String bop;
    private String bopf;
    private String pekoeFbop;
    private String op;

    public HighGrownTea() {
    }

    
    
    public HighGrownTea(int saleNumber, String saleYear) {
        this.saleNumber = saleNumber;
        this.saleYear = saleYear;
    }

    public ArrayList getHGTObj(HighGrownTea hgt){
         Transaction transaction = null;
        ArrayList<HighGrownTea> hgts = null;
        Session session = null;
        
        try{
            session = HibernateUtil.getInstance().getCurrentSession(); 
            
            transaction = session.beginTransaction();
            hgts = (ArrayList<HighGrownTea>)session.createQuery("FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", hgt.getSaleYear()).list();
            transaction.commit();
            
            System.out.println(hgts.size());
            //return hgts;
        }
        catch(Exception ex){
            System.out.println("*******************"+ex.toString()+"*******************");
        }
        
        
        return hgts;
        
    }
    
    @Column(name="BOP")
    public String getBop() {
        return bop;
    }

    public void setBop(String bop) {
        this.bop = bop;
    }

    @Column(name="BOPF")
    public String getBopf() {
        return bopf;
    }

    public void setBopf(String bopf) {
        this.bopf = bopf;
    }

    @Id
    @GenericGenerator(name="generator", strategy="increment")
    @GeneratedValue(generator="generator")
    @Column(name="ID")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Column(name="OP")
    public String getOp() {
        return op;
    }

    public void setOp(String op) {
        this.op = op;
    }

    @Column(name="PEKOE/FBOP")
    public String getPekoeFbop() {
        return pekoeFbop;
    }

    public void setPekoeFbop(String pekoeFbop) {
        this.pekoeFbop = pekoeFbop;
    }

    @Column(name="QUATATIONS IN (RS/KGS)")
    public String getQuotation() {
        return quotation;
    }

    public void setQuotation(String quotation) {
        this.quotation = quotation;
    }

    @Id
    @Column(name="SALES_NO")
    public int getSaleNumber() {
        return saleNumber;
    }

    public void setSaleNumber(int saleNumber) {
        this.saleNumber = saleNumber;
    }

    @Id
    @Column(name="SALES_YEAR")
    public String getSaleYear() {
        return saleYear;
    }

    public void setSaleYear(String saleYear) {
        this.saleYear = saleYear;
    }
    
    
}

This is the code which call this entity class's method

Code:

<%@ page import="com.aura.data.model.*" %>
<%@ page import="com.aura.data.trn.*" %>
<%@ page import="java.util.*" %>

<%
    //If the userAction is 'view' then this form should be filled
    String userAction = null;
    String saleNumber = null;
    String saleYear = null;

    try{
        userAction = session.getAttribute("userAction").toString();
        saleNumber = session.getAttribute("sale_number").toString();
        saleYear = session.getAttribute("sale_year").toString();
        
        
        
        System.out.println(userAction);
        if(userAction.equals("view")){
        
            
        }
    }
    catch(IllegalStateException ise){
    
    }
    
    //HighGrownTea highGrownTea = new HighGrownTea();
    HighGrownTea highGrownTea = new HighGrownTea(Integer.parseInt(saleNumber), saleYear);
    
    ArrayList hgtList = highGrownTea.getHGTObj(highGrownTea);
    
    System.out.println(hgtList.size());
    
%>

I cannot find stackTrace....
this is the error

================
org.hibernate.QueryException: could not resolve property: saleNumber of: com.aura.data.trn.HighGrownTea [FROM com.aura.data.trn.HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year]
================

regards,
Dilan.

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

ok this is my Entity class again

Code:

package com.aura.data.trn;

import com.aura.util.HibernateUtil;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.annotations.GenericGenerator;


//To represent TRN_HGT_QUATA
@Entity
@Table(name="TRN_HGT_QUATA")
public class HighGrownTea implements Serializable {

    private int id;
    private int saleNumber;
    private String saleYear;
    private String quotation;
    private String bop;
    private String bopf;
    private String pekoeFbop;
    private String op;

    public HighGrownTea() {
    }

    
    
    public HighGrownTea(int saleNumber, String saleYear) {
        this.saleNumber = saleNumber;
        this.saleYear = saleYear;
    }

    public ArrayList getHGTObj(HighGrownTea hgt){
         Transaction transaction = null;
        ArrayList<HighGrownTea> hgts = null;
        Session session = null;
        
        try{
            session = HibernateUtil.getInstance().getCurrentSession(); 
            
            transaction = session.beginTransaction();
            hgts = (ArrayList<HighGrownTea>)session.createQuery("FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", hgt.getSaleYear()).list();
            transaction.commit();
            
            System.out.println(hgts.size());
            //return hgts;
        }
        catch(Exception ex){
            System.out.println("*******************"+ex.toString()+"*******************");
        }
        
        
        return hgts;
        
    }
    
    @Column(name="BOP")
    public String getBop() {
        return bop;
    }

    public void setBop(String bop) {
        this.bop = bop;
    }

    @Column(name="BOPF")
    public String getBopf() {
        return bopf;
    }

    public void setBopf(String bopf) {
        this.bopf = bopf;
    }

    @Id
    @GenericGenerator(name="generator", strategy="increment")
    @GeneratedValue(generator="generator")
    @Column(name="ID")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Column(name="OP")
    public String getOp() {
        return op;
    }

    public void setOp(String op) {
        this.op = op;
    }

    @Column(name="PEKOE/FBOP")
    public String getPekoeFbop() {
        return pekoeFbop;
    }

    public void setPekoeFbop(String pekoeFbop) {
        this.pekoeFbop = pekoeFbop;
    }

    @Column(name="QUATATIONS IN (RS/KGS)")
    public String getQuotation() {
        return quotation;
    }

    public void setQuotation(String quotation) {
        this.quotation = quotation;
    }

    @Id
    @Column(name="SALES_NO")
    public int getSaleNumber() {
        return saleNumber;
    }

    public void setSaleNumber(int saleNumber) {
        this.saleNumber = saleNumber;
    }

    @Id
    @Column(name="SALES_YEAR")
    public String getSaleYear() {
        return saleYear;
    }

    public void setSaleYear(String saleYear) {
        this.saleYear = saleYear;
    }
    
    
}

This is the code which call this entity class's method

Code:

<%@ page import="com.aura.data.model.*" %>
<%@ page import="com.aura.data.trn.*" %>
<%@ page import="java.util.*" %>

<%
    //If the userAction is 'view' then this form should be filled
    String userAction = null;
    String saleNumber = null;
    String saleYear = null;

    try{
        userAction = session.getAttribute("userAction").toString();
        saleNumber = session.getAttribute("sale_number").toString();
        saleYear = session.getAttribute("sale_year").toString();
        
        
        
        System.out.println(userAction);
        if(userAction.equals("view")){
        
            
        }
    }
    catch(IllegalStateException ise){
    
    }
    
    //HighGrownTea highGrownTea = new HighGrownTea();
    HighGrownTea highGrownTea = new HighGrownTea(Integer.parseInt(saleNumber), saleYear);
    
    ArrayList hgtList = highGrownTea.getHGTObj(highGrownTea);
    
    System.out.println(hgtList.size());
    
%>

I cannot find stackTrace....
this is the error

================
org.hibernate.QueryException: could not resolve property: saleNumber of: com.aura.data.trn.HighGrownTea [FROM com.aura.data.trn.HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year]
================

regards,
Dilan.

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

ok this is my Entity class again

Code:

package com.aura.data.trn;

import com.aura.util.HibernateUtil;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.annotations.GenericGenerator;


//To represent TRN_HGT_QUATA
@Entity
@Table(name="TRN_HGT_QUATA")
public class HighGrownTea implements Serializable {

    private int id;
    private int saleNumber;
    private String saleYear;
    private String quotation;
    private String bop;
    private String bopf;
    private String pekoeFbop;
    private String op;

    public HighGrownTea() {
    }

    
    
    public HighGrownTea(int saleNumber, String saleYear) {
        this.saleNumber = saleNumber;
        this.saleYear = saleYear;
    }

    public ArrayList getHGTObj(HighGrownTea hgt){
         Transaction transaction = null;
        ArrayList<HighGrownTea> hgts = null;
        Session session = null;
        
        try{
            session = HibernateUtil.getInstance().getCurrentSession(); 
            
            transaction = session.beginTransaction();
            hgts = (ArrayList<HighGrownTea>)session.createQuery("FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", hgt.getSaleYear()).list();
            transaction.commit();
            
            System.out.println(hgts.size());
            //return hgts;
        }
        catch(Exception ex){
            System.out.println("*******************"+ex.toString()+"*******************");
        }
        
        
        return hgts;
        
    }
    
    @Column(name="BOP")
    public String getBop() {
        return bop;
    }

    public void setBop(String bop) {
        this.bop = bop;
    }

    @Column(name="BOPF")
    public String getBopf() {
        return bopf;
    }

    public void setBopf(String bopf) {
        this.bopf = bopf;
    }

    @Id
    @GenericGenerator(name="generator", strategy="increment")
    @GeneratedValue(generator="generator")
    @Column(name="ID")
    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Column(name="OP")
    public String getOp() {
        return op;
    }

    public void setOp(String op) {
        this.op = op;
    }

    @Column(name="PEKOE/FBOP")
    public String getPekoeFbop() {
        return pekoeFbop;
    }

    public void setPekoeFbop(String pekoeFbop) {
        this.pekoeFbop = pekoeFbop;
    }

    @Column(name="QUATATIONS IN (RS/KGS)")
    public String getQuotation() {
        return quotation;
    }

    public void setQuotation(String quotation) {
        this.quotation = quotation;
    }

    @Id
    @Column(name="SALES_NO")
    public int getSaleNumber() {
        return saleNumber;
    }

    public void setSaleNumber(int saleNumber) {
        this.saleNumber = saleNumber;
    }

    @Id
    @Column(name="SALES_YEAR")
    public String getSaleYear() {
        return saleYear;
    }

    public void setSaleYear(String saleYear) {
        this.saleYear = saleYear;
    }
    
    
}

This is the code which call this entity class's method

Code:

<%@ page import="com.aura.data.model.*" %>
<%@ page import="com.aura.data.trn.*" %>
<%@ page import="java.util.*" %>

<%
    //If the userAction is 'view' then this form should be filled
    String userAction = null;
    String saleNumber = null;
    String saleYear = null;

    try{
        userAction = session.getAttribute("userAction").toString();
        saleNumber = session.getAttribute("sale_number").toString();
        saleYear = session.getAttribute("sale_year").toString();
        
        
        
        System.out.println(userAction);
        if(userAction.equals("view")){
        
            
        }
    }
    catch(IllegalStateException ise){
    
    }
    
    //HighGrownTea highGrownTea = new HighGrownTea();
    HighGrownTea highGrownTea = new HighGrownTea(Integer.parseInt(saleNumber), saleYear);
    
    ArrayList hgtList = highGrownTea.getHGTObj(highGrownTea);
    
    System.out.println(hgtList.size());
    
%>

I cannot find stackTrace....
this is the error

================
org.hibernate.QueryException: could not resolve property: saleNumber of: com.aura.data.trn.HighGrownTea [FROM com.aura.data.trn.HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year]
================

regards,
Dilan.

littypreethkr · **Posted:** Tue Feb 24, 2009 6:15 am

I can see an @Id annotation for the saleNumber and the saleYear getters. Why its so? U already have a generated @id for the id attribute. You cant have more than one id in the same entity.

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

In my database I have a composite primary key(id, sales_no and sales_year). I wanted to refer those database fields in my entity class.

I saw somewhere that entity class can have several Id fields.

mmerder · **Posted:** Tue Feb 24, 2009 6:21 am

If you want to use more than one Id-column you have to use composite ids, either using @IdClass or @EmbeddedId.
But as you have a generated Id in your class, it is already unique so you can remove @Id from your field saleNumber and saleYear.

mmerder · **Posted:** Tue Feb 24, 2009 6:23 am

double post

littypreethkr · **Posted:** Tue Feb 24, 2009 6:25 am

If you are having a composite primary key then the mapping should be using @Embeddable annotation. Plz see section 2.2.6. Mapping composite primary and foreign keys of hibernate_annotation.pdf

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

In my database I have a composite primary key(id, sales_no and sales_year). I wanted to refer those database fields in my entity class.

I saw somewhere that entity class can have several Id fields.

dilanalex · **Joined:** Thu Dec 11, 2008 2:30 am **Posts:** 47

Ok I removed unwanted Id fields.
Now it doesnt show exceptions.

I changed query like this.

Code:

hgts = (ArrayList<HighGrownTea>)session.createQuery("SELECT hgt.saleYear FROM HighGrownTea hgt WHERE hgt.saleNumber=:sales_no AND hgt.saleYear=:sales_year").setInteger("sales_no", hgt.getSaleNumber()).setString("sales_year", hgt.getSaleYear()).list();

But result is 0.

===========================
Hibernate: select highgrownt0_.SALES_YEAR as col_0_0_ from TRN_HGT_QUATA highgrownt0_ where highgrownt0_.SALES_NO=? and highgrownt0_.SALES_YEAR=?
===========================