Skip to content
Print

Should ORMs Insulate Developers from SQL?

25
Feb
2008

This is a question which is fundamental to any ORM design.  And really from a philosophical standpoint, how should ORMs deal with SQL?  Isn’t the whole point of the ORM to sit between the developer and the database as an all-encompassing, object oriented layer?

A long time ago in an office far, far away, a very smart cookie named Gavin King got to work on what would become the seminal reference implementation for object relational mapping frameworks the world over (or so Java developers would like to think).  This project was to be bundled with JBoss, possibly the most popular enterprise application server, and would support dozens of databases out of the box.  It was to offer heady benefits such as totally object-oriented database access, transparent multi-tier caching and a flexible transaction model.  At its core though, Hibernate was design to resolve a single problem: application developers hate SQL.

No really, it’s true!  Bread-and-butter application developers really dislike accessing data with SQL.  This has led to endless conflict (and bad jokes) between application developers and database administrators.  Often times the developer team would write a set of boilerplate lines in Java and then copy/paste these arbitrarily throughout their code, swapping in the relevant query as supplied by the DBA.  For obvious reasons, this would become very hard to maintain and just intensified the bad blood between developer and database.

If you think about it though, it’s a bit odd that this intense dislike would mutate from just hating the insanity of JDBC to hating JDBC, SQL and RDBMS in general.  SQL is a very nice, almost mathematical language which allows phenomenally powerful queries to be expressed simply and elegantly.  It abstracts developers from the headache of database-specific hashing APIs and algorithms which are almost filesystems in complexity.  The language was designed to make it as easy as possible to get data out of a relational database.  The fact that this effort backfired so utterly is a source of endless confusion to me.

But irregardless, we were talking about ORMs.  When it was first introduced, Hibernate held out the promise that developers would never again have to wade knee deep through a sea of half-set SQL.  Instead, developers would pass around POJOs (Plain Old Java Object(s)), modifying their values like any other Java bean and then handing these objects off to the data mapper, which would handle the details of persistence.  Furthermore, Hibernate promised that developers would never again have to worry about which databases support which non-standard SQL extensions.  Since developers would never have to work with SQL, anything database-specific could be handled within the persistence manager deep in the bowels of Hibernate itself.

This all seems lovely and wonderful, but there’s a catch: it doesn’t work so well in practice.  Now before you stone me, I’m not talking about Hibernate specifically now, but ORMs in general.  It turns out to be completely impossible to interact with a relational database solely through an object-oriented filter.  This is easily seen with a simple example:

SELECT * FROM people WHERE age > 21 GROUP BY lastName

How in the world are you going to represent that in an object model?  Sure, maybe you can provide a little abstraction for the query details, but it starts to get complex if you try to handle things like grouping non-declaratively.  The developers working on Hibernate quickly realized this problem and came up with an innovative solution: write their own query language!  After all, SQL is too confusing, so why not invent an entirely new query language with the “feel” of SQL (to keep the DBAs happy) but without all of the database-specific wrinkles?

This query language is now called “HQL”, and as the name implies, it’s really SQL, but not quite.  Here’s how the aforementioned example would look in HQL (disclaimer: I’m not a Hibernate expert, so I may have gotten the syntax wrong):

FROM Person WHERE :age > 21 GROUP BY :lastName

Remarkably similar, that.  Executing this query in a Hibernate persistence manager yields an ordered list of Person entities pre-populated with data from the query.  It seems to make a lot of sense, but there are a number of problems with this approach.  First, it requires Hibernate to literally have its own compiler to translate HQL queries into database-specific SQL.  Second, it hasn’t really solved the core problem that many developers have with SQL: it’s a declarative query language.  As you can see, HQL is really just SQL in disguise, so it really doesn’t eliminate SQL from your database access, just dresses it in a funny hat.

Other ORMs have appeared over the years, taking alternative approaches to the problem of object-relational mapping, but none of them quite eliminating the query language.  Even DSL-based ORMs like ActiveRecord fail to remove SQL entirely:

class Person < AR::Base; end
 
