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

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
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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