1 2 Previous Next 20 Replies Latest reply on Mar 29, 2012 1:14 PM by alexh_97

    On dbunit integration

    nickarls

      So, I started tinkering with junit-dbunit integration for in-container runs and I thought I'd share some code and see if its useful for someone and talk about how to improve this. It's by no means feature complete, just working out the general architechture. The code is derived from http://ctpjava.blogspot.com/2010/07/test-drive-with-arquillian-and-cdi-part.html

       

      So let's start with the entity, nothing special here:

       

      @Entity

      public class Greeting

      {

         @Id @GeneratedValue

         private long id;

       

         private String data;

       

       

         public long getId()

         {

            return id;

         }

       

       

         public void setId(long id)

         {

            this.id = id;

         }

       

       

         public String getData()

         {

            return data;

         }

       

       

         public void setData(String data)

         {

            this.data = data;

         }

       

      }

       

       

      to go with it I have in src/test/resources a persistence.xml against the default datasource

       

       

      <persistence xmlns="http://java.sun.com/xml/ns/persistence"

                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

                xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

                version="2.0">

          <persistence-unit name="concept">

          <jta-data-source>java:/DefaultDS</jta-data-source>

                          <properties>

                                    <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />

                                    <property name="hibernate.hbm2ddl.auto" value="create-drop" />

                          </properties>

                </persistence-unit>

      </persistence>

       

       

      and in src/test/resources I also have Foo.xls with a single tab named "Greeting" with "Id" in A1, "Data" in B1, "1" in A2 and "Foo" in "B2"

       

      to mark test data I have an annotation @Data, which is used like @Data(xls = "Foo.xls", ds = "java:/DefaultDS"), you get the point. It looks like

       

       

      @Retention(RetentionPolicy.RUNTIME)

      @Target(ElementType.METHOD)

      public @interface Data

      {

         String xls();

         String ds();

      }

       

       

      and it's used by a junit @Rule which looks like

       

       

      public class DataRule extends TestWatchman implements MethodRule

      {

         private abstract class DataSeederOperation

         {

            protected ExcelDataSeeder dataSeeder;

       

       

            public DataSeederOperation(FrameworkMethod method)

            {

               Data data = method.getAnnotation(Data.class);

               if (null == data)

               {

                  return;

               }

               dataSeeder = createDataSeeder(data.xls(), data.ds());

            }

       

       

            public void execute()

            {

               try

               {

                  if (dataSeeder != null)

                  {

                     actions();

                  }

               }

               catch (Exception e)

               {

                  throw new RuntimeException(e);

               }

            }

       

       

            public abstract void actions();

         }

       

       

         public static boolean runningInAS()

         {

            Exception e = new Exception();

            StackTraceElement[] stack = e.getStackTrace();

            for (int i = stack.length - 1; i >= 0; i--)

            {

               if (stack[i].getClassName().contains("http11"))

               {

                  return true;

               }

            }

            return false;

         }

       

       

         @Override

         public void starting(FrameworkMethod method)

         {

            if (!runningInAS())

            {

               return;

            }

            new DataSeederOperation(method)

            {

               @Override

               public void actions()

               {

                  dataSeeder.prepare();

               }

            }.execute();

         }

       

       

         @Override

         public void finished(FrameworkMethod method)

         {

            if (!runningInAS())

            {

               return;

            }

            new DataSeederOperation(method)

            {

               @Override

               public void actions()

               {

                  dataSeeder.cleanup();

               }

            }.execute();

         }

       

       

         private ExcelDataSeeder createDataSeeder(String xlsName, String dsJndi)

         {

            DataSource dataSource = null;

            if (xlsName == null || "".equals(xlsName))

            {

               throw new RuntimeException("xlsName cannot be null");

            }

            if (dsJndi == null || "".equals(dsJndi))

            {

               throw new RuntimeException("dsJndi cannot be null");

            }

            try

            {

               dataSource = (DataSource) new InitialContext().lookup(dsJndi);

            }

            catch (NamingException e)

            {

               throw new RuntimeException(String.format("DataSource %s was not found", dsJndi));

            }

            InputStream xls = getClass().getClassLoader().getResourceAsStream(xlsName);

            if (xls == null)

            {

               throw new RuntimeException(String.format("Excel file %s was not found", xlsName));

            }

            try

            {

               return new ExcelDataSeeder(xls, dataSource.getConnection());

            }

            catch (Exception e)

            {

               throw new RuntimeException("ExcelSeeder could not be created", e);

            }

         }

       

       

      }

       

       

      which uses an ExcelDataSeeder to populate and clean out the database. There is also an unorthodox check to see if we're running on the server side or the client side, since Rules are run in both places. The ExcelDataSeeder looks like

       

       

      public class ExcelDataSeeder

      {

       

       

         private DatabaseConnection databaseConnection;

         private final InputStream xlsStream;

       

       

         public ExcelDataSeeder(InputStream xlsStream, Connection connection) throws DatabaseUnitException

         {

            databaseConnection = new DatabaseConnection(connection);

            this.xlsStream = xlsStream;

         }

       

       

         public void prepare()

         {

            try

            {

               setupDatabase();

               fillDatabase();

            }

            catch (Exception e)

            {

               throw new RuntimeException(e);

            }

       

       

         }

       

       

         public void cleanup()

         {

            try

            {

               setupDatabase();

               cleanDatabase();

            }

            catch (Exception e)

            {

               throw new RuntimeException(e);

            }

         }

       

       

         private void setupDatabase() throws IOException, SQLException, DatabaseUnitException

         {

            databaseConnection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new DefaultDataTypeFactory());

         }

       

       

         private void fillDatabase() throws IOException, SQLException, DatabaseUnitException

         {

            XlsDataSet ds = new XlsDataSet(xlsStream);

            DatabaseOperation.CLEAN_INSERT.execute(databaseConnection, ds);

         }

       

       

         private void cleanDatabase() throws DatabaseUnitException, SQLException

         {

            IDataSet dataSet = databaseConnection.createDataSet();

            DatabaseOperation.DELETE_ALL.execute(databaseConnection, dataSet);

         }

       

       

      }

       

       

      and the test itself looks like

       

       

      @RunWith(Arquillian.class)

      public class DBTest

      {

       

       

         @PersistenceContext

         EntityManager em;

       

       

         @Resource

         UserTransaction ut;

       

       

         @Rule

         public DataRule rule = new DataRule();

       

       

         @Deployment

         public static WebArchive createTestArchive()

         {

            return ShrinkWrap.create(WebArchive.class, "test.war").addLibrary(new File("C:/java/m2repo/org/apache/poi/poi/3.2-FINAL/poi-3.2-FINAL.jar")).addLibrary(new File("C:/java/m2repo/org/dbunit/dbunit/2.4.8/dbunit-2.4.8.jar")).addResource("Foo.xls", ArchivePaths.create("WEB-INF/classes", "Foo.xls")).addClasses(Data.class, DataRule.class, ExcelDataSeeder.class, Greeting.class).addManifestResource(EmptyAsset.INSTANCE, ArchivePaths.create("beans.xml")).addManifestResource("persistence.xml", ArchivePaths.create("persistence.xml"));

         }

       

       

         @Test

         @Data(xls = "Foo.xls", ds = "java:/DefaultDS")

         public void testPreloaded() throws NotSupportedException, SystemException

         {

            try

            {

               ut.begin();

               Greeting g = new Greeting();

               em.persist(g);

               Assert.assertEquals(2, em.createQuery("select g from Greeting g", Greeting.class).getResultList().size());

            }

            finally

            {

               ut.rollback();

            }

         }

       

       

      }

       

       

      This should enable you to use in-container tests that have be pre-populated with excel data.

       

      Now there are some clear points of improvement that could be done

       

      • Re-introduce interfaces from the original article to support multiple seeding formats
      • Package the db-support stuff in a jar of it's own
      • Beef up the @Data annotation to have a transactional attribute that does the UT-stuff (and a rollback attribute perhaps), pre-query, post-query etc)
      • Better integration so the precence of a @Data would pack the xls and the required libs into the test archive (pointers on how this could be done?)

       

      Other suggestions are welcome!

        • 1. On dbunit integration
          craiggreenhalgh

          How would I change this to run in a remote container?

           

          I'm having trouble loading the files to parse.

           

          I deploy them to with my war but then cannot load these as a resource stream?

           

          Any ideas?

           

          Thanks

           

          Craig

          • 2. On dbunit integration
            nickarls

            It should work just by doing "mvn test -Pjbossas-embedded-6"

             

            createTestArchive sticks the Foo.xls in the classpath root where it can be read as a resource

            • 3. On dbunit integration
              craiggreenhalgh

              Hi what would be the best way of making the following more generic:

               

              if (!runningInAS())

              {

                       return;

              }

               

              My tests work fine using a remote server.  Soon as run them using a manage server, runningInAS is always false?  An as a result my DB is never updated

               

              Im using Jboss6 Final, remote and managed

               

              Thanks

               

              Craig

              • 4. On dbunit integration
                nickarls

                You could try looking for java:/defaultDS (or whatever) in JNDI, it should fail on the client side.

                • 5. Re: On dbunit integration
                  luiz.filipe.abrahao

                  Hello,

                   

                  Is this supposed to work with Glassfish?

                   

                  if I don't change the runningInAS() it returns always false. if I replace 'http11' to 'http' or to 'sun.grizzly' I get:

                   

                   

                  java.lang.RuntimeException: java.lang.RuntimeException: org.dbunit.dataset.NoSuchTableException: Did not find table 'USER' in schema 'null'
                      at com.eukleia.elf.test.entity.DataRule$DataSeederOperation.execute(DataRule.java:36)
                      at com.eukleia.elf.test.entity.DataRule.starting(DataRule.java:70)
                      at org.junit.rules.TestWatchman$1.evaluate(TestWatchman.java:46)
                      at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
                      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
                      at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
                      at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
                      at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
                      at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
                      at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
                      at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
                      at org.jboss.arquillian.junit.Arquillian$2.evaluate(Arquillian.java:163)
                      at org.jboss.arquillian.junit.Arquillian$3$1.evaluate(Arquillian.java:186)
                      at org.jboss.arquillian.junit.Arquillian$MultiStatementExecutor.execute(Arquillian.java:297)
                      at org.jboss.arquillian.junit.Arquillian$3.evaluate(Arquillian.java:182)
                      at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
                      at org.jboss.arquillian.junit.Arquillian.run(Arquillian.java:127)
                      at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
                      at org.junit.runner.JUnitCore.run(JUnitCore.java:136)
                      at org.jboss.arquillian.junit.JUnitTestRunner.execute(JUnitTestRunner.java:69)
                      at org.jboss.arquillian.protocol.servlet_3.ServletTestRunner.doGet(ServletTestRunner.java:84)
                      at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
                      at javax.servlet.http.HttpServlet.service(HttpServlet.java:844)
                      at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1518)
                      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:277)
                      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:171)
                      at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:651)
                      at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:591)
                      at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:94)
                      at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:87)
                      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:158)
                      at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:321)
                      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:222)
                      at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:165)
                      at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:802)
                      at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:705)
                      at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:986)
                      at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:178)
                      at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:135)
                      at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:102)
                      at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:88)
                      at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:76)
                      at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:53)
                      at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:57)
                      at com.sun.grizzly.ContextTask.run(ContextTask.java:69)
                      at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:526)
                      at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:507)
                      at java.lang.Thread.run(Unknown Source)
                  Caused by: java.lang.RuntimeException: org.dbunit.dataset.NoSuchTableException: Did not find table 'USER' in schema 'null'
                      at com.eukleia.elf.test.entity.ExcelDataSeeder.prepare(ExcelDataSeeder.java:31)
                      at com.eukleia.elf.test.entity.DataRule$1.actions(DataRule.java:68)
                      at com.eukleia.elf.test.entity.DataRule$DataSeederOperation.execute(DataRule.java:31)
                      ... 47 more
                  Caused by: org.dbunit.dataset.NoSuchTableException: Did not find table 'USER' in schema 'null'
                      at org.dbunit.database.DatabaseTableMetaData.<init>(DatabaseTableMetaData.java:146)
                      at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:299)
                      at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
                      at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
                      at com.eukleia.elf.test.entity.ExcelDataSeeder.fillDatabase(ExcelDataSeeder.java:55)
                      at com.eukleia.elf.test.entity.ExcelDataSeeder.prepare(ExcelDataSeeder.java:28)
                      ... 49 more
                  
                  

                   

                  I am not quite sure what the runningInAS() does, I tried to read the documentation of the @rule, but it is stil not clear for me.

                   

                  Thank you!

                  • 6. On dbunit integration
                    craiggreenhalgh

                    It is trying to execute the SQL, the exception is

                     

                    java.lang.RuntimeException: java.lang.RuntimeException: org.dbunit.dataset.NoSuchTableException: Did not find table 'USER' in schema 'null'

                     


                    • 7. On dbunit integration
                      nickarls

                      What does your excel file look like? Sheet names correspond to tables, first row in sheet is column names.

                      • 8. Re: On dbunit integration
                        bmajsak

                        Quick update - now it's an offical extension! https://github.com/arquillian/arquillian-extension-persistence

                        • 9. Re: On dbunit integration
                          tcmartin24

                          I searched for this in both the JBoss main repository and the snapshot repository and didn't find it.  Is it just not out there yet, or is there some other way to go about using this in a maven-based project?

                          • 10. Re: On dbunit integration
                            bmajsak

                            Hi Terry,

                             

                            indeed it's not yet available in JBoss repository, however it should be as Alpha1 in the next few days. I will keep you updated.

                             

                            For the time being you can simply clone GitHub repository and give it a try. Many thanks for your interest!

                            • 11. Re: On dbunit integration
                              tcmartin24

                              Thanks Bartosz, I did download it and incorporate it into a project I'm working on.  However, I'm getting an exception when I try to run a test:
                              Data source not defined!

                               

                              I see it's coming from MetaDataProvider.isPersistenceFeatureEnabled().  How should the underlying PersistenceConfiguration.setDefaultDataSource() get set?  My test uses a data source configured via sun-resources.xml and bound to JNDI and I have a persistence.xml that creates an PersistenceUnit which gets injected into an EJB being tested.  The test runs fine without the persistence extension.  I assume I just need to somehow specify to the persistence extension framework, what my default datasource is?

                               

                              Thanks,

                              Terry

                              • 12. Re: On dbunit integration
                                bmajsak

                                Hi Terry,

                                 

                                indeed the documentation is still on my TODO list Sorry for that.

                                 

                                You have basically two options to define the data source:

                                1. Use @DataSource annotation on test or class level
                                2. Define the default one in arquillian.xml as it's done in the "integration tests" module - see example

                                 

                                As one of the next improvements I'm considering scanning persistence.xml to obtain "default" data source instead of forcing user to define it. But for the time being you can use one of the options mentioned above.

                                 

                                HTH

                                • 13. Re: On dbunit integration
                                  tcmartin24

                                  I have another question for you Bartosz.  I'm trying to inject a PersistenceContext into my test class, as you do in your test example, but I'm getting this error when I run the test:

                                   

                                  java.lang.IllegalArgumentException: ArquillianServletRunner not found. Could not determine ContextRoot from ProtocolMetadata, please contact DeployableContainer developer.

                                   

                                  I only get this when I try to inject the PersistenceContext into the test class, my test that just injects an EJB which performs persistence itself, runs just fine.

                                   

                                  At first, I thought it meant I just don't have some dependency that includes ArquillianServletRunner, but after searching my whole maven repository and the arquillian project download and not finding any file by that name, I guess that's not the problem.  I've tried to make my config files, especially arquillian.xml, just like your example, as well as my test class.

                                   

                                   

                                  Ideas?

                                   

                                  Thanks,

                                  Terry

                                  • 14. Re: On dbunit integration
                                    bmajsak

                                    If you could share your project settings (pom), stacktrace and test class through gist/pastebin etc that would help me identifying the problem

                                    Off the top of my head - have you included beans.xml in your deployment?

                                    1 2 Previous Next