The lecturer of an arquillian training course I visited mentioned that it should be no problem to just define different datasources for seeding/cleaning and for the PersistenceContext of the EJBs. So I sat down to test this.
TL;DR: It's possible to just use two different datasources.
Here are my test setup and the results of my tests.
Local test setup
Database
As the database I installed an Oracle XE as we use Oracle databases in my company. As the database users of the components don't have their own schema but access the tables of the schema owner I created three database users:
- User "bish" is the schema owner of the schema "bish" which contains the empty table "Emp" I use in the test
- User "readinguser" which got "SELECT, INSERT, UPDATE" priviledges for the table "bish.Emp"
- User "writinguser" which got "SELECT, INSERT, UPDATE, DELETE" priviledges for the table "bish.Emp"
Application server
As an application server I used an Wildfly 10.x and definied two data sources, one for each of my two testusers
<datasource jndi-name="java:/ReadingDS" pool-name="ReadingDS" enabled="true">
<connection-url>jdbc:oracle:thin:@localhost:1521:xe</connection-url>
<driver>oracle</driver>
<pool>
<min-pool-size>1</min-pool-size>
<max-pool-size>5</max-pool-size>
<prefill>true</prefill>
</pool>
<security>
<user-name>readinguser</user-name>
<password>oracle</password>
</security>
</datasource>
<datasource jndi-name="java:/WritingDS" pool-name="WritingDS" enabled="true">
<connection-url>jdbc:oracle:thin:@localhost:1521:xe</connection-url>
<driver>oracle</driver>
<pool>
<min-pool-size>1</min-pool-size>
<max-pool-size>5</max-pool-size>
<prefill>true</prefill>
</pool>
<security>
<user-name>writingguser</user-name>
<password>oracle</password>
</security>
</datasource>
Test application
Then I wrote a small application with an entity, and EJB, persistence.xml, arquillian.xml, dataSet and test class
Entity (Only table definition with explicit schema naming shown)
@Entity
@Table(name = "Emp", schema = "bish")
public class Emp implements Serializable {
// Straight forward entity...
}
EJB with two methods for selecting and deleting all entries
@Stateless
@Remote(IEmpService.class)
@LocalBean
public class EmpService implements IEmpService {
@PersistenceContext
private EntityManager em;
public void removeAllEmps() {
em.createQuery("DELETE FROM Emp").executeUpdate();
}
public List<Emp> getAllEmps() {
return em.createQuery("FROM Emp", Emp.class).getResultList();
}
}
Persistence unit inside persistence.xml to use the "ReadingDS" inside the EJB
<persistence-unit name="ReadingUnit" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>java:/ReadingDS</jta-data-source>
<shared-cache-mode>NONE</shared-cache-mode>
</persistence-unit>
Arquillian.xml with definition of using the "WritingDS" to seed/clean the table and schema definition
<extension qualifier="persistence">
<property name="defaultDataSeedStrategy">CLEAN_INSERT</property>
<property name="defaultCleanupStrategy">USED_ROWS_ONLY</property>
<property name="defaultDataSource">java:/WritingDS</property>
</extension>
<extension qualifier="persistence-dbunit">
<property name="schema">bish</property>
</extension>
Dataset "empBefore.xml" used in test class
<?xml version="1.0" encoding="UTF-8"?>
<dataset>
<EMP EMPNO="9998" ENAME="TEst" JOB="Eins" HIREDATE="1982-01-23" SAL="1300" DEPTNO="10"/>
<EMP EMPNO="9999" ENAME="Test" JOB="Zwei" MGR="9998" HIREDATE="1982-01-23" SAL="1300" DEPTNO="10"/>
</dataset>
Test class:
@RunWith(Arquillian.class)
public class DataSourceTest {
@Deployment
public static JavaArchive createDeployment() {
// ...
}
@EJB
EmpService testclass;
@Rule
public ExpectedException thrown = ExpectedException.none();
@UsingDataSet("empBefore.xml")
@Test
public void GetAllEmps() {
List<Emp> allEmps = testclass.getAllEmps();
Assert.assertEquals(2, allEmps.size());
}
@UsingDataSet("empBefore.xml")
@Test
public void DeleteAllEmps() {
thrown.expect(EJBException.class);
thrown.expectCause(CoreMatchers.isA(PersistenceException.class));
testclass.removeAllEmps();
}
}
The test
I first executed the GetAllEmps test method to see if the table is correctly seeded with the data of the DataSet and if the select method of the EJB works. On my first execution I got the following exception. (Sorry for posting so much text but it's important, see below!)
19:15:51,553 WARN [com.arjuna.ats.arjuna] (default task-38) ARJUNA012140: Adding multiple last resources is disallowed. Trying to add LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@666ebccc[connectionListener=11852abe connectionManager=3f58cd97 warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a80002:d99c90f:59971e1c:4c, node_name=1, branch_uid=0:ffffc0a80002:d99c90f:59971e1c:50, subordinatenodename=null, eis_name=java:/ReadingDS > productName=Oracle productVersion=Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production jndiName=java:/ReadingDS])), but already have LastResourceRecord(XAOnePhaseResource(LocalXAResourceImpl@6027d87b[connectionListener=41a0034d connectionManager=329cdd5f warned=false currentXid=< formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a80002:d99c90f:59971e1c:4c, node_name=1, branch_uid=0:ffffc0a80002:d99c90f:59971e1c:4e, subordinatenodename=null, eis_name=java:/WritingDS > productName=Oracle productVersion=Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production jndiName=java:/WritingDS]))
19:15:51,554 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-38) SQL Error: 0, SQLState: null
19:15:51,554 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-38) javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@11852abe[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@7fc47256 connection handles=0 lastReturned=1503076551554 lastValidated=1503075869230 lastCheckedOut=1503076551553 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@6893c4c mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@61e62cb9[pool=ReadingDS] xaResource=LocalXAResourceImpl@666ebccc[connectionListener=11852abe connectionManager=3f58cd97 warned=false currentXid=null productName=Oracle productVersion=Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production jndiName=java:/ReadingDS] txSync=null]
19:15:51,554 ERROR [org.jboss.as.ejb3.invocation] (default task-38) WFLYEJB0034: EJB Invocation failed on component EmpService for method public java.util.List de.test.EmpService.getAllEmps(): javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
Thanks to this SO-Question I could fix the problem by setting the following systemproperty in my wildfly:
<system-properties>
<property name="com.arjuna.ats.arjuna.allowMultipleLastResources" value="true"/>
</system-properties>
The most important thing about this exception is the fact that wildfly tries to create two connections, one for each data source (see highlighted JNDI-names) in exception text. Before setting the system-property I verified that by removing the @UsingDataSet-Annotation. After removing the test case failed because the assertion (Assert.assertEquals(2, allEmps.size());) failed as there were zero rows in the table - which indicates there was no second connection created for seeding. So I created the system property, used the DataSet and got a green bar.
The second test method tries to delete all entries in the set, which must fail in an exception as the user behind the readingDS datasource has no rights to delete rows in the table. This test was also successfull. The full exception log was:
javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not execute statement
[...]
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute statement
[...]
... 187 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
[...]
... 217 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
[...]
... 226 more
As you can see the delete statement failes because of insufficient privileges
Conclusion
It's possible to use different data sources to seed/delete tables by defining the datasource in the arquillian.xml and inside an persistence-unit for an EJB. Arquillian and the application server can handle these different datasources correctly.
