Getting started with the project

1
Getting started with the project

• In this tutorial, we are going to set up the
  following
• A test case for the AccountRowSet constructor.
• A test case for the AccountRowSet method
  updateAccount.
• A test case for the AccountTableModel constructor.

2
Database connections

• When connecting to the database and obtaining a
  row set, a lock is put on that table and no other
  connection can open a read/write connection to
  that table until the connection is closed.
• Be very careful to close row sets when you are
  finished with them.
• If a test case appears to go into a loop, it is
  likely that you are attempting to put a second
  lock on a table.
• The test case will probably finish after 60-90
  seconds.

3
Setting up row sets

• The following setup code will ensure that only
  one connection is open to the database for each
  table.
• // Data access object (DAO) factory
• IDAOFactory factory new DAOFactory( )
• // These methods ensure only one row set is open
• IAccountRowSet ars factory.getAccountRowSet( )
• // Creating transactions requires having account
• // objects created first.
• TransactionRowSet.setDaoFactory( factory )
• ITransactionRowSet trs factory.getTransactionRow
  Set( )
• // These classes need row set references to
  persist updates
• PersistentAccount.setDao( ars )
• PersistentTransaction.setDao( trs )

4
Setting up table models

• When account table models or transaction table
  models are used, they will need references to the
  corresponding row set.
• Assuming the code on slide 3 has been executed,
  the following code will ensure the models work
  correctly.
• IAccountRowSet ars
• ITransactionRowSet trs
• IAccountTableModel atm new AccountTableModel()
• atm.setDao( ars )
• IAccountTableModel ttm new AccountTableModel()
• ttm.setDao( trs )

5
Database tables

• Table ACCOUNT_TYPE
• Contains definitions of the account types
• This table is used to provide values for the type
  of account, and is not read by the accounting
  software.
• Within the accounting software, account types are
  stored as constants of the enumerated type
  AccountType.
• Stored with each constant is the database integer
  value for the type, and a string to display.
• Table ACCOUNT
• Contains the accounts used in the system.
• Table TRANSACTIONS
• Contains the transactions stored in the system.

6
Table ACCOUNT_TYPE

• The purpose of this table is to provide account
  type ID values for the ACCOUNT table.
• The equivalent values in the software are in the
  enumerated type AccountType.

7
Class AccountRowSet

• The purpose of this class is to read the ACCOUNT
  table from the database, and create a set of
  IAccount objects.
• Methods are provided to insert, update, or delete
  accounts.
• Accounts have

8
Class TransactionRowSet

• The purpose of this class is to read the
  TRANSACTIONS table from the database, and create
  a set of ITransaction objects.
• Methods are provided to insert, update, or delete
  transactions.
• Transactions have

9
Database constraints

• In table ACCOUNT
• The TYPE_ID field must be a value in the TYPE_ID
  column of table ACCOUNT_TYPE.
• That is, it must be a value from 1 to 6.
• An account cannot be inserted if the above
  constraint is violated.
• An account cannot be deleted if there are
  transactions that are associated with the
  account.
• In table TRANSACTIONS
• The DEBIT_ACCOUNT_ID and CREDIT_ACCOUNT_ID fields
  must be a value in the ACCOUNT_ID column of table
  ACCOUNT_TYPE.
• A transaction cannot be inserted if the above
  constraint is violated.

10
Setting up the database

• We have to keep the database constraints in mind
  when using dbUnit to setup the database, or
  during test cases.
• When doing a dbUnit clean insert, all items in
  a table will be deleted. Unless all transactions
  are deleted at the same, the accounts cannot be
  deleted from table ACCOUNT.
• Create one database setup file that loads both
  tables.

11
Setting up the database

• Plan
• Create four accounts, with id values 1, 2, 3, 4.
• Create two transactions.
• First transaction debit account 1, credit
  account 3.
• Second transaction debit account 3, credit
  account 4.
• Account with id value 2 has no transactions
  referring to it, so it can be safely deleted.

12
dbUnit setup file

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltdatasetgt
• ltACCOUNT ACCOUNT_ID"1" ACCOUNT_NAME"A1"
  TYPE_ID"1"
• OPENING_BALANCE"567.89" /gt
• ltACCOUNT ACCOUNT_ID"2" ACCOUNT_NAME"A2"
  TYPE_ID"2"
• OPENING_BALANCE"12.34" /gt
• ltACCOUNT ACCOUNT_ID"3" ACCOUNT_NAME"A3"
  TYPE_ID"4"
• OPENING_BALANCE"1.11" /gt
• ltACCOUNT ACCOUNT_ID"4" ACCOUNT_NAME"A4"
  TYPE_ID"5"
• OPENING_BALANCE"2.22" /gt
• ltTRANSACTIONS TRANSACTION_ID"1"
  TRANSACTION_DATE"2007-07-08"
  AMOUNT"11.22"
  DEBIT_ACCOUNT_ID"1"
  CREDIT_ACCOUNT_ID"3" DETAILS"Hello" /gt
