Costum sql for collection loading problem<load-collection

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

Hello,

I'm trying to load a set with costum sql, but I really can't get it working. Below I've posted a copy of all my simplified code(If you copy-paste everything to you're computer and configure an mysql schema you should be able to run it). I really can't see what is caussing the "org.hibernate.MappingException: Unknown collection role: Team.matches".

any help is greatly appriciated.

Hibernate version:3.1.1
Mysql 5.0.16

The Team class code

Code:

package test;

import java.util.Set;

public class Team {

    Long id;
    String name;
    Set matches;

    public Long getId() {return id;}
    public void setId(Long id){this.id = id;}

    public String getName() {return name;}
    public void setName(String name){this.name = name;}

    public Set getMatches(){return matches;}
    public void setMatches(Set matches){this.matches = matches;}
}

The Team class mapping
<

Code:

?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="test.Team" table="teams">

        <id name="id" column="id">
            <generator class="native"/>
        </id>

        <property name="name"/>

        <set name="matches" inverse="true">
            <key/>
            <one-to-many class="test.Match"/>
            <loader query-ref="matches"/>
        </set>

    </class>
    <sql-query name="matches">
    <load-collection alias="mat" role="Team.matches"/>
        SELECT {mat.*} FROM matches mat 
        WHERE mat.team1 = :id
        OR mat.team2 = :id 
    </sql-query>


</hibernate-mapping>

The Macth code

Code:

package test;

public class Match {

    Long id;
    Team team1;
    Team team2;
    String result;

    public Long getId() {return id;}
    public void setId(Long id){this.id = id;}

    public Team getTeam1() {return team1;}
    public void setTeam1(Team team1) {this.team1 = team1;}

    public Team getTeam2(){return team2;}
    public void setTeam2(Team team2){this.team2 = team2;}

    public String getResult(){return result;}
    public void setResult(String result){this.result = result;}
}

The Match mapping

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="test.Match" table="matches">

        <id name="id" column="id">
            <generator class="native"/>
        </id>

        <many-to-one name="team1"/>
        <many-to-one name="team2"/>

        <property name="result"/>

    </class>
</hibernate-mapping>

My hibernate.cfg.xml(bassicly copied from the ref manual)

Code:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="connection.username">root</property>
        <property name="connection.password">paswoord</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

        <mapping resource="test/Team.hbm.xml"/>
        <mapping resource="test/Match.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

My main class (just try to get an SessionFactory)

Code:

package test;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;


public class HibTestMain {
    public static void main(String[] args){
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
    }

}

The error and log resulting from executing hibTestMain

Code:

.......
18:45:17,656 DEBUG SchemaExport:296 - alter table matches add index FK321E8933BD79BFCE (team2), add constraint FK321E8933BD79BFCE foreign key (team2) references teams (id)
18:45:17,671  INFO SchemaExport:202 - schema export complete
18:45:17,671 DEBUG DriverManagerConnectionProvider:129 - returning connection to pool, pool size: 1
18:45:17,671 DEBUG SessionFactoryImpl:353 - Checking 0 named HQL queries
18:45:17,671 DEBUG SessionFactoryImpl:373 - Checking 1 named SQL queries
18:45:17,671 DEBUG SessionFactoryImpl:381 - Checking named SQL query: matches
18:45:17,671 DEBUG QueryPlanCache:111 - unable to locate native-sql query plan in cache; generating (SELECT {mat.*} FROM matches mat)
18:45:17,687 ERROR SessionFactoryImpl:336 - Error in named query: matches
org.hibernate.MappingException: Unknown collection role: Team.matches
   at org.hibernate.impl.SessionFactoryImpl.getCollectionPersister(SessionFactoryImpl.java:521)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.addCollection(SQLQueryReturnProcessor.java:136)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.processCollectionReturn(SQLQueryReturnProcessor.java:172)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:101)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:87)
   at org.hibernate.loader.custom.SQLCustomQuery.<init>(SQLCustomQuery.java:105)
   at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:21)
   at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:113)
   at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:409)
   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:327)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
   at test.HibTestMain.main(HibTestMain.java:9)
   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:585)
   at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)
Exception in thread "main" org.hibernate.HibernateException: Errors in named queries: matches
   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:338)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
   at test.HibTestMain.main(HibTestMain.java:9)
   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:585)
   at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)

Process finished with exit code 1

max · **Posted:** Sun Feb 05, 2006 4:39 am

your role is named Team.matches, instead of mat.matches which would be the correct one. remember to use the right alias.

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

I'm sorry Max, but I can't get it working. I really must be overlooking something very stupid here

I tried the following Mapping for the Team class, according to your advice I change the role to 'mat.matches'. I don't understand why I should use 'mat.matches' as role instead of 'Team.matches'. It looks to me that the role is: {OwnerClassName.propertyName} thus,==> MyClassname.MyCollection. Anyway, I doesn't work in either case, and I really would like to get this example running.

Thanks in Advance,

Code:

<hibernate-mapping>
    <class name="test.Team" table="teams">

        <id name="id" column="id">
            <generator class="native"/>
        </id>

        <property name="name"/>

        <set name="matches" inverse="true">
            <key/>
            <one-to-many class="test.Match"/>
            <loader query-ref="matches"/>
        </set>

    </class>

    <sql-query name="matches">
    <load-collection alias="mat" role="mat.matches"/>
        SELECT {mat.*} FROM matches mat
        WHERE mat.team1 = :id
        OR mat.team2 = :id
    </sql-query>

</hibernate-mapping>

