Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBC for unit tests? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBC for unit tests?

    Hi All,

    Am using Spring Jdbc as the underlying for writing a small Jdbc unit testing framework. I feel Spring Jdbc is the most powerful of the Jdbc frameworks that are there. So, in the framework, all testcases extend a JdbcTestCase that gives access to the JdbcTemplate which is used to setup data for the tests. Also, the Assert's make use of the JdbcTemplate for assertion, most of which is row based assertion. Though, it ties the framework close to Spring Jdbc, its quite less work to do on the assertions and gives all benefits of Spring Jdbc to setup data for each testcase.

    Just wondering wether this is the right thing to do? And alternatives? Any thoughts/comments?

    Thanks,

    Amit

  • #2
    Sounds reasonable. Have you looked at the org.springframework.test package, in spring-mock.jar? This includes a test superclass that defines a JdbcTemplate, and also creates and rolls back a transaction to avoid the need for setup and teardown scripts outside the JUnit test.

    Comment


    • #3
      Amazing. Didn't know about this. Read and tried it a bit. Thanks.

      Got working something(scrappily). Each testcase uses the template from AbstractTransactionalDataSourceSpringContextTests to: set up data, run the Dao(passing the template to it), do the asserts and later rolling back the setup data along with the actual Dao updates .

      So, will start using the spring-mock.jar. Only the jar name looks a bit confusing, cos its setting a real datasource.

      Comment


      • #4
        Originally posted by amkush
        Amazing. Didn't know about this. Read and tried it a bit. Thanks.

        Got working something(scrappily). Each testcase uses the template from AbstractTransactionalDataSourceSpringContextTests to: set up data, run the Dao(passing the template to it), do the asserts and later rolling back the setup data along with the actual Dao updates .

        So, will start using the spring-mock.jar. Only the jar name looks a bit confusing, cos its setting a real datasource.
        Could you show an example? Javadoc has little information

        Comment


        • #5
          Sure, Just give me sometime, its a bit scrappy at the moment.

          Comment


          • #6
            Managed to do work on this.

            It works well. You need to take care of using the JdbcTemplate from AbstractTransactionalDataSourceSpringContextTests
            and pass it around to the dao and data creator classes within the same transaction.

            So, the code looks like this,

            SpringJdbcTestCase extends the AbstractTransactionalDataSourceSpringContextTests and provides the root application context
            for application test cases. It also, incorrectly (suggestions welcome), sets the template of a utility JdbcAssert within the same transaction. The JdbcAssert class has a list of utility methods for expected row(s) assertion against the actual sql.

            Code:
            import org.springframework.test.AbstractTransactionalDataSourceSpringContextTests;
            
            public abstract class SpringJdbcTestCase extends AbstractTransactionalDataSourceSpringContextTests {
            
              protected String[] getConfigLocations() {
                return new String[] { "applicationContext.xml" };
              }
            
              protected void onSetUpInTransaction() throws Exception {
                super.onSetUpInTransaction();
                setTemplatesInTransaction();
              }
            
              protected void setTemplatesInTransaction() {
                JdbcAssert.template = this.jdbcTemplate; 
              }
            }
            For each application testcase, the dao to be tested and the data creator helper classes use the JdbcTemplate(from the superclass) and are setup in the same transaction. The test cases, setup the data using the data creator helpers(intend to use it like the ObjectMother pattern), run the dao to be tested, assert using JdbcAssert and later roll back the setup and actual data after the testcase completes. Sorry, for the emp example.

            Code:
            import java.util.ArrayList;
            import java.util.List;
            
            import net.sf.jdbcunit.JdbcAssert;
            import net.sf.jdbcunit.Row;
            
            public class SimpleJdbcDaoTest extends SpringJdbcTestCase {
                
              SimpleJdbcDao dao;    
              EmpTable empTable;
                
              protected void onSetUpInTransaction() throws Exception {
                super.onSetUpInTransaction();
                dao = new SimpleJdbcDao(jdbcTemplate);
                empTable = new EmpTable(jdbcTemplate);
              }
                
              protected void onTearDownInTransaction() {
                dao = null;
                empTable = null;
              }
            
              public void testInsert() {
                dao.insert();
                    
                Row expectedRow = new Row(new String[] { "1", "FLASH", "CHIEF", null, "12/01/2005", "1000", 
                                                         "0", "10" });
                JdbcAssert.assertRowEquals("SELECT * FROM EMP WHERE EMPNO = 1", expectedRow);    
              }
            
                public void testInserts() {        
                    // dao.insert();
                    // If we inserted a list of already present records.
                    
                    List rows = new ArrayList();
                    rows.add(new Row( new String[] {"7369","SMITH", "CLERK", "7902", "17/12/1980","800",null,"20"}));
                    rows.add(new Row( new String[] {"7499","ALLEN", "SALESMAN", "7698", "20/02/1981","1600","300","30"}));
                    rows.add(new Row( new String[] {"7521","WARD", "SALESMAN", "7698", "22/02/1981","1250","500", "30"}));
                    rows.add(new Row( new String[] {"7566","JONES", "MANAGER", "7839", "02/04/1981","2975",null,"20"}));
                    rows.add(new Row( new String[] {"7654","MARTIN", "SALESMAN", "7698" , "28/09/1981","1250","1400", "30"}));
                    rows.add(new Row( new String[] {"7698","BLAKE", "MANAGER", "7839", "01/05/1981","2850", null,"30"}));
                    rows.add(new Row( new String[] {"7782","CLARK", "MANAGER", "7839", "09/06/1981","2450",null,"10"}));
                    rows.add(new Row( new String[] {"7788","SCOTT", "ANALYST", "7566", "19/04/1987","3000",null,"20"}));
                    rows.add(new Row( new String[] {"7839","KING", "PRESIDENT", null, "17/11/1981","5000",null,"10"}));
                    rows.add(new Row( new String[] {"7844","TURNER", "SALESMAN", "7698", "08/09/1981","1500","0","30"}));
                    rows.add(new Row( new String[] {"7876","ADAMS", "CLERK", "7788", "23/05/1987","1100",null,"20"}));
                    rows.add(new Row( new String[] {"7900","JAMES", "CLERK", "7698", "03/12/1981","950" ,null,"30"}));
                    rows.add(new Row( new String[] {"7902","FORD", "ANALYST", "7566", "03/12/1981","3000",null,"20"}));
                    rows.add(new Row( new String[] {"7934","MILLER", "CLERK", "7782", "23/01/1982","1300",null,"10"}));
            
                    JdbcAssert.assertRowEqualsByIndex("SELECT * FROM EMP ORDER BY EMPNO", rows);        
                }
            
              public void testUpdate() {        
                empTable.createRecord();
                
                dao.update();
                // Change to Value?
                Row expectedRow = new Row(new String[] { "1", "MANAGER" });
                JdbcAssert.assertRowEquals("SELECT EMPNO, JOB FROM EMP WHERE ENAME ='FLASH'", expectedRow);        
              }
                
              public void testDelete() {        
                empTable.createRecord();
                
                dao.delete();
            
                JdbcAssert.assertRowIsNull("SELECT * FROM EMP WHERE ENAME = 'FLASH'");
              }
                   
            }
            The dao and helper classes are as follows:

            Code:
            import java.util.Calendar;
            
            import org.springframework.jdbc.core.JdbcTemplate;
            
            public class SimpleJdbcDao {
                
              private JdbcTemplate template;
                
              /**
               * @return Returns the template.
               */
              public JdbcTemplate getTemplate() {
                return template;
              }
            
              /**
               * @param template The template to set.
               */
              public void setTemplate(JdbcTemplate template) {
                this.template = template;
              }
            
              public SimpleJdbcDao(JdbcTemplate template) {
                // template = JdbcUnit.getJdbcTemplate();
                this.template = template;
              }
            
              public void insert() {
                            
                Calendar c = Calendar.getInstance();
                c.set(2004, 12, 12);
                System.out.println("Template: "+template);
                System.out.println("Rows updated: "+template.update(new StringBuffer().append("INSERT INTO ")
                                                      .append("   EMP ( EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO )")
                                                      .append("VALUES ( ?, ?, ?, ?, ?, ?, ? )").toString(), 
                                    new Object[] {new Integer(1), "FLASH", "CHIEF", c.getTime(), new Integer(1000), new Integer(0), new Integer(10)})); 
              }
                
              public void delete() {
                template.update("DELETE FROM EMP WHERE EMPNO = ? ", new Object[] {new Integer(1)});
              }
                
              public void update() {
                template.update("UPDATE EMP SET JOB = ? WHERE ENAME = ?", new Object[] {"MANAGER", "FLASH"});  
              }
            }
            Code:
            import java.util.GregorianCalendar;
            
            import org.springframework.jdbc.core.JdbcTemplate;
            
            public class EmpTable extends TableDao {
            
              public EmpTable(JdbcTemplate jdbcTemplate) {
                super(jdbcTemplate);
              }
                
               public void createRecord() {
                 createRow(new StringBuffer().append("INSERT INTO ")
                                             .append("   EMP ( EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO )")
                                             .append("VALUES ( ?, ?, ?, ?, ?, ?, ? )").toString(), 
                           new Object[] {new Integer(1), "FLASH", "MANAGER", new GregorianCalendar().getTime(), new Integer(1000), new Integer(0), new Integer(10)});
               }
            
               public void deleteRecord() {
                 deleteRow("DELETE FROM EMP WHERE EMPNO = ?", new Object[] { new Integer(1)});
               }
            }
            At the moment, its simple and uses about 10 classes for simplifying testing with the DB.

            Any thoughts? Better ways of doing it?

            Suggestions/Comments welcome.

            Comment


            • #7
              It took me a few tries but I ended up with a solution that seems OK. I'm open to suggestions/improvements tho.

              First I have a base test classs
              Code:
              package somepackage;
              
              import javax.sql.DataSource;
              
              import org.springframework.test.AbstractTransactionalSpringContextTests;
              
              /**
               * Basic test of the jdbc code for Project.
               */
              public abstract class TestBase extends AbstractTransactionalSpringContextTests
              {
                 private DataSource ds = null;
              
                 /**
                  * Constructor for DBProjectDAOTest.
                  * 
                  */
                 public TestBase()
                 {
                    super();
                 }
              
                 /**
                  * @see org.springframework.test.AbstractDependencyInjectionSpringContextTests#getConfigLocations()
                  */
                 protected String[] getConfigLocations()
                 {
                    return new String[] { "somepackage/testContext.xml" };
                 }
              
                 /**
                  * @see junit.framework.TestCase#setUp()
                  */
                 protected void onSetUpInTransaction() throws Exception
                 {
                    super.onSetUpInTransaction();
                    ds = (DataSource) getContext(contextKey()).getBean("dataSource");
                    SQLUtil.setDataSource(ds);
                    internalSetup();
                 }
              
                 abstract protected void internalSetup() throws Exception;
              
                 /**
                  * @return Returns the ds.
                  */
                 protected final DataSource getDataSource()
                 {
                    return ds;
                 }
              
              }
              I then wrote a small config (testConfig.xml) file and placed it in the same directory
              Code:
              <?xml version="1.0" encoding="UTF-8"?>
              <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http&#58;//www.springframework.org/dtd/spring-beans.dtd">
              
              
              <beans>
              
                <bean id="dataSource" class="org.springframework.jdbc.datasource.SingleConnectionDataSource">
                  <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
                  <property name="url"><value>jdbc&#58;someurl/value></property>
                  <property name="username"><value>user</value></property>
                  <property name="password"><value>password</value></property>
                </bean>
                
                <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                  <property name="dataSource"><ref local="dataSource"/></property>
                </bean>
                     
              </beans>
              I then extend the TestBase and implement the internalSetup method to do my setup.

              Something like
              Code:
              package somepackage;
              
              
              /**
               */
              public class DBOrganizationDAOTest extends TestBase
              &#123;
              
                 private OrganizationDAO ta = null;
              
                 public void testNormal&#40;&#41;
                 &#123;
              // test stuff goes here
                 &#125;
              ...
                 protected void internalSetup&#40;&#41; throws Exception
                 &#123;
                    ta = new DBOrganizationDAO&#40;getDataSource&#40;&#41;&#41;;
                 &#125;
              &#125;
              Pretty clean but is there a better / simpler way?

              Comment


              • #8
                Is there a way to mock out the db?

                Is it worthwhile to mock out the database when performing these kinds of tests? Would it make sense to attempt porting the database to some kind of XML format for this kind of testing? Or, would it be easier to use something like hsqldb (assuming db vendor nuances aren't an issue) for the sake of ease and portability when testing? This would alleviate the need to install a database system required for the tests.
                - sargon

                Comment

                Working...
                X