51Testing软件测试论坛

标题: 【转贴】JUnit关于数据库访问的单元测试 [打印本页]

作者: testing 时间: 2005-9-6 12:42
标题: 【转贴】JUnit关于数据库访问的单元测试
最近在看<<JUnit in Action>>对单元测试有了进一步的了解,以下是对数据库进行单元测试的一个例子.
   这是一个web程序,servlet从request里提取SQL语句,然后从DB里取得数据返回给JSP.

   首先,假设你的数据库访问代码与业务逻辑代码已经清晰地区分开来.那么我们就可以对数据库访问进行不同的单元测试.

   1.对业务逻辑的单元测试--------这些测试的目标是将业务逻辑代码部分与数据库访问代码部分(持久化代码JDBC/JDO...)隔离开来,只对业务逻辑代码部分进行单元测试.采用什么策略?当然是mock objects.:-) 以下是例子:

   public class AdminServlet extends HttpServlet {

      public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException {}

      public String getCommand(HttpServletRequest request) throws ServletException {}  //从request里取得SQL语句

      public Collection executeCommand(String command) throws Exception {}

      public void callView(HttpServletRequest request) {} //传递数据给JSP

   }

   各种不同的方法的执行流程是:doGet接受HTTP请求并调用getCommand方法抽取出其中的SQL查询,接着调用executeCommand来执行数据库调用并将结果以一个Collection返回.结果放入HTTP请求中,最后,doGet调用callView来激活给用户显示结果的JSP页面.

   现在的问题在于如何在不执行由executeCommand运行的数据库访问代码的前提下对doGet,getCommand,callView进行单元测试.方法是建立数据库访问层.为数据库访问层建立一个接口.有了接口就可以采用mock objects策略来对数据库访问层进行单元测试.

   数据库访问层接口:

   public interface DataAccessManager {

      Collection execute(String sql) throws Exception;

   }

   接着重构AdminServlet类

   public class AdminServlet extends HttpServlet
{
// [...]

private DataAccessManager dataManager;
public void init() throws ServletException
{
      super.init();

      try
      {
         this.dataManager = new JdbcDataAccessManager();
      catch (NamingException e)
      {
         throw new ServletException(e);
      }
}

public Collection executeCommand(String command)
      throws Exception
{
      return this.dataManager.execute(command);
}
}


   JdbcDataAccessManager类是DataAccessManager的执行类. 在进行单元测试时我们所要做的是建立一个DataAccessManager的mock objects实现来替换真实的JdbcDataAccessManager.

   继续重构成AdminServlet:

public class AdminServlet extends HttpServlet
{
// [...]

private DataAccessManager dataManager;

public DataAccessManager getDataAccessManager()
{
      return this.dataManager;
}

public void setDataAccessManager(DataAccessManager manager)
{
      this.dataManager = manager;
}

public void init() throws ServletException
{
      super.init();

      try
      {
         setDataAccessManager(new JdbcDataAccessManager());
      }
      catch (NamingException e)
      {
         throw new ServletException(e);
      }
}

public Collection executeCommand(String command)
      throws Exception
{
      return this.dataManager.execute(command);
}
}

   添加一个setter DataAccessManager的方法来便于传入mock objects.

下面就是怎样来建立mock objects,我们用DynaMock API来实现,当然你也可以用easymock,JMock等来实现.下面是一个编写DynaMock TestCase的模板:

import java.util.ArrayList;

import com.mockobjects.dynamic.Mock;

import com.mockobjects.dynamic.C;

import junit.framework.TestCase;

public class TestAdminServletDynaMock extends TestCase
{
public void testSomething() throws Exception
{
      Mock mockManager = new Mock(DataAccessManager.class);
      DataAccessManager manager =
         (DataAccessManager) mockManager.proxy();

      mockManager.expectAndReturn("execute", C.ANY_ARGS,
         new ArrayList());

      AdminServlet servlet = new AdminServlet();
      servlet.setDataAccessManager(manager);

      // Call the method to test here. For example:
      // manager.doGet(request, response)

      // [...]
}
}

   你用DynaMockAPI创建一个DataAccessManager mock objects,接下来当调用execute方法时你让mock objects返回你设定的值.那么AdminServlet其余的方法就可以很方便的用这个值进行单元测试了.

   2.对数据库访问的单元测试--------数据库访问代码用一套持久化API访问你的数据库.这类测试的目标是验证你是否正确使用了持久化API.测试策略主要是采用mock objects来模拟持久化API在不与数据库连接并且不在容器内的情况下运行测试.

