DAL(5)

Query Engine

The Query Engine is the component of DAL thatbuilds and performs actual SQL queries, it performs operations that correspondto the SQL operations SELECT, INSERT, UPDATE, and DELETE. Its conceptions ofthese operations are somewhat different from SQL's, though.

Read Single

The read single operation correspondsto a SQL SELECT that returns (at most) one row. To perform a read singleoperation, you must first prepare a DO that is populated with property valuesthat the row to be read must match. A Data Object used in this way is called a prototypicalDO (protoDO for short). When a protoDO is to be used for a readsingle it is most often populated with a primary key value only.

Once you have prepared the protoDO, you passit to a Query Engine method such as readSingle. The method reads the row and returns it in anew DO.

Read Multiple

The read multiple operation is similarto read single except that it can return an arbitrary number of rows. Itexpects a protoDO that may match more than one row. It returns a list of DOsfor the rows that match. DAL provides means of limiting the number of rows aread multiple operations can return.

Insert

The insert operation expects a DO thatis populated with property values to be placed in the new row. It inserts therow and returns.

Update

The update and delete operationswork on a "read, then change" model. First the application reads therow to be updated or deleted through either a read single or a read multipleoperations. Then it adds or changes the values of the appropriate properties inthe DO and passes it to one of the Query Engine's "update" methods.

Delete

To delete the row, the application simplypasses the DO to one of the "delete" methods.

 

The Query Engine hasmethods for inserting, updating, and deleting multiple rows as well as singleones.

 

Applications can perform some types of CRUDoperations by calling DAO methods. For example, a DO typically defines a"find by" method (e.g., findByDate(), findById() ) thatapplications can use to search the table on its primary key. It is goodpractice to perform a CRUD operation by calling a DAO method instead of a QueryEngine method when a suitable method exists.

 

Readsets, insertsets, and updatesets

An application that requests a read operationmust supply a readset: a list of columns to be read. The Query Enginereads those columns, and only those columns, reducing the load on the databaseserver and the network. The load reduction can be substantial when the tablecontains many fields and the application uses only a few of them, and when thetable contains very large fields that the application does not use.  Similarly, the Query Engine requires an updatesetfor an update operation, and an insertset for an insert operation.Unlike the readset, however, the Query Engine automatically selects the mostappropriate updateset or insertset for each operation. Readsets and updatesetsmust be defined by the application; the Query Engine itself creates insertsetsat run time.

Subobjects: Accessing Related Tables

Subobjects is a concept, which DAL usesto perform queries on two related tables. Traditional SQL joins perform asimilar function, but are rarely used in DAL. Subobjects are logicallyequivalent to rows, although they may contain data elements drawn from two ormore tables, and they may not contain all of the columns from any giventable.  A subobject is identified anddefined by annotations in the associated primary object's DO. Thus subobjectsare meaningful only in the context of a particular application.

 

There are two types of subobjects: independent and contained (alsocalled "embedded").

·        Independentsubobjects are the more common type. They havetheir own DAOs. Corresponding records in a primary object and an independentsubobject may or may not be created and deleted together, depending on theneeds of the application.  DAL accessesan independent subobject through a separate SQL query. If rows in table B arelinked as independent subobjects to rows in table A by columns A.X and B.X, arow in table B is loaded only when column A.X is first referenced. Thisapproach has two important advantages over a SQL join:

o      It costsnothing in cases where the linking field is never referenced, because thesubobject is never loaded.

o      Itimposes no constraints on the physical location of the subobject, which can beon a different host than the primary object, or can even be hosted by adifferent database engine.

Independent subobjects are designated by JPAannotations such as @OneToOne, @OneToMany, and @ManyToOne. DEDE does notcurrently support the @ManyToMany annotation.

·        Contained Subobjects

A contained subobjecthas a much stronger connection to the primary object than an independentsubobject does. Generally, a contained subobject is only accessed through theDAO of the primary object. Its lifecycle is tied to the primary object'slifecycle; they are typically created or deleted at the same time.

