StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHibernate noob fetch join problem
primarykey
Id
2931936
data
AcceptedAnswerId
2932830
AnswerCount
3
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2010-05-28T19:32:21.123
FavoriteCount
3
LastActivityDate
2013-05-17T17:04:56.740
LastEditDate
2010-05-28T23:18:08.307
LastEditorUserId
309534
OwnerUserId
309534
ParentId
0
PostTypeId
1
Score
9
ViewCount
10559
LastEditorDisplayName
text
Body
I have two classes, Test2 and Test3. Test2 has an attribute test3 that is an instance of Test3. In other words, I have a unidirectional OneToOne association, with test2 having a reference to test3. When I select Test2 from the db, I can see that a separate select is being made to get the details of the associated test3 class. This is the famous 1+N selects problem. To fix this to use a single select, I am trying to use the fetch=join annotation, which I understand to be @Fetch(FetchMode.JOIN) However, with fetch set to join, I still see separate selects. Here are the relevant portions of my setup.. hibernate.cfg.xml: <pre><code><property name="max_fetch_depth">2</property> </code></pre> Test2: <pre><code>public class Test2 { @OneToOne (cascade=CascadeType.ALL , fetch=FetchType.EAGER) @JoinColumn (name="test3_id") @Fetch(FetchMode.JOIN) public Test3 getTest3() { return test3; } </code></pre> NB I set the FetchType to EAGER out of desperation, even though it defaults to EAGER anyway for OneToOne mappings, but it made no difference. Thanks for any help! Edit: I've pretty much given up on trying to use FetchMode.JOIN - can anyone confirm that they have got it to work ie produce a left outer join? In the docs I see that "Usually, the mapping document is not used to customize fetching. Instead, we keep the default behavior, and override it for a particular transaction, using left join fetch in HQL" If I do a left join fetch instead: query = session.createQuery("from Test2 t2 left join fetch t2.test3"); then I do indeed get the results I want - ie a left outer join in the query. Edit number 2: Guys, thank you so much for your responses. Now I want to get to the bottom of this. I usually find that when I investigate something, I end up learning a lot more than I thought I would. One thing I've learned already - I was running on old builds of hibernate because I didn't realize that the maven repository was out of date. Now I'm hooked up to the jboss repository too, and I have the latest versions of hibernate and hibernate annotations - 3.5.1-Final in both cases. I've set up a small test case that simplifies it as much as I can - I'm still seeing the problem in 3.5.1-Final, tho' I'm 99% certain it's just something stupid I'm not setting up right, especially Ross, given that you got it to work (thanks for taking the time to try it by the way) So I have these classes (full text this time) Class A <pre><code>package com.play.hibernate2; import javax.persistence.CascadeType; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.OneToOne; import org.hibernate.annotations.Fetch; import org.hibernate.annotations.FetchMode; @Entity public class A { private Integer id; private B b; public A() { super(); } @Id @GeneratedValue public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } @OneToOne (cascade=CascadeType.ALL) @Fetch(FetchMode.JOIN) public B getB() { return b; } public void setB(B b) { this.b = b; } } </code></pre> Class B <pre><code>package com.play.hibernate2; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; @Entity public class B { private Integer id; public B() { super(); } @Id @GeneratedValue public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } } </code></pre> My whole hibernate.cfg.xml <pre><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>  <property name="connection.driver_class">com.mysql.jdbc.Driver</property>  <property name="connection.url">jdbc:mysql://localhost:3306/play</property> <property name="connection.username">play</property> <property name="connection.password">play</property>  <property name="connection.pool_size">1</property>  <property name="dialect">org.hibernate.dialect.MySQLDialect</property>  <property name="current_session_context_class">thread</property>  <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>  <property name="show_sql">true</property> <property name="generate_statistics">true</property>  <property name="format_sql">true</property> <property name="use_sql_comments">true</property>  <property name="max_fetch_depth">2</property>  </session-factory> </hibernate-configuration> </code></pre> The testing class <pre><code>package com.play.hibernate2; import java.util.List; import java.util.Map; import org.hibernate.FlushMode; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.AnnotationConfiguration; import org.hibernate.tool.hbm2ddl.SchemaExport; public class RunTests4 { private SessionFactory sessionFactory; public static void main(String[] args){ RunTests4 d = new RunTests4(); d.run3(); } public void run3(){ Session session = getSession(); session.beginTransaction(); createEntities(session); session.getTransaction().commit(); System.out.println("NOW WITH A NEW TRANSACTION"); session = getSession(); session.beginTransaction(); Query query = session.createQuery("from A"); List results = query.list(); for (int i=0; i<results.size(); i++){ System.out.println("Row "+i+" was:"); A a = (A)results.get(i); System.out.println("Result "+i); System.out.println(a.toString()); } session.getTransaction().commit(); } public void createEntities(Session session){ for (int i=0; i<2; i++){ A a = new A(); B b = new B(); a.setB(b); session.save(a); } } public Session getSession(){ if (sessionFactory == null){ AnnotationConfiguration config = new AnnotationConfiguration(); config.addAnnotatedClass(A.class); config.addAnnotatedClass(B.class); config.configure(); new SchemaExport(config).create(true,true); sessionFactory = config.buildSessionFactory(); } Session session = sessionFactory.getCurrentSession(); return session; } } </code></pre> And finally the log output showing the extra selects to get back the associated class <pre><code>2 [main] INFO org.hibernate.cfg.annotations.Version - Hibernate Annotations 3.5.1-Final 23 [main] INFO org.hibernate.cfg.Environment - Hibernate 3.5.1-Final 28 [main] INFO org.hibernate.cfg.Environment - hibernate.properties not found 32 [main] INFO org.hibernate.cfg.Environment - Bytecode provider name : javassist 37 [main] INFO org.hibernate.cfg.Environment - using JDK 1.4 java.sql.Timestamp handling 160 [main] INFO org.hibernate.annotations.common.Version - Hibernate Commons Annotations 3.2.0.Final 176 [main] INFO org.hibernate.cfg.Configuration - configuring from resource: /hibernate.cfg.xml 176 [main] INFO org.hibernate.cfg.Configuration - Configuration resource: /hibernate.cfg.xml 313 [main] INFO org.hibernate.cfg.Configuration - Configured SessionFactory: null 338 [main] INFO org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect 462 [main] INFO org.hibernate.cfg.AnnotationBinder - Binding entity from annotated class: com.play.hibernate2.Test2 545 [main] INFO org.hibernate.cfg.annotations.EntityBinder - Bind entity com.play.hibernate2.Test2 on table Test2 649 [main] INFO org.hibernate.cfg.AnnotationBinder - Binding entity from annotated class: com.play.hibernate2.Test3 650 [main] INFO org.hibernate.cfg.annotations.EntityBinder - Bind entity com.play.hibernate2.Test3 on table Test3 651 [main] INFO org.hibernate.cfg.AnnotationBinder - Binding entity from annotated class: com.play.hibernate2.A 651 [main] INFO org.hibernate.cfg.annotations.EntityBinder - Bind entity com.play.hibernate2.A on table A 653 [main] INFO org.hibernate.cfg.AnnotationBinder - Binding entity from annotated class: com.play.hibernate2.B 653 [main] INFO org.hibernate.cfg.annotations.EntityBinder - Bind entity com.play.hibernate2.B on table B 678 [main] INFO org.hibernate.cfg.AnnotationConfiguration - Hibernate Validator not found: ignoring 687 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - Running hbm2ddl schema export 688 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - exporting generated schema to database 691 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Using Hibernate built-in connection pool (not for production use!) 691 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Hibernate connection pool size: 1 698 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - autocommit mode: false 711 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/play 711 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - connection properties: {user=play, password=****} alter table A drop foreign key FK412E010759 alter table Test2 drop foreign key FK4CF5DC04B7E1B79 drop table if exists A drop table if exists B drop table if exists Test2 drop table if exists Test3 create table A ( id integer not null auto_increment, b_id integer, primary key (id) ) create table B ( id integer not null auto_increment, primary key (id) ) create table Test2 ( id integer not null auto_increment, name varchar(255), value integer not null, test3_id integer, primary key (id) ) create table Test3 ( id integer not null auto_increment, name varchar(255), value integer not null, primary key (id) ) alter table A add index FK412E010759 (b_id), add constraint FK412E010759 foreign key (b_id) references B (id) alter table Test2 add index FK4CF5DC04B7E1B79 (test3_id), add constraint FK4CF5DC04B7E1B79 foreign key (test3_id) references Test3 (id) 2562 [main] INFO org.hibernate.tool.hbm2ddl.SchemaExport - schema export complete 2564 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - cleaning up connection pool: jdbc:mysql://localhost:3306/play 2571 [main] INFO org.hibernate.cfg.search.HibernateSearchEventListenerRegister - Unable to find org.hibernate.search.event.FullTextIndexEventListener on the classpath. Hibernate Search is not enabled. 2575 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Using Hibernate built-in connection pool (not for production use!) 2575 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - Hibernate connection pool size: 1 2575 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - autocommit mode: false 2575 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost:3306/play 2575 [main] INFO org.hibernate.connection.DriverManagerConnectionProvider - connection properties: {user=play, password=****} 2622 [main] INFO org.hibernate.cfg.SettingsFactory - RDBMS: MySQL, version: 5.1.30 2622 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-5.1.9 ( Revision: ${svn.Revision} ) 2633 [main] INFO org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect 2635 [main] INFO org.hibernate.engine.jdbc.JdbcSupportLoader - Disabling contextual LOB creation as JDBC driver reported JDBC version [3] less than 4 2636 [main] INFO org.hibernate.transaction.TransactionFactoryFactory - Using default transaction strategy (direct JDBC transactions) 2638 [main] INFO org.hibernate.transaction.TransactionManagerLookupFactory - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended) 2638 [main] INFO org.hibernate.cfg.SettingsFactory - Automatic flush during beforeCompletion(): disabled 2638 [main] INFO org.hibernate.cfg.SettingsFactory - Automatic session close at end of transaction: disabled 2638 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC batch size: 15 2638 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC batch updates for versioned data: disabled 2638 [main] INFO org.hibernate.cfg.SettingsFactory - Scrollable result sets: enabled 2638 [main] INFO org.hibernate.cfg.SettingsFactory - JDBC3 getGeneratedKeys(): enabled 2638 [main] INFO org.hibernate.cfg.SettingsFactory - Connection release mode: auto 2639 [main] INFO org.hibernate.cfg.SettingsFactory - Maximum outer join fetch depth: 2 2639 [main] INFO org.hibernate.cfg.SettingsFactory - Default batch fetch size: 1 2639 [main] INFO org.hibernate.cfg.SettingsFactory - Generate SQL with comments: enabled 2639 [main] INFO org.hibernate.cfg.SettingsFactory - Order SQL updates by primary key: disabled 2639 [main] INFO org.hibernate.cfg.SettingsFactory - Order SQL inserts for batching: disabled 2639 [main] INFO org.hibernate.cfg.SettingsFactory - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory 2641 [main] INFO org.hibernate.hql.ast.ASTQueryTranslatorFactory - Using ASTQueryTranslatorFactory 2641 [main] INFO org.hibernate.cfg.SettingsFactory - Query language substitutions: {} 2641 [main] INFO org.hibernate.cfg.SettingsFactory - JPA-QL strict compliance: disabled 2641 [main] INFO org.hibernate.cfg.SettingsFactory - Second-level cache: enabled 2641 [main] INFO org.hibernate.cfg.SettingsFactory - Query cache: disabled 2644 [main] INFO org.hibernate.cfg.SettingsFactory - Cache region factory : org.hibernate.cache.impl.bridge.RegionFactoryCacheProviderBridge 2644 [main] INFO org.hibernate.cache.impl.bridge.RegionFactoryCacheProviderBridge - Cache provider: org.hibernate.cache.NoCacheProvider 2644 [main] INFO org.hibernate.cfg.SettingsFactory - Optimize cache for minimal puts: disabled 2644 [main] INFO org.hibernate.cfg.SettingsFactory - Structured second-level cache entries: disabled 2648 [main] INFO org.hibernate.cfg.SettingsFactory - Echoing all SQL to stdout 2648 [main] INFO org.hibernate.cfg.SettingsFactory - Statistics: enabled 2649 [main] INFO org.hibernate.cfg.SettingsFactory - Deleted entity synthetic identifier rollback: disabled 2649 [main] INFO org.hibernate.cfg.SettingsFactory - Default entity-mode: pojo 2649 [main] INFO org.hibernate.cfg.SettingsFactory - Named query checking : enabled 2649 [main] INFO org.hibernate.cfg.SettingsFactory - Check Nullability in Core (should be disabled when Bean Validation is on): enabled 2697 [main] INFO org.hibernate.impl.SessionFactoryImpl - building session factory 2796 [Finalizer] INFO org.hibernate.connection.DriverManagerConnectionProvider - cleaning up connection pool: jdbc:mysql://localhost:3306/play 2929 [main] INFO org.hibernate.impl.SessionFactoryObjectFactory - Not binding factory to JNDI, no JNDI name configured Hibernate: /* insert com.play.hibernate2.B */ insert into B values ( ) Hibernate: /* insert com.play.hibernate2.A */ insert into A (b_id) values (?) Hibernate: /* insert com.play.hibernate2.B */ insert into B values ( ) Hibernate: /* insert com.play.hibernate2.A */ insert into A (b_id) values (?) NOW WITH A NEW TRANSACTION Hibernate: /* from A */ select a0_.id as id2_, a0_.b_id as b2_2_ from A a0_ Hibernate: /* load com.play.hibernate2.B */ select b0_.id as id3_0_ from B b0_ where b0_.id=? Hibernate: /* load com.play.hibernate2.B */ select b0_.id as id3_0_ from B b0_ where b0_.id=? Row 0 was: Result 0 com.play.hibernate2.A@351daa0e Row 1 was: Result 1 com.play.hibernate2.A@2e879860 </code></pre> Edit Number 3: If I do things Ross' way, with a load, a left outer join gets created. If I do it with a list, separate selects are issued. Here's the relevant code. Only changing this reproduces the difference in behavior: <pre><code> /* generates the left outer join A a = (A)session.load(A.class,1); System.out.println(a.getId()+" = "+a.getB().getName()); */ // Creates separate selects for each object b associated with each a Query query = session.createQuery("from A"); List results = query.list(); A a = (A)results.get(0); System.out.println(a.getId()+" = "+a.getB().getName()); </code></pre> I guess it might be called a 'bug'. As I mentioned earlier, in the docs, they say it's 'usual' to specify the fetch mode in the HQL rather than in the mapping, which I'm thinking might mean that the HQL way has had more foot traffic to bed it down..? (by the way I added an extra 'name' field to A and B otherwise hibernate optimizes the retrieve because it can get all of B just from the foreign key on A)
Tags
<java><hibernate>
Title
Hibernate noob fetch join problem
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USJeremy
UserOwnerUserId
1. USJeremy
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
2. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
3. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POHibernate noob fetch join problem
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POHibernate noob fetch join problem
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POHibernate noob fetch join problem
 UserUserId
 UScyberyoung
 VoteTypeVoteTypeId
 VTFavorite
CommentsPostId

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.