   现在我们来测试数据库访问代码.JDBC,JDO等等本身设计得很好，并且使用java接口,这样一来就非常适宜于mock objects策略.对于JDBC API 来说，有现成的mock objects包(MockObjects.com JDBC API),它比较成熟，已经为JDBC代码单元测试作好了准备.下面是我们要测的数据库访问代码:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.beanutils.RowSetDynaClass;

public class JdbcDataAccessManager implements DataAccessManager
{
private DataSource dataSource;

public JdbcDataAccessManager() throws NamingException
{
      this.dataSource = getDataSource();
}

protected DataSource getDataSource() throws NamingException
{
      InitialContext context = new InitialContext();
      DataSource dataSource =
         (DataSource) context.lookup("java:/DefaultDS");
      return dataSource;
}

protected Connection getConnection() throws SQLException
{
      return this.dataSource.getConnection();
}

public Collection execute(String sql) throws Exception
{
      Connection connection = getConnection();

      // For simplicity, we'll assume the SQL is a SELECT query
      ResultSet resultSet =
         connection.createStatement().executeQuery(sql);

      RowSetDynaClass rsdc = new RowSetDynaClass(resultSet);

      resultSet.close();
      connection.close();

      return rsdc.getRows();
}
}

   数据库访问代码主要在execute方法内,我们要给里面的使用到的JDBC API提供mock objects,首先我们需要模拟Connection对象，接着模拟的Connection对象就可以返回mock ResultSet,mock Statement等等.现在我们关心的问题是如何将一个mock Connection对象传递给JdbcDataAccessManager类.很简单,再写一个JdbcDataAccessManager的子类,添加setter方法,我们测了这个子类也就相当于测了DataAccessManager类.

import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.NamingException;
import javax.sql.DataSource;

public class TestableJdbcDataAccessManager
extends JdbcDataAccessManager
{
private Connection connection;

public TestableJdbcDataAccessManager() throws NamingException
{
      super();
}

public void setConnection(Connection connection)
{
      this.connection = connection;
}

protected Connection getConnection() throws SQLException
{
      return this.connection;
}

protected DataSource getDataSource() throws NamingException
{
      return null;
}
}

   下面我们来编写测试代码:

import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;

import org.apache.commons.beanutils.DynaBean;

import com.mockobjects.sql.MockConnection2;
import com.mockobjects.sql.MockResultSetMetaData;
import com.mockobjects.sql.MockSingleRowResultSet;
import com.mockobjects.sql.MockStatement;

import junit.framework.TestCase;

public class TestJdbcDataAccessManagerMO4 extends TestCase
{
private MockSingleRowResultSet resultSet;
private MockResultSetMetaData resultSetMetaData;
private MockStatement statement;
private MockConnection2 connection;
private TestableJdbcDataAccessManager manager;

protected void setUp() throws Exception
{
      resultSetMetaData = new MockResultSetMetaData();

      resultSet = new MockSingleRowResultSet();
      resultSet.setupMetaData(resultSetMetaData);

      statement = new MockStatement();

      connection = new MockConnection2();
      connection.setupStatement(statement);

      manager = new TestableJdbcDataAccessManager();
      manager.setConnection(connection);
}

protected void tearDown()
{
      connection.verify();
      statement.verify();
      resultSet.verify();
}

public void testExecuteOk() throws Exception
{
      String sql = "SELECT * FROM CUSTOMER";
      statement.addExpectedExecuteQuery(sql, resultSet);

      String[] columnsUppercase = new String[] {"FIRSTNAME",
         "LASTNAME"};
      String[] columnsLowercase = new String[] {"firstname",
         "lastname"};
      String[] columnClasseNames = new String[] {
         String.class.getName(), String.class.getName()};

      resultSetMetaData.setupAddColumnNames(columnsUppercase);
      resultSetMetaData.setupAddColumnClassNames(
         columnClasseNames);
      resultSetMetaData.setupGetColumnCount(2);

      resultSet.addExpectedNamedValues(columnsLowercase,
         new Object[] {"John", "Doe"});

      connection.setExpectedCreateStatementCalls(1);
      connection.setExpectedCloseCalls(1);

      Collection result = manager.execute(sql);

      Iterator beans = result.iterator();

      assertTrue(beans.hasNext());
      DynaBean bean1 = (DynaBean) beans.next();
      assertEquals("John", bean1.get("firstname"));
      assertEquals("Doe", bean1.get("lastname"));

      assertTrue(!beans.hasNext());
}

public void testExecuteCloseConnectionOnException()
      throws Exception
{
      String sql = "SELECT * FROM CUSTOMER";

      statement.setupThrowExceptionOnExecute(
         new SQLException("sql error"));

      connection.setExpectedCloseCalls(1);

      try
      {
         manager.execute(sql);
         fail("Should have thrown a SQLException");
      }
      catch (SQLException expected)
      {
         assertEquals("sql error", expected.getMessage());
      }
}
}

