Hibernate - Query Issues With Many-To-One Relationships

[Leonard Bedner]

Hello all.

Specs:

Microsoft Windows XP Professional Edition
MySQL 5.1
Hibernate Core 3.0
JDK 1.6

So, I currently have 2 tables that have a many-to-one relationship, named Ability and AbilityType. Here are the sql commands used to create the tables:

AbilityType

create table AbilityType
(
  id int not null primary key,
  name varchar(30),
  description text
)type=InnoDB;

Ability

create table Ability
(
  id int not null primary key,
  name varchar(30),
  description text,
  abilityTypeID int not null references AbilityType(id)
)type=InnoDB;

Here is my hibernate config file:

<?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>

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

    
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost/*</property>
    <property name="hibernate.connection.username">*</property>
    <property name="hibernate.connection.password">*</property>
    <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    
    <!-- Automatic schema creation (begin) === -->     
    <!-- <property name="hibernate.hbm2ddl.auto">create</property> -->

    
    <!-- Simple memory-only cache -->
    <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>

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

    <!-- ############################################ -->
    <!-- # mapping files with external dependencies # -->
    <!-- ############################################ -->

    <mapping resource="com/brc/roe/db/ability/AbilityType.hbm.xml"/>
    <mapping resource="com/brc/roe/db/ability/Ability.hbm.xml"/>
    
</session-factory>
</hibernate-configuration>

Here are the XML mappings for the tables:

  <?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="com.brc.roe.db.ability.AbilityType" table="AbilityType">

      <id name="id" column="ID">
         <generator class="assigned" />
      </id>

      <property name="name" type="string">
         <column name="name"/>
      </property>

      <property name="description" type="text">
         <column name="description" />
      </property>
      
   </class>
</hibernate-mapping>

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

<?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="com.brc.roe.db.ability.Ability" table="Ability">

      <id name="id" column="ID">
         <generator class="assigned" />
      </id>

      <property name="name" type="string">
         <column name="name"/>
      </property>

      <property name="description" type="text">
         <column name="description" />
      </property>
      
      <many-to-one name="abilityType" class="com.brc.roe.db.ability.AbilityType">
         <column name="abilityTypeID" />
      </many-to-one>
      
   </class>
</hibernate-mapping>

Here are the Java files for the tables:

package com.brc.roe.db.ability;

public class AbilityType
{
   Long id;
   String name;
   String description;
   
   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 String getDescription() {return description;}
   public void setDescription(String description) {this.description = description;}
}

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

package com.brc.roe.db.ability;

public class Ability
{
   private long id;
   private String name;
   private String description;
   private AbilityType abilityType;
   
   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 String getDescription() {return description;}
   public void setDescription(String description) {this.description = description;}

   public AbilityType getAbilityType() {return abilityType;}
   public void setAbilityType(AbilityType abilityType) {this.abilityType = abilityType;}
}

Now, when I run a normal join on these tables to get all Abilities by a specific AbilityType, I am expecting to get 5 results of type com.brc.roe.db.ability.Abilityin the result set. What happens however is, I get 5 results of type java.lang.Object arrays. Within the 2 slots of these arrays are a reference to both a com.brc.roe.db.ability.Ability AND a com.brc.roe.db.ability.AbilityType.

Now, the results in of themselves are correct, but is there any way to just get the com.brc.roe.db.ability.Ability's? Here is my code:

   public List<Ability> fetchAbilityByType(String typeName)
   {
      Session session = SessionFactoryUtil.getSessionFactory().getCurrentSession();
      session.beginTransaction();
      
      Query query = session.createQuery("from AbilityType t, Ability a where t.name = '" + typeName + "' and t.id = a.abilityType");
      List<Ability> list = query.list();
      
      session.getTransaction().commit();
      
      return list;
   }

If any more information is needed, let me know.

Thank you.

[David Newton]

I might have mapped this differently if I knew I'd be going from type -> ability... by making an inverse relationship you can get an ability type and its associated abilities w/o having to do a join like that. I'm not sure if that's what's causing the behavior you're seeing--I've never really done what you're doing the way you're doing it. I make those kinds of relationships explicit. Something to think about, anyway.

[Leonard Bedner]

David Newton wrote:I might have mapped this differently if I knew I'd be going from type -> ability... by making an inverse relationship you can get an ability type and its associated abilities w/o having to do a join like that. I'm not sure if that's what's causing the behavior you're seeing--I've never really done what you're doing the way you're doing it. I make those kinds of relationships explicit. Something to think about, anyway.

Wow... Okay, first off, I definitely put in the wrong test code. My apologies... Here is the stuff I was using when I initially posted this topic:

 
	public List fetchAbilitiesByType(String typeName)
	{
		Session session = SessionFactoryUtil.getSessionFactory().getCurrentSession();
		session.beginTransaction();
		
		Query query = session.createQuery("from " + ABILITY + " a, " + ABILITY_TYPE + " t where t.name = '" + typeName + "' and t.id = a.abilityType");
		List list = query.list();
		
		session.getTransaction().commit();
		
		return list;
	}

And here is the native SQL I was using in the command line, which returns exactly what I want (or so I thought...):

select * from Ability a, AbilityType t where t.name = 'Passive Ability' and t.id = a.abilityTypeID;

After sitting and thinking about what you said, I realized I was looking at this the wrong way. I really should be using this query:

select a.* from Ability a, AbilityType t where t.name = 'Passive Ability' and t.id = a.abilityTypeID;

I now realize that the first query did return what I wanted, plus more, since it is taking the results from BOTH tables. That explains why I was getting back BOTH Ability AND AbilityType objects in the result set. In the 2nd query, it's only taking the results from the Ability table, which is really what I need. So I guess I now have 2 followup questions:

1) How can I replicate the 2nd query using HQL? I tried using this:

Query query = session.createQuery("select a.* from " + ABILITY + " a, " + ABILITY_TYPE + " t where t.name = '" + typeName + "' and t.id = a.abilityTypeID");

And got this stack trace:

org.hibernate.hql.ast.QuerySyntaxException: expecting IDENT, found '*' near line 1, column 10 [select a.* from com.brc.roe.db.ability.Ability a, com.brc.roe.db.ability.AbilityType t where t.name = 'Passive Ability' and t.id = a.abilityTypeID]
	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
	at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
	at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
	at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
	at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
	at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
	at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
	at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
	at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
	at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
	at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1585)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:342)
	at $Proxy0.createQuery(Unknown Source)
	at com.brc.roe.db.ability.AbilityManager.fetchAbilitiesByType(AbilityManager.java:55)
	at com.brc.roe.db.ability.AbilityManagerTest.testFetchAbilityByType(AbilityManagerTest.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at junit.framework.TestCase.runTest(TestCase.java:168)
	at junit.framework.TestCase.runBare(TestCase.java:134)
	at junit.framework.TestResult$1.protect(TestResult.java:110)
	at junit.framework.TestResult.runProtected(TestResult.java:128)
	at junit.framework.TestResult.run(TestResult.java:113)
	at junit.framework.TestCase.run(TestCase.java:124)
	at junit.framework.TestSuite.runTest(TestSuite.java:232)
	at junit.framework.TestSuite.run(TestSuite.java:227)
	at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:83)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

I then tried this method:

Query query = session.createSQLQuery("select a.* from " + ABILITY + " a, " + ABILITY_TYPE + " t where t.name = '" + typeName + "' and t.id = a.abilityTypeID");

It worked, but when I got the list, it was a list of Object arrays (that included all the data in said arrays). Obviously, I want Ability objects.

2) All of the above said, do you still think I am going across this wrong, starting way back with how I created the AbilityType and Ability tables, and the relationship between them? Or was that just because of how I originally wrote the HQL statement (which obviously was showing something I didn't want).

Thanks for your time. It is much appreciated!

[Leonard Bedner]

Wouldn't you believe it. I was just informed that I needed to change this:

Query query = session.createQuery("select a.* from " + ABILITY + " a, " + ABILITY_TYPE + " t where t.name = '" + typeName + "' and t.id = a.abilityType");

With this:

Query query = session.createQuery("select a from " + ABILITY + " a, " + ABILITY_TYPE + " t where t.name = '" + typeName + "' and t.id = a.abilityType");

So, I just needed to use "a" instead of "a.*"... You live and you learn, I guess... Thanks, David. You definitely got me thinking, which made me realize my initial query wasn't any good to begin with.

[David Newton]

I like it when I can mumble some barely-cognizant noises and have it lead someone else to actually do the work ;)

[Ben Narendren]

I believe what you need might be something like this


from Ability as ability
inner join fetch ability.abilityType

This way you don't have to deal with object arrays.

If you don't want to fetch the abilityType , you need something like

from Ability as ability
inner join ability.abilityType