Object Relation Mapping From JDBC to Persistent OO Ing. Vincenzo Curci

1. Object Relation Mapping From JDBC to Persistent OOIng. Vincenzo Curci Subjects The JDBC approach Object relational mapping (ORM) concepts The ORM Persistence Layers approach

2. JDBC Java Database Connectivity JDBC is a portable SQL CLI written in Java. Versions of JDBC  JDBC 1.x JDBC 2.x JDBC 3.0 (In JDK 1.4) JDBC 4.0

3. JDBC Architecture JDBC driver provides connections to database via drivers java.sql.* is mainly interfaces for JDBC Drivers The driver for the database determines the actual functionality

4. JDBC Classic usage import java.sql.*; public class SampleConnection { public static void main (String args[]) throws Exception { String dbUrl = "jdbc:mysql://rugby.sdsu.edu:8777/test"; String user = "whitney"; String password = "mylittleSecret"; System.out.println("Load Driver!"); Class.forName("com.mysql.jdbc.Driver"); Connection conn; conn = DriverManager.getConnection( dbUrl, user, password); Statement getTables = conn.createStatement(); ResultSet tableList = getTables.executeQuery("SELECT * FROM name"); while (tableList.next() ) System.out.println("Last Name: " + tableList.getString(1) + '\t' + "First Name: " + tableList.getString( "first_name")); conn.close(); } }

5. JDBC Queries Connection conn = DriverManager.getConnection(database, user, password); Statement namesTable = conn.createStatement(); ResultSet namesFound = namesTable.executeQuery("SELECT * FROM name"); executeUpdate Use for INSERT, UPDATE, DELETE or SQL that return nothing executeQuery Use for SQL (SELECT) that return a result set execute Use for SQL that return multiple result sets Uncommon

6. JDBC PreparedStatement Example import java.sql.*; import java.io.*; Connection conn; conn = DriverManager.getConnection( dbUrl, user, password); String findPerson = "SELECT * FROM name WHERE last_name = ?"; PreparedStatement find = conn.prepareStatement( findPerson ); find.setObject( 1, "whitney" ); ResultSet person = find.executeQuery(); while (person.next() ) System.out.println("Last Name: " + person.getObject(1) + '\t' + "First Name: " + person.getObject( "first_name")); find.clearParameters(); find.setObject( 1, "olson" ); person = find.executeQuery(); while (person.next() ) System.out.println("Last Name: " + person.getObject(1) + '\t' + "First Name: " + person.getObject( "first_name")); conn.close();

7. JDBC Classesjava.sql package InterfacesClasses CallableStatement Connection DatabaseMetaData Driver PreparedStatement ResultSet ResultSetMetaData Statement Classes Date DriverManager DriverPropertyInfo Time Timestamp Types

8. ORM - Object-Relational Mapping Introduction OO is the predominant approach used to build mainstream systems RDBMS are the most prevalent implementation of data stores OO design models problem domain as real-world objects, RDB development is to normalize data O-R modelling is a necessary but not sufficient to build strong and flexible systems

9. ORM - Relational Characteristics Relation (based on PK-FK) Attribute (simple, basic types: I NF) Domain Tuple <Person ID# = "123-45-6789" Name = �Vincenzo Curci" City = �Roma"> (Uniformity of the tuple data type) Attribute Value Relation Value Relation Variable Database

10. ORM - Relational Database Design The relational model is composed of entities and relations. Figure 1 illustrates LINEITEM and PRODUCT tables and various relationship between them

11. ORM - Object Modeling Terminology Identity (memory pointer or surrogate) State (complex data) Behavior Encapsulation Associations = Pointers or references Structure with Class Inheritance

12. ORM - UML versus ER All ER model constructs correspond to UML constructs UML ER Class Entity Attribute Attribute Association Relationship Multiplicity Cardinality, Participation Generalisation - In Extended ER: Generalisation Package - (Schema ?)

13. ORM - Object-Relational Mapping Object-Relational Mapping is a tricky concept to uderstand and realize in practice See: http://www.cetus-links.org - �OR Mapping� Relational databases have no concept of inheritance Must implement inheritance using relational constructs Various mapping strategies Have to map the following UML model constructs: Classes Attributes Class hierarchies (generalisation, inheritance) Associations Constraints

14. ORM - Mapping classes and attributes Mapping rules (roughly): Class ? Table Object ? Row OID ? Primary key (artificial) Single-valued attribute ? Column Multi-valued attribute ? Separate table, composite primary key, foreign key pointing back

15. ORM - Mapping associations (1) Mapping rules (straight and rough): One table per association Foreign keys to tables of each participating class Primary key depends on association multiplicity Binary association: 1 : 1 : Primary key = foreign key on either side. 1 : * : Primary key = foreign key to class on * side. * : * : Primary key = combination of both foreign keys. N-ary association (N > 2): Primary key depends on the multiplicity constraints.