   首先,我们用mockobject.sql包里的MockConnection建立一个 Mock Connection对象,TestableJdbcDataAccessManager把它设置进去,然后再设置Mock Statement和Mock ResultSet,并且设置Mock ResultSet返回的期望值.然后我们就可以来进行单元测试了, 去验证数据库访问代码的返回值.注意:在tearDown()里的connection,statement和resultSet的verify方法.这是对模拟对象添加的预期验证.比如statement.addExpectedExecuteQuery(sql, resultSet) statement.verify()就会验证SQL字符串是不是就是不做任何修改地传递过来的那个,执行结果是不是resultSet对象.connection.setExpectedCreateStatementCalls(1) connection.verify就会验证是否仅创建了一个Statement.

   3.数据库集成单元测试--------这类测试主要是检测数据库的功能:连接,查询,存储过程,触发,约束以及引用完整性.这些测试必须在容器内并且有数据源相连的情况下进行.有很多第三方工具可以用来做这种测试,比如用cactus来进行容器内测试,DbUnit来预载测试数据库.

   为了运行集成单元测试,我们的测试框架需要有两个功能:将测试数据预置进数据库的能力和从运行的容器内进行测试的能力.

   我们的测试策略是用JBoss做为J2EE容器.MySQL作为测试数据库.Cactus作为测试代理.DbUnit作为装载及比较数据的工具,Ant作为运行测试工具.下面让我们进入测试吧.

   编写真正的JdbcDataAccessManager类:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.beanutils.RowSetDynaClass;

public class JdbcDataAccessManager implements DataAccessManager
{
private DataSource dataSource;

public JdbcDataAccessManager() throws NamingException
{
      this.dataSource = getDataSource();
}

protected DataSource getDataSource() throws NamingException
{
      InitialContext context = new InitialContext();
      DataSource dataSource =
         (DataSource) context.lookup("java:/MySqlDS");
      return dataSource;
}

protected Connection getConnection() throws SQLException
{
      return this.dataSource.getConnection();
}

public Collection execute(String sql) throws Exception
{
      Connection connection = getConnection();

      // For simplicity, we'll assume the SQL is a SELECT query
      ResultSet resultSet =
         connection.createStatement().executeQuery(sql);

      RowSetDynaClass rsdc = new RowSetDynaClass(resultSet);

      resultSet.close();
      connection.close();

      return rsdc.getRows();
}
}

注意要配置一个mysql数据源.假设在mysql数据库里建了一个叫jbossdb的数据库.然后在%JBOSS_HOME%/docs/examples/jca目录下找到mysql-ds.xml文件,对它进行编辑.

<?xml version="1.0" encoding="UTF-8"?>












<datasources>
  <local-tx-datasource>
<jndi-name>MySqlDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
<driver-class>org.gjt.mm.mysql.Driver</driver-class>
<user-name>sa</user-name>
<password></password>
  </local-tx-datasource>

</datasources>

保存以后考到%JBOSS_HOME%/server/default/deploy目录下(假设你的JBOSS服务器以default模式起).下面编写测试代码:

import java.util.Collection;
import java.util.Iterator;

import javax.naming.InitialContext;

import org.apache.cactus.ServletTestCase;
import org.apache.commons.beanutils.DynaBean;
import org.dbunit.database.DatabaseDataSourceConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.operation.DatabaseOperation;

public class TestJdbcDataAccessManagerIC extends ServletTestCase
{
protected void setUp() throws Exception
{
      IDatabaseConnection connection =
         new DatabaseDataSourceConnection(new InitialContext(),
         "java:/MySqlDS");

      IDataSet dataSet = new FlatXmlDataSet(
         this.getClass().getResource(
         "/junitbook/database/data.xml"));

      try
      {
         DatabaseOperation.CLEAN_INSERT.execute(connection,
            dataSet);
      }
      finally
      {
         connection.close();
      }
}

public void testExecuteOk() throws Exception
{
      JdbcDataAccessManager manager =
         new JdbcDataAccessManager();

      Collection result =
         manager.execute("SELECT * FROM CUSTOMER");

      Iterator beans = result.iterator();

      assertTrue(beans.hasNext());
      DynaBean bean1 = (DynaBean) beans.next();
      assertEquals("John", bean1.get("firstname"));
      assertEquals("Doe", bean1.get("lastname"));

      assertTrue(!beans.hasNext());
}
}

在setup()里我们利用DbUnit从一个xml文件转载测试数据到数据库里.这里需要一个xml文件data.xml

