ORMs and Hibernate - SQL Anywhere Blog Center - Sybase

saucecopywriterInternet και Εφαρμογές Web

2 Φεβ 2013 (πριν από 4 χρόνια και 6 μήνες)

132 εμφανίσεις

Object Relational Mappers:

Friend or Foe?

Glenn
Paulley
, Director, Engineering

Sybase
iAnywhere

http://iablog.sybase.com/paulley

What is “object
-
relational mapping”?


Object
-
oriented programming technologies are typically used
to implement business applications today


Relational databases are the predominant persistent data
storage solution


Impedance mismatch
between the two paradigms: objects vs.
relations


Estimated that 30
-
40% of a JDBC application involves coercing data
from tuples to object instances and back again


ORM toolkits are designed to address this impedance
mismatch


61 different ORM toolkits are listed in Wikipedia for Java, C++, Delphi,
Ruby, .NET, PHP, Python, Perl

2

The promise


Part 1

Developers spend far too much time worrying about their
backend database, its tables and their relationships, the names
and parameters of stored procedures and views, as well as the
schema of the data that they return. Microsoft’s new Entity
Framework changes the game for .NET developers so that we
no longer have to be concerned with the details of the data
store as we write our applications. We can focus on the task of
writing our applications, rather than accessing the data.


--

Julie Lerman,
Programming Entity Framework
, pp. 1

3

What is “object
-
relational mapping”?


To exploit object behaviour fully, data
-
access from within an
object
-
oriented programming language should offer:


Separation of concerns


Information hiding


Inheritance


Change detection


Uniqueness capability


Database independence



4

Change detection


ORM toolkits require mechanisms to track changes to objects
made by the application


When a transaction is complete, write the changes to the database
within an atomic transaction


Need appropriate guarantees to prevent lost updates; either

o
Pessimistic locking using the database server’s locking
implementation

o
Optimistic locking using timestamps or version numbers, handled
by the ORM layer but supported by the underlying relational
schema


5

Uniqueness


Mappings are usually a correspondence between a row in a normalized
table and a class instance


Specified using metadata; Hibernate (
NHibernate
) and Entity
Framework utilize XML


For example, a row of the Employee table will correspond to an
instance of the Employee object within the application


Mappings are often not isomorphic

o
Sophisticated
ORMs

such as Hibernate and LINQ permit object models
that differ substantially from the underlying relational store

o
Object
-
oriented language features offer greater semantic flexibility in
application design than 1NF values from a relational database


Need to establish a correspondence between an in
-
memory object and a
database row


Must be independent of how the object was acquired: a database
query, or navigating a reference to another object


Predicated on the existence of primary keys in the database

6

Database independence


Many ORM toolkits attempt to offer database independence,
so that applications can be ported from one DBMS to another


Create common APIs and models to interact with a variety of DBMS
platforms


Useful with mobilized applications where the consolidated
database is one DBMS, and local databases are different


APIs tend to be modeled on SQL
-
92!


Hibernate’s HQL


Entity Framework’s LINQ

7

The promise


Part 2


ORM Toolkits can:


Eliminate tedious, repetitive code that instantiates object instances
from tuples using a SELECT statement and a CURSOR


Insulate, to some extent, the application developer from vendor
-
specific SQL extensions


Permit the application developer to exploit object
-
orientation and
model and manipulate the application view differently from the
relational model


Allow data manipulation to be done at the ‘object’ level, rather than
(only) at a SQL statement level


8

NHibernate


HelloNHibernate example


static void
CreateEmployeeAndSaveToDatabase
()


{


Employee
tobin

= new Employee();


tobin.name

= "Tobin Harris";



using (
ISession

session =
OpenSession
())


{


using (
ITransaction

transaction =
session.BeginTransaction
())


{


session.Save
(
tobin
); // generates appropriate SQL INSERT statement


transaction.Commit
();


}


Console.WriteLine
("Saved Tobin to the database");


}


}


Look Mom


no SQL!

9

NHibernate


HelloNHibernate example


static void
LoadEmployeesFromDatabase
()


