Object Persistence

1
Object Persistence
DB
Application objects

• Persistence moving objects to/from permanent
  storage.
• For this presentation data transfer and
  consistency between Java objects and a data base.

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)
• Using mock objects for the database connection.
• Mockrunner (mockrunner.sourceforge.net) provides
  some predefined mock objects for databases.

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 given names,
  family names, and an id number.
• Java object
• public class Customer
• private int id
• private String givenName
• private String familyName
• // get and set methods for each of these.
• Data base table CUSTOMER has three columns
• CUSTOMER_ID the primary key, a non-null integer
  column that is automatically incremented as table
  rows are added
• GIVEN_NAME customers given name, a string
• FAMILY_NAME customers family name, a string

6
Database Table Definition
7
Example with references

• Databases and objects use fundamentally different
  methods for establishing associations.
• Java object
• public class Order
• private int id
• private Customer myCustomer
• private String details
• // etc.
• Data base table ORDER has three columns
• ORDER_ID the primary key, a non-null integer
  column that is automatically incremented as table
  rows are added
• CUSTOMER_ID contains a value from the
  CUSTOMER_ID column in the CUSTOMER table
• DETAILS information about the order

8
Create a table with a relationship
9
Object-Relational Mapping (ORM)
SQL
DB
Application objects
ORM Manager

• Objects use references (pointers) to establish
  associations.
• Database tables use foreign keys.
• The objective of an object-relational mapping
  component is to do the translation between
  objects and database tables
• Two major tasks
• Object attributes mapped to/from table columns
• Object references mapped to/from foreign keys

10
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 "CUSTOMER_ID"
• static final String GIVEN_NAME_COL
  "GIVEN_NAME"
• static final String FAMILY_NAME_COL
  "FAMILY_NAME"
• MapltInteger, Customergt getAllCustomers( )
• Customer getCustomer( int id )
• boolean updateCustomer( Customer customer )
• boolean insertCustomer( Customer customer )
• boolean deleteCustomer( Customer customer )
• void close( )

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

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

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

14
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 MySQL
• Driver class is in the mysql-connector-java.jar
  file, and this archive must be on the class path.
• Class name is com.mysql.jdbc.ClientDriver
• To load the driver in Java, include the following
  statements
• String driverClassName "com.mysql.jdbc.ClientDri
  ver"
• Class.forName( driverClassName )

15
Setting up the JDBC connection

• Step 2 Setup the URL for the database
• format
• jdbcdb_vendor//hostport/location
• For MySQL, running as a client on the same
  computer using the default MySQL port, the first
  part is
• jdbcmysql//localhost3306/database

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

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

18
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

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

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

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

22
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

23
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

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

25
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

26
Setup from Database Tools

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

27
Setup from Eclipse

• Load data from text file.

28
Ant script to load data

• lt?xml version"1.0" encoding"UTF-8"?gt
• ltproject basedir"." name"Database Demo"
  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

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

30
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 CUSTOMER_ID"1" GIVEN_NAME"Alan"
  FAMILY_NAME"Williams" /gt
• ltCUSTOMER CUSTOMER_ID"2" GIVEN_NAMEJohn"
  FAMILY_NAME"Doe" /gt
• lt/datasetgt

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

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