java hibernate Unknown column ' ' in 'field list'

Question

I need a help.

When i use getAllStreets() method i have error in HQL:

org.hibernate.exception.SQLGrammarException: Unknown column 'this_1_.houses_id' in 'field list'

I guess he must write this_1_id instead this_1_.houses_id

May be i did wrong Entity and relation ?

2 entity - Houses and Streets

ER - model:

Table Streets

• Id
• Name
• Houses_id

Table Houses

• id
• name

My Classes:

Street

@Entity
@Table(name="Streets")
public class Street {
    private Long id;
    private String name;
    private Long houses_id;
    private House house;
    public Street(){}    
    @Id
    @GeneratedValue(generator="increment")
    @GenericGenerator(name="increment", strategy="increment")
    @Column(name="id")
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @ManyToOne
    @JoinTable(name="Houses", joinColumns = @JoinColumn(name="id"), inverseJoinColumns=@JoinColumn(name="houses_id"))
    public House getHouse() {
        return house;
    }
    public void setHouse(House house) {
        this.house = house;
    }
    @Column(name="houses_id")
    public Long getHouses_id() {
        return houses_id;
    }
    public void setHouses_id(Long houses_id) {
        this.houses_id = houses_id;
    }
}

House

@Entity
@Table(name="Houses")
public class House {

    private Long id;
    private String name;
    public House(){}

    @Id
    @GeneratedValue(generator = "increment")
    @GenericGenerator(name="increment", strategy="increment")
    @Column(name="id")
    public Long getId() {
        return id;
    }
    public void setId(Long id) {
        this.id = id;
    }
    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

My DAOIMP:

StreetDAOImp:

public class StreetDAOImpl implements StreetDAO {
    @Override
    public void addStreet(Street street) throws SQLException {
        // TODO Auto-generated method stub
        Session session = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            session.beginTransaction();

            session.save(street);
            session.getTransaction().commit();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally{
            if(session != null && session.isOpen()){
                session.close();
            }
        }
    }
    @Override
    public Collection getAllStreets() throws SQLException {
        // TODO Auto-generated method stub
        Session session = null;
        List<Street> streets = new ArrayList<Street>(); 
        try {
            session = HibernateUtil.getSessionFactory().openSession();

            streets = session.createCriteria(Street.class).list();
            //Query q = session.createQuery("select str from com.ff.model.Street str join str.houses h where h.id = str.houses_id");
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        return streets;
    }
}

HouseDAOImpl:

public class HouseDAOImpl implements HouseDAO {

    @Override
    public void addHouse(House house)throws SQLException {
        // TODO Auto-generated method stub
        Session session = null;
        try {
            session = HibernateUtil.getSessionFactory().openSession();
            session.beginTransaction();
            session.save(house);
            session.getTransaction().commit();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        finally{
            if(session != null && session.isOpen()){
                session.close();
            }
        }
    }

    @Override
    public Collection getAllHouses() throws SQLException {
        // TODO Auto-generated method stub
        Session session = null;
        List<House> houses = new ArrayList<House>();

        try {
            session = HibernateUtil.getSessionFactory().openSession();
            houses = session.createCriteria(House.class).list();
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
         finally {
              if (session != null && session.isOpen()) {
                session.close();
              }

    }
    return houses;
}}

Error:

log4j:WARN No appenders could be found for logger (org.jboss.logging).
log4j:WARN Please initialize the log4j system properly.
Hibernate: select this_.id as id1_1_, this_.houses_id as houses2_1_1_, this_.name as name1_1_, this_1_.houses_id as houses3_0_1_, house2_.id as id0_0_, house2_.name as name0_0_ from Streets this_ left outer join Houses this_1_ on this_.id=this_1_.id left outer join Houses house2_ on this_1_.houses_id=house2_.id
org.hibernate.exception.SQLGrammarException: Unknown column 'this_1_.houses_id' in 'field list'
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at $Proxy14.executeQuery(Unknown Source)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2031)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:124)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1621)
    at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374)
    at com.ff.DAO.StreetDAOImpl.getAllStreets(StreetDAOImpl.java:48)
    at FFMain.main(FFMain.java:58)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'this_1_.houses_id' in 'field list'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
    ... 16 more

Answer 1

Please make sure the table you are calling have the right columns. I had the issue and I found that the mapped domain columns were not matching in the database.

Answer 2

Check out this code:

@JoinTable(name="Houses", joinColumns = @JoinColumn(name="id"), inverseJoinColumns=@JoinColumn(name="houses_id"))

I'm not sure what are you trying to achieve here, but JoinTable is usually used to resolve ManyToMany relations with intermediary table. So this code implies that you have table Houses with id and houses_id columns. Error message says that there is no houses_id in Houses table (which sounds logical to me)
Maybe you should try ManyToOne and JoinColumn instead? For example:

@JoinColumn(name="house_id")

or houses_id if that's your foreign key in streets table. Plural sounds strange if this is really many-to-one relationship.

Answer 3

I noticed that, while I used a variable name like userName or bloodGroup, it's getting the error "Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'user0_.blood_group' in 'field list' " To overcome this issue change the variable name like username or blood_Group. I hope it will get the excepted result.