Person.find(:all, :conditions => 'age > 21', :group => 'lastName')

It’s sort of SQL-free, but you can still see bits and pieces of a query language around the edges.  In fact, what ActiveRecord is actually doing here is building a proper SQL query around the SQL fragments which are passed as parameters.  It’s a system which is ripe for SQL injection, but surprisingly leads to very few problems in real-world applications.  This is the approach which is also taken by ActiveObjects for its database query API.

So ORMs in and of themselves seem to have failed to entirely eliminate SQL from the picture, but what about other frameworks?  There are a few quite recent efforts which seem to have nearly succeeded in eliminating the direct use of SQL completely from application code.  Ambition is perhaps the best (and most clever) example of this, though others like scala-rel are catching up fast.  Ambition is designed from the ground up to interact naturally with ActiveRecord, so the two combined perhaps represent the first “true” ORM: one which does not require the developer at any point to deal with any SQL whatsoever.

But was it really worthwhile?  As clever as things like Ambition are, is it really that much easier than just writing queries in SQL?  As Nathan Hamblen so eloquently said (when referring to a totally different topic):

…is the end of the ORM rainbow.  You get there, throw yourself a party and realize that important things are broken.

A quote taken out of context perhaps, but I think it applies to the “cult of SQL genocide” with as much validity.  In the end, by denying yourself access to the powerful and well-understood mechanism that is SQL, you’re just crippling your own application and forcing yourself to write more code instead of less.

So what’s the “right” approach?  Is there a happy medium between ActiveRecord+Ambition and full-blown SQL on Rails?  I think so, and that is the approach I have been trying to implement with ActiveObjects.  As I’m sure you know, ActiveObjects takes a lot of its inspiration from ActiveRecord, so the syntax for querying the database is very similar:

EntityManager em = ...
em.find(Person.class, Query.select().where("age > 21").group("lastName"));
 
// ...or
em.find(Person.class, "name > 21");   // no grouping

You still have the full power of SQL available to you.  You can still write complex, nested boolean conditionals and funky subqueries, but there’s no longer any need to be burdened with the whole of SQL’s verbosity.  As with vanilla ActiveRecord, this code intends to be a bit of a hand-holder, shielding innocent application developers from the fierce world of RDBMS.

Is this the right way to go?  I’m honestly not sure.  I’ve met a lot of developers that would give their left eye to never have to look at another SQL statement again (for developers already missing a right eye, this isn’t much of a stretch).  On the other hand, there are purists like myself who revel in the freedom afforded by a powerful, declarative language.  It’s hard to say which path is better, but at the end of the day, it’s really the question itself that matters.  Giving application developers the choice to select whichever approach they feel is most appropriate, that is the solution.

