Persistence Related Design Patterns

1
Persistence Related Design Patterns

• Lior Limonadlimond_at_bgumail.bgu.ac.il
• ISE DepartmentBen Gurion University

2
Object Oriented Application
Class Diagram
3
Object Oriented Application
Power Outage
Class Diagram
4
Object Oriented Application
Class Diagram
5
Object Oriented Application
Class Diagram
6
What is persistency?

• Persistence means saving an object in a durable
  storage, in order to save their state through the
  life time of the application.
• Durable storage means any medium that enables
  retrieving the saved data back whenever it is
  needed.
• Disk file using XML or Java serialization.
• Any other self created data file formats.
• Object Database.
• Printing and Scanning using OCR methods.
• Relational Database widely used.

7
Why patterns are needed?

• Mismatch between layers
• Different perception of reality
• Different languages to manipulate entities

Application (Object Oriented)
OO Programming Language
Persistent Layer
Relational DB
SQL
8
Using the Builder Pattern for SQL Generation

• Most object oriented developers are less familiar
  with SQL statements.
• SQL statements embedded within the code are hard
  to maintain and often introduce a lot of errors
  into the code.
• Keeping the same structure of SQL statements may
  improve performance of most DBMS that perform
  tuning and optimizations.

9
Using the Builder Pattern for SQL Generation

• Builder Pattern useful when having several
  complex objects that follow similar steps in
  their construction.
• Common SQL Statements
• UPDATE command,Table,columns list
  data,criteria
• INSERT command,Table,columns list data
• DELETE command,Table,criteria

10
Using the Builder Pattern for SQL Generation

• Construction parts are part of the SQLBuilder
  object.
• The SQLDirector is responsible for executing the
  construction parts on the builder.

11
Using the Builder Pattern for SQL Generation

• Director of the Construction
• The Director may be implemented as a simple
  method of the builder

• public class SQLDirector
• public static String buildSQL( SQLBuilder
  builder )
• StringBuffer buffer new StringBuffer()
• buffer.append( builder.getCommand() )
• buffer.append( builder.getTable() )
• buffer.append( builder.getWhat() )
• buffer.append( builder.getCriteria() )
• return buffer.toString()

12
Using the Builder Pattern for SQL Generation

• The SQLBuilders Superclass
• An interface would be suitable as well
• Using abstract class let us share code across
  builders like the getTable() that always returns
  the table name

public abstract class SQLBuilder public
abstract String getCommand() public abstract
String getTable() public abstract String
getWhat() public abstract String
getCriteria()
13
Using the Builder Pattern for SQL Generation

• InsertBuilder part 1 of 6

public class InsertBuilder extends SQLBuilder
private String table private String
criteria public void setTable( String table )
this.table table public String
getTable() return table
14
Using the Builder Pattern for SQL Generation

• InsertBuilder part 2 of 6

public class InsertBuilder extends SQLBuilder
private String table private String
criteria public String getCommand()
return "INSERT INTO " public String
getCriteria() return ""
15
Using the Builder Pattern for SQL Generation

• InsertBuilder part 3 of 6

public class InsertBuilder extends SQLBuilder
private Map columnsAndData new HashMap()
public void addColumnAndData( String columnName,
Object value ) if( value ! null )
columnsAndData.put( columnName, value )

16
Using the Builder Pattern for SQL Generation

• InsertBuilder part 4 of 6
• getWhat ?(column1,column2,column3) VALUES
  (value1,value2,value3)

public String getWhat() StringBuffer
columns new StringBuffer() StringBuffer
values new StringBuffer() StringBuffer
what new StringBuffer()
17
Using the Builder Pattern for SQL Generation

• InsertBuilder part 5 of 6

public String getWhat() String
columnName null Iterator iter
columnsAndData.keySet().iterator() while(
iter.hasNext() ) columnName (String)
iter.next() columns.append( columnName )
values.append( columnsAndData.get(
columnName ) ) if( iter.hasNext() )
columns.append( ',' ) values.append(
',' )
18
Using the Builder Pattern for SQL Generation

