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

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.

Answer 2

I know there is an accepted answer. I would like to mention an alternative answer to anyone who comes on this page with the same problem - unknown column in field list.

I was trying to pick up Survey details (from Survey Table) when I query UserSurveyLog (which has common SurveyID between both tables) via mapping

Mapping in UserSurvey Table

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(referencedColumnName = "SurveyID")
private Survey survey;

public Survey getSurvey() {    return survey;  }

public void setSurvey(Survey survey) {   this.survey = survey;  }

public Integer getUserSurveyLogID() {
    return userSurveyLogID;
}

Look at this error I receive

Hibernate: insert into hifiniteDB.UserSurveyLog (CompletedDate, CreatedByUserName, CreatedDate, ExpiryDate, LastDeliveredDate, ModifiedByUserName, ModifiedDate, OrgNodeID, ProgramUserID, RootOrgNodeID, survey_SurveyID, SurveyID, SurveyStatus, SurveyTriggerID, SurveyTriggerType, UserDiagnosisID, UserID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 2019-10-09 14:39:45.722 [https-jsse-nio-8443-exec-3] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Unknown column 'survey_SurveyID' in 'field list'

Observe that survey_SurveyID has been added by the mapping I have made on the Survey Table. Don't know why just doing a fetch adds this column to the Insert on UserSurvey Log that should have been independent.

The way I solved this was by adding an insertable=false, updatable = false on the @join column.

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(referencedColumnName = "SurveyID", insertable = false,updatable = false)
private Survey survey;

Answer 3

check also getters and setters names on your class, all must be the same

    public String getNome() {//wrong Nome
        return name;
    }


    public void getName(String name) {//right
        this.name = name;
    }

Answer 4

Please check whether your database has all the tables. If there are any tables or attributes in the table missing, add them to your database and re-add your hibernate mapping files. I fixed this error in that way. I got 2 attributes missing in two tables of my database and once I added them and re-add hibernate, the error was fixed.

Answer 5

Just check your variable names in your POJO and the names provided in the database. When they're not the same this error is thrown.

Answer 6

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 7

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.