A Java EE application

1
A Java EE application
Container
HTTP request
Servlet
HttpServletRequest
Browser
HttpServletResponse
HTTP response, containing HTML
Application server
DB
2
Test setup
Container
dbUnit
JUnit
Cactus
HTTP request
Servlet
HttpServletRequest
JUnit
HttpServletResponse
Application server Tomcat 6.0
httpUnit
HTTP response, containing HTML
MySQL 5.0 DB
3
Object Persistence
SQL
DB
Application objects

• Persistence moving objects to/from permanent
  storage.
• For this presentation data transfer and
  consistency between Java objects and a data base
  using the Structured Query Language (SQL).

4
For this presentation

• Some assumptions on the environment for the
  purposes of this presentation
• Code is based on the Java Enterprise Edition (EE)
  software development kit, version 1.4 or earlier
  (i.e. not Java EE 5)
• Use of a web application server that conforms to
  the Java EE specification
• Demonstration Uses Apache Tomcat v. 6.0
• Use of a relational database with a JDBC
  connector.
• Demonstration Uses MySQL v. 5.0.

5
Databases

• In production, the database is typically resident
  on a separate host from applications that use it.
• For a client application with a direct
  connection, two hosts are involved.
• For a web application, three hosts are involved
  client, web server, database host.
• For testing, additional options are
• Using an embedded in-memory database (such as
  Derby now included with Java 6)
• Mocking the database connection
• Mockrunner (mockrunner.sourceforge.net) has some
  predefined JDBC mock object classes

6
Database integration test purposes

• In a typical application, one should avoid
  testing (unless you are a product vendor of such
  a component)
• the data base management system (i.e. assume it
  does its job)
• the access protocol
• third party object persistence components
• One should test
• Configuration of parameters for connections (to
  be sure they work)
• If object persistence code is custom-built, it
  should be tested.
• Objective test your code, not the platform.

7
Questions for Testing

• How do we connect to the database?
• Does the client send the correct SQL commands to
  the database?
• Are the expected results returned?
• How do we set up the database...
• ... before any testing?
• ... for specific tests?

8
How many databases?

• Recommended to have four separate databases
• The production database. Never used for testing.
• The development database, where each developer /
  tester has their own area. Limited amounts of
  data, and tables are unstable. Most testing
  should use this one.
• A shared database with realistic amounts of data,
  to ensure no performance problems occur, and that
  there are no surprises at full scale.
• A deployment database which can be set up to swap
  in with the production database when ready to go.
  This is the one where pre-deployment tests
  should be run.
• At least the first two of these are necessary,
  but all four are preferable, especially for large
  projects.

9
Example

• Suppose that we have customers with first names,
  last names, and an id number.
• Java object
• public class Customer
• private int id
• private String firstName
• private String lastName
• // get and set methods for each of these.
• Data base table CUSTOMER has three columns
• ID the primary key, an auto-increment non-null
  column
• FIRSTNAME customers first name
• LASTNAME customers last name.

10
Database Table Definition
11
Customer Table contents
12
Class to be tested

• Test classes that implement an interface
  CustomerTable, which moves data to and from the
  database.
• public interface CustomerTable
• static final String TABLE_NAME "CUSTOMER"
• static final String ID_COL "ID"
• static final String FIRST_NAME_COL
  "FIRSTNAME"
• static final String LAST_NAME_COL
  "LASTNAME"
• MapltInteger, Customergt getAllCustomers( )
• Customer getCustomer( int id )
• boolean updateCustomer( Customer customer )
• boolean insertCustomer( Customer customer )
• boolean deleteCustomer( Customer customer )
• void close( )

13
Database Connections

• The connection to a database via a programming
  language is via a connection protocol API
  (application programming interface)
• ODBC open database connectivity
• JDBC specific version for Java
• Since databases are not compatible, there are
  normally specific drivers loaded in for each type
  of database (Oracle, MySQL, etc.)
• Specify a URL such as jdbcmysql//ste5007.site.uo
  ttawa.ca3306/db to connect, and then provide a
  userid and password.
• After logging in, SQL commands are issued to
  insert, view, update, or delete data.
• Results from the database are loaded into a
  O/JDBC object for use.

14
JDBC Database Connections
Table1
Schema1
Table2
JDBC
Driver
DB
Java Program
Table3
Schema2
Table4

• JDBC handles the Java interface, while the driver
  is customized for the particular database to be
  used.
• Aside from the connection protocol specifics, SQL
  has many variations among database vendors.

15
Primary JDBC classes

• Java classes used for JDBC
• DriverManager used to load the class which is
  the driver for a specific database.
• Connection the database connection interface.
• Statement An SQL statement to be executed on
  the connection.
• ResultSet The set of results that are returned
  by a query.
• The result set can be updated and saved back to
  the data base as necessary.
• The DataSource interface can be used for any
  object that can be used as a database connection.

16
JDBC data source

• public class MyJdbcDataSource implements
  DataSource
• private String url "jdbcmysql//localhost33
  06/csi5118"
• private String userid "csi5118"
• private String pwd "/ put password here
  /"
• public MyJdbcDataSource( ) throws
  ClassNotFoundException
• // Load driver class depends on database
  used
• Class.forName( "com.mysql.jdbc.Driver" )
• public Connection getConnection( ) throws
  SQLException
• return DriverManager.getConnection( url,
  userid, pwd )

17
Loading a ResultSet

• public class CustomerTableRowSet implements
  CustomerTable
• private MyJdbcDataSource datasource
• private Connection connection
• private ResultSet results
• public CustomerTableRowSet( )
• throws ClassNotFoundException,
  SQLException
• datasource new MyJdbcDataSource( )
• connection datasource.getConnection( )
• Statement stmt connection.createStatement(
  )
• String sqlCommand "SELECT FROM "
  TABLE_NAME
• results stmt.executeQuery( sqlCommand )

18
Using a JDBC Connection

• Two approaches can be used for the JDBC
  connection.
• Send SQL commands directly to the data base. In
  this case, the ResultSet is used only to transfer
  data to Java objects. SQL commands would also
  be used for database updates.
• The full range of SQL capabilities and variants
  for a particular DB are available for use.
• After an initial SQL query that obtains a result
  set, use the ResultSet commands to update values.
• Java code is decoupled from a particular vendors
  database, but available functions are more
  limited.

19
Update, using SQL command

• public boolean updateCustomer( Customer customer
  )
• try
• Statement stmt connection.createStatement(
  )
• StringBuffer sqlCommand new StringBuffer(
  "UPDATE " )
• sqlCommand.append( TABLE_NAME )
• sqlCommand.append( " SET " )
• sqlCommand.append( FIRST_NAME_COL )
• sqlCommand.append( "" )
• sqlCommand.append( encodeSQLString(
  customer.getFirstName( ) ) )
• sqlCommand.append( ", " )
• sqlCommand.append( LAST_NAME_COL )
• sqlCommand.append( "" )
• sqlCommand.append( encodeSQLString(
  customer.getLastName( ) ) )
• sqlCommand.append( " WHERE " )
• sqlCommand.append( ID_COL )
• sqlCommand.append( "" )
• sqlCommand.append( customer.getId( ) )

20
Update, using RowSet

• public boolean updateCustomer( Customer customer
  )
• boolean ok false
• ok this.setCursorAtCustomer( customer ) //
  not a JDBC method
• if ( ok )
• try
• results.updateString( FIRST_NAME_COL,
  customer.getFirstName( ) )
• results.updateString( LAST_NAME_COL,
  customer.getLastName( ) )
• results.updateRow( )
• catch ( SQLException e ) return false
• return ok

21
Potential test purposes

• Test creation of domain objects from a ResultSet.
• Verify the correct SQL commands are generated
  when accessing or updating data.
• Test the data base schema
• Tables / columns exist
• Primary key columns correct
• Foreign key constraints correct
• Triggers are correct
• Stored procedures are correct
• Access privileges are correct
• JDBC resources are cleaned up

22
Test case functions

• Setup Have the database loaded with specific
  data before the test starts.
• Test do something interesting with the database
• After a test check that the database has been
  updated as per the test purpose.
• Not necessary for read only tests.

23
Database setup strategies

• Use direct access provided by data base
  management system
• MySQL example
• Run an SQL script from the query browser.
• Use an IDE or Ant
• The Eclipse data tools platform provides a
  facility to connect to a data base, browse
  tables, and load data.
• Ant has an sql task that will connect to a
  database and run SQL commands.
• Use dbUnit
• dbUnit can load a database using an XML file.
• Use Java JDBC and execute SQL statements

24
Setup from Database Tools

• SQL script to delete rows from table, and then
  load two rows into a MySQL database

25
Setup from Eclipse

• Load data from text file.

26
Ant script to load data

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltproject basedir"." name"DBSetup"
  default"insertData"gt
• ltproperty name"sql.dir" location"basedir/d
  ata" /gt
• ltproperty file"mysql.properties" /gt
• lttarget name"insertData" description"Insert
  table data"gt
• ltsql
• driver"db.driver"
• url"db.url"
• userid"db.user"
• password"db.pwd"
• autocommit"yes"
• onerror"continue"
• caching"yes"
• gt
• lttransaction src"sql.dir/loadData.sql
  " /gt
• lt/sqlgt
• lt/targetgt
• lt/projectgt

27
dbUnit

• dbUnit is an open source tool that can help with
  database testing.
• http//dbunit.sourceforge.net
• Functions
• Use an XML file to store data to be loaded into a
  database table before running a test.
• Use an XML file as an expected table after a
  test.
• The expected table can omit columns such as
  auto-generated primary keys, and the actual table
  can be filtered based on the file.

28
Flat XML data set

• Each row of the table is specified by one tag,
  with the name of the table, and then values for
  the columns.
• lt?xml version"1.0" encoding"UTF-8"?gt
• ltdatasetgt
• ltCUSTOMER ID"1" FIRSTNAME"Alan"
  LASTNAME"Williams" /gt
• ltCUSTOMER ID"2" FIRSTNAME"John" LASTNAME"Doe"
  /gt
• lt/datasetgt

29
dbUnit Database operations

• The following operations are based on a data set
  loaded from the XML file
• UPDATE rows in the data set are updated,
  assuming they are already present in the table.
• INSERT rows in the data set are inserted if
  they are not in the table already
• DELETE rows in the data set are deleted from
  the table, but other rows are left untouched.
• DELETE_ALL all rows in the table are deleted.
• CLEAN_INSERT combines the DELETE_ALL and INSERT
  operations
• This is best for setting a table to have known
  data.

30
Using dbUnit for test setup

• _at_Before
• public void setUp( ) throws Exception
• // Set up the connection for dbUnit
• IDatabaseConnection connection new
  DatabaseConnection(
• new MyJdbcDataSource().getConnection() )
• // Locate data set file, and read it
• IDataSet dataSet new FlatXmlDataSet(
• this.getClass( ).getResource(
  "/customer/data.xml" ) )
• // Load the data into the database, after
  erasing all data
• DatabaseOperation.CLEAN_INSERT.execute(connecti
  on,dataSet)
• connection.close( )

31
Using dbUnit to verify data

• _at_Test
• public void testDeleteCustomer( ) throws
  Exception
• MapltInteger, Customergt map
  table.getAllCustomers( )
• Customer deleteMe map.get( 1 )
• table.deleteCustomer( deleteMe )
• // Load data set with expected table values
• IDataSet expectedDataSet
• expectedDataSet new FlatXmlDataSet(
• this.getClass( ).getResource(
  "/customer/afterDelete.xml" ) )
• ITable expectedTable expectedDataSet.getTable
  (CustomerTable.TABLE_NAME)
• // Get actual database table values
• IDataSet databaseDataSet new
  DatabaseConnection(
• dataSource.getConnection( ) ).createDataSet( )
• ITable actualTable databaseDataSet.getTable(C
  ustomerTable.TABLE_NAME)
• Assertion.assertEquals( expectedTable,
  actualTable )

32
Web-based applications

• Application server functions
• Receive HTTP request
• Look up and connect to database.
• Send query to database and obtain ResultSet
• Construct HTTP response
• Normally, a web page that includes data from the
  ResultSet.

Client
Application server
Database
33
The Servlet Environment
Container
HTTP request
Servlet
HttpServletRequest
Browser
HttpServletResponse
HTTP response, containing HTML
Application server
DB
34
Resource Location on Servers

• To find resources (such as a database) on a
  server, a naming service can be used to associate
  a resource name with a specific location.
• A directory service an extension of a naming
  service can be used to locate resources based
  on attributes.
• To use the directory or naming service, an access
  protocol must be used.
• General example Lightweight Directory Access
  Protocol (LDAP)
• Java-specific example Java Naming Directory
  Interface (JNDI)

35
Data sources

• A generic interface DataSource can be used for
  obtaining a connection to a resource
• One implementation a JDBC connection
• JNDI returns a DataSource implementation object
  as a result of locating a requested resource.

36
Specifying an Application Server Resource

• Web application that specifies use of a resource
  on a server, contained in META-INF/context.xml
• lt?xml version"1.0" encoding"UTF-8"?gt
• ltContext path"/CustomerApp" docBase"CustomerApp"
  
• debug"5" reloadable"true"
  crossContext"true"gt
• ltResource name"jdbc/seg3203"
• auth"Container"
• type"javax.sql.DataSource"
• maxActive"100 "
• maxIdle"30"
• maxWait"10000"
• username"seg3203"
• password"(insert password)"
• driverClassName"com.mysql.jdbc.Driver
  "
• url"jdbcmysql//localhost3306/seg3203?autoRecon
  necttrue"/gt
• lt/Contextgt

37
Code in Servlet to access JNDI resource

• public class MyJndiDataSource implements
  DataSource
• // The jndiName string is server-dependent.
  This works for
• // Apache Tomcat 5.5 and 6.0
• private String jndiName "javacomp/env/jdbc/s
  eg3203"
• private DataSource dataSource
• public MyJndiDataSource( ) throws
  NamingException
• InitialContext context new
  InitialContext()
• dataSource (DataSource) context.lookup(
  jndiName )
• public Connection getConnection( ) throws
  SQLException
• return dataSource.getConnection( )

38
Testing Database Code in ServletUse Cactus for
In-Container Testing
Container
JUnit Test Case
Servlet
JUnit Test Proxy
Application server
Test DB
39
In-Container Servlet-Database Testing

• Essentially, there is no difference between a
  client-database test case, and a servlet-database
  test case, except
• JNDI is used to look up the data base at the
  server.
• The test case must inherit from ServletTestCase,
  and be run by Cactus.
• dbUnit can still be used for setting up the
  database from the server.

40
Constructing a web page response

• The simplest approach is to have the servlet
  write HTML code to the responses output stream.
• More advanced approach use Java Server Pages
  (JSP), or the Java Server Faces (JSF) framework

41
Example CustomerServlet

• protected void doGet( HttpServletRequest request,
• HttpServletResponse
  response )
• throws ServletException, IOException
• // Get data from database
• ListltCustomergt customers getCustomerData(
  request )
• // Create HTML response
• response.setContentType( "text/html" )
• PrintWriter pw response.getWriter( )
• pw.println( "lthtmlgtltheadgtlttitlegtCustomer
  Listlt/titlegtlt/headgtltbodygt" )
• pw.println( "lth2gtCustomer listlt/h2gt" )
• pw.println( "lttable border\"1\"gt" )
• pw.println( "lttrgtltthgtIDlt/thgtltthgtFirst
  namelt/thgtltthgtLast namelt/thgtlt/trgt" )
• for ( Customer aCustomer customers )
• pw.println( "lttrgt" )
• pw.println( "lttdgt" aCustomer.getId( )
  "lt/tdgt" )
• pw.println( "lttdgt" aCustomer.getFirstName(
  ) "lt/tdgt" )
• pw.println( "lttdgt" aCustomer.getLastName(
  ) "lt/tdgt" )

42
Result from running Servlet
43
HTML returned by Servlet
44
Checking a Web Page

• The next step is to verify a response back at the
  client, by ensuring that a web page with the
  expected information is returned.
• The HttpUnit tool can be integrated with Cactus
  to check results returned by an application
  server.
• Open source tool at
• http//httpunit.sourceforge.net
• Be careful when using Cactus and HttpUnit
  together, as they both have WebResponse classes
• Cactus org.apache.cactus.WebResponse
• HttpUnit com.meterware.httpunit.WebResponse

45
Items that can be located

• Links in web page
• A link has a click() method that can be used to
  follow the link
• Tables
• Determine number of rows and columns
• Obtain table contents by position
• Forms
• Locate form items, and set/check their values
• Click buttons
• Others
• Images
• Frames
• Cookies
• HTML elements

46
Web response checking with Cactus

• Checking the web page response is done in the
  method endXXX, where testXXX is the test method
  that is run.
• endXXX is run at the client, so it has the HTTP
  response sent by the server.

47
Test for doGet for Customer Servlet

• Aside from exceptions that could be thrown, there
  is no output checking in the test case.
• The response is verified by endDoGet()
• _at_Test
• public void testDoGet( ) throws Exception
• CustomerServlet servlet
• servlet new CustomerServlet( )
• servlet.doGet( request, response )

48
Checking the Response with HttpUnit

• public void endDoGet( WebResponse theResponse )
• throws SAXException
• assertTrue( theResponse.isHTML( ) )
• int numTablesExpected 1
• int numTablesActual theResponse.getTables(
  ).length
• assertEquals( numTablesExpected,
  numTablesActual )
• WebTable theTable theResponse.getTables(
  )0
• // Rows can be similarly checked
• int colsExpected 3
• int colsActual theTable.getColumnCount( )
• assertEquals( colsExpected, colsActual )
• // Example of checking a table cell
• String expected "ID"
• String actual theTable.getCellAsText( 0, 0
  )

49
Running the End-to-End Test
50
Summary

• Tools needed to run the end-to-end test
• Eclipse Integrated Development Environment
• Apache Tomcat Java application server
• MySQL Database
• JUnit For running test cases
• Cactus In-container testing
• dbUnit For database test setup
• httpUnit For checking the web page