• InsertBuilder part 6 of 6

public String getWhat() what.append( "
(" ) what.append( columns )
what.append( ") VALUES (" ) what.append(
values ) what.append( ") " ) return
what.toString()
19
Using the Builder Pattern for SQL Generation

• BuilderMain - SQL Statements are now Objects!

public class BuilderMain public static void
main( String args ) InsertBuilder builder
new InsertBuilder() builder.setTable(
"employees" ) builder.addColumnAndData(
"employee_id", new Integer( 221 ) )
builder.addColumnAndData( "first_name", "'Shane'"
) builder.addColumnAndData( "last_name",
"'Grinnell'" ) builder.addColumnAndData(
"email", "'al_at_yahoo.com'" ) String sql
SQLDirector.buildSQL( builder )
System.out.println( sql )
INSERT INTO employees (first_name, last_name,
email) VALUES(Shane,Grinnell,al_at_yahoo.com)
_
20
Using the Strategy Pattern to Handle Different
Databases

• Richard Sperko I once had a very smart
  Smalltalk programmer suggest to me that that you
  could write code without ever using an if
  statement...because anytime you implement a
  switch statement, there is a high probability
  that another part of the code will need to know
  the difference between the different cases as
  well

21
Using the Strategy Pattern to Handle Different
Databases

• Strategy pattern takes all behavior that differs
  between the databases and put it into one object
  that you can switch in and out, depending on the
  database you are working with.

22
Using the Strategy Pattern to Handle Different
Databases

• The JDBCHelper class does not need to know which
  database is being used to do the work.
• The JDBCHelper may also be used to handle
  different formats of data, e.g.
• MSAccess date format - 10-13-1970 000000
• SQL Server date format 1970-10-13 000000

23
Using the Strategy Pattern to Handle Different
Databases

• JDBCHelper usage example -

• public static void main( String args )
• Employee employee buildTestEmployee()
• Connection con null
• try
• DatabaseStrategy strategynew
  SQLServerStrategy( "localhost", "chapter06",
  "root", "password" )
• JDBCHelper helper new JDBCHelper(strategy)
  
• InsertBuilder builder new
  InsertBuilder()
• builder.setTable( "employees" )
• builder.addColumnAndData( "oid",
• helper.format( employee.getGuid() ) )
• builder.addColumnAndData( "first_name",
• helper.format( employee.getFirstName()
  ) )
• builder.addColumnAndData( "last_name",
• helper.format( employee.getLastName() )
  )
• builder.addColumnAndData( "employed",
• helper.format( employee.getEmployed() )
  )
• builder.addColumnAndData( "date_of_birth",
• helper.format( employee.getDateOfBirth()
  ) )

• con helper.getConnection()
• Statement statement con.createStatement()
  
• System.out.println( SQLDirector.buildSQL(
  builder ) )
• statement.executeUpdate( SQLDirector.buildSQ
  L( builder ) )
• catch( Exception e )
• e.printStackTrace()
• finally
• if( con ! null )
• try
• con.close()
• catch( SQLException e )

24
Using the Strategy Pattern to Handle Different
Databases

• JDBCHelper

public class JDBCHelper private
DatabaseStrategy strategy public JDBCHelper(
DatabaseStrategy strategy ) throws
ClassNotFoundException this.strategy
strategy strategy.loadDriver()
public Connection getConnection() throws
SQLException String url
strategy.generateURL() return
DriverManager.getConnection( url ) public
String format( Object value ) return
strategy.format( value )
25
Using the Strategy Pattern to Handle Different
Databases

• DatabaseStrategy (abstract class/interface)

public abstract class DatabaseStrategy
protected String server protected Sttring
databaseName protected String user
protected String passwd public
DatabaseTrategy(String server, String
databaseName, String user, String passwd)
this.databaseNamedatabaseName
this.serverserver this.useruser
this.passwdpasswd public abstract
void loadDriver() throws ClassNotFoundException
public abstract String generateURL() public
abstract String format( Object value )
26
Using the Strategy Pattern to Handle Different
Databases

• MSAccessStrategy example -

public class MSAccessStrategy extends
DatabaseStrategy private SimpleDateFormat
dateFormat new SimpleDateFormat(
"MM-dd-yyyy HHmmss" ) public void
loadDriver() throws ClassNotFoundException
Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" )
public String generateURL() return
"jdbcodbc" database public String
format( Object value ) ... return
value.toString()
27
Using the Strategy Pattern to Handle Different
Databases

• SQLServerStrategy example -

public class SQLServerStrategy extends
DatabaseStrategy public void loadDriver()
throws ClassNotFoundException
Class.forName( "com.microsoft.jdbc.sqlserver.SQLSe
rverDriver" ) public String generateURL(
) StringBuffer buffer new StringBuffer(
"jdbcmicrosoftsqlserver//" )
buffer.append( server ) buffer.append(
"1433databasename" ) buffer.append(
database ) buffer.append( "user" )
buffer.append( user ) if( passwd ! null
passwd.length() ! 0 ) buffer.append(
"password" ) buffer.append( passwd )
System.out.println( buffer.toString() )
return buffer.toString()
28
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• Facade putting a class in front of the library
• Simplifies the relationship between objects.
• Decouple consumer classes from the internals of
  the library.

29
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• Through the facade, we can execute a query and
  iterate through the objects stored in the
  database.

30
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• QueryFacade usage example -

• public class FacadeMain
• public static void main( String args )
• QueryFacade facade new QueryFacade( new
  EmployeeResultSetMap() )
• try
• Iterator iterator facade.execute( "SELECT
  FROM EMPLOYEES" )
• Employee employee null
• while( iterator.hasNext() )
• employee (Employee) iterator.next()
• System.out.println( employee.getGuid() )
• catch( SQLException e )
• e.printStackTrace()
• finally
• facade.close()

31
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• QueryFacade part 1 of 3

public class QueryFacade private Connection
connection private ResultSetMap map private
Statement statement private ResultSet
resultSet public QueryFacade( ResultSetMap
map ) this.map map try
Class.forName( "com.mysql.jdbc.Driver" )
catch( ClassNotFoundException e )
e.printStackTrace() public Iterator
execute( String query ) throws SQLException
connection getConnection() statement
connection.createStatement() resultSet
statement.executeQuery(query) return new
FacadeIterator()
32
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• QueryFacade part 2 of 3

public void close() if( resultSet ! null
) try resultSet.close()
catch( SQLException e ) if(
statement ! null ) try
statement.close() catch(
SQLException e ) if( connection !
null ) try connection.close()
catch( SQLException e )
private Connection getConnection() throws
SQLException String url
"jdbcmysql//localhost3306/chapter06?userrootp
asswordpassword" return DriverManager.getCon
nection( url )
33
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• FacadeIterator
• Inner class of the QueryFacade
• Acts as an adapter
• Wraps the result set and make it appear as an
  iterator

34
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• QueryFacade part 3 of 3

public Object next() Object current
next try if( resultSet.next() )
next map.convert( resultSet )
else next null
return current catch(
SQLException e )
private class FacadeIterator implements Iterator
private Object next FacadeIterator()
next() public boolean
hasNext() return next ! null
35
Using the Facade Pattern to Simplify Queries ( a
bit of Decoration)

• ResultSet mapper interface

public interface ResultSetMap public Object
convert( ResultSet resultSet ) throws
SQLException
36

• As you consider using the patterns, never
  forget theyre a starting point, not a final
  destination. Theres no way that any author can
  see all the many variations that software
  projects have. Martin Fowler

37
The Book - Java Persistence for Relational
Databases

• Author Richard Sperko, architect level consultant
  for Centare Group, Ltd. Has 11 years of software
  development experience and extensive knowledge in
  Java/J2EE and other Object Oriented technologies.
  Certified as SUN Microsystems java developer.
• Foreword by John Carnell, Principal Architect,
  NetChange, LLC
• a! APRESS www.apress.com , 2003