When I try to obtain an Sessionfactory basis on the mapping I still get following Error. It looks to be somethings with the rolename, but I really don't see it:

Code:

14:29:56,187 DEBUG SessionFactoryImpl:381 - Checking named SQL query: matches
14:29:56,187 DEBUG QueryPlanCache:111 - unable to locate native-sql query plan in cache; generating (SELECT {mat.*} FROM matches mat
        WHERE mat.team1 = :id
        OR mat.team2 = :id)
14:29:56,203 ERROR SessionFactoryImpl:336 - Error in named query: matches
org.hibernate.MappingException: Unknown collection role: mat.matches
   at org.hibernate.impl.SessionFactoryImpl.getCollectionPersister(SessionFactoryImpl.java:521)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.addCollection(SQLQueryReturnProcessor.java:136)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.processCollectionReturn(SQLQueryReturnProcessor.java:172)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.processReturn(SQLQueryReturnProcessor.java:101)
   at org.hibernate.loader.custom.SQLQueryReturnProcessor.process(SQLQueryReturnProcessor.java:87)
   at org.hibernate.loader.custom.SQLCustomQuery.<init>(SQLCustomQuery.java:105)
   at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:21)
   at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:113)
   at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:409)
   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:327)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
   at test.HibTestMain.main(HibTestMain.java:9)
   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:585)
   at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)
Exception in thread "main" org.hibernate.HibernateException: Errors in named queries: matches
   at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:338)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1176)
   at test.HibTestMain.main(HibTestMain.java:9)
   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:585)
   at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)

Process finished with exit code 1

max · **Posted:** Sun Feb 05, 2006 12:26 pm

sorry misread the stacktrace/mapping files.

the role name is classname.associationname and that should be "test.Team.matches" if i'm not completely wrong this time ;)

if anything else fails then go look in the unittests for load-collection and try and see if there is something else wrong.

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

Hello max,thanks for your help using the fully qualified name got me some steps further. I don't get anymore errors

But unfortunally, the result isn't what i should be )-: .

I've tried to test with following main-method. In an first hibernate Session I create some Teams and Matches. In a seccond hibernate Session, I try to get the matches by team.getMathes, but this results in an empty Set.

An ordinary SQLQuery(bassicly the same as in my mappings), does work as it should.

My test main method

Code:

package test;

import org.hibernate.*;
import org.hibernate.cfg.Configuration;

import java.util.Set;
import java.util.Iterator;
import java.util.List;


public class HibTestMain {
    public static void main(String[] args){
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();

        /////////////////////
        //first I just create some objects
        //////////////////////
        Session hibSession1 = sessionFactory.openSession();
        Transaction tx1 = hibSession1.beginTransaction();

        Team team1 = new Team();
        team1.setName("team one");

        Team team2 = new Team();
        team2.setName("team two");

        Match match1 = new Match();
        match1.setTeam1(team1);
        match1.setTeam2(team2);
        match1.setResult("1-3");

        Match match2 = new Match();
        match2.setTeam1(team1);
        match2.setTeam2(team2);
        match2.setResult("5-9");

        Match match3 = new Match();
        match3.setTeam1(team1);
        match3.setTeam2(team2);
        match3.setResult("4-4");

        hibSession1.saveOrUpdate(team1);
        hibSession1.saveOrUpdate(team2);

        hibSession1.saveOrUpdate(match1);
        hibSession1.saveOrUpdate(match2);
        hibSession1.saveOrUpdate(match3);

        tx1.commit();
        hibSession1.close();

        /////////////////////
        //openening an new Session to get the an by hibernate initialized collection
        //////////////////////

        Session hibSession2 = sessionFactory.openSession();
        Transaction tx2= hibSession2.beginTransaction();

        //the set with matches of team1  is empty, but it should contain 3 matches
        Team team1SecondVersion = (Team)hibSession2.get(Team.class,1L);
        Set matches = team1SecondVersion.getMatches();
        System.out.println("matches.size = " + matches.size()); //result is 0
        for(Iterator <Match>it=matches.iterator();it.hasNext();){
            System.out.println("iterating through matches of team)" + it.next());
        }

        //this sqlQuery works as it should
        SQLQuery sqlQuery = hibSession2.createSQLQuery(
                " SELECT {mat.*} FROM matches mat" +
                " WHERE mat.team1 = :id " +
                " OR mat.team2 = :id");

        sqlQuery.addEntity("mat", Match.class);
        sqlQuery.setLong("id",1L);
        List sqlQueryResult = sqlQuery.list();
        System.out.println("sqlQueryResult.size = " + sqlQueryResult.size()); //result is 3
        for(Iterator it= sqlQueryResult.iterator();it.hasNext();){
            System.out.println("iterating queryResult: " +it.next());
        }

        tx2.commit();
        hibSession2.close();
    }
}

I've tried serveral things, and I keep noticing very strange unexpected behaviour. e.g. The hbm2ddl.auto creates an extra id column at the end of my mathes column.

Code:

mysql> describe matches;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| match_id | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| team1    | bigint(20)   | YES  | MUL | NULL    |                |
| team2    | bigint(20)   | YES  | MUL | NULL    |                |
| result   | varchar(255) | YES  |     | NULL    |                |
| id       | bigint(20)   | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

When I remove the matches Set and the sql-query from the Team mapping this behaviour is gone. I really don't get why hibernate is doing
this.

thanks in advance

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

Could someone please point me the way to the unit tests

Heox · **Joined:** Mon Jan 17, 2005 1:09 pm **Posts:** 19

UnitTests can be found in the directory of hibernate when you download it.

the solution of my problem was: <key column="team1"/>