Object-Relational Mapping

Because of foreign-key constraints in the database schema, the order of operations corresponding to the changes in the domain model is important. For example, a new row must be inserted into the database before another new row is inserted or an existing row is updated to refer to the new row. Similarly, a column in an existing row referring to a row to be deleted must be set to null before the row is deleted.

Automatic ordering of operations in the database based on database constraints is a significant usabil-ity feature of ORM technologies compared with other technologies in which the programmer must perform the operations in the correct order.

Queries. One of the primary benefits of ORM is that queries are expressed in terms of the domain object model and not the relational schema. This becomes important if the domain model or queries are complex.

One of the challenges in designing the query language for ORM is to make it rich enough to allow most application queries to be implemented in the query language, but restricted to allow all object domain queries to be mapped directly to SQL.

For example, to find all instances of Employee where the weekly salary is greater than some parameter, the SQL query is:

 

SELECT * FROM EMPLOYEE WHERE WEEKLY_SALARY > ?

The corresponding query using the domain object model would be:

SELECT FROM Full TimeEmployee WHERE weeklySalary > :salary

 

The provider generates the SQL from the domain model and the mapping. The “:” is a parameter marker that allows the application to bind parameters to the query by name.

In SQL, all the tables involved in the query are declared in the FROM clause and the joining conditions are expressly included in the WHERE clause. To find instances of Employee whose weekly salary is greater than some parameter and who work in a Department with some name, the SQL is a bit more complex:

SELECT E.* FROM EMPLOYEE E, DEPARTMENT D WHERE E.WEEKLY_SALARY > ? AND D.NAME = ? AND E.DEPARTMENT = D.ID

The corresponding query using the domain object model would be:

SELECT FROM Full TimeEmployee

WHERE weeklySalary > :salary && dept.name = :dept

NAVIGATING RELATIONSHIPS IN QUERIES

One of the biggest differences among ORM implementations is how navigation in queries is expressed. In JDOQL (Java Data Objects Query Language), each query filter is a Java Boolean expression. The Boolean collection methods such as contains(), containsKey(), and containsValue() are used to navigate multivalued relationship fields.

Other domain query languages use constructs that are more similar to SQL to navigate relationships. For example, in Java Persistence API, the query language uses special keywords such as JOIN, IN, and OUTER to navigate relationships.

To find instances of Employee where the weekly salary is greater than some parameter, who work in a Department with a specific name, and work on a Project with a specific name, the SQL is even more complex:

SELECT E.* FROM EMPLOYEE E, DEPARTMENT D, PROJECT P,
EMPLOYEE_PROJECT EP

WHERE E.WEEKLY_SALARY > ? AND D.NAME = ?

AND E.DEPARTMENT = D.ID AND E.ID = EP.EMPID

AND P.ID = EP.PROJID AND P.NAME = ?

The corresponding domain model query in JDOQL is:

SELECT FROM Employee WHERE weeklySalary > :salary && dept.name == :dptname && projects.contains(p) && p.name == :prjname

This example query uses the Set.contains(Object) method of the Set<Project> projects field to map to the join table in the relational model. The two tokens :dptname and :prjname are variables that are provided to the query at runtime.