• ltTRANSACTIONS TRANSACTION_ID"2"
  TRANSACTION_DATE"2007-07-09"
  AMOUNT"22.44 DEBIT_ACCOUNT_ID"3"
  CREDIT_ACCOUNT_ID"4" DETAILS"Bye" /gt
• lt/datasetgt

13
XML file format

• Name of tag indicates into which table the row
  should be inserted.
• One file can load both tables.
• Tag attributes are of the form
• database_column_name?value?
• Points to watch for
• Be careful to match the database table name
  exactly ACCOUNT or TRANSACTIONS (all capitals,
  note S on latter table name).
• Be careful to match the database column name
  exactly (all capitals, underscores).

14
Locating the XML file

• The file name is accessed relative to the Java
  class path for the project.
• A file in the top level source folder is at
  location /
• A file in a package is in a sub-folder with the
  package name.
• For example, if you store the file dbsetup.xml in
  a package called accounting.db, then the file
  location, relative to the project classpath, is
  /accounting/db/dbsetup.xml

15
Loading the XML file

• The following Java code will read the XML file
  and create a dataset.
• String name ?/accounting/db/dbsetup.xml?
• URL url this.getClass().getResource(name)
• IDataSet dataSet new FlatXmlDataSet( url )
• The first line is the location of the XML file,
  relative to the Java class path for the project.
• The second line used to obtain a uniform resource
  locator (URL) for the file.
• The third line creates a dbUnit FlatXMLDataSet,
  containing the information for all the database
  tables, as read from the XML file..

16
Loading the database

• Once the XML file is read, the database must be
  loaded with the contents of the file.
• It is recommended to load the database in an
  _at_Before method in a test case.
• This will set up the database before every _at_Test
  method.
• A connection must be made for dbUnit to access
  the database.
• An object of type java.sql.Connection must be
  passed to dbUnit.
• Strategy get a Connection created by the
  accounting software.

17
Obtaining a connection from the application

• The connection is contained within the JDBC
  RowSet implementation.
• In the accounting software this is the class
  ca.uottawa.site.seg3203.MyJdbcRowSet.
• There is a method getConnection() in this class,
  but it is protected.
• The class accounting.db.AbstractDerbyRowSet
  extends this class, so it has access to
  getConnection()
• Strategy create a class TestConnection which
  extends AbstractDerbyRowSet, and has a public
  method that returns the Connection object.
• This class will just be used for testing.

18
Class TestConnection

• import java.sql.Connection
• public class TestConnection extends
  AbstractDerbyRowSet
• public TestConnection() throws Exception
• // The name of a database table has to be
  given
• // to the superclass AbstractDerbyRowSet
• super( "ACCOUNT" )
• public Connection getTheConnection()
• return super.getConnection( )

19
Code to insert data in database

• // Assumption data set has been created already
  (see slide 15)
• // Set up the connection for dbUnit
• Connection connection new TestConnection().ge
  tTheConnection( )
• IDatabaseConnection dbUnitConnection new
  DatabaseConnection( connection )
• // Load the data into the database, after erasing
  all data
• DatabaseOperation.CLEAN_INSERT.execute(
  dbUnitConnection,
  dataSet )
• // Be sure to close connection to release
  database resources
• dbUnitConnection.close( )

20
Test setup and teardown

• Variables in test class
• private IDAOFactory factory
• private IAccountRowSet ars
• private ITransactionRowSet trs
• Closing the connections
• _at_After
• public void tearDown( ) throws Exception
• ars.close( )
• trs.close( )

21
Summary The _at_Before method

• _at_Before
• public void setUp( ) throws Exception
• String name "/accounting/db/dbsetup.xml"
• URL url this.getClass( ).getResource( name
  )
• IDataSet dataSet new FlatXmlDataSet( url )
• Connection connection new
  TestConnection().getTheConnection( )
• IDatabaseConnection dbUnitConnection
  new DatabaseConnection( connection )
• DatabaseOperation.CLEAN_INSERT.execute(
  dbUnitConnection,
  dataSet )
• dbUnitConnection.close( )
• factory new DAOFactory( )
• ars factory.getAccountRowSet( )
• trs factory.getTransactionRowSet( )
• PersistentAccount.setDao( ars )
• PersistentTransaction.setDao( trs )

22
Check point

• At this point, you may want to create an empty
  test case, and see if the test case _at_Before
  method and _at_After methods work.
• This checks that the XML file is being read
  properly, and the database setup is performed.
• If you have the QuantumDB plug-in installed, you
  can connect to the database, view the tables, and
  see if they match the values in the XML file.

23
Account Table
24
Test the AccountRowSet constructor

• Now that we have set up the database to a known
  state, we can create some tests.
• The AccountRowSet constructor will read in the
  ACCOUNT database table, and create a set of
  IAccount objects.
• Test purpose see if the objects are created
  correctly.
• Test summary
• Create the account row set already done in
  _at_Before method using the factory.
• This reads the database table.
• See if the row set contains 4 objects.
• Get each object, and test that the attributes are
  as expected.

25
Test case, part 1

