Having worked both sides of application development from database entity-relationship modeling, through to implementation and deployment, our team was positioned in early 2006, with the development of two web-based applications looming on the horizon.

A thorough evaluation of the benefits offered us through the use of an Object/Relational Mapping technology was introduced as a development approach to alleviate “the developer from 95 percent of common data persistence-related programming tasks”.

Such an approach would give us a competitive edge with our clients and fast-track the project plan by enabling our developers more focus on what’s important in application development functionality and user experience, rather than its database persistence mechanism.

As we progressed with our evaluation, we learned the most obvious commercial choice in the ORM arena is not necessarily the one to set your sites on. While we found the depth of Hibernate™’s features well documented, and much written regarding Hibernate™ strategy as industry-standard, we soon realized the trade-offs involved in Hibernate™ real-world, practical application. Once getting past the simple two-table, three-column examples that serve as introductory tutorials at various websites, Hibernate can begin to look more like a cumbersome relational database methodology itself, rather than the non-evasive application persistence layer we had hoped an object/relational mapping tool would provide our DAO.

There were 3 core functional goals in mind that we expected would be facilitated through the use of an object/relational mapping tool set: 1) configurable ease in obtaining the mapping of relational database tables and columns to the properties of their respective object representations; 2) a means to define relationally intact representative graphs of those objects based on database Primary/Foreign key relationships, and 3) a non-evasive, natural implementation into what would have been our application’s traditional DAO layer to facilitate both retrieval and persistence of those graphs defined.

Along with our interest in Hibernate™, the ITCWorks™ open source relational mapping project, presented by I Technologies Corp, was introduced to members of our team by word-of-mouth in an open forum OO user group meeting in April, 2006. ITCWorks™ was offered as a rich framework library incorporating a robust object/relational mapping solution.

Two concepts stood out as a direct result of those initial conversations that had not been fully appreciated up to that point from the commercially available ORM products in our evaluation:

First, all information necessary to produce an object mapping for database persistence is already contained within the database schema itself; second, it is through a mapping tool’s ability to integrate with, and therefore leverage the familarity and versitility of SQL (not introducing a proprietary database query language), that developer productivity gains could be fully realized in an object/relational strategy.

Initially, we viewed these more as theoretical goals, and doubted that either tool could adequately achieve a concrete implementation. With relational database nuances specific to each vendor, the complexities bridging from the object world to the SQL/mapping component, and back again, were perceived as significant obstacles in a truly reverse-engineered paradigm.

Our project required a object/relational mapping tool that had feature for “reversing” either newly created database schemas, or our client’s older legacy databases including Oracle Express, UDB Enterprise Edition, MySQL, and even SQL Server. We found that ITCPro™ a visual User Interface built on top of ITCWorks™ offered these features and integrated as a plug-in to our Eclipse workbench environment. We found that ITCPro™ using standard JDBC connections produces complete and comprehensive object/relational database mappings. It also supported retrieval-specific Primary/Foreign key-based object graphings, ready for use in application development by the developers.

We found ITCPro™ easy to use with no manual database mapping exercise. It handled processing and generation against a 50+ database-table schema in less than one minute. Ambiguous directional database-model navigation— as is the case in associative (many-to-many) relationships— are decided simply by the order in which the tables are dragged onto the ITCPro™ GUI ORM editor. A relationally intact representative set of POJO Database Value Objects, complete with java-standard naming for their column-to-property getters/setters, and built-in Dirty Object Detection, are also generated at the same time mappings are generated.

The ITCWorks™ object/relational database mappings are stored in a user-named file with an xsm extention (XML SQL Mapping). The document contains standard Structured Query Language and becomes the backbone of the ITCPro™’s ORM engine at runtime. Unlike a new application-embedded query language, this paradigm provided a neat abstraction of all database retrieval and persistence performed by an application and is implemented with developer-familiarity of industry-standard SQL. The XML SQL Mapping document is complete and ready-for-use immediately upon generation. We found that the flexibility of using SQL is underscored in development as dictated by database requirements of the application itself. SQL can be manipulated through direct developer modification of the xsm document, or via the ITCPro™ GUI editor (also integrated into the Eclipse environment). This approach allowed our developers the ability to add column functions, grouping and summing, sub-selects, or anything else needed as database application functionality as part of the already generated object graphs. This way data access could be created on their own for retrieval in custom generated database value objects. In the end, persistence to the database is achieved on a graph of objects via a single “save” method, and in our case, called from what would have been our traditional DAO application layer.

When approaching new application development, organizational work-flow in most cases still begins with functional specification (1), and the usual progression to database modeling and definition (2) in support of that functionality. Conversely, in Hibernate™’s “Top-down” development, “you start with an existing domain model, and (ideally) complete freedom with respect to the database schema.” From there, you manually write the Hibernate™ XML mapping and/or annotate the Java source to generate the database schema. There are, however, fundamental problems with this approach. First, any XML mapping/annotation that you must do manually for Hibernate™ at this point, is yet against non-existent database entities. The Hibernate™ object/relational mapping exercise by developers is made more abstract in the absence of any real database implementation. Second, the traditional creation or generation of the database schema (depending upon the modeling tool used) is usually a function of Database Administrators within the organization, and the “ideal, complete freedom” promoted here with respect to the database schema, simply won’t exist— just as asking your DBA to complete the mappings for you in order to generate the schema, would be equally unrealistic.

By far, the most common approach in development process is as Hibernate™ defines, “Bottom-up”, whether working with already existing production databases, or running DDL for the creation of newly modeled databases as the target of new application development. Hibernate™ warns, however, “not all class association details and Java-specific meta information can be automatically generated from a SQL database schema with this strategy, so expect some manual work.” ICTPro™’s ORM object/relational reverse-engineering from a SQL database schema results in a more comprehensive solution, and any manual modifications added specific to database function is done using SQL, the relational database query language of choice.

In the final analysis, as object/relational technologies continue to mature, the software approach employed there will not include paradigms which simply trade one development complexity for another, but that naturally integrate with already established developer-application standards for true realization of the productivity claims they make.

From our first ORM++ product demonstration, through to our second J2EE application implementation, ITCPro™ ‘s object/relational mapping and persistence turned out to be nothing we had initially imagined. ITCPro™ was able to do more than we had initially thought, and it did a better job than we initially thought. From an object/relational mapping solution based on evaluation of other products in the arena, ITCPro™ goes beyond just the normal object relational mapping. It offers a more precise and easy to use reverse engineering, it generates easy to understand standard SQL code, and it offers a graphical view of databases and query tools. ITCPro™ saved hours of development time and surpassed other products that offer the same type of service.

About the Author: Dave Anthony is an independent Senior Java Developer with more than ten years of experience in Java software development for large insurance companies.