Mocking db calls in unit test

Here is my java class which is accessing DB to fetch movie names and then it validates if the fetched movie name has a movie called ‘Sangam’.

package com.sanjit;

import java.sql.*;

/**
* Class accessing the DB to fetch some data, which is then used for some
* validation
*
* @author Sanjit Mohanty
* @version 0.1
*
*          <pre>
* Revision History:
* VERSION  DATE           AUTHOR            COMMENT
* 0.1      21-Jul-2010    Sanjit Mohanty    initial create
* </pre>
*/

public class DBCallClass {

static Connection con;

public boolean isMovieSangam() throws SQLException {
ResultSet rs = fetchData();

while (rs.next()) {
String movieName = rs.getString("moviename");

if (movieName == "Sangam") {
return true;
}
}

return false;
}

public ResultSet fetchData() throws SQLException {

String connectionURL = "jdbc:postgresql://localhost:123456/movies;user=java;password=samples";

try {
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(connectionURL);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select moviename from movies");

return rs;
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
con.close();
}

return null;
}
}

In order to unit test the above class, i’ve to mock the actual DB call and then return the test data. I’ve used powermock (with easymock api) library to achieve this.

So, the JUnit class for the above looks as below:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Assert;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;

import static org.easymock.EasyMock.expect;
import static org.powermock.api.easymock.PowerMock.*;

import org.powermock.core.classloader.annotations.PrepareForTest;
import org.powermock.modules.junit4.PowerMockRunner;

import com.sanjit.DBCallClass;

/**
* Example: Mocking the DB calls using powermock. Main Class:
* {@link com.sanjit.DBCallClass}
*
* @author Sanjit Mohanty
* @version 0.1
*
*          <pre>
* Revision History:
* VERSION  DATE           AUTHOR            COMMENT
* 0.1      21-Jul-2010    Sanjit Mohanty    initial create
* </pre>
*/

@RunWith(PowerMockRunner.class)
@PrepareForTest({ DBCallClass.class, DriverManager.class })
public class DBCallClassTest {

private String connectionURL;
private String sql;
private boolean isMovieSangam;

@Before
public void initialization() {
connectionURL = "jdbc:postgresql://localhost:123456/movies;user=java;password=samples";
sql = "select moviename from movies";
isMovieSangam = false;
}

@Test
public void testIsMovieSangam() throws Exception {

// Create mocks and set expectations
createMocksAndSetExpectations();

// Instantiate the class under test
DBCallClass dbCall = new DBCallClass();

// Invoke the method to test
isMovieSangam = dbCall.isMovieSangam();

// Verify the results
doVerification();
}

private void createMocksAndSetExpectations() throws Exception {

Connection mockConnection = createMock(Connection.class);
Statement mockStatement = createMock(Statement.class);
ResultSet mockResultSet = createMock(ResultSet.class);

// Mocking Static Class
mockStatic(DriverManager.class);
expect(DriverManager.getConnection(connectionURL)).andReturn(
mockConnection);
replay(DriverManager.class);

expect(mockConnection.createStatement()).andReturn(mockStatement)
.anyTimes();
mockConnection.close();
replay(mockConnection);

expect(mockStatement.executeQuery(sql)).andReturn(mockResultSet);
replay(mockStatement);

expect(mockResultSet.next()).andReturn(true);
expect(mockResultSet.getString("moviename")).andReturn("Sangam");
replay(mockResultSet);
replay(mockStatement);
}

private void doVerification() {
Assert.assertEquals(true, isMovieSangam);
}
}

To use powermock (with easymock api) in a maven project, include the following dependencies to the pom.xml file:

<dependency>
<groupId>org.powermock</groupId>
<artifactId>powermock-module-junit4</artifactId>
<version>1.4.9</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.powermock</groupId>
<artifactId>powermock-api-easymock</artifactId>
<version>1.4.9</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.easymock</groupId>
<artifactId>easymock</artifactId>
<version>3.0</version>
</dependency>

But if you are using a non-maven based java project then you need to place cglib-nodep-2.2.2.jar,
easymock-3.1.jar, javassist-3.16.1-GA.jar, powermock-easymock-1.4.12-full.jar, powermock-module-junit4-1.4.12.jar
and objenesis-1.2.jar in your classpath. Pl. note versions are just an indicator, you should use the latest
versions of these jars.

Advertisements

