Data Access Layer

1
Data Access Layer

• WSEP 06
• Elaborations 1 ? 2 ? 3 ? 4

2
The problem Introducing Persistency
Activity Marking all persistent objects within
the domain model. Why?
3
The problem Persistent Objects

• Persistency ? Saving an object in a durable way,
  making it possible to access an object even after
  the object has been removed from memory.
• Methods
• Simple data files serialization (!searching)
• XML - (!searching)
• Object databases (!performance)
• Printing and Scanning using OCR methods.
• Relational databases (widely used)

4
The problem Introducing Persistency
Activity TradingSystem, Signal ? Persistent
5
Layered architecture
UI Layer
TSWindow
Application Layer
ProcessAddTSSessionFacade
ProcessSubscribeToTSSessionFacade
Domain Layer
ltltfacadegtgt Dito
TS
Signal
Portfolio
6
The problem
UI Layer
Application Layer
Domain Layer
Technical space 1 - OO
ltltfacadegtgt Dito
TS
Signal
Portfolio
Mismatch between layers Different perception
of reality Different languages to manipulate
entities
?
Technical space 2 SQL, XQL, Self-QL
Storage
Relational
XML
Files
7
Solution Steps 1. Schema mapping
Mapping persistent objects schema ? relational
database schema
Class ? Table Attribute ? Column Relations 11
? PKFK 1M ? PKFK MN ? Table
Example
8
Solution Steps 2. Object Identifier

• Related Problems
• Consistency between object state in-memory and
  in-storage.
• Unexpected object duplication.

Object Identifier Assigning and object
identifier (OID) to each record and object. -
Usually an alphanumeric value. - Unique to a
specific object. Implementation - Domain layer
OID interface / Encapsulating class - Storage
Primary Key - fixed length character value.
9
Introducing Data-Access Layer
UI Layer
Application Layer
Domain Layer
Technical space 1 - OO
ltltfacadegtgt Dito
TS
Signal
Portfolio
Data Access Layer
Technical space 2 SQL, XQL, Self-QL
Storage
Relational
XML
Files
10
Data Access Layer 3. Accessing a Persistent
Service with a Facade

• Introduce a facade for the services provided by
  the Data-Access layer.
• get (OID, Class) Object
• put (OID, Object)
• Example
• OID oid new OID(AB123)
• TradingSystem ts (TradingSystem)
  PersistenceFacade.getInstance() .get(oid,Trad
  ingSystem.class)

11
Data Access Layer 3a. Direct Mapping Active
Record

• An object that wraps a row in a database table or
  view, encapsulates the database access, and add
  domain logic to the data M.Fowler
• Classes match very closely the record structure
  of an underlying database.
• Appropriate when the domain logic is simple.
• Couples the object design to the database design.
• Complex responsibilities to domain objects
  violation of High Cohesion and maintaining a
  separation of concerns.

12
Data Access Layer 3b. Indirect Mapping -
Introducing DataMappers

• A layer of Mappers that moves data between
  objects and a database while keeping them
  independent of each other and the mapper itself
  M.Fowler
• The PersistenceFacade delegates requests to
  DataMappers
• The DataMappers responsible for
  materialization/dematerialization of object
  between memory and the persistent layer and for
  object caching.
• A different mapper class is defined for each
  persistent object class.

13
Data Access Layer 3b. Indirect Mapping -
Introducing DataMappers
Class PersistentFacade // Public Object get
(OID oid, Class persistentClass) IMapper mapper
(IMapper) mappers.get(persistentClass) return
mapper.get(oid) //
14
Preventing duplicationsIdentity Map

• Ensures that each object gets loaded only once by
  keeping every loaded object in a map. Looks up
  objects using the map when referring to them
  M.Fowler

15
Using the Builder Pattern for SQL Generation

• Separate the construction of a complex object
  from its representation so that the same
  construction process can create different
  representations.GOF
• 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

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

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

18
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()
19
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
20
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 ""
21
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 )

22
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()
23
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(
',' )
24
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()
25
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)
_
26
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

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

28
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

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

30
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 )
31
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
DatabaseStrategy(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 )
32
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()
33
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()