title: Spring Hibernate integration test returns unexpected result
body: <p>I am using Maven, Hibernate and Spring in my application. I have implemented entity classes, DAO classes and service classes in packages of their own. I have problem when testing a service. When unit testing a DAO method that this specific service is calling the result is expected. But when testing a service method that is using this DAO method I don't get the same result. I think this problem is related to integration. Service is annoted with @Transactional. Data source and service are annotated with @Autowired in testing class of service. I attached context configuration of the service testing class.</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="
      http://www.springframework.org/schema/beans 
      http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
      http://www.springframework.org/schema/jee 
      http://www.springframework.org/schema/jee/spring-jee-2.0.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-2.5.xsd
      http://www.springframework.org/schema/tx
      http://www.springframework.org/schema/tx/spring-tx-2.0.xsd"&gt;
  
      &lt;bean id="class1Dao" 
          class="org.project.datalayer.implementation.Class1DaoImplementation"&gt;
      &lt;/bean&gt;
  
      &lt;bean id="service" 
          class="org.project.services.implementation.Service1Implementation"&gt;
          &lt;property name="class1Dao"&gt;
              &lt;ref bean="class1Dao" /&gt;
          &lt;/property&gt;
      &lt;/bean&gt;    
  
      &lt;bean id="dataSource"
          class="org.apache.commons.dbcp.BasicDataSource"&gt;
          &lt;property name="driverClassName"
            value="org.hsqldb.jdbcDriver" /&gt;
          &lt;property name="url" value="jdbc:hsqldb:mem:project" /&gt;
          &lt;property name="username" value="username" /&gt;
          &lt;property name="password" value="password" /&gt;
      &lt;/bean&gt;
  
    &lt;bean id="entityManagerFactory"
      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"&gt;
      &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;property name="jpaVendorAdapter"&gt;
        &lt;bean
          class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" /&gt;
      &lt;/property&gt;
    &lt;/bean&gt;
  
    &lt;context:annotation-config /&gt;
  
    &lt;bean id="transactionManager"
      class="org.springframework.orm.jpa.JpaTransactionManager"&gt;
      &lt;property name="entityManagerFactory"
        ref="entityManagerFactory" /&gt;
    &lt;/bean&gt;
  
    &lt;tx:annotation-driven /&gt;
  
  &lt;/beans&gt;
  </code></pre>
  
  <p>Sample code as requested:</p>
  
  <p><strong>Entities</strong></p>
  
  <pre><code>@MappedSuperclass
  public abstract class ClassBase implements Serializable {
        @Id()
        @GeneratedValue(strategy = GenerationType.AUTO)
        @Column(name = "id")
        private Integer id;
  
        @Version
        private int version =  0;
  
        private Date created;
  
        private Date updated;
  
        /**
         * @return the id
         */
        public Integer getId() {
              return id;
        }
  
        @PrePersist
        @Column(name = "created", nullable = false)
        protected void onCreate() {
              this.created = new Date();
        }
  
        @PreUpdate
        @Column(name = "updated", nullable = false)
        protected void onUpdate() {
              this.updated = new Date();
        }
  
        /**
         * @return the version
         */
        public int getVersion() {
              return this.version;
        }
  
        /**
         * @param version the version to set
         */
        public void setVersion(int version) {
              this.version = version;
        }
  
        /**
         * @return the created
         */
        public Date getCreated() {
              return this.created;
        }
  
        /**
         * @return the updated
         */
        public Date getUpdated() {
              return this.updated;
        }
  }
  
  @Entity
  @Table(name = "class1")
  class Class1 extends ClassBase {
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "class1_id")
    private List&lt;Class2&gt; class2List = new ArrayList&lt;Class2&gt;();
  
    @OneToMany(mappedBy = "class1", cascade = CascadeType.ALL)
    private List&lt;Class1Class4&gt; class1Class4List = new ArrayList&lt;Class1Class4&gt;();
  
  }
  
  @Entity
  @Table(name = "class1_class4")
  class Class1Class4 extends ClassBase {
    @ManyToOne(optional = false, cascade = CascadeType.ALL)
    @JoinColumn(name="class4_id")
    private Class4 class4;
  
    @ManyToOne(optional = false, cascade = CascadeType.ALL)
    @JoinColumn(name="class1_id")
    private Class1 class1;
  
  }
  
  @Entity
  @Table(name = "class2")
  class Class2 extends ClassBase {
    @OneToMany(mappedBy = "class1", cascade = CascadeType.ALL)
    private Class1 class1;
  
    @OneToMany(mappedBy = "class2", cascade = CascadeType.ALL)
    private List&lt;Class2Class3&gt; class2Class3List = new ArrayList&lt;Class2Class3&gt;();
  }
  
  @Entity
  @Table(name = "class2_class3")
  class Class2Class3 extends ClassBase {
    @ManyToOne(cascade = CascadeType.ALL)
      @JoinColumn(name = "class2_id")
    private Class2 class2;
  
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "class3_id")
    private Class3 class3;    
  }
  
  @Entity
  @Table(name = "class3")
  class Class3 extends ClassBase {
    @OneToMany(mappedBy = "class3", cascade = CascadeType.ALL)
    private List&lt;Class2Class3&gt; class2Class3List = new ArrayList&lt;Class2Class3&gt;();
  
    @OneToOne(cascade = CascadeType.ALL)
        @JoinColumns({
          @JoinColumn(name = "class4_id",
              referencedColumnName = "id")
        })
    private Class4 class4;
  
    @ManyToOne(cascade = CascadeType.ALL)
    @ForeignKey(name = "fk_class3_to_parent", inverseName = "fk_parent_to_class3")
    private Class3 parent;
  
    @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
    private Set&lt;Class3&gt; descendantList = new HashSet&lt;Class3&gt;();
  
    ...
  }
  
  @Entity
  @Table(name = "class4")
  class Class4 extends ClassBase {
    @OneToOne(mappedBy="class4")
    private Class3 class3;
  
    @OneToMany(mappedBy = "class4", cascade = CascadeType.ALL)
    private List&lt;Class1Class4&gt; class1Class4List = new ArrayList&lt;Class1Class4&gt;();
  }
  </code></pre>
  
  <p><strong>DAO</strong></p>
  
  <pre><code>public class Class1DaoImplementation extends BaseDaoTemplate&lt;Class1&gt; implements Class1Dao {
      public Class1DaoImplementation() {
          super(Class1.class);
      }       
  
      public Class getPublicByPrimaryKey(Integer id) {
          EntityManager em = getEntityManager();
                  Query q = 
                      em.createQuery("SELECT distinct(p) FROM Class1 p"
                          + " INNER JOIN p.class5 ps"
                          + " INNER JOIN FETCH p.class2 it"
                          + " INNER JOIN FETCH it.class2Class3List itfs"                      
                          + " INNER JOIN FETCH itfs.class3 f"                 
                          + " LEFT JOIN FETCH f.parent fp" // PROBLEM: null when running integration test
                          + " LEFT JOIN FETCH f.descendants fd"
                          + " WHERE p.id = :class1Id"
                              + " AND ps.id = 5"
                          + " ORDER BY p.id"
                          );
                  q.setParameter("class1Id", id);
  
                  return (Class1) q.getSingleResult();
        }
  }
  </code></pre>
  
  <p>Could the problem be related to the complexity of the query of the method getPublicByPrimaryKey? But if the method is working fine during unit tests why it is working differently while running integration tests?</p>
  
  <p>Test data:</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version='1.0' encoding='UTF-8'?&gt;
  &lt;dataset&gt;
      &lt;!--ELEMENT CLASS3 EMPTY--&gt;  
      &lt;!--ATTLIST CLASS3  
          PARAM1 CDATA #REQUIRED
          ID CDATA #REQUIRED  
          PARAM2 CDATA #REQUIRED  
          PARENT_ID CDATA #IMPLIED  
      --&gt;  
  ]&gt; 
  &lt;class3 param1="0" id="1" param2="5" /&gt;
  &lt;class3 param1="0" id="2" param2="5" /&gt;
  &lt;class3 param1="0" id="3" param2="5" parent_id="1" /&gt;
  &lt;class3 param1="0" id="4" param2="5" parent_id="1" /&gt;
  &lt;class3 param1="0" id="5" param2="5" parent_id="2" /&gt;
  </code></pre>
  
  <p></p>
  
  <p>Loading of test data:
      Connection connection =
          DataSourceUtils.getConnection(dataSource);</p>
  
  <pre><code>ArrayList&lt;String&gt; dbunitFilePaths = new ArrayList&lt;String&gt;();
  dbunitFilePaths.add("/class3.xml");
  
  ListIterator&lt;String&gt; dbunitFilePathsIterator = dbunitFilePaths.listIterator();
  
  try {
  
      IDatabaseConnection dbUnitConnection =
          new DatabaseConnection(connection); 
  
      String dbunitFilePath;
      ClassPathResource xml;
      IDataSet[] dataSets = new IDataSet[dbunitFilePaths.size()];
  
      for(int i = 0; i &lt; dbunitFilePaths.size(); i++) {
          dbunitFilePath = dbunitFilePathsIterator.next();
  
          xml = new ClassPathResource(dbunitFilePath);
  
          dataSets[i] = new FlatXmlDataSetBuilder().build(xml.getInputStream());
  
      }
  
      CompositeDataSet compositeDataSet = new CompositeDataSet(dataSets);
  
      DatabaseOperation.CLEAN_INSERT.execute(dbUnitConnection,
          compositeDataSet);
  
  } finally {
      DataSourceUtils.releaseConnection(connection, dataSource);
  }
  </code></pre>
  
  <p>If I edit the file loading method like this...</p>
  
  <pre><code>    ...
  
      ListIterator&lt;String&gt; dbunitFilePathsIterator = dbunitFilePaths.listIterator();
      FlatXmlDataSetBuilder flatXmlDataSetBuilder;
  
      try {
  
      ...
  
      xml = new ClassPathResource(dbunitFilePath);
  
      flatXmlDataSetBuilder = new FlatXmlDataSetBuilder().setColumnSensing(true);
  
      dataSets[i] = flatXmlDataSetBuilder.build(xml.getInputStream());
  
      }
  
      ...
  </code></pre>
  
  <p>...I then get the following message for the all BUT the first test method:</p>
  
  <pre><code>    java.sql.SQLException: Integrity constraint violation FK_CLASS3_TO_PARENT table: CLASS3
  </code></pre>