Comments

  1. Another relevant aspect: If you build a nice object oriented model, you might find that the marshaling of an object includes lots of statements like these:
    1. select fields from address where personId = ? (as a prepared statement, then execute by filling in the ? value)

    2. select fields from shippingAddress where addressID = ? (same procedure)

    (Typically, case 1 is to fetch a number of related objects, and 2 is to get additional information for a single object)

    So to marshall on single object to some appropriate depth, you’d get a tree of sql statements, many of them identical but for the id parameter.

    I once had the opportunity of replacing a few thousand sql calls with one in a situation like this.

    We had a (somewhat naive) process: “object fetches itself and its relations, resulting objects do the same, to depth n” over a “relationToNeighbours” table.

    We were able to replace this with a hand crafted process object that ran a two part outer join to self over the table. The process object then created an array of objects and attached them to each other, resulting in a fully populated graph with one sql statement.

    You need to be able to bypass the fairly naive ORM mapping constructs to do this kind of thing

    Arne D H Monday, February 25, 2008 at 3:15 am
  2. I agree with most of your points.

    In fact, I think the usage of ORMs hinders the adoption of ANSI SQL by RDBMS producs (i.e. who needs OVER (..) when everybody uses an abstraction layer (the ORM) that can handle all the non-standard extensions doing the same thing?)

    blad Monday, February 25, 2008 at 4:13 am
  3. > SELECT * FROM people WHERE age > 21 GROUP BY lastName

    Maybe I’m not thinking right but that doesn’t seem to be a valid query. Surely you need an aggregate function?

    Asd Monday, February 25, 2008 at 5:05 am
  4. I can answer this question, based on a general thinking pattern I follow.

    Every complicated piece can be simplified. Simplified helps humans. Other people may call this way to think “abstraction”.
    So in regard to SQL, SQL is not very close to human “thinking”. (Same can be said about XML, because XML so quickly increases to something totally unreadable)

    Now any way to abstract the system, to make it more “clean” or “elegant” in regards to human thinking will be a benefit. Personally I would even use a pure DSL without legacy code, and from this DSL generate the language code (what language is that? Query.select().where(“age > 21″).group(“lastName”) seems a bit unrubyish for example, it’s rare to see someone making those ugly empty .() inside chain invocations of method )

    sha Monday, February 25, 2008 at 5:09 am
  5. Your problem could be solved, if the ORM was able to capture AST trees in the host language and compile it down to db-specific SQL expressions. For example, your problem could be solved by:

    result = [p for p in itertools.groupby((p for p in Person.find() if p.age > 21), lambda p: p.lastName)]

    Is not that exactly what Linq is supposed to do?

    (And Asd is right, you can select only columns present in your group by clause or aggregates).

    Qwe Monday, February 25, 2008 at 5:21 am
  6. @Asd: it’s not valid SQL, but it is valid MySQL :)

    IMHO ORM on SQL is a leaky abstraction, so to avoid making things worse, ORM should not hide SQL.

    ORM is great when you need basic CRUD, but when it tries to do anything more complex, it’s just reinventing SQL and giving it some awkward syntax.

    kl Monday, February 25, 2008 at 6:03 am
  7. Asd is right, your sql statement needs an aggregate function, e.g.

    > select lastName, count(id), avg(income) from people where age > 21 group by lastName

    The difficulty with which you can replicate this result set with an ORM remains and the SQL statement is really trivial. It’s always been a mystery to me why developers don’t like SQL. For one it’s much closer to set theory formalism than your standard OO language, and that’s something I’d expect developers to like.

    alq Monday, February 25, 2008 at 6:11 am
  8. I understand your point, but to be fair to the designers of Hibernate, this is a bit of a straw man argument. I would be surprised if Gavin King ever claimed that Hibernate will shield the application developer from understanding RDBMS and SQL. I don’t keep up with that community, but I have read Hibernate in Action which was co-written by Gavin King and I came away with an entirely different impression. The authors say upfront that the best Hibernate domain layers are developed by application developers that can straddle the fence between objects and relational.

    Joe Monday, February 25, 2008 at 6:57 am
  9. in my approach to orm i simply extended SQL with path-expressions which are then resolved to joins and so forth.
    i think to hide SQL from Objects is bad because it leads to a misunderstanding of the concept of relational databases which is that you can only get single-rows back, no set-valued attributes no-nothing else.
    basic example of the problem is:

    User HAS_MANY Posts

    now showing all the users and their posts may lead to something like this:

    $users = Users.findAll();
    foreach($users as $user)
    $posts = $user->getPosts();

    this would lead to O(n) SQL queries simply because the posts could not have been included in the first Users.findAll();

    my approach leads to something like this:

    Posts.find(‘include’ => ‘user’);

    this has the complexity of O(1) instead of O(n)

    so since in the database post would have a foreign key on user it should not be the other way around in the application.

    in conclusion: i think it is important to only provide points of access to the data which also exist in the database in just the same way. the example of course could be done in other ways i just wanted to give an impression of the problem.

    Ludwig Monday, February 25, 2008 at 6:59 am
  10. Hi there, you might want to look at my framework, JRel, which is an attempt to expose the relational underpinnings of SQL RDBMSs, but with a pure Java syntax. It’s not (deliberately) an ORM, but instead a way of writing relational queries using the native syntax of Java. At runtime the queries are translated into SQL.

    For example:

    Query query = new Query().leftOuterJoin(roleTable.attribute(“role_id”).eq(userRoleTable.attribute(“role_id”)))
    .join(userRoleTable.attribute(“user_id”).eq(userTable.attribute(“user_id”)))
    .restrict(userTable.attribute(“user_id”).eq(5))
    .project(roleTable.getDomain());

    Is the query, outputting:

    SELECT role.role_id, role.name FROM user JOIN user_role ON user_role.user_id = user.user_id LEFT OUTER JOIN role ON role.role_id = user_role.role_id WHERE user.user_id = 5

    At runtime.

    I also have utilities to statically bind column attributes to make for cleaner queries where you can do things like:

    .restrict(USER.USER_ID.eq(5)) instead.

    Ryan Daum Monday, February 25, 2008 at 7:37 am
  11. Ah forgot to mention the URL: http://www.thimbleware.com/projects/jrel

    Ryan Daum Monday, February 25, 2008 at 7:38 am
  12. I don’t think that’s a correct characterization of the motivation behind the creation of Hibernate. Hibernate isn’t about avoiding SQL, or indeed avoiding HQL, but about avoiding the mechanics of translating result sets into POJOs and of materializing the associations between them reasonably efficiently.

    Dave Monday, February 25, 2008 at 8:26 am
  13. While you don’t need an aggregate function, the results don’t make much sense given that query.

    But are ORM systems even supposed to support mapping to result sets that don’t correspond to individual rows in the database? If a single row in the result set does not contain a way to map the object back to a row in the table (a primary key), updates don’t make much sense. Aggregate results with group by are not going to return single row references if they are truly useful.

    So really the question is not “Should ORMs insulate developers from SQL?” but “Should ORMs be used to generate reports?” I’ve provided fixed queries for reporting purposes that return objects similar to those returned for row finding, but since they do not map to a single row they are non-updatable. These get defined in the model, where any other SQL is going to go, and are still “hidden” from the developer (if that is one of the goals) and are maintained in one place.

    Andy Monday, February 25, 2008 at 9:14 am
  14. I believe the proper HQL/JPQL syntax for your query would be:

    “SELECT p FROM people p WHERE age > :age GROUP BY p.lastName”
    with “age” being a named parameter.

    You might also want to check out the Hibernate Criteria API, as it seems to do a decent job of programmatically defining query logic in the manner you’re suggested. I’d be interested to hear your opinion on its approach. Something similar should also be included in the upcoming JPA 2.0 spec, I believe.

    Cedric Hurst Monday, February 25, 2008 at 9:50 am
  15. I’d say the use or lack of use of SQL is 6 one way half a dozen another (although a developer that can’t or won’t write SQL is a bad developer in my book). The real benefit of ORM mapping is being able to query the DB and get back objects instead of a result set that requires me to then fill in my object manually and has very limited functionality. I also think ORM helps bridge the gap of thought between Developers and DBAs. If a DBA can see that an object is more or less a row in the table and the developer can see that a row in the table is more or less an object than we have something to build on and we can agree that we are looking at the problem domain somewhat similarly.

    I’m still very new to this concept in that I have never used it for an actual product, but all my recent ORM reading has lead me to this thought. I do however like that the query in ActiveObjects looks ‘object oriented’ as it were.

    Big Spune Monday, February 25, 2008 at 10:08 am
  16. @Big

    You are right that the “correct” point to an ORM is to allow developers to work with objects rather than result sets. The problem of course is that comparatively few result sets translate nicely into objects. Even if you allow relational objects to be created from partial data (e.g. SELECT id,firstName FROM people), it still doesn’t solve the problem of joined result sets or queries with functions. ActiveRecord solves this by wrapping every result set in an instance of AR::Base. This works in Ruby thanks to method_missing, but it’s impractical in Java because result sets with arbitrary fields could not be represented statically as classes.

    A comment farther up made the point that perhaps the question is not “Should ORMs Insulate Developers from SQL” but rather “Should ORMs be Used for Reports”. I think this is spot on the heart of the matter. Once you start using your ORM of choice, you want to let it dominate your entire design and handle every database access for you. This is simply impractical (not to mention inefficient) in the case of basic data mining and report generation.

    Daniel Spiewak Monday, February 25, 2008 at 11:15 am
  17. Lore is already doing all that:

    Cars.find(10).with(Car.name.like(“Viper”) & Car.age > 21).sort_by(Car.name, :desc).entities

    Shiny, hm?

    tobi Monday, February 25, 2008 at 11:36 am
  18. My ORM, Lore, is already doing all that and much more:

    Cars.find(10).with(Car.name.like(“Viper”) & Car.age > 21).sort_by(Car.name, :desc).entities

    It also implements multi-tier caching, a very nice style for declaring / deriving models …

    Get it from here: http://lore.rubyforge.org

    tobi fuchs Monday, February 25, 2008 at 11:38 am
  19. Hmm, I’m thinking this too. Perhaps he meant “order by” given his paragraph after the HQL example: “yields an *ordered* list of Person entities pre-populated with data from the query”.

    I’m not totally convinced that the problem with SQL is that developers hate it. What I think developers hate is doing “getBoolean”, “getInteger” and “getObject” for each column to each property, which is a lot of boilercode. And then setBoolean, setInteger, etc when they want to persist information.

    What I like about HQL that I don’t like about the other more “programmatic” approaches is exactly the fact that it is compiled and yields SQL as a result of compilation. That way you are not bending the languague for something it’s not meant for. You are creating a new language (DSL) specific for that task.

    Maybe it would be interesting if we had a compiler that would convert the following into the proper SQL statement.

    var people = Person.all.filter(function(p) {
    if(p.age > 24) {
    people.add(p);
    }
    });
    people.sort(‘lastName’);

    People using SQL the way they use objects. That would be true ORM.

    Though, I’d still think that HQL is more readable.

    Marcos Toledo Monday, February 25, 2008 at 11:54 am
  20. http://code.google.com/p/scala-rel/source/browse/trunk/scala-rel/src/test/scala/com/thimbleware/rel/Test.scala

    Pretty close to what you wanted. :-)

    Daniel Spiewak Monday, February 25, 2008 at 11:58 am
  21. Indeed,

    But I just now checked the Ambition link and it’s very, very close to what I meant:

    SQL::User.select { |m| m.name == ‘jon’ && m.age == 21 }.to_s
    => “SELECT * FROM users WHERE users.name = ‘jon’ AND users.age = 21″

    I wonder how it would handle group functions, sorting and joins :-) ..I’ll look it up later

    Marcos Toledo Monday, February 25, 2008 at 12:15 pm
  22. Maybe you misunderstand the whole Idea of ORMs – it’s not that it allows you to write sql, through objects, saving them in an RDB. The premise is that databeses (and sql) in general are broken and fail to represent the real-word “objects” and use cases. I mean, really – how many times have you had to “think how to represent” something in a databse – that’s just wrong – it must be made easy, enough so you can do it in a half drunk state. So you don’t have to think about it every feakin time. And defining relations between objects is sooo much easier to grasp and remember. SQL in general feels like a premature optimization – it was created to operate with spreadsheet like data format with a waterfall design strategy(schema first), and it simply cant, however hard it may try, give all the power applications require. ( you may be able to write really complex relations with it but hay, you can’t save objects correctly, so what do I care )

    IvanK Monday, February 25, 2008 at 12:18 pm
  23. Is there anything like Ambition for JPA / EJBQL?

    I’m tired of waiting till runtime just to see that I have some typos in the SQL or EJBQL between the Java lines. I would be very happy if this also would be checked at compiletime.

    Richie Monday, February 25, 2008 at 3:07 pm
  24. Not for EJBQL as far as I know, but scala-rel (as noted above) is roughly Ambition for Scala. I’m assuming it could easily be modified to generate HQL rather than SQL.

    Daniel Spiewak Monday, February 25, 2008 at 3:11 pm
  25. IvanK: SQL was “not designed with spreadsheet like data format”, it was designed with the relational algebraic data model, which is more like Lisp & Prolog than Excel. SQL itself is bollucks, but the data model it is inspired by is much more powerful for general purpose data manipulation than the object oriented model can ever be. It’s a powerful pseudo-declarative, elegant and mathematical model which captures real world facts and relationships in a malleable way.

    Rows and columns is just a visual representation of a physical base relation (“table”); the power of the relational model is the query, which allows you to restructure relations on the fly using a set of powerful operators. The OO model has nothing like a join or cross product; once you have a hierarchy of objects, you are stuck with that taxonomy.

    Today’s databases are flawed because the world of the RDBMS has been stuck in since the early 80s with the handicapped SQL standard. A true modern relational system would provide relation-valued expressions, a more powerful and intuitive type system, and a more decentralized query model.

    The object model is good for the two domains it was _designed_ for: certain kinds of simulation (Simula) and graphical user interfaces (Smalltalk.) Leave the heavy data lifting to a tool designed for it.

    The premise of the original posting is sound ; ORMs are often harmful by nature of hiding the developer from the expressiveness and power of their database. They introduce an awkward layer of abstraction that usually causes more harm than good. You can see it in the very vocabulary that Java developers often use for databases — they call it a “store” and refer to what they are doing as “persistence”, when in fact what they should be thinking about is data management and modeling.

    IMHO we have ORMs because JDBCs and our method of interacting with SQL sucks, not because the object model is somehow superior to the relational.

    Ryan Daum Monday, February 25, 2008 at 8:10 pm
  26. I should also mention that I’m the author of scala-rel, but these days I am working on JRel instead (http://www.thimbleware.com/projects/jrel) as I see more of a need/market in the Java space (where I work for $$) for this kind of tool. In the long run I will revive scala-rel by wrapping JRel itself with a nice Scala syntax taking advantage of powerful static typing.

    Ryan Daum Monday, February 25, 2008 at 8:12 pm
  27. Ryan: How is

    Query query = new Query()
    .from(customer, product, order)
    .select(customer.attribute(“id”).eq(5))
    .project(customer.attribute(“first_name”), customer.attribute(“id”), product.attribute(“id”))
    .join(product.attribute(“id”).eq(order.attribute(“product_id”)))
    .join(customer.attribute(“id”).eq(order.attribute(“customer_id”)));

    Any better than the corresponding SQL:
    SELECT c.first_name, c.id, p.id
    FROM order AS o
    JOIN product AS p ON p.id = o.product_id
    JOIN customer AS c ON c.id = o.customer_id
    WHERE c.id = 5

    Your version is longer and more complicated. Where’s the gain?

    Milo Monday, February 25, 2008 at 10:46 pm
  28. I’ve started my own SQL-centric ORM (SQLOrm, who would have thought ;-) for many of the reasons you state.

    I have been using Toplink for almost 1 year, and for the most part I think the model simply does not fit well with the MVC in a J2EE architecture as when the view is to display the object graph from the controller, the transaction is closed and hence all un-fetched attributes and relationships are null. Toplink seems perfect for a Swing app outside J2EE.

    see http://sqlorm.sourceforge.net/

    Kasper Graversen Tuesday, February 26, 2008 at 5:46 am
  29. One advantage is that it is a Java data structure and subject to manipulation. Adding new restrictions, new joins, and retrieving new attributes can be done dynamically and without doing awkward string munging. This is great for forms with multiple optional search criterion, or anything else that needs to dynamically create a query.

    Secondly, the syntax tree generated is consistent across updates, inserts, deletes, and queries, so it’s possible to do things like retrieve the range from a query, modify it, and then feed it back to an insert; again without resorting to error-prone string manipulations.

    Another advantage is that the query form there is not tied 1:1 with SQL — it is meant to reflect (roughly) the relational algebra, not a SQL query — and so can be theoretically used with another storage backend. With some coding it’s possible to write JRel query planners that could execute complicated queries against hashtables, ResultSets, etc. anything that presents a relation structure with a domain and range of tuples.

    Also, my code is not more complicated; it is just different. It is longer by virtue only of the way the attributes are being declared there. There is another use pattern for JRel which the attributes are early bound:

    Query query = new Query()
    .from(customer)
    .select(customer.id.eq(5))
    .project(customer.first_name, customer.id, product.id)
    .join(product.id.eq(order.product_id))
    .join(customer.id.eq(order.customer_id));

    This to me reads a lot better, even better in an IDE with syntax hilighting. And the advantage here is the early-bound attributes means that the IDE & compiler are detecting your errors related to attribute names (and, in scala-rel, the types.)

    Ryan Daum Tuesday, February 26, 2008 at 8:11 am
  30. @Ryan

    I took a look at JRel. It looks a lot like how queries are done in ActiveObjects (with the exception of your statically-checked field names). :-) I have to admit though, I’m not totally sold on the SQL builder idea. I have the API in ActiveObjects because for most cases, it leads to less code. Pragmatically, the main advantage to it is the underlying builder can generate SQL which is database-specific from a generic hierarchy in memory (good example is TOP vs LIMIT). The problem I see is that Java is just so darn verbose…

    It seems a shame that your needs moved on from scala-rel, which I thought was a really amazing effort. I’m quite looking forward to when you revive the project by wrapping around JRel. Once more having an active project to produce a statically checked Ambition would be really cool.

    Daniel Spiewak Tuesday, February 26, 2008 at 8:45 am
  31. Thanks Daniel; again my goal was to avoid making a SQL builder or an ORM and to try and keep close to the spirit of the relational model itself.

    (What’s kind of ironic is that I have found that once you have a framework like JRel in place it’s actually 10x easier to build an ORM. In one of my projects I built an layer for JRel to handle JPA annotated model objects. By the end I was 1/3rd into building a complete JPA implementation before I smacked my head against the table and said “enough!” )

    As for scala-rel, I just found the Scala language was not yet stable enough and the tool support just not good enough for me yet. When the IntelliJ folks fix their Scala plugin, and the Scala language features sit still for a while then I will maybe try to take a crack at convincing my patrons to use it. In the meantime, I’m stuck with Java’s terrible syntax and type system but get its great tools and runtime.

    Ryan Daum Tuesday, February 26, 2008 at 9:03 am
  32. Hmm, I wonder why nobody came up with the Hibernate Criteria API.

    “FROM Person WHERE :age > 21 GROUP BY :lastName”

    becomes:

    List persons = session.createCriteria(Person.class)
    .add(Restrictions.gt(“age”, 21))
    .setProjection(Projections.alias(Projections.groupProperty(“lastname”), “ln”))
    .list();

    I am not sure what the grouping actually means here. It looks kind of ugly too. Apart from grouping, it is quite easy to create complex queries with this API.

    More examples on: http://www.hibernate.org/hib_docs/reference/en/html/querycriteria.html

    Erik van Oosten Wednesday, February 27, 2008 at 4:57 am
  33. >> I’m not a Hibernate expert, so I may have gotten the syntax wrong

    You really should learn more about Hibernate. It actually has amazing support for SQL and about 50 other ways to query your data. One of them will likely work for you.

    Clinton

    Clinton Begin Wednesday, February 27, 2008 at 7:26 am
  34. No, the intent of ORM is not just to abstract the developer away from SQL. Most java developers do not mind SQL. There are many reasons for ORM — for example, to remove the transactional complexity of JDBC (try-catch-finally, connection pooling, etc..), to prevent the developer from having intimate knowledge of the schema (i.e; column names) and work with member fields instead. Schema generation from POJOs. Moreover, SQL is NOT database-neutral. Each database has its own quirks. A common language such as HQL will make the application truly independent of the underlying database (with Hibernate, each database has its own “Dialect”, an adapter that translates into the correct SQL).

    Xcd Esz Wednesday, February 27, 2008 at 7:54 am
  35. Interesting post :)

    Thomas Hansen Wednesday, February 27, 2008 at 8:35 am
  36. I love SQL. It think its tremendously powerful.

    But as others have mentioned, the lure of ORM mappers is that they handle the crap of translating the result sets from the database into objects. That is worth its weight in gold. Someone said that a developer that doesn’t know SQL is a bad developer. I believe a correlary point is that a good developer will hate not using a ORM when it makes sense. A good ORM won’t prevent you from writing SQL when you need too, it should still take care of wrapping up the result set into objects that make sense for your application.

    planetmcd Wednesday, February 27, 2008 at 12:41 pm
  37. Since it has not been said before I’d like to mention iBATIS (http://ibatis.apache.org). For me it’s a good compromise between a full-fledged ORM tool like Hibernate and hand-written ResultSet-To-JavaBean mapping. Furthermore I’d like to add that ORM tools are not always the best tool for the job. Once you enter the world of DWH and BI things are getting difficult with ORM tools.

    Lars Hoss Thursday, February 28, 2008 at 1:51 am
  38. Agree with Lars Hoss,
    there are situations where database features can’t be masked. I’ll not speak for example of table creation (which could be partitioned, clustered, external or global temporary to name only these options in Oracle).

    But when it is a matter of SQL, some clean solutions can’t be expressed as so clean with ORMs.

    Correct me if I’m wrong but (regarding Oracle SQL features):
    - the MERGE operator is not supported by any ORM and mut be translated by a SELECT then an optional INSERT or UPDATE,
    - query factoring using the WITH clause feature can’t be generated yet (although native SQL may be used in some ORM but we then return to the SQL world),
    - with the 11g version, the MODEL clause is available right now in SQL but when in an ORM?
    (feature of MODEL: http://technology.amis.nl/blog/?p=2066)

    However, I’m agree with the result mapping to Java object argument! Maybe a tool that can (starting from a SQL query) generate code to easily get Objects (hierarchy) would help developers a lot…

    Loïc Tuesday, March 11, 2008 at 2:40 pm
  39. Actually, Oracle has a *ton* of SQL extensions which are specific to it and not implemented by any other database. This is why things like MERGE, MODEL and WITH aren’t implemented by any ORM: because they aren’t implemented by any database except Oracle.

    Daniel Spiewak Tuesday, March 11, 2008 at 2:49 pm
  40. I’ve searched the web but it seems (regarding reports like this one: http://www.oracle.com/corporate/analyst/reports/infrastructure/dbms/34052.pdf) that Oracle is one of the most used database. (Is it still true will this be true in the future?)

    However, this is IMHO again one argument to prefer SQL over ORM ;o) To benefit of great features not yet present in ORMs or too much complex to integrate but still powerful.

    Loïc Tuesday, March 11, 2008 at 3:25 pm
  41. Gavin King and other Hibernate developers are pretty clear that they expect Hibernate users to understand both object orientation and also SQL and relational databases.

    My problem is that Hibernate itself has a vicious learning curve and requires a good chunk of code scaffolding and dependencies to work. I’ve beat my head into the wall enough that I can make Hibernate dance to my tune, but I can’t help but wonder if some of the other ORM tools mentioned here might be less painful and easier to learn and incorporate. I’ll also toss SimpleORM and Beankeeper into the ring as possibilities, though I’ve not yet used either in production.

    Mike S. Wednesday, May 14, 2008 at 12:59 pm
  42. The biggest advantage ORM gives is – your application is not tied to specific database. Using ORM is no excuse to forget sql. SQL understanding is must to use ORM effectively.

    But there is one thing I hate. Suppose if I have to fetch a single field from some table. I need to query whole entity or either write JPQL to populate views, which some say is no ORM anymore. But I would better prefer HQL to populate scattered values over multiple entities. Use whatever suits you. Using SQL will tie you to specific database. Hating ORM or hating SQL/HQL serves no purpose. Use whatever serves purpose.

    Sanjeev Kumar Dangi Wednesday, December 28, 2011 at 12:41 am

Post a Comment

Comments are automatically formatted. Markup are either stripped or will cause large blocks of text to be eaten, depending on the phase of the moon. Code snippets should be wrapped in <pre>...</pre> tags. Indentation within pre tags will be preserved, and most instances of "<" and ">" will work without a problem.

Please note that first-time commenters are moderated, so don't panic if your comment doesn't appear immediately.

*
*