{


using (
ISession

session =
OpenSession
())


{


IQuery

query =
session.CreateQuery
(


"from Employee as
emp

order by
emp.name

asc
");



IList
<Employee>
foundEmployees

=
query.List
<Employee>();



Console.WriteLine
("
\
n{0} employees found:",


foundEmployees.Count
);



foreach

(Employee employee in
foundEmployees
)


Console.WriteLine
(
employee.SayHello
());


}


}

10

The Challenge


Part 1


ORM toolkits introduce an additional level of complexity to the
application


Example: Java Hibernate 3.2.6 is

o
266 packages, 1938 classes, 18,680 functions, over 118K LOC


Can be difficult to debug, perform performance analysis

o
Most frameworks suffer from a lack of appropriate tools

o
Ayende Rahien has developed an NHibernate Profiler that provides
sophisticated debugging and performance analysis for NHibernate
applications

11

The Challenge


Part 2


Performance analysis is problematic because the application’s
behaviour is not tied directly to specific interactions with the
database


ORM tools obfuscate the difference between expensive and
inexpensive database interactions


Complex object
-
relational mappings may cause
very

complex SQL
queries to be generated


Generated SQL requests may not reach the database server in the
same order due to ORM
write
-
behind

policies


Can be difficult for the application developer to understand what
caused the construction of a specific SQL request

12

Complex SQL (LINQ generated)

SELECT

[Project9].[ContactID] AS [ContactID],[Project9].[C1] AS [C1],[Project9].[C2] AS [C2],[Project9].[ContactID1] AS [ContactID1]
,[P
roject9].[SalesOrderID] AS [SalesOrderID],

[Project9].[TotalDue] AS [TotalDue]

FROM ( SELECT


[Distinct1].[ContactID] AS [ContactID],


1 AS [C1],


[Project8].[ContactID] AS [ContactID1],



[Project8].[SalesOrderID] AS [SalesOrderID],



[Project8].[TotalDue] AS [TotalDue],


[Project8].[C1] AS [C2]



FROM



(SELECT DISTINCT


[Extent1].[ContactID] AS [ContactID]





FROM


[DBA].[Contact] AS [Extent1]



INNER JOIN [DBA].[SalesOrderHeader] AS [Extent2]

ON


EXISTS (SELECT


cast(1 as bit) AS [C1]



FROM


( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]



LEFT OUTER JOIN


(SELECT [Extent3].[ContactID] AS [ContactID]



FROM [DBA].[Contact] AS [Extent3]


WHERE [Extent2].[ContactID] = [Extent3].[ContactID] )AS [Proj
ect1] ON cast(1 as bit) = cast(1 as bit)



LEFT OUTER JOIN


(SELECT


[Extent4].[ContactID] AS [ContactID]



FROM [DBA].[Contact] AS [Extent4]


WHERE [Extent2].[ContactID] = [Extent4].[ContactID] ) AS [Pro
ject2] ON cast(1 as bit) = cast(1 as bit)



WHERE ([Extent1].[ContactID] = [Project1].[ContactID]) OR (([Extent1].[ContactID] IS NULL) AND ([Project
2].[ContactID] IS NULL))

)

) AS [Distinct1]



LEFT OUTER JOIN




(SELECT


[Extent5].[ContactID] AS [ContactID],


[Extent6].[SalesOrderID] AS [SalesOrderID],


[Extent6].[TotalDue] AS [TotalDue]
,


1 AS [C1]



FROM


[DBA].[Contact] AS [Extent5]



INNER JOIN [DBA].[SalesOrderHeader] AS [Extent6]



ON


EXISTS (SELECT


cast(1 as bit) AS [C1]



FROM


( SELECT cast(1 as bit) AS X ) AS [SingleRowTable2]



LEFT OUTER JOIN


(SELECT


[Extent7].[ContactID] AS [ContactID]



FROM [DBA].[Contact] AS [Extent7]


WHERE [Extent6].[ContactID] = [Extent7].[ContactID] )AS [Proj
ect5] ON cast(1 as bit) = cast(1 as bit)



LEFT OUTER JOIN


(SELECT


[Extent8].[ContactID] AS [ContactID]



FROM [DBA].[Contact] AS [Extent8]


WHERE [Extent6].[ContactID] = [Extent8].[ContactID] )AS [Proj
ect6] ON cast(1 as bit) = cast(1 as bit)





WHERE ([Extent5].[ContactID] = [Project5].[ContactID]) OR (([Extent5].[ContactID] IS NULL) AND ([Project6
].[ContactID] IS NULL))



)

) AS [Project8]

ON ([Project8].[ContactID] = [Distinct1].[ContactID]) OR (([Project8].[ContactID] IS NULL) AND ([Distinct1].[ContactID] IS NU
LL)
)

)


AS [Project9]

ORDER BY [Project9].[ContactID] ASC, [Project9].[C2] ASC

13

Equivalent SQL query

select


Extent6.ContactID,



1 as C1,



1 as C2,

Extent6.ContactID as ContactID1,



Extent6.SalesOrderID as SalesOrderID,

Extent6.TotalDue as TotalDue

from DBA.SalesOrderHeader as Extent6

order by Extent6.ContactID asc


Can your query optimizer get there?


And what program construction caused the original statement to
be formed in the first place?

14

The Challenge


Part 3


Abstraction is great, but….


The application programmer now relies on the ORM toolkit for
concurrency control and the generation of database requests, the two
most important performance factors of a database application


In practice, ease of development is traded
-
off for poorer run
-
time performance due to additional code complexity


Julie Lerman: 220% increase in query execution cost between using a
DataReader directly and using Microsoft’s LINQ to Entities (pp. 503)


Jeff Atwood:
(
http://www.codinghorror.com/blog/archives/001281.html
)
A simple
example shows a factor of two difference between using a DataReader
and LINQ to SQL.

15

The Challenge


Part 4


Object
-
instance
-
at
-
a
-
time navigation through the object model
can result in multiple, separate interactions to the database
server to retrieve the rows to create the objects


Termed the
N+1 Selects

problem


Application developer must tradeoff
prefetching

applicability in
various scenarios


these are documented in the various toolkits


Objects are not partially instantiated; all object attributes are required
for the constructor


Transactional semantics are complex once caching is
introduced


Transactional semantics often differ across
DBMSs
, even with the
identical isolation level


Developers must (still) be aware of the potential for lost updates


16

The Challenge


Part 5


The SQL dialect supported by ORM toolkits is typically a very
restricted subset of ANSI SQL


Hibernate’s

HQL supports inner and outer join, simple GROUP BY and
HAVING, quantified
subqueries


Workarounds are to perform the computation on the client (within the
application), or work outside of the ORM toolkit using a direct
database connection through a native API


ORM toolkits often support only limited, straightforward DDL
for schema creation/modification


Not supported in current versions of Entity Framework


Hibernate
-
generated schemas are not recommended for production
applications

o
Deployment of the application should involve separate DDL scripts

17

Important aspects of ORM toolkits


Mapping specification


Query language


Persistence


Class inheritance


Fetch strategies


Caching



We look and see how these are implemented in NHibernate, a
popular open
-
source ORM toolkit for C# applications


18

NHIBERNATE

AS AN EXAMPLE OF AN ORM

19

Brief introduction to NHibernate


Open
-
source, LGPL C# ORM toolkit


Forked from the original Java implementation called Hibernate


Support for similar, but not identical, metadata XML


Similar architecture, but significant differences in methods and
support


Hibernate was originally developed by Christian Bauer, Gavin
King, and a worldwide team of developers


NHibernate now maintained by another group worldwide


Current distribution is 2.1 (released in July 2009)


http://hibernate.org


20

NHibernate application architecture

21

NHibernate.DLL

NHibernate mapping specification

CREATE TABLE "GROUPO"."SalesOrders" (


"ID" integer NOT NULL DEFAULT autoincrement


,"CustomerID" integer NOT NULL


,"OrderDate" date NOT NULL


,"FinancialCode" char(2) NULL


,"Region" char(7) NULL


,"SalesRepresentative" integer NOT NULL


,CONSTRAINT "SalesOrdersKey" PRIMARY KEY ("ID")

)


ALTER TABLE "GROUPO"."SalesOrders“


ADD FOREIGN KEY "FK_SalesRepresentative_EmployeeID"
("SalesRepresentative“) REFERENCES "GROUPO"."Employees" ("EmployeeID")

ALTER TABLE "GROUPO"."SalesOrders“


ADD FOREIGN KEY "FK_FinancialCode_Code" ("FinancialCode")


REFERENCES "GROUPO"."FinancialCodes" ("Code“)


ON DELETE SET NULL

ALTER TABLE "GROUPO"."SalesOrders“


ADD FOREIGN KEY "FK_CustomerID_ID" ("CustomerID")


REFERENCES "GROUPO"."Customers" ("ID“)

22

NHibernate mapping specification

NHibernate mapping file SalesOrders.hbm.xml:

<?xml version="1.0"?>

<!DOCTYPE hibernate
-
mapping PUBLIC "
-
//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate
-
mapping
-
3.0.dtd">

<!
--

Generated Mar 3, 2009 11:59:55 AM by Hibernate Tools 3.2.2.GA
--
>

<hibernate
-
mapping>


<class name="SalesOrders" table="SalesOrders">


<comment>sales orders that customers have submitted to the sporting goods company</comment>


<id name="id" type="int">


<column name="ID" />


<generator class="assigned" />


</id>


<many
-
to
-
one name="employees" class="Employees" fetch="select">


<column name="SalesRepresentative" not
-
null="true" />


</many
-
to
-
one>


<many
-
to
-
one name="financialCodes" class="FinancialCodes" fetch="select">


<column name="FinancialCode" length="2" />


</many
-
to
-
one>


<many
-
to
-
one name="customers" class="Customers" fetch="select">


<column name="CustomerID" not
-
null="true" />


</many
-
to
-
one>


<property name="orderDate" type="date">


<column name="OrderDate" length="10" not
-
null="true" />


</property> .............[snip]


</class>

</hibernate
-
mapping>


23

C# class implementation

using System;

using System.Collections.Generic;

using NHibernate;


namespace HelloNHibernate

{


class SalesOrder


{


public Int32 id;



public Employees employees;



public FinancialCodes financialCodes;



public Customers customers;



public DateTime orderDate;



public String region;



public Set salesOrderItemses = new HashSet(0);



public SalesOrders() {}



public SalesOrders(Int32 id, Employees employees, Customers customers, DateTime orderDate) {



this.id = id;



this.employees = employees;



this.customers = customers;



this.orderDate = orderDate;


} ….

24

HQL: Hibernate Query Language


Subset of ANSI query specification with support for


DISTINCT


GROUP BY, simple aggregation


INNER, LEFT
-

and RIGHT
-
OUTER JOIN


Quantified subqueries


Supports object
-
oriented dot
-
notation for many
-
to
-
one, one
-
to
-
one associations, for example:


select s.id, s.orderDate, s.region, s.customers.givenName, s.customers.surname

from SalesOrders s

where s.orderDate between '2001
-
03
-
16' and '2001
-
03
-
26' and s.region <> 'Central'

order by s.orderDate

25

HQL: Hibernate Query Language


NHibernate’s HQL supports SELECT, inner and outer JOIN,
WHERE, HAVING, simple GROUP BY, UNION, ORDER BY, self
-
joins with different correlation names


HQL does not support recursion, common table expressions,
window functions, derived tables, other set operators, table
functions, array or structured types, APPLY/OUTER APPLY,
CROSS JOIN, GROUP BY CUBE/ROLLUP/GROUPING SETS, FULL
OUTER JOIN

select distinct c.givenName

from Customers c left join c.salesOrderses

where c.city in (select c2.city from Customers c2 where
c2.companyName like '%Power%')



26

HQL: Hibernate Query Language


HQL is augmented by “dialects” that implement specific
methods to modify the SQL generated by NHibernate before
execution on the server


HQL has optional support for (implemented by a specific
dialect):


UNION ALL (for entity
-
type hierarchies)


LIMIT (SELECT TOP N), OFFSET


IDENTITY, GUID, SEQUENCE data types


Syntax to declare an updateable cursor and locking mode


Case
-
insensitive string comparisons


27

Object persistence in NHibernate


Saving objects


Once an object is created or modified, it must be saved explicitly and
then the transaction must be committed:

session.Save(<object name>);

tx.Commit();


Loading objects


The NHibernate session interface offers several load() methods for
loading objects into memory from database tuples:


Cat fritz = (Cat) session.Load(typeof(Cat), generatedId);

Cat cat = (Cat) session.Get(typeof(Cat), id, LockMode.Upgrade); // OR this

session.Save(cat);

session.Flush(); //force the SQL INSERT

session.Refresh(cat); //re
-
read the state (after the trigger executes)



28

Object persistence in NHibernate


A refresh() method is implemented to reload objects from the
database


Useful for when attributes are modified by database triggers upon
INSERT or UPDATE

o
Highly error
-
prone


Updating objects


NHibernate manages changes to persistent objects transparently and
automatically

o
If an attribute is altered, the appropriate NHibernate session will
queue the change for writing to the database using SQL

o
One can force changes to be written at a certain point using the
flush() method, controlled by isDirty() and setFlushMode()


29

Class inheritance


Hibernate and NHibernate offer a variety of built
-
in techniques
to handle different normalizations of entity
-
type hierarchies:


Single table with discriminator value


Multiple tables fully normalized into BCNF


A hybrid model consisting of a mixture of the two


If represented as different objects in a mapping, an ETH
requires careful construction and different equals() and
hashcode() implementations

30

FETCH strategies


A major pain point is the N+1 SELECTs problem


Navigation through the object model iteratively causes additional rows
to be retrieved from the database, using independent SQL requests

o
“client
-
side” join; performance tends to be extremely poor due to
the additional latency

o
Need careful construction of HQL queries to minimize client
-
server
latency


Alternatively, in the mapping one may specify the selection method:
either “lazy” or “eager”

o
One must tradeoff this method will global application behaviour,
or override it on a case
-
by
-
case basis using HQL
-
specific syntax

31

Concurrency control


NHibernate

relies on the database’s locking scheme for
concurrency control


ANSI isolation levels 0
-
3 are supported directly; 1 or 2 is
recommended (READ COMMITTED and REPEATABLE READ)

o
DBMS that support snapshot isolation require tweaks to their
NHibernate

dialect implementation


Lock mode for individual HQL statements or instantiations of objects
can be specified directly, ie

Customer c = (Customer)
session.Get
(
Customer.class
, 101,
LockMode.Upgrade);



NHibernate

has builtin support for optimistic concurrency
control


Can use either version numbers (added to the schema of the table), or
base change control on all of the values in the row


32

Caching and concurrency control


Like many runtime environments, NHibernate supports built
-
in
caching controls to speed
-
up database interaction


Two levels of caching are supported:


Level 1: Persistence context cache

o
Lifespan is a transaction or a conversation without sharing.

o
Guarantees scope of the object and object identity. Mandatory.


Level 2: Pluggable, scope is process or cluster (shared)

o
Configurable on a class
-
by
-
class basis

o
Selectable concurrency control strategies: Transactional, Read
-
write, non
-
strict read
-
write, read
-
only

o
ASP.NET Cache, PrevalenceCache are implementations


33

Summary


Are ORM toolkits a failed abstraction?


Similar arguments to compiler technology in the 1970s


There are productivity gains with such tools, but there are tradeoffs
with additional run
-
time costs


Jeff Atwood: programmers must
still

understand the entire program
stack from end to end

o
Application complexity is increased

o
However
-

for open source toolkits, documentation and support is
what one would expect with “free” software


Developer productivity gains are real


Permits specialization of development teams; database expertise is
still necessary

34

Research and development opportunities


Robust, industrial
-
strength debugging frameworks


Identify how a particular database interaction was caused by what specific
application program
behaviour


Analysis of concurrency control
behaviour

to determine application
correctness, for example with respect to lost updates


Query processing optimizations specific for ORM application toolkits


Identifying common classes of SQL queries for specific sets of
optimizations


Place in the dialect layer, in the DBMS itself, or in a separate tool that can
support the optimization of
multiple

inter
-
related statements


Mechanisms to support additional types of mappings and more
complex schemas


Support for recursion in toolkit languages


35

Questions?


Watch my blog for continuing articles on supporting ORM
toolkits with SQL Anywhere:
http://iablog.sybase.com/paulley


References:


Christian Bauer and Gavin King (November 2007).
Java Persistence with Hibernate

(revised edition of
Hibernate in
Action
). Manning Publications, Greenwich, CT. ISBN 1
-
932394
-
88
-
5. Seventh printing.


Dave Minter and Jeff Linwood (2005)
Pro Hibernate 3
. Apress Books, Berkeley, CA.


Julia Lerman ( February 2009).
Programming Entity Framework.
O’Reilly Media, Inc., Sebastopol, California. ISBN 978
-
0
-
596
-
52028
-
1.


Roger Jennings (2009).
Professional ADO.NET 3.5 with LINQ and the Entity Framework
. Wiley Publishing, Indianapolis,
Indiana. 978
-
0
-
470
-
18261
-
1.


Joseph C. Rattz, Jr. (2007). Pro LINQ: Language Integrated Query in C# 2008. Apress Books, Berkeley, CA. 978
-
1
-
59059
-
789
-
7.


Pierre Henri Kuate, Tobin Harris, Christian Bauer and Gavin King (2009).
NHibernate in Action
. Manning Publications,
Greenwich, CT. ISBN 978
-
1
-
932394
-
92
-
4.


ACM Queue 6(3), May/June 2008, “Bridging the Object
-
Relational Divide”.


36