10 thoughts on “Mocking db calls in unit test

  1. Hi Sanjit…

    I tried above code to mock driverManager.getConnection ,

    but it throws noclassdeffounderror net/sf/cglib/proxy/enhancer…
    i searched in google it says to attach cglib,objenesis and easymock-3.0 jar file should be attached in classpath ,
    and i attached above file then its throwing Exception as drivermanager is not an Interface….

    please suggest me to solve this issue, I struck testing this driveramanager

    thanking you…………

    Like

    • If you are just running the program from the blog, then the following jars in your classpath should suffice:

      1. cglib-nodep-2.2.jar
      2. easymock-3.0.jar
      3. javassist-3.15.0-GA.jar
      4. powermock-easymock-1.4.12-full.jar
      5. postgresql-9.2-1002.jdbc3.jar
      6. JRE
      7. JUnit jars.

      Note: Jar versions are just indicative. You can use latest versions as well!

      Otherwise, if you trying out something else, share me the code snippet of test driver & corresponding class under test.

      _Sanjit

      Like

  2. Thanks for your reply

    please analyse the code whether i am wrong..

    ************************ Class under Test ************************************

    public class QuoteErrorTabDao extends QuoteConnection implements ApplicationConstant{
    List params = null;
    QueryRepository queryRepository = null;

    public QuoteErrorTabDao() {
    queryRepository = QueryManager.getInstance();
    }

    public boolean insertError(QuoteErrorTabVO quoteErrorTabVO, Connection con){

    boolean success = false;
    Logger.info(getClass(), “Start of insertError method”);
    params = new ArrayList();

    String quoteHeaderQuery = queryRepository.getQuery(QUOTE,
    WRITE_QUOTE, INS_QUOTE_ERROR);

    params.add(quoteErrorTabVO.getClient());
    if(quoteErrorTabVO.getTrnId()!= 0)
    params.add(quoteErrorTabVO.getTrnId());
    else
    params.add(null);
    params.add(quoteErrorTabVO.getErrorCode());
    params.add(quoteErrorTabVO.getErrorMessage());
    params.add(quoteErrorTabVO.getErrorDescription());
    params.add(quoteErrorTabVO.getQuoteNumber());
    params.add(quoteErrorTabVO.getQuoteVersion());
    params.add(quoteErrorTabVO.getSrcTrnId());
    params.add(quoteErrorTabVO.getClientRequest());
    params.add(quoteErrorTabVO.getServiceResponse());
    params.add(quoteErrorTabVO.getCreatedBy());
    params.add(quoteErrorTabVO.getLastModifiedBy());

    try {
    int rowsInserted = executeUpdatePreparedStatement(con, quoteHeaderQuery, params);
    if(rowsInserted == 1){
    success = true;
    Logger.debug(getClass(), “error record successfully inserted”);
    }
    } catch (CoronaException e) {
    Logger.error(getClass(),”Exception occured while inserting data in QuoteErrorTab table “);
    }
    Logger.info(getClass(), “End of insertError method”);

    return success;
    }
    }

    *********************************************************************************************************

    Junit Testcase for above class

    ********************************************************************************************************

    @RunWith(PowerMockRunner.class)
    //@PrepareForTest({DriverManager.class})
    public class QuoteErrorTabDaoTest
    {
    @InjectMocks
    private QuoteErrorTabDao quoteErrorTabDao;

    @Mock
    private DriverManager driverManager;

    @Mock
    private Connection connection;

    @Mock
    private Statement statement;

    @Mock
    private PreparedStatement prepStatement;

    @Mock
    private QuoteConnection quoteConnection;

    /**
    * As DriverManager is not an Interface so there is no point of Mocking the object,
    * so make a connection, perform operations, and rollback your changes….
    *
    * I tried https://code.google.com/p/powermock/wiki/MockSystem
    * this way then also its not possible to get mocked object for DriverManager as its not an interface .
    *
    * FYI http://stackoverflow.com/questions/5737939/testing-database-connection-with-drivermanagerjava
    *
    */

    /**
    * Getting DriverManager,Connection,Statement,PreparedStatement Mocked Object in setUp
    */

    @Before
    public void setUp() throws Exception
    {
    Class.forName(“oracle.jdbc.driver.OracleDriver”);
    // PowerMockito.mockStatic(DriverManager.class);
    driverManager=Mockito.mock(DriverManager.class);
    connection=Mockito.mock(Connection.class);
    statement=Mockito.mock(Statement.class);
    prepStatement=Mockito.mock(PreparedStatement.class);
    connection=driverManager.getConnection(“jdbc:oracle:thin:@g9u0696.houston.hp.com:1525:CRNAD”,”QUOTE_IDS”,”QUOTE_IDS^12″);
    // when(driverManager.getConnection(“jdbc:oracle:thin:@g9u0696.houston.hp.com:1525:CRNAD”, “QUOTE_IDS”, “QUOTE_IDS^12”)).thenReturn(connection);
    statement=connection.createStatement();
    System.setProperty(“ids.config.dir”, “c:/finalresttest/conf/”);
    System.out.println(“init block…..”);

    // I commented your;’s way of mocking as it is not working……

    /* Class.forName(“oracle.jdbc.driver.OracleDriver”);
    connection = createMock(Connection.class);
    statement = createMock(Statement.class);

    // Mocking Static Class
    mockStatic(DriverManager.class);
    // when(driverManager.getConnection(“jdbc:oracle:thin:@g9u0696.houston.hp.com:1525:CRNAD”, “QUOTE_IDS”, “QUOTE_IDS^12”)).thenReturn(connection);
    // connection=driverManager.getConnection(“jdbc:oracle:thin:@g9u0696.houston.hp.com:1525:CRNAD”,”QUOTE_IDS”,”QUOTE_IDS^12″);
    expect(driverManager.getConnection(“jdbc:oracle:thin:@g9u0696.houston.hp.com:1525:CRNAD”,”QUOTE_IDS”,”QUOTE_IDS^12″)).andReturn(connection);
    replay(DriverManager.class);

    expect(connection.createStatement()).andReturn(statement).anyTimes();
    // mockConnection.close();
    replay(Connection.class);

    // expect(mockStatement.executeQuery(sql)).andReturn(mockResultSet);
    // replay(mockStatement);
    */
    }

    /**
    *
    * testcase for insertError method with TrnId equals Null
    */

    @Test
    public void TestinsertError()
    {
    QuoteErrorTabVO quoteErrorTabVO=new QuoteErrorTabVO();
    boolean returnVal=quoteErrorTabDao.insertError(quoteErrorTabVO, connection);
    assertTrue(returnVal);
    }

    /**
    *
    * testcase for insertError method with TrnId equals NOT Null
    */

    @Test
    public void TestinsertErrorForTrnIdNotNull()
    {
    QuoteErrorTabVO quoteErrorTabVO=new QuoteErrorTabVO();
    quoteErrorTabVO.setTrnId(12L);
    quoteErrorTabVO.setErrorCode(“errorCode”);
    quoteErrorTabVO.setErrorMessage(“errorMessage”);
    quoteErrorTabVO.setErrorDescription(“errorDescription”);
    quoteErrorTabVO.setQuoteNumber(“12”);
    quoteErrorTabVO.setQuoteVersion(“2.0”);
    quoteErrorTabVO.setSrcTrnId(“srcTrnId”);
    quoteErrorTabVO.setClientRequest(“GET”);
    quoteErrorTabVO.setServiceResponse(“serviceResponse”);
    quoteErrorTabVO.setCreatedBy(“Rohith”);
    quoteErrorTabVO.setLastModifiedBy(“Rohith”);
    boolean returnVal=quoteErrorTabDao.insertError(quoteErrorTabVO, connection);
    assertTrue(returnVal);
    }

    /**
    *
    * Closing mocked JDBC Connection
    */

    @After
    public void destroy() throws SQLException
    {
    statement.close();
    connection.close();
    }

    }

    ************************************************************

    as i used

    1) cglib-nodep-2.2.2 jar
    2) easymock-3.0 jar
    3) objenesis-1.3 jar file

    after including the above jarfiles in classpath , and also mandatory Junit jar file
    its showing the error message as

    DriverManager is not an Interface

    suggest me to solve this issue….

    Waiting for your reply…..

    Thanking you,………..

    Like

  3. Acolyte framework is useful for such purposes: https://github.com/cchantep/acolyte .

    With this lib you can create a connection instance for which you provide handler. Implementing handler, you are able to ‘dispatch’ query or update : producing resultsets or update count (or warning).

    Handler can also be registered on acolyte.Driver, do that managed connected will be get throught getConnection (jdbcUrl). In this way, just configuring an Acolyte/test JDBC URL, existing code can be executed against mock connection without more change.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s