Databases
StackOverflow2013
Postsdbo
POHibernate bidirectional OneToMany with JoinTable and polymorphic classes with table per concrete class
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate bidirectional OneToMany with JoinTable and polymorphic classes with table per concrete class
    primarykey
    data
    text
    <p>I'm using Hibernate 4.1.7 Java with Spring, working on MySql.</p> <p>I have a Publication class, which is abstract and has 3 subclasses, and a Page class which is also abstract and has 2 subclasses.</p> <p>In Publication there is a List and in Page i have a Publication, so it's a common OneToMany/ManyToOne bidirectional relation, but since I need the pages to be sorted I'm using a cross table as shown :</p> <pre><code>// Publication.java @OneToMany(fetch=FetchType.LAZY) @JoinTable(name="publication_pages", joinColumns=@JoinColumn(table="publication_pages", name="pub_id", referencedColumnName="id"), inverseJoinColumns=@JoinColumn(table="publication_pages", name="pg_id", referencedColumnName="id")) @OrderColumn(name="i") public List&lt;Page&gt; getPages() { return pages; } // Page.java @ManyToOne @JoinTable(name="publication_pages", joinColumns=@JoinColumn(table="publication_pages", name="pg_id", referencedColumnName="id", insertable=false, updatable=false), inverseJoinColumns=@JoinColumn(table="publication_pages", name="pub_id", referencedColumnName="id", insertable=false, updatable=false)) public Publication getPublication() { return publication; } </code></pre> <p>Hibernate does not complain about annotations, but as soon as I try to call getPages, it generates a query that searches for pub_id on Page subclasses tables, instead that only in the cross table. </p> <p>The error is :</p> <pre><code>com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'page1_.pub_id' in 'field list' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:532) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:616) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) at $Proxy54.executeQuery(Unknown Source) at org.hibernate.loader.Loader.getResultSet(Loader.java:1897) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1698) at org.hibernate.loader.Loader.doQuery(Loader.java:832) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:293) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:263) at org.hibernate.loader.Loader.loadCollection(Loader.java:2094) at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:61) at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:678) at org.hibernate.event.internal.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:82) at org.hibernate.internal.SessionImpl.initializeCollection(SessionImpl.java:1801) at org.hibernate.collection.internal.AbstractPersistentCollection$4.doWork(AbstractPersistentCollection.java:524) at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:212) at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:520) at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:125) at org.hibernate.collection.internal.AbstractPersistentCollection$1.doWork(AbstractPersistentCollection.java:152) at org.hibernate.collection.internal.AbstractPersistentCollection$1.doWork(AbstractPersistentCollection.java:139) at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:212) at org.hibernate.collection.internal.AbstractPersistentCollection.readSize(AbstractPersistentCollection.java:138) at org.hibernate.collection.internal.PersistentList.isEmpty(PersistentList.java:121) </code></pre> <p>An I doing something wrong, or is a Hibernate bug, or is it simply impossible due to the high number of unions it performs in the query?</p> <p>TIA</p> <p>EDIT: this is the query hibernate is generating (I removed all the not-related columns) :</p> <pre><code>Hibernate: select pages0_.pub_id as pub1_46_3_, pages0_.pg_id as pg2_43_3_, pages0_.i as i3_, page1_.id as id42_0_, [...] page1_.pub_id as pub1_43_0_ [...] from publication_pages pages0_ inner join ( select id, [...] from page_fixed union select id, [...] from page_query ) page1_ on pages0_.pg_id=page1_.id left outer join ( select id, [...] from publication ) publicatio2_ on page1_.pub_id=publicatio2_.id left where pages0_.pub_id=? </code></pre> <p>As you can see, it uses page1_.pub_id in the join (as if pub_id was on Page) instead of pages0_.pub_id (which is correctly used on the where clause).</p> <p>This happens on a call to Publication.getPages().isEmpty() </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSimone Gianni
    1. USSimone Gianni
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhy exactly do you use a cross table for sorting? Have you considered to implement the Comparable interface in the Page class and to annotate the list of pages in Publication with `@Sort(type = SortType.NATURAL)`?
      singulars
      1. POHibernate bidirectional OneToMany with JoinTable and polymorphic classes with table per concrete class
      1. USFelix Ebert
    2. COCause i need the getPages() collection be something ordered (one Page will be the first page of the publication, another one the second page and so on), and the DDL has been designed with a cross table. But I think I can agree with others to get rid of the cross table if we manage to keep the "sorted list" behavior. Comparable does not seem an option, cause it needs a Page.getPageNumber that must be "manually" handled in code, instead of relying on a List.
      singulars
      1. POHibernate bidirectional OneToMany with JoinTable and polymorphic classes with table per concrete class
      1. USSimone Gianni
    3. COi have tested your code with hibernate 4.1.7 and a call to publication.getPages() didn't result in an error - weird. Hibernate generated the following query: `Select pages0_.pub_id as pub1_8_2_, pages0_.pg_id as pg2_6_2_, pages0_.i as i2_, page1_.id as id5_0_, page1_1_.pub_id as pub1_6_0_, publicatio2_.id as id8_1_ from publication_pages pages0_ inner join page page1_ on pages0_.pg_id=page1_.id left outer join publication_pages page1_1_ on page1_.id=page1_1_.pg_id left outer join publication publicatio2_ on page1_1_.pub_id=publicatio2_.id where pages0_.pub_id=?`
      singulars
      1. POHibernate bidirectional OneToMany with JoinTable and polymorphic classes with table per concrete class
      1. USFelix Ebert
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload