Object Persistence

1
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).

2
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)

3
Database 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.

4
Questions for Testing

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

5
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, a non-null integer column
  that is automatically incremented as table rows
  are added
• FIRSTNAME customers first name, a string
• LASTNAME customers last name, a string

6
Database Table Definition
7
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( )

8
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, Derby, DB/2, etc.)
• Specify a URL such as jdbcderby//ste5007.site.uo
  ttawa.ca1527/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.

9
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.

10
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.
• If supported by the driver, 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.

11
Setting up the JDBC connection

• Step 1 Load the appropriate driver class.
• Normally, the driver is provided by the database
  vendor, and is specific to a particular database
  and version.
• The class needs to be on the class path.
• For Derby
• Driver class is in the derbyclient.jar file, and
  this archive must be on the class path.
• Class name is org.apache.derby.jdbc.ClientDriver
• To load the driver in Java, include the following
  statements
• String driver "org.apache.derby.jdbc.ClientDrive
  r"
• Class.forName( driver )

12
Setting up the JDBC connection

• Step 2 Setup the URL for the database
• format
• jdbcdb_vendor//hostport/location
• For Derby, running as a client on the same
  computer using the default Derby port, the first
  part is
• jdbcderby//localhost1527/location
• The location can be specified exactly as a folder
  name
• jdbcderby//localhost1527/C\\DerbyDatabases\\s
  eg3203
• A default database location folder is also set by
  the Derby server. Locations relative to that
  folder can be specified directly
• jdbcderby//localhost1527/seg3203

13
Setting up the JDBC connection

• Step 3 Connect to the database.
• Ask the DriverManager for a connection.
• java.sql.Connection connection
• connection
• DriverManager.getConnection( url, username,
  password )
• Step 4 Create a statement from the connection.
• The parameters set properties of ResultSet
  objects produced by executing the statement.
• java.sql.Statement stmt
• stmt connection.createStatement(
  ResultSet.TYPE_SCROLL_SENSITIVE,
• ResultSet.CONCUR_UPDA
  TABLE,
• ResultSet.HOLD_CURSOR
  S_OVER_COMMIT )

14
Using the JDBC connection

• Step 5 Create an SQL statement to be executed.
• The return value is a ResultSet object containing
  rows of the database table that are retrieved
  from the database.
• String sqlCommand "SELECT FROM CUSTOMER"
• Java.sql.ResultSet results
• results stmt.executeQuery( sqlCommand )

15
JDBC data source

• public class JdbcDataSource implements DataSource
• private String url
• private String userid DBProperties.getString(
  "DB.USERID" )
• private String pwd DBProperties.getString(
  "DB.PASSWORD" )
• public JdbcDataSource( ) throws
  ClassNotFoundException
• Class.forName( DBProperties.getString(
  "DB.DRIVER_CLASS" ) )
• String vendor DBProperties.getString(
  "DB.VENDOR" )
• String host DBProperties.getString(
  "DB.HOST" )
• String port DBProperties.getString(
  "DB.PORT" )
• String location DBProperties.getString(
  "DB.DBNAME" )
• url "jdbc" vendor "//" host ""
  port
• "/" location
• public Connection getConnection( String
  username, String password )
• throws SQLException

16
Loading a ResultSet

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

17
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.

18
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( ) )

19
Update, using ResultSet

• 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

20
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

21
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.

22
Database setup strategies

• Use direct access provided by data base
  management system
• Derby example
• Run an SQL script using the ij console tool.
• Use an IDE or Ant
• Various Eclipse plug-ins (Data Tools Platform,
  Quantum, ...) are available 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

23
Setup from Database Tools

• Interactive session using ij console tool for
  Derby
• ij version 10.2
• ijgt connect 'jdbcderby//localhost1527/C\\Derby
  Databases\\seg3203userlab3passwordlab3'
• ijgt delete from customer
• 2 rows inserted/updated/deleted
• ijgt insert into customer ( firstname, lastname )
  values ( 'Alan', 'Williams' )
• 1 row inserted/updated/deleted
• ijgt insert into customer ( firstname, lastname )
  values ( 'John', 'Doe' )
• 1 row inserted/updated/deleted
• ijgt select from customer
• ID FIRSTNAME
  LASTNAME
• --------------------------------------------------
  -----------------------
• 1 Alan
  Williams
• 2 John
  Doe
• 2 rows selected

24
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
  data into the customer database table."gt
• ltsql
• driver"DB.DRIVER_CLASS"
• url"DB.URL"
• userid"DB.USERID"
• password"DB.PASSWORD"
• autocommit"yes"
• onerror"continue"
• caching"yes"
• gt
• lttransaction src"sql.dir/loadData.sql" /gt
• lt/sqlgt
• lt/targetgt
• lt/projectgt

25
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.

26
Flat XML data set

• Each row of a 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
• Several tables can be specified within one
  dataset.

27
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.

28
Using dbUnit for test setup

• _at_Before
• public void setUp( ) throws Exception
• // Set up the connection for dbUnit
• IDatabaseConnection connection new
  DatabaseConnection(
• new JdbcDataSource().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( )

29
Using dbUnit to verify data

• _at_Test
• public void testDeleteCustomer( ) throws
  Exception
• // Database set up and loaded in _at_Before
  method. Delete a customer.
• MapltInteger, Customergt map
  table.getAllCustomers( )
• Customer deleteMe map.get( 1 )
• table.deleteCustomer( deleteMe )
• // Load data set with expected table values
• IDataSet expectedDataSet new FlatXmlDataSet(
  
• this.getClass( ).getResource(
  "/customer/afterDelete.xml" ) )
• ITable expectedTable expectedDataSet.getTable
  (CustomerTable.TABLE_NAME)
• // Get actual database table values after
  deletion
• IDataSet databaseDataSet new
  DatabaseConnection(
• dataSource.getConnection( ) ).createDataSet( )
• ITable actualTable databaseDataSet.getTable(C
  ustomerTable.TABLE_NAME)
• // Compare expected and actual data sets.
  dbUnit assertEquals() used.