3.2.2. Testing a Class that Uses the Database

3.2.2.1. Summary

We will describe how to test classes that use the database, such as database access class.

When testing the classes that use the database, operations related to database (test data input, data verification) can be performed by using the classes provided by this framework.

3.2.2.1.1. Overall picture

../../../../../_images/class_structure.png

3.2.2.1.1.1. Main Classes, resources

Name Role Creation unit
Test class Implement the test logic. Inherit DbAccessTestSupport. Creating one per class to be tested
Test data (Excel file) Describe the test data, such as preliminary data stored in a table and expected results. Create one per test class
Class to be tested The class to be tested. －
DbAccessTestSupport Provides the necessary functions for testing using the database, such as preparation data input. Also, starts and ends a database transaction before and after the execution of a test(To use transactions other than the default) －

3.2.2.2. Basic test methods

How to use APIs of this framework in accordance with the purpose, is described below.

3.2.2.2.1. Testing read process pattern

During read process testing, to check that the class to be tested fetches the data as expected, you can check the lookup result from the database by the following procedure:

  1. Registers the preparation data to the database.
  2. Invoke the method of the class to be tested.
  3. Check whether the search result received as a return value is the expected value.

3.2.2.2.1.1. Sequence

../../../../../_images/select_sequence.png

3.2.2.2.1.2. Example of test source code implementation

public class DbAccessTestSample extends DbAccessTestSupport {

    /**
     * Test all searches. <br/>
     * Check that all the * records registered in
     * Employee table can be acquired.
     */
    @Test
    public void testSelectAll() {

        // Register preparation data in the database
        // Write sheet name in the argument.
        setUpDb("testSelectAll");

        // Invoke the method to be tested.
        EmployeeDbAcess target = new EmployeeDbAccess();
        SqlResultSet actual = target.selectAll();

        // Confirmation of results
        // Confirm that the expected value described in Excel is equal to the actual value
        // In the argument, specify the name of the sheet containing the expected value, ID of the expected value, and the actual value
        assertSqlResultSetEquals("testSelectAll", "expected", actual);
    }
}

3.2.2.2.1.3. Test data description example

3.2.2.2.1.3.1. Preparation data to be pre-registered in the database

Enter the data as follows.

  • Line 1: SETUP_TABLE=<Name of table to be registered>
  • Line 2: Column name of the table
  • Line 3 ~ : Records to be registered (corresponding to the column name of the second row)

SETUP_TABLE=EMPLOYEE

ID EMP_NAME DEPT_CODE
00001 Yamada Taro 0001
00002 Tanaka Ichiro 0002

SETUP_TABLE=DEPT

ID DEPT_NAME
0001 Human Resources Department
0002 Administration Department
3.2.2.2.1.3.2. Expected value after test execution

Enter the data as follows.

  • Row 1: LIST_MAP=< ID of the expected value, which is unique in the sheet (arbitrary string)
  • Row 2: Column name or alias specified in the SELECT statement
  • Row 3 ~ : Search results (corresponding to the column name of Row 2)

LIST_MAP=expected

ID EMP_NAME DEPT_NAME
00001 Yamada Taro Human Resources Department
00002 Tanaka Ichiro Administration Department

3.2.2.2.2. Testing update process

When checking that the class to be tested updates the data as expected, you can check the update result of the database by the following procedure:

  1. Registers the preparation data to the database.
  2. Invoke the method of the class to be tested.
  3. Commits the transaction.
  4. Check that the database values are updated as expected.

Important

In the Nablarch Application Framework, it is assumed that multiple types of transactions will be used together. Therefore, when checking the database contents after executing the class to be tested, you must commit the transaction. If the transaction is not committed, the test result is not checked normally.

Tip

You need not commit when testing the read process.

3.2.2.2.2.1. Sequence

../../../../../_images/update_sequence.png

3.2.2.2.2.2. Example of test source code implementation

public class DbAccessTestSample extends DbAccsessTestSupport {
    @Test
    public void testDeleteExpired() {

        // Register preparation data in the database
        // Specify the sheet name in the argument.
        setUpDb("testDeleteExpired");

        // Invoke the method to be tested.
        EmployeeDbAcess target = new EmployeeDbAccess();
        SqlResultSet actual = target.deleteExpired();  // Delete the expired data

        // Commit the transaction
        commitTransactions();

        // Confirmation of results
        // Confirm that the expected value described in Excel is equal to the actual value
        // In the argument, specify the name of the sheet containing the expected value, and the actual value
        assertTableEquals("testDeleteExpired", actual);
    }

3.2.2.2.2.3. Test data description example

3.2.2.2.2.3.1. Preparation data to be pre-registered in the database

Enter the data as follows.

  • Line 1: SETUP_TABLE=<Name of the table to be registered>
  • Line 2: Column name of the table
  • Line 3 ~ : Records to be registered (corresponding to the column name of the second row)

SETUP_TABLE=EMPLOYEE

ID EMP_NAME EXPIRED
00001 Yamada Taro TRUE
00002 Tanaka Ichiro FALSE
3.2.2.2.2.3.2. Expected value after test execution

Enter the data as follows.

