SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

ORM and Data Abstraction Layer

ORM and Data Abstraction Layer

Joseph Smith
Joseph  Smith
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 77
Hi all,

I work for a company using MS SQL databases exclusively, but we integrate with varying technologies and applications. I am trying to design a data abstraction layer based on ORM to alleviate tightly coupled code development. I was hoping to get some feedback from other DB professionals about likes/dislikes/recommendations for what to use to develop this (i.e. Hibernate, ObJectRelationalBridge, self-developed, etc). Does anyone have any thoughts about it?

Thanks in advance!
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225920 Visits: 40423
There have been several discussions on SSC regarding this. You may want to try searching this site for ORM, NHibernate, Hibernate.

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Florian Reischl
Florian Reischl
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20739 Visits: 3934

Since I didn't participate the previous discussions, let me share my ideas here. :-P

Disclaimer: I come from .NET side. However,if you are a Java developer, things should be quite alike.

XYZ.Data.Core Library (abstract DAL)
First I start with a XYZ.Data.Core library which represents an abstract data abstraction layer (DAL).
The core DAL contains a IDataContext interface which works like ISession in (N)Hibernate, DataContext in LINQ2SQL or ObjectContext in Entity Framework (EF).
Second part of the Data.Core library is a IDataContextFactory with one method "GetContext():IDataContext" and a generic implementation of this interface. The generic DataContextFactory works with a IoC (inversion of control) to resolve the specific implementation. This can be done by libraries like Microsoft Unity-Library in .NET or any other IoC library or even by an own reflection implementation.
Last but not least the core library contains a here as defined by Martin Fowler in his great book "Patterns for Enterprise Application Architecture". My idea of the Object-Query is keep it simple. Don't try to realize all possible queries. Simple things like "SELECT xyz FROM myTable" and basic "INNER JOIN" operations are enough. They cover more than 90% of required queries. The rest should be done in custom SQL.

This library can be reused in every project and is completely independent.

XYZ.Domain Library
After that create a XYZ.Domain library which contains your domain model and represents the BLL (business logic layer). The Domain library references the - completely abstract - XYZ.Data.Core library to handle the CRUD operations. It contains all your business logic, micro-services and other components you need to handle your project requirements.

Hibernate is just an example. This library represents the real DAL implementation. It references both previous libraries and implements the Data.Core.IDataContext and the Data.Core.IDataContextFactory (which can be resolved by the generic DataContextFactory). If you work with a completely stored procedures based INSERT/UPDATE/DELETE interface, you should use mapping objects for each domain object. If not, the DataContext just works like a proxy of the Hibernate ISession. A helper class transforms the Data.Core.Query to a DAL specific query (e.g. a ICriteria in (N)Hibernate).

Academical business specific DAL
I know, the academical solution says there shouldn't even be any coupling between the BLL and the specific DAL but I work in a Enterprise Application environment and writing hundreds of Data Transfer Objects (DTOs) and their interfaces for hundreds of domain objects appears to be quiet unpractical to me. The academical approach would change the sequence of the domain library and the domain specific DAL. In my opinion a specific DAL will never be reusable for other projects.

Loose coupling between BLL and DAL
The BLL never works with the specific DAL like Data.Hibernate. It uses the generic DataContextFactory of Data.Core library to get a IDataContext and do CRUD operations on the persistence layer. This makes it possible to completely replace the DAL (like Hibernate) with any other persistence ignorant (PI) O/R-Mapper (like EF 2.0 or LINQ2SQL) without any changes within your BLL.

Service Layer
If you want to create a service layer (SL) or not depends on the size of your project and some other requirements like SOA. If you need/want a SOA solution, you need a SL. Indeed, even if you don't want/need SOA, a SL might be useful. It can be used as a abstraction of complex BLLs to orchestrate all required operations for higher level business tasks. The SL should only with the BLL (in my opinion). It should not work with the DAL, if possible, but it must never work with the specific DAL. If you do SOA, you need to populate DTOs to the outside world at this point. In other case you might want to share interfaces which represent the data of your BLL objects or even the BLL objects. (Without SOA, I prefer the interfaces approach, which is an additional lose coupling).

Hope this helps.

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Mike Bishop
Mike Bishop
Mr or Mrs. 500
Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)Mr or Mrs. 500 (560 reputation)

Group: General Forum Members
Points: 560 Visits: 171

Hi. My two cents and the same disclaimer as above (I am SQL Server / .NET / C# guy)
Recently had a disappointing experience as a SQL DBA in a Hibernate shop (yes, the Java Hibernate, not the NHibernate). The problems stem from a lack of understanding of exactly what Hibernate can do for you. Even though the Bauer and King book tell people not too, Java folks still insist they can "design the database" just by using Hibernate (i.e., hbm2ddl).

If this is your situation, you should be aware of the problems with the standard SQL Server dialect that Red Hat ships with Hibernate. The dialect is really extended from a base used by both SQL Server and Sybase. It drove me crazy why Java booleans were being translated to tinyint. Well, if you look at the java class, AbstractTransactSQLDialect, you'll quickly see why. You can also see why "text" data types are still be translated in deprecated SQL Server syntax.




You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum