- hibernate error -

[jazzynote]

Hello everyone,

I'm having a problem with a project i am doing.

Here is my DB structure:



I am trying to work this structure out in hibernate and oracle.
The problem I am having is that I can't insert a game. I guess something is wrong with the mapping of the foreign key columns.

Could someone give me a hand please?

Teams.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 14-nov-2010 21:52:29 by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
    <class name="entities.Teams" table="TEAMS" schema="SCOTT">
        <id name="teamid" type="long">
            <column name="TEAMID" precision="22" scale="0" />
            <generator class="increment" />
        </id>
        <many-to-one name="categories" class="entities.Categories" fetch="select">
            <column name="CATEGORYID" precision="22" scale="0" not-null="true" />
        </many-to-one>
        <many-to-one name="divisions" class="entities.Divisions" fetch="select">
            <column name="DIVISIONID" precision="22" scale="0" not-null="true" />
        </many-to-one>
        <property name="name" type="string">
            <column name="NAME" length="50" not-null="true" />
        </property>
        <set name="gamesesForHometeamid" inverse="true">
            <key>
                <column name="HOMETEAMID" precision="22" scale="0" not-null="true" />
            </key>
            <one-to-many class="entities.Games" />
        </set>
        <set name="gamesesForVisitorsteamid" inverse="true">
            <key>
                <column name="VISITORSTEAMID" precision="22" scale="0" not-null="true" />
            </key>
            <one-to-many class="entities.Games" />
        </set>
    </class>
</hibernate-mapping>

Teams.java

package entities;
// Generated 14-nov-2010 21:52:29 by Hibernate Tools 3.2.1.GA


import java.util.HashSet;
import java.util.Set;

/**
 * Teams generated by hbm2java
 */
public class Teams  implements java.io.Serializable {


     private long teamid;
     private Categories categories;
     private Divisions divisions;
     private String name;
     private Set gamesesForHometeamid = new HashSet(0);
     private Set gamesesForVisitorsteamid = new HashSet(0);

    public Teams() {
    }

	
    public Teams(long teamid, Categories categories, Divisions divisions, String name) {
        this.teamid = teamid;
        this.categories = categories;
        this.divisions = divisions;
        this.name = name;
    }
    public Teams(long teamid, Categories categories, Divisions divisions, String name, Set gamesesForHometeamid, Set gamesesForVisitorsteamid) {
       this.teamid = teamid;
       this.categories = categories;
       this.divisions = divisions;
       this.name = name;
       this.gamesesForHometeamid = gamesesForHometeamid;
       this.gamesesForVisitorsteamid = gamesesForVisitorsteamid;
    }
   
    public long getTeamid() {
        return this.teamid;
    }
    
    public void setTeamid(long teamid) {
        this.teamid = teamid;
    }
    public Categories getCategories() {
        return this.categories;
    }
    
    public void setCategories(Categories categories) {
        this.categories = categories;
    }
    public Divisions getDivisions() {
        return this.divisions;
    }
    
    public void setDivisions(Divisions divisions) {
        this.divisions = divisions;
    }
    public String getName() {
        return this.name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    public Set getGamesesForHometeamid() {
        return this.gamesesForHometeamid;
    }
    
    public void setGamesesForHometeamid(Set gamesesForHometeamid) {
        this.gamesesForHometeamid = gamesesForHometeamid;
    }
    public Set getGamesesForVisitorsteamid() {
        return this.gamesesForVisitorsteamid;
    }
    
    public void setGamesesForVisitorsteamid(Set gamesesForVisitorsteamid) {
        this.gamesesForVisitorsteamid = gamesesForVisitorsteamid;
    }




}

Games.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 14-nov-2010 21:52:29 by Hibernate Tools 3.2.1.GA -->
<hibernate-mapping>
    <class name="entities.Games" table="GAMES" schema="SCOTT">
        <id name="gameid" type="long">
            <column name="GAMEID" precision="22" scale="0" />
            <generator class="increment" />
        </id>
        <many-to-one name="teamsByHometeamid" class="entities.Teams" fetch="select">
            <column name="HOMETEAMID" precision="22" scale="0" not-null="true" />
        </many-to-one>
        <many-to-one name="teamsByVisitorsteamid" class="entities.Teams" fetch="select">
            <column name="VISITORSTEAMID" precision="22" scale="0" not-null="true" />
        </many-to-one>
        <property name="start" type="string">
            <column name="START" length="50" not-null="true" />
        </property>
        <property name="hometeamscore" type="long">
            <column name="HOMETEAMSCORE" precision="22" scale="0" not-null="true" />
        </property>
        <property name="visitorsteamscore" type="long">
            <column name="VISITORSTEAMSCORE" precision="22" scale="0" not-null="true" />
        </property>
        <property name="status" type="long">
            <column name="STATUS" precision="22" scale="0" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

Games.java

package entities;
// Generated 14-nov-2010 21:52:29 by Hibernate Tools 3.2.1.GA


import java.math.BigDecimal;

/**
 * Games generated by hbm2java
 */
public class Games  implements java.io.Serializable {