  • Line 1: EXPECTED_TABLE=<Name of the table to be checked>
  • Line 2: Column name of the table to be checked
  • Line 3 ~: Expected value

EXPECTED_TABLE=EMPLOYEE

ID EMP_NAME EXPIRED
// CHAR(5) VARCHAR(64) BOOLEAN
00002 Tanaka Ichiro FALSE

3.2.2.3. How to omit description of database test data

When describing the preparation data of the database and the expected values, description in columns not relevant to the test can be omitted. Default values configured for the omitted columns by the automated testing framework. By using this function, readability of the test data improves. Also, even if the table definition is changed, data modification work is saved for columns that are not relevant, thereby improving maintainability.

This function is especially useful for test cases related to the update process. When only one out of the many columns is updated, there is no need to describe the unnecessary columns.

Important

When describing the expected values of database search results, one must describe all the columns to be searched (it is not possible to check only the primary key of a record).

Even when testing the registration process, since all the columns of the newly registered records need to be checked, it is not possible to omit columns using this function.

3.2.2.3.1. When omitting a column of DB preparation data

If you omit a column when writing the data for database preparation, the omitted column is treated as having Default values configured.

However, the column of the primary key cannot be omitted.

3.2.2.3.2. When omitting a column of DB expected values

If you simply omit a column that is not relevant from the DB expected values, the omitted column is excluded from comparison. When testing the update process, “checking that an irrelevant column is not updated” is also a necessary perspective. In this case, use EXPECTED_COMPLETE_TABLE for data type, instead of EXPECTED_TABLE. If EXPECTED_TABLE is used, the omitted column is excluded from comparison, whereas if EXPECTED_COMPLETE_TABLE is used, comparison is carried out assuming that the omitted column contains the Default values.

3.2.2.3.3. Specific examples

Examples when all the columns are described and when only the relevant columns are described, are shown below.

3.2.2.3.3.1. Test case example

The following test cases are used as examples:

For records that have passed the “expiration date”, the “Delete flag” should be updated to 1.[1]

[1]The date on which this test is executed is January 1, 2011.

The table to be used (SAMPLE_TABLE) has the following columns:

Column name Description
PK1 Primary key
PK2 Primary key
COL_A Columns that are not used by the function under test
COL_B Columns that are not used by the function under test
COL_C Columns that are not used by the function under test
COL_D Columns that are not used by the function under test
Expiration date Data that has passed the expiration data will be processed
Delete flag The value of records that have passed the expiration date, will be updated to “1”

3.2.2.3.3.2. When all the columns are described without omission (bad example)

Readability decreases as all the columns are described.[2]Also, if there is a change in the table definition, even a column that is not relevant must be modified.

[2]The columns COL_A, COL_B, COL_C and COL_D are not relevant to this test case.

Preparation data

SETUP_TABLE=SAMPLE_TABLE

PK_1 PK_2 COL_A COL_B COL_C COL_D Expiration date Delete flag
01 0001 1a 1b 1c 1d 20101231 0
02 0002 2a 2b 2c 2d 20110101 0

Expected value

EXPECTED_TABLE=SAMPLE_TABLE

PK_1 PK_2 COL_A COL_B COL_C COL_D Expiration date Delete flag
01 0001 1a 1b 1c 1d 20101231 1
02 0002 2a 2b 2c 2d 20110101 0

3.2.2.3.3.3. When only the relevant columns are described (good example)

By describing only the relevant columns, readability and maintainability improve. The columns relevant to this test case are as follows:

  • Primary key column to uniquely identify a record (PK_1, PK_2)
  • “Expiration date” column, which is a condition for extracting records to be updated
  • “Delete flag” column to be updated

In addition, even if there is a change in the table definition, there is no impact on columns that are not relevant.

Preparation data

Only the columns relevant to the test to be executed are described.

SETUP_TABLE=SAMPLE_TABLE

PK_1 PK_2 Expiration date Delete flag
01 0001 20101231 0
02 0002 20110101 0

Expected value

When describing expected value, use EXPECTED_COMPLETE_TABLE instead of EXPECTED_TABLE.

EXPECTED_COMPLETE_TABLE=SAMPLE_TABLE

PK_1 PK_2 Expiration date Delete flag
01 0001 20101231 1
02 0002 20110101 0

3.2.2.3.4. Default values

The following values are used as default values unless explicitly specified in the component configuration file of the automated testing framework.

Column Default values
Numeric type 0
String type Half width spaces
Date type 1/1/1970 00:00:00.0

3.2.2.3.5. How to change the default values

3.2.2.3.5.1. Settings items list

You can configure the following values in the component configuration file using nablarch.test.core.db.BasicDefaultValues

Configuration item name Description Configuration value
charValue Default value for string type A single ASCII character
numberValue Default value for numeric type 0 or a positive integer
dateValue Default value for date type JDBC time stamp escape format (yyyy-mm-dd hh:mm:ss.fffffffff)

3.2.2.3.5.2. Example of component configuration file description

An example of a component configuration file description when the following setting values are used is shown below.

Configuration item name Configuration value
charValue a
numberValue 1
dateValue 1/1/2000 12:34:56.123456789 
<!-- TestDataParser -->
<component name="testDataParser" class="nablarch.test.core.reader.BasicTestDataParser">
  <!-- Database default values -->
  <property name="defaultValues">
    <component class="nablarch.test.core.db.BasicDefaultValues">
      <property name="charValue" value="a"/>
      <property name="dateValue" value="1/1/2000 12:34:56.123456789"/>
      <property name="numberValue" value="1"/>
    </component>
  </property>
  <!-- Middle is omitted -->
</component>

3.2.2.4. Important points

3.2.2.4.5. To set up data in a table with a foreign key

Delete and register data by determining the parent-child relationship of a table using the same function as Master Data Recovery Function. For more information, see When using a table with a foreign key.