The @Embeddedannotation designates a table as a contained subobject. Thus the terms"contained subobject and "embedded subobject" are used interchangeably.

Lazy Fetch

Lazy fetch (alsocalled lazy load) is the transparent retrievalof data that was not loaded when arow wasinitially read.

There are two types of lazy fetch: subobjectlazy fetch and attribute lazy fetch.

·        In subobject lazy fetch, the Query Engine retrieves a subobject when the subobject isrequested through a getter of the primary object's DO. The Query Engine usesthis mechanism to fetch independent subobjects.

 

·        In attribute lazy fetch, the Query Engine retrieves all of a rows unloaded columns when anyone of those columns is accessed through a getter method. You invoke attributelazy fetch simply by trying to load an unloaded column.

 

Attribute lazy fetch generally should be avoided because of itsimpact on database loading and application response time. Its use isadvantageous in some cases, though; specifically, where the application needsto refer to many columns in the row, or to columns that contain large amountsof data, but it needs to refer to those columns in only a small proportion ofcases, and it cannot tell when it reads the row whether that it will need thosecolumns or not.  In such cases, doing alazy fetch may yield a net increase in efficiency by letting the QueryEngine avoid reading a large amount of data in most cases. The resultingsavings may outweigh the cost of doing a lazy fetch in a few cases.  You must evaluate the tradeoffs between usinglazy fetch and reading unnecessary data on a case-by-case basis.

DDR Hints

DDR hints are modifiers for a DO which convey information abouthow it is to be used. The name "hint" arose from the fact that inmany cases DAL does not have to pay attention to them, but it can takeadvantage of them to improve its performance. One example of the type ofinformation conveyed in DDR hints is, "the application will use this DOfor reads only." Another is, "use a database located at the localdata center if possible."

 

When a DO requests a database operation itpasses a list of DDR hints called a hint group to the Query Engine. TheQuery Engine converts the hint group to an object of type Map, called a hintmap, and uses it to influence the requested operation. The Query Enginealso passes the hint map to any touple providers that it calls to locate thetables it must query.

 

DAL gives the Query Engine some hintsautomatically, but DAL also allows an application to build and pass a hint mapdirectly. This is useful for passing hints that embody the developer'sknowledge about what the application is doing, and also for passing hints addressedto touple providers. The Query Engine passes such hints on, even if it does notrecognize them.

Templatized SQL

DAL queries are defined with templatizedSQL: standard SQL with embedded placeholders. When an operation isperformed, DAL substitutes values supplied by the application, or by DALitself, for the placeholders. Templatized SQL is built in part on the JDBCconcepts of prepared SQL statements and SQL statement parameters. If you arefamiliar with JDBC, you will recognize many features of templatized SQL.

 

Templatized SQL has several advantages overother possible means of constructing flexible queries:

·        Itincreases each SQL statement's range of usefulness, thus reducing the number ofstatements that must be coded and debugged.

·        It letsyou operate on tables and columns that are identified at run time, rather thanhard coded.

·        Itexpresses data operations in the familiar form of SQL statements.

·        It letsyou use all of the features of SQL when necessary.

·        Itleverages the power of SQL statement parameters as implemented JDBC.

 

When the Query Engine executes a templatizedSQL query, it substitutes values for placeholders, yielding a valid"pure" SQL query. Some of these values come from the protoDO that waspassed to the Query Engine; others come from sources outside the application.

 

The following example shows how placeholdersare used in a templatized SQL query.

SELECT /*<CALCOMMENT/>*/<SELECTFIELDS/> FROM <TABLES/> WHERE a.ID=:m_id

 

The /<CALCOMMENT/>/ placeholdergenerates CAL logging information supplied by DAL.  It should appear in every SQL statement.

The <SELECTFIELDS/> placeholdergenerates the names of the table columns used in the query's readset.

The <TABLES/> placeholder generates thephysical table name(s) of the tables that contain the columns in the readset.

The :m_id placeholder substitutes the value ofthe _m_id_ attribute from the protoDO. In this case the colon, taken incontext, is the placeholder. It may be followed by the name of any attributedefined in the protoDO.