• IAccount acc
• int sizeExpected
• int sizeActual
• Integer idExpected
• Integer idActual
• String nameExpected
• String nameActual
• AccountType typeExpected
• AccountType typeActual
• Money openBalanceExpected
• Money openBalanceActual
• // Check if there are four accounts in the row
  set.
• sizeExpected 4
• sizeActual ars.getSize( )
• Assert.assertEquals( sizeExpected, sizeActual )

26
Test case, part 2

• // Get the first account
• acc ars.getAccountForId( 1 )
• // Check account attributes ID 1, name A1, type
  ASSET, open balance
• // 567.89
• idExpected 1
• idActual acc.getId( )
• Assert.assertEquals( idExpected, idActual )
• nameExpected "A1"
• nameActual acc.getName( )
• Assert.assertEquals( nameExpected, nameActual )
• typeExpected AccountType.ASSET // Corresponds
  to type 1 in database
• typeActual acc.getType( )
• Assert.assertEquals( typeExpected, typeActual )

27
Test case part 3

• At this point, you can repeat the previous checks
  for the remaining 3 objects.
• Question is this useful?

28
Updating an account

• Test purpose change the name of an account, and
  see if
• The account object is updated.
• The database table is updated.
• Test summary
• Get an account from the row set.
• Check its name.
• Change the accounts name using setName()
• Check that the object has stored the new name.
• Use dbUnit to verify that the database table also
  has the new name.

29
Test version 1 no database check

• _at_Test
• public void testUpdateAccount( ) throws Exception
• // Check that row set is not null
• Assert.assertNotNull( "Account row set is
  null", ars )
• // Get an account.
• IAccount acc ars.getAccountForId( 2 )
• // Check that we have the original account
  name.
• String nameExpected "A2"
• String nameActual acc.getName( )
• Assert.assertEquals( nameExpected, nameActual
  )
• // Change the name.
• acc.setName( "New name" )

30
Checking the database update

• For this, three steps are required.
• Load the expected data from an XML file, create a
  dataset from the file, and select the expected
  table.
• Load the actual data from a database connection,
  create a dataset from the connection, and select
  the actual table.
• Compare the expected and actual tables.

31
Table verification - expected

• The expected table needs to be loaded from an XML
  file.
• From the dataset, a table has to be identified,
  since the dataset can contain information for
  more than one table.
• String name "/accounting/db/updateAccountName.xm
  l"
• URL url this.getClass( ).getResource( name )
• IDataSet expectedDataSet new FlatXmlDataSet(
  url )
• ITable expectedTable expectedDataSet.getTabl
  e( "ACCOUNT" )

32
Table verification - actual

• The actual table needs to be loaded from a
  database connection.
• The connection can be obtained in the same manner
  as is used in the _at_Before method use the
  TestConnection class.
• From the connection, a data set can be made, and
  then the table can be selected.
• Connection connection new TestConnection(
  ).getTheConnection( )
• IDatabaseConnection dbUnitConnection new
  DatabaseConnection( connection )
• IDataSet databaseDataSet dbUnitConnection.cr
  eateDataSet( )
• ITable actualTable databaseDataSet.getTable(
  "ACCOUNT" )
• dbUnitConnection.close( ) // Be sure to do this!

33
Table verification - comparison

• dbUnit has a method called assertEquals, where
  two objects of type ITable are compared.
• NOTE the method is in the class Assertion, to
  distinguish it from the JUnit class Assert.
• Assertion.assertEquals( expectedTable,
  actualTable)

34
Testing a table model

• The table models are responsible for supplying
  data to be displayed in the GUI tables.
• The table model will be given a row and column
  index, and be asked to return an object that is
  to be rendered in that table cell.
• For each column, the table model must provide
• The name of the column (for the column heading
  shown in the table).
• The type of objects in the column, so that the
  appropriate class renderer can be chosen.

35
The account table model

• This is used to display the table of accounts.
• Columns
• 0 the account ID, type is Integer.
• 1 the account name, type is String.
• 2 the account type, type is String.
• 3 the opening balance, type is Money.

36
Test case

• Purpose Create an account table model based on
  the database row set. Verify that the first row
  in the table model matches the first row in the
  database.
• Test steps
• Create a table model and provide it with the
  account row set.
• Choose the table model item at row 0, column 0.
• Ensure that the object returned is an Integer.
• Check that the value is 1.
• Repeat the above three steps for columns 1
  (name), 2 (type), and 3 (opening balance).

37
Test case to check value at (0,0)

• _at_Test
• public void testAccountTableModel( )
• AccountTableModel atm new AccountTableModel(
  )
• atm.setDao( ars ) // ars is AccountRowSet
• // Row 0 is first account. Column 0 is ID.
• // Value should be Integer, with value 1.
• Object obj atm.getValueAt( 0, 0 )
• assertNotNull( obj )
• assertTrue( obj instanceof Integer ) //
  Verify return type
• Integer expectedInt new Integer( 1 )
• IntegeractualInt ( Integer ) obj
• Assert.assertEquals( expectedInt, actualInt )