Many-to-many with different FKs to same table

dberner727 · **Posted:** Thu Jun 08, 2006 1:17 pm

Hi,

I'm having a problem getting some many-to-many associations working correctly. I have the following tables (relevant columns included only):

POD
pod_rec_nbr (unique)
pod_basin (= PK1)
pod_nbr (= PK2)
pod_suffix (= PK3)

PRIORITY
prior_rec_nbr (= PK)

SUMMARY
basin (= PK1)
nbr (= PK2)
suffix (= PK3)

And the association tables:

PRIOR_POD_JC
prior_rec_nbr (= FK -> priority)
pod_rec_nbr (= FK -> pod)

POD_JC
basin (= FK1 -> summary)
nbr (= FK2 -> summary)
suffix (= FK3 -> summary)
pod_basin (= FK1 -> pod)
pod_nbr (= FK2 -> pod)
pod_suffix (= FK3 -> pod)

As you can see, I'm trying to do a many-to-many association between Pods and Priorities, and between Pods and Summaries. Hibernate does not like the fact that I am using two different foreign keys into POD, and crashes while creating the session factory. Any help would be appreciated.

Hibernate version: 3.2

Mapping documents:
Pod.java

Code:

...
   @ManyToMany(cascade = { CascadeType.ALL }, mappedBy="pods")
   public Collection<Priority> getPriorities() {
      return this.priorities;
   }

   public void setPriorities(Collection<Priority> priorities) {
      this.priorities = priorities;
   }

   @ManyToMany(cascade = { CascadeType.ALL }, mappedBy="pods")
   public Collection<Summary> getSummaries() {
      return summaries;
   }
   
   public void setSummaries(Collection<Summary> summaries) {
      this.summaries = summaries;
   }
...

Summary.java

Code:

...
   @ManyToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
   @JoinTable(
      name="pod_jc",
      joinColumns={@JoinColumn(name="basin"),@JoinColumn(name="nbr"),@JoinColumn(name="suffix")},
      inverseJoinColumns={@JoinColumn(name="pod_basin"),@JoinColumn(name="pod_nbr"),@JoinColumn(name="pod_suffix")}
   )
   public Collection<Pod> getPods() {
      return this.pods;
   }

   public void setPods(Collection<Pod> pods) {
      this.pods = pods;
   }
...

Priority.java

Code:

...
   @ManyToMany(cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
   @JoinTable(
      name="prior_pod_jc",
      joinColumns={@JoinColumn(name="prior_rec_nbr",referencedColumnName="prior_rec_nbr")},
      inverseJoinColumns={@JoinColumn(name="pod_rec_nbr",referencedColumnName="pod_rec_nbr")}
   )
   public Collection<Pod> getPods() {
      return this.pods;
   }

   public void setPods(Collection<Pod> pods) {
      this.pods = pods;
   }
...

Code between sessionFactory.openSession() and session.close():
HibernateEntityTest.java

Code:

package us.nm.state.ose.ewaters.hibernate.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Map.Entry;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.hibernate.EntityMode;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.metadata.ClassMetadata;
import org.testng.annotations.Configuration;
import org.testng.annotations.Test;

public class HibernateEntityTest {
   
   private static Logger logger = Logger.getLogger(HibernateEntityTest.class);
   private SessionFactory sessionFactory;
   private Session session;
   private Transaction tx;
   
   @Configuration(beforeSuite=true,alwaysRun=true)
   public void configureLogger() throws IOException {
      InputStream input = new FileInputStream("log4j.test.properties");
      Properties loggerProps = new Properties();
      loggerProps.load(input);
      PropertyConfigurator.configure(loggerProps);
   }
   
   @Configuration(beforeSuite=true,alwaysRun=true,dependsOnMethods={"configureLogger"})
   public void configureHibernate() throws IOException {
      AnnotationConfiguration config = new AnnotationConfiguration();
      config.configure(new File("hibernate.cfg.xml"));
      sessionFactory = config.buildSessionFactory();
   }
   
   @Configuration(beforeTestMethod=true,alwaysRun=true)
   public void openSession() {
      session=sessionFactory.openSession();
      tx = session.beginTransaction();
   }
   @Configuration(afterTestMethod=true,alwaysRun=true)
   public void closeSession() {
      try {
         tx.commit();
      } catch (Throwable t) {
         logger.warn("Could not commit transaction, rolling back instead",t);
         tx.rollback();
      } finally {
         session.close();
      }
   }
   
   @Test(groups={"configuration"})
   public void testConfiguration() {
      // Empty test method just verifies that the configuration works
   }
   
   @SuppressWarnings("unchecked")
   @Test(groups={"hibernate","read","all"})
   public void testEntityReads() {
      Map<String,ClassMetadata> classMap = sessionFactory.getAllClassMetadata();
      for(Entry<String,ClassMetadata> entry: classMap.entrySet()) {
         testEntityRead(entry.getKey(),entry.getValue());
      }
   }
   
   private void testEntityRead(String entityName, ClassMetadata metadata) {
      logger.info(String.format("Querying for entity %s",entityName));
      List list = session.createQuery(String.format("from %s",entityName)).setMaxResults(10).list();
      if (list.size() == 0) {
         logger.info(String.format("No entities available."));
         return;
      }
      Object entity = list.get(0);
      logger.info(String.format("Retrieved entity (class %s), reading properties.",entity.getClass().getName()));
      for (String propName: metadata.getPropertyNames()) {
         logger.info(String.format("Reading property %s.",propName));
         Object value = metadata.getPropertyValue(entity,propName,EntityMode.POJO);
         if (logger.isDebugEnabled()) {
            try {
               logger.debug(value);
            } catch (ClassCastException ex) {
               logger.debug("(Can't convert value to String)");
            }
         }
      }
   }
}

Full stack trace of any exception that occurs:

Code:

org.hibernate.AnnotationException: A Foreign key refering us.nm.state.ose.ewaters.hibernate.Pod has the wrong number of column. should be 1
   at org.hibernate.cfg.annotations.TableBinder.bindFk(TableBinder.java:255)
   at org.hibernate.cfg.annotations.CollectionBinder.bindManytoManyInverseFk(CollectionBinder.java:1088)
   at org.hibernate.cfg.annotations.CollectionBinder.bindManyToManySecondPass(CollectionBinder.java:997)
   at org.hibernate.cfg.annotations.CollectionBinder.bindStarToManySecondPass(CollectionBinder.java:527)
   at org.hibernate.cfg.annotations.CollectionBinder$1.secondPass(CollectionBinder.java:468)
   at org.hibernate.cfg.CollectionSecondPass.doSecondPass(CollectionSecondPass.java:35)
   at org.hibernate.cfg.Configuration.secondPassCompile(Configuration.java:1049)
   at org.hibernate.cfg.AnnotationConfiguration.secondPassCompile(AnnotationConfiguration.java:302)
   at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1205)
   at us.nm.state.ose.ewaters.hibernate.test.HibernateEntityTest.configureHibernate(HibernateEntityTest.java:42)
   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.testng.internal.MethodHelper.invokeMethod(MethodHelper.java:552)
   at org.testng.internal.Invoker.invokeConfigurationMethod(Invoker.java:318)
   at org.testng.internal.Invoker.invokeConfigurations(Invoker.java:152)
   at org.testng.SuiteRunner.privateRun(SuiteRunner.java:205)
   at org.testng.SuiteRunner.run(SuiteRunner.java:146)
   at org.testng.eclipse.runner.RemoteTestNG.run(RemoteTestNG.java:98)
   at org.testng.eclipse.runner.RemoteTestNG.main(RemoteTestNG.java:138)

Name and version of the database you are using:Informix

The generated SQL (show_sql=true): Fails before configuration completes.

Debug level Hibernate log excerpt:

Code:

Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.Ejb3Column.debug (Log4JLogger.java:84) Binding column view_rowid unique false
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.annotations.PropertyBinder.debug (Log4JLogger.java:84) binding property viewRowid with lazy=false
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.annotations.SimpleValueBinder.debug (Log4JLogger.java:84) building SimpleValue for viewRowid
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.annotations.PropertyBinder.debug (Log4JLogger.java:84) Building property viewRowid
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.annotations.PropertyBinder.debug (Log4JLogger.java:84) Cascading viewRowid with null
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.annotations.PropertyBinder.debug (Log4JLogger.java:84) Building property id
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.annotations.PropertyBinder.debug (Log4JLogger.java:84) Cascading id with null
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.AnnotationBinder.debug (Log4JLogger.java:84) Bind @EmbeddedId on id
Jun 2006 08:32:59,718 [DEBUG] org.hibernate.cfg.AnnotationConfiguration.debug (Log4JLogger.java:84) processing manytoone fk mappings
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.Configuration.debug (Log4JLogger.java:84) processing extends queue
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.Configuration.debug (Log4JLogger.java:84) processing collection mappings
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.CollectionSecondPass.debug (Log4JLogger.java:84) Second pass for collection: us.nm.state.ose.ewaters.hibernate.Priority.pods
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.annotations.CollectionBinder.debug (Log4JLogger.java:84) Binding as ManyToMany: us.nm.state.ose.ewaters.hibernate.Priority.pods
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.CollectionSecondPass.debug (Log4JLogger.java:84) Mapped collection key: prior_rec_nbr, element: pod_rec_nbr
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.CollectionSecondPass.debug (Log4JLogger.java:84) Second pass for collection: us.nm.state.ose.ewaters.hibernate.Summary.pods
Jun 2006 08:32:59,828 [DEBUG] org.hibernate.cfg.annotations.CollectionBinder.debug (Log4JLogger.java:84) Binding as ManyToMany: us.nm.state.ose.ewaters.hibernate.Summary.pods