  <dataset>
<CUSTOMER FIRSTNAME="John" LASTNAME="Doe"/>
</dataset>
可以把它和测试代码文件放在同一个目录下.这个TestCase就是先在数据库里的Customer表里加一条记录,再进行测试.table我们可以利用ant来建,先编写一个data.sql

CREATE TABLE CUSTOMER (lastname varchar primary key,
firstname varchar);

.注意所有文件以及需要的包的安放按照junit test的传统安置方式安放.好,要用ant来运行了,先写build.xml

build.properties

cactus.home.jboss3x = E:/JBoss/jboss-3.2.3/jboss-3.2.3

database = //localhost:3306/jbossdb

lib.dir = ../repository

mysqldb.jar = ${cactus.home.jboss3x}/server/default/lib/mm.mysql-2.0.4-bin.jar

beanutils.jar = ${lib.dir}/commons-beanutils/jars/commons-beanutils-1.6.1.jar
collections.jar = ${lib.dir}/commons-collections/jars/commons-collections-2.1.jar
servlet.jar = ${lib.dir}/servletapi/jars/servletapi-2.3.jar

cactus.jar = ${lib.dir}/cactus/jars/cactus-1.6.1.jar
cactus.ant.jar = ${lib.dir}/cactus/jars/cactus-ant-1.6.1.jar
aspectjrt.jar = ${lib.dir}/aspectj/jars/aspectjrt-1.1.1.jar
dbunit.jar = ${lib.dir}/dbunit/jars/dbunit-1.5.5.jar
exml.jar = ${lib.dir}/dbunit/jars/exml-dbunit-1.5.5.jar
logging.jar = ${lib.dir}/commons-logging/jars/commons-logging-1.0.3.jar
httpclient.jar = ${lib.dir}/commons-httpclient/jars/commons-httpclient-2.0-rc1.jar

build.xml

<?xml version="1.0"?>

<project name="Database" default="test" basedir=".">

  <property file="build.properties.sample"/>
  <property name="conf.dir" location="conf"/>

  <target name="compile">
<mkdir dir="target/classes"/>
<javac destdir="target/classes" srcdir="src/java">
   <classpath>
      <pathelement location="${beanutils.jar}"/>
      <pathelement location="${servlet.jar}"/>
   </classpath>
</javac>
  </target>

  <target name="war" depends="compile">
<war destfile="target/database.war"
      webxml="src/webapp/WEB-INF/web.xml">
   <classes dir="target/classes"/>
   <lib file="${beanutils.jar}"/>
   <lib file="${collections.jar}"/>
</war>
  </target>

  <target name="createdb" depends="war">
<sql driver="org.gjt.mm.mysql.Driver"
      url="jdbc:mysql

{database}"
      userid="root"
      password="">
   <fileset dir="${conf.dir}">
      <include name="data.sql" />
   </fileset>
   <classpath>
      <pathelement location="${mysqldb.jar}"/>
   </classpath>
</sql>
  </target>

  <target name="clean">
<delete dir="target"/>
  </target>

  

  <target name="compile.cactustest" depends="createdb">
<mkdir dir="target/cactus-test-classes"/>
<javac destdir="target/cactus-test-classes"
      srcdir="src/test-cactus">
   <classpath>
      <pathelement location="target/classes"/>
      <pathelement location="${beanutils.jar}"/>
      <pathelement location="${dbunit.jar}"/>
      <pathelement location="${cactus.jar}"/>
   </classpath>
</javac>
<copy todir="target/cactus-test-classes">
   <fileset dir="src/test-cactus">
      <include name="**/*.xml"/>
   </fileset>
</copy>
  </target>