16. ORM - Mapping associations (2)

17. ORM � Architectural Decision Something in between is needed, what? Architectural Decision: A Persistence Layer The internal architecture of a similar layer can be very complex: Usage and learning curve Performance

18. ORM � What: Persistence Layer Abstracts persistence details from the application layer

19. ORM � Benefits: Persist. Layer A Persistence Layer �should� provide an abstraction for the object model from the database schema Uniform access to persistent data �always� with OO construct Portability across databases and schemas (in theory) Protects developers from database issues (partly) This layer should be in �any� application that has an object model Don�t need one if you are building a simple �window on data� type of application. No feasible for big batch operations (too slow)

20. ORM � Requirements: Persistence Layer Developers require Arbitrary objects persistent Querying interface Connection and transaction management Small impact on object model or db schema No hard-coded or hand-written SQL Complex mapping support Multiple table support, inheritance Conversion from database types �Mixed� foreign keys

21. ORM � Requirements: Persistence Layer Other requirements and issues include: Caching and locking mechanisms Querying Object level, dynamic querying Object and collection references Deferred loading of referenced objects Performance optimizations Minimize updates, batched reading and writing Database features Stored procedures, sequencing, etc.

22. ORM - Persistence Layer Dual Nature 1- Design- Development - Configuration usage: Mapping of Classes-Relations How good or flexible is the mapping capability? 2- Runtime: Usage, performance, business logic How fast is the layer (respect to JDBC) How simple is to learn, use and maintain (respect to JDBC) Which language uses to access �data� (SQL, EQL, �) Transaction management: How is made

23. ORM Types of Persist. Layer �Simple�: hand made hard coded Mapping metadata directly in classes, be a DB broker or a DAO approach Variation: code generation from hard wired metadata Repository based External configuration metadata (e.g. XML mapping file, with or w/o visual tool) How different Persistent objects of a layer must be from POJOs?

24. LAYER - Two Examples of Persistent Layer Entity EJB version 2.0 (going to 3.0) POJO Approach (Plain Old Java Object): Hibernate (www.hibernate.org)

25. LAYER - EJB Entity Beans Persistence Persistence: the key feature of entity beans Entity persistence is �automatic� to the client Timing of load and store is left to the container The client interaction model is one that hides the details of persistence (no explicit update calls) Transactions may be controlled by the client (control of the container is advised: is simpler, as is declarative, and more flexible, it can change without recompilation)

26. LAYER - EJB Entity: BMP and CMP BMP (Bean Managed Persistence) Developers must implement the persistence code in specific �ejb� callbacks methods of the Entity Bean Database reads and writes occur in these specific methods The container calls these methods, usually on other method or transaction boundaries CMP (Container Managed Persistence) Persistence is based on information in the deployment descriptors More �automatic� persistence No special code in the bean Description of the persistence done with tools and XML files The �ejb� callbacks methods a have different meaning (normally are not implemented)

27. LAYER - EJB CMP 2.X EJB CMP 2.X specification allows for relationships between beans provides a standard query mechanism (EJBQL) addresses issues of "local" vs. "remote" EJB 2.0 CMP does not, on its own, define a mapping layer to RDBMS The EJB Schema-RDBM Database mapping is left to the container vendor Mappings are �partially� defined if final EJB container is not known Aspects of runtime behavior (locking, caching, sequencing) are not addressed, but left to the container vendor and normally outside developer control Very, very complex to manage and technology bound (an Entity Bean cannot be reused outside an EJB container) EJB 2.0 CMP meets many of the criteria for a good persistence solution Support for transparent relationship mapping Support for object-level querying by EQL (to be improved, not so powerful as SQL)

28. LAYER � Hibernate: �Trendy� ORM Requirements. Transparent Persistence (for POJO/JavaBeans) Persistent/transient instances (also detachable from DB and serializable over the net) Automatic Dirty Checking Transitive Persistence (Association support) Lazy Fetching Outer Join Fetching Runtime SQL Generation Three Basic Inheritance Mapping Strategies

29. LAYER � Hibernate: Features Persistence for JavaBeans Support for very fine-grained, richly typed object models Powerful queries Support for detached persistent objects

30. LAYER � Hibernate: Persistent Class = POJO

31. LAYER � Hibernate: Schema Mapping with XML file Readable metadata Column / table mappings Surrogate key generation strategy Collection metadata Fetching strategies

32. LAYER � Hibernate: Example of use Retrieve an AuctionItem and change description

33. LAYER � Hibernate: Many Query Options Hibernate Query Language (HQL) �Minimal� OO dialect of ANSI SQL Criteria Queries Extensible framework for expressing query criteria as objects Includes �query by example� Native SQL Queries