     private long gameid;
     private Teams teamsByHometeamid;
     private Teams teamsByVisitorsteamid;
     private String start;
     private long hometeamscore;
     private long visitorsteamscore;
     private long status;

    public Games() {
    }

    public Games(long gameid, Teams teamsByHometeamid, Teams teamsByVisitorsteamid, String start, long hometeamscore, long visitorsteamscore, long status) {
       this.gameid = gameid;
       this.teamsByHometeamid = teamsByHometeamid;
       this.teamsByVisitorsteamid = teamsByVisitorsteamid;
       this.start = start;
       this.hometeamscore = hometeamscore;
       this.visitorsteamscore = visitorsteamscore;
       this.status = status;
    }
   
    public long getGameid() {
        return this.gameid;
    }
    
    public void setGameid(long gameid) {
        this.gameid = gameid;
    }
    public Teams getTeamsByHometeamid() {
        return this.teamsByHometeamid;
    }
    
    public void setTeamsByHometeamid(Teams teamsByHometeamid) {
        this.teamsByHometeamid = teamsByHometeamid;
    }
    public Teams getTeamsByVisitorsteamid() {
        return this.teamsByVisitorsteamid;
    }
    
    public void setTeamsByVisitorsteamid(Teams teamsByVisitorsteamid) {
        this.teamsByVisitorsteamid = teamsByVisitorsteamid;
    }
    public String getStart() {
        return this.start;
    }
    
    public void setStart(String start) {
        this.start = start;
    }
    public long getHometeamscore() {
        return this.hometeamscore;
    }
    
    public void setHometeamscore(long hometeamscore) {
        this.hometeamscore = hometeamscore;
    }
    public long getVisitorsteamscore() {
        return this.visitorsteamscore;
    }
    
    public void setVisitorsteamscore(long visitorsteamscore) {
        this.visitorsteamscore = visitorsteamscore;
    }
    public long getStatus() {
        return this.status;
    }
    
    public void setStatus(long status) {
        this.status = status;
    }




}

Test.java

public class Test {
 public static void main(String[] args) {
        Session session = HibernateUtil.getSessionFactory().openSession();
        Transaction transaction = null;
        try {
            transaction = session.beginTransaction();
  
            Games g = new Games();
            Teams t1 = new Teams();
            Teams t2 = new Teams();
            t1 = (Teams) session.get(Teams.class, 1L);
            t2 = (Teams) session.get(Teams.class, 2L);
            g.setHometeamscore(2L);
            g.setVisitorsteamscore(3L);
            g.setStart("NU");
            g.setStatus(1L);
            g.setTeamsByHometeamid(t2);
            g.setTeamsByVisitorsteamid(t1);
            session.save(g);
            transaction.commit();

        } catch (HibernateException e) {
            transaction.rollback();
            System.out.println("error : " + e.getMessage());
        } finally {
            session.close();
        }
    }
}

Error

Hibernate: select teams0_.TEAMID as TEAMID2_0_, teams0_.CATEGORYID as CATEGORYID2_0_, teams0_.DIVISIONID as DIVISIONID2_0_, teams0_.NAME as NAME2_0_ from SCOTT.TEAMS teams0_ where teams0_.TEAMID=?
Hibernate: select teams0_.TEAMID as TEAMID2_0_, teams0_.CATEGORYID as CATEGORYID2_0_, teams0_.DIVISIONID as DIVISIONID2_0_, teams0_.NAME as NAME2_0_ from SCOTT.TEAMS teams0_ where teams0_.TEAMID=?
Hibernate: select max(GAMEID) from GAMES
Hibernate: insert into SCOTT.GAMES (HOMETEAMID, VISITORSTEAMID, START, HOMETEAMSCORE, VISITORSTEAMSCORE, STATUS, GAMEID) values (?, ?, ?, ?, ?, ?, ?)
14-nov-2010 23:44:14 org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 1747, SQLState: 42000
14-nov-2010 23:44:14 org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-01747: invalid user.table.column, table.column, or column specification

14-nov-2010 23:44:14 org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: could not insert: [entities.Games]
error : could not insert: [entities.Games]
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2267)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2660)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:56)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:234)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
        at Test.main(Test.java:50)
Caused by: java.sql.SQLException: ORA-01747: invalid user.table.column, table.column, or column specification

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
        at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)
        at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:23)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2247)
        ... 11 more

Thanks in advance!!!

[Eranga]

Did you notice this?

invalid user.table.column, table.column, or column specification

Recheck your queries.

[jazzynote]

Yes I did, but i can't figure out column. I can insert properly in the other tables so I'm guessing the problem is in the Games table. But I can't see the solution?

Thanks in advance for your reply!

[jazzynote]

Problem solved.

Changing the order of the property's in the Games.hbm.xml did the trick.

And also renaming the start table to something else because start is a oracle preserved keyword :D.

[Eranga]

And also renaming the start table to something else because start is a oracle preserved keyword :D.

This is a big deal, sometimes it take a long time to identify that we have use such key-words. The things is it's not easy to remember the whole list. ;)

[Eranga]

Changing the order of the property's in the Games.hbm.xml did the trick.

Did you find the reason of it, how it works by just changing the order of it. Better to address that as well, before make more troubles on you at a later time.