Tests with DBunit and Oracle 10g autogenerated primary key identifiers not synchronized (JPA/Hibernate)

I'm testing a JPA/Hibernate application with DBunit and Oracle 10g. When I start my test I load to the database 25 rows with an identifier.

That's the xml where I have my data, that I insert with DBUnit

  <entity entityId="1" .... 
  <entity entityId="2" ....
  <entity entityId="3" ....
  <entity entityId="4" .... 

That's my entity class with JPA annotations (not hibernate specific)

@Entity
@Table(name = "entity")
public class Entity{
@Id
@GeneratedValue(strategy=GenerationType.Auto)
private Integer entityId;
...}

Those are the parameter values of the database connection with Oracle10g

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.208.131:1521:database
jdbc.username=hr
jdbc.password=root
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
dbunit.dataTypeFactoryName=org.dbunit.ext.oracle.Oracle10DataTypeFactory

After insert this data in Oracle I run a test where I make Entity entity = new Entity() (I don't have to s开发者_StackOverflow中文版et manually the identifier because it's autogenerated)

@Test
public void testInsert(){

   Entity entity = new Entity();

   //other stuff

   entityTransaction.begin();
   database.insertEntity(entity);//DAO call
   entityTransaction.commit();

}

and when the test makes the commit of the transaction I get the following error

javax.persistence.RollbackException: Error while commiting the transaction
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:71)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    ...
Caused by: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
    at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:54)
    ... 26 more
Caused by: java.sql.BatchUpdateException: ORA-00001: restricción única (HR.SYS_C0058306) violada

    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:345)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10844)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
    ... 34 more 

I have debugged it and the problem is that the entityId of the new object is 1, and already exists a entity with that Id. So, I don't know who is the responsable DBunit ? Oracle ? Why are not synchronized the identifiers of Oracle database and the identifier that JPA/hibernate gives to my entity in my testing code?

Thanks for your time

I think the AUTO generation type, in Oracle, is in fact a sequence generator. If you don't specify which sequence it must use, Hibernate is probably creating one for you and using it, and its default start value is 1.

Using AUTO is useful for quick prototyping. For a real application, use a concrete generation type (SEQUENCE, for Oracle), and create your sequences yourself with an appropriate start value to avoid duplicate keys.

You could use ids < 0 in your test data sets. Not only will your sequences never come in conflict with test records, but also you'll easily distinguish records that were inserted by the tests.

The AUTO sequencing strategy usually defaults to the TABLE sequencing strategy, but in the case of Oracle, the sequencing strategy uses an Oracle sequence named hibernate_sequence (which is the default, unless you specify a sequence name in the strategy). The starting value of the sequence happens to be 1, which conflicts with the existing entity that is loaded using DbUnit, hence resulting in the ConstraintViolationException exception being thrown.

For the purpose of unit tests, you could perform either of the following:

• Issue a ALTER SEQUENCE... command to set the next value of the sequence, after loading data into the database. This will ensure that the JPA provider will use a sequence value that does not conflict with the existing Ids of the entities populated from your current XML file, by DbUnit.
• Specify the name of the sequence in XML file loaded as the IDataSet eventually used by DbUnit. The actual sequence values will have to be replaced in the IDataSet using a SELECT <sequence_name>.nextval FROM DUAL. The following section is reproduced as is and is credited to this site:

I spend a couple of hours reading the dbUnit docs/facs/wikis and source code trying to figure out how to use Oracle sequences, but unless I overlooked something, I think this is not possible with the current implementation.

So I took some extra time to find a workaround to insert Oracle sequence generated IDs into dbUnit's datasets, muchlike what ReplacementDataSet does. I subclassed DatabaseTestCase already earlier in a abstract class (AbstractDatabaseTestCase) to be able to use a common connection in case of insertion of my testcases in a testsuite. But I added the following code just now. It looks up the first row of each table in the dataset to determine which columns need sequence replacement. The replacement is done on the "${…}" expression value.

This code is "quick and dirty" and surely needs some cleanup and tuning.

Anyways, this is just a first try. I'll post further improvements as I go, if this can be of any help to anyone.

Stephane Vandenbussche

private void replaceSequence(IDataSet ds) throws Exception {
    ITableIterator iter = ds.iterator();
    // iterate all tables
    while (iter.next()) {
      ITable table = iter.getTable();
      Column[] cols = table.getTableMetaData().getColumns();
      ArrayList al = new ArrayList(cols.length);
      // filter columns containing expression "${...}"
      for (int i = 0; i < cols.length; i++) {
        Object o = table.getValue(0, cols[i].getColumnName());
        if (o != null) {
          String val = o.toString();
          if ((val.indexOf("${") == 0) && (val.indexOf("}") == val.length() - 1)) {
            // associate column name and sequence name
            al.add(new String[]{cols[i].getColumnName(), val.substring(2, val.length()-1)});
          }
        }
      }
      cols = null;
      int maxi = table.getRowCount();
      int maxj = al.size();
      if ((maxi > 0) && (maxj > 0)) {
        // replace each value "${xxxxx}" by the next sequence value
        // for each row
        for (int i = 0; i < maxi; i++) {
          // for each selected column
          for (int j = 0; j < maxj; j++) {
            String[] field = (String[])al.get(j);
            Integer nextVal = getSequenceNextVal(field[1]);
            ((DefaultTable) table).setValue(i, field[0], nextVal);
          }
        }
      }
    }
  }

  private Integer getSequenceNextVal(String sequenceName) throws SQLException, Exception {
      Statement st = this.getConnection().getConnection().createStatement();
      ResultSet rs = st.executeQuery("SELECT " + sequenceName + ".nextval FROM dual");
      rs.next();
      st = null;
      return new Integer(rs.getInt(1));
  }

My AbstractDatabaseTestCase class has a boolean flag "useOracleSequence" which tells the getDataSet callback method to call replaceSequence.

I can now write my xml dataset as follows :

  <dataset>
   <MYTABLE FOO="Hello" ID="${MYTABLE_SEQ}"/>
   <MYTABLE FOO="World" ID="${MYTABLE_SEQ}"/>
   <OTHERTABLE BAR="Hello" ID="${OTHERTABLE_SEQ}"/>
   <OTHERTABLE BAR="World" ID="${OTHERTABLE_SEQ}"/>
  </dataset>

where MYTABLE_SEQ is the name of Oracle sequence to be used.