  <target name="test" depends="war,compile.cactustest">

<taskdef resource="cactus.tasks">
   <classpath>
      <pathelement location="${cactus.ant.jar}"/>
      <pathelement location="${cactus.jar}"/>
      <pathelement location="${logging.jar}"/>
      <pathelement location="${aspectjrt.jar}"/>
      <pathelement location="${httpclient.jar}"/>
   </classpath>
</taskdef>

<cactifywar srcfile="target/database.war"
      destfile="target/test.war">
   <classes dir="target/cactus-test-classes"/>
   <lib file="${dbunit.jar}"/>
   <lib file="${exml.jar}"/>
</cactifywar>

<cactus warfile="target/test.war" fork="yes" printsummary="yes"
      haltonerror="true" haltonfailure="true">
   <containerset>
      <jboss3x dir="${cactus.home.jboss3x}"
         output="target/jbossresult.txt">
      </jboss3x>
   </containerset>
   <formatter type="brief" usefile="false"/>
   <batchtest>
      <fileset dir="src/test-cactus">
      <include name="**/TestJdbcDataAccessManagerIC.java"/>
      </fileset>
   </batchtest>
   <classpath>
      <pathelement location="target/classes"/>
      <pathelement location="target/cactus-test-classes"/>
      <pathelement location="${dbunit.jar}"/>
   </classpath>
</cactus>

  </target>

</project>

接着我们就可以run case了,我试了一下没问题,若有问题,应该是配置或版本出了问题.

   我本人对JUnit进行数据库测试的看法是,在测试之前一定要有一个好的测试策略,在这基础之上在进行测试代码的编写.对数据库业务逻辑和数据库访问代码的测试代码编写相对简单一些.对数据库进行集成测试要复杂和麻烦一些,但在这过程中能学到不少东西和加深对一些过程和配置的理解.

   以上是我一些不成熟的学习心得,在这抛砖引玉,希望更多的同行来讨论.

作者: 魏乐 时间: 2005-9-21 13:15
标题: JUnit in Action 我最近也在学习中
希望能在工作中把它用起来。

作者: steven.chen 时间: 2006-9-30 15:45
非常感谢

作者: woaishazhude 时间: 2007-3-1 17:08
十分感谢！

作者: 质量新知 时间: 2007-3-28 15:09
Great! thank you very much.

作者: song9394 时间: 2008-5-8 13:41
正需要，

作者: zzlovebird 时间: 2009-4-22 19:45
标题: 太感谢了
正在写关于Junit的论文如果有好的资料希望您推荐一下谢了

作者: qdd6501 时间: 2011-6-20 11:43
resultSet.setupMetaData(resultSetMetaData); 为什么我写这句的时候会报错啊？
错误：
The type com.mockobjects.MockObject cannot be resolved. It is indirectly referenced from required .class files

代码是：
private MockMultiRowResultSet resultSet;
private MockResultSetMetaData resultSetMetaData;
private MockStatement statement;
private MockConnection2 connection;
public void setUp(){
resultSetMetaData= new MockResultSetMetaData();
resultSet = new MockMultiRowResultSet();
resultSet.setupMetaData(resultSetMetaData);
statement = new MockStatement();
      connection = new MockConnection2();
      connection.setupStatement(statement);
}

作者: 武反昏 时间: 2011-10-28 08:57
广西瑞康医院成立六十周年：勇立潮头一甲子，敢为人民谋健康

   勇立潮头一甲子敢为人民谋健康——记广西中医学院附属瑞康医院成立六十周年
   六十一甲子，峥嵘岁月稠。伴随着伟大祖国雄迈的前进步伐，广西中医学院附属瑞康医院历经60年的风雨洗礼，如今深深扎根大西南，面向东盟，成为庇佑八桂大地人民健康的保护神，成为西南地区最大的中西医结合医院。
   时光荏苒，岁月如歌。一代代的瑞康人把青春和热血播洒在这片土地上，浇灌出美丽的生命之花，一代代的瑞康人凭着敢为天下先的豪情、勇担责任的勇气、不怕困难挫折的决心、坚持不懈的敬业精神的优秀气质把医院一步步发展壮大，如今已成为八桂医学界的一颗璀璨明珠。
   近8万平方米的现代化医疗大楼2003投入使用，实现全数字化管理，新门诊住院综合楼正拔地而起；世界最先进的肿瘤放疗机器人——射波刀安装并投入临床使用；3.0T核磁共振、美国产高能电子直线加速器，胶囊内镜等一大批先进医疗设备投入使用，使医院的诊疗手段达到国内领先水平；国家“三级甲等”医院、全国百姓放心示范医院、国家重点建设的中西医结合医院、全国卫生系统先进集体……越来越多的荣誉更是见证瑞康医院的发展步伐。

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)