From 4b78404c20490a615459267426ce9e6737bf4485 Mon Sep 17 00:00:00 2001 From: wei <> Date: Fri, 14 Jul 2006 09:20:45 +0000 Subject: Moving files. --- .../protected/pages/Manual/ComplexProperties.page | 247 --------------------- 1 file changed, 247 deletions(-) delete mode 100644 demos/sqlmap-docs/protected/pages/Manual/ComplexProperties.page (limited to 'demos/sqlmap-docs/protected/pages/Manual/ComplexProperties.page') diff --git a/demos/sqlmap-docs/protected/pages/Manual/ComplexProperties.page b/demos/sqlmap-docs/protected/pages/Manual/ComplexProperties.page deleted file mode 100644 index f62795f8..00000000 --- a/demos/sqlmap-docs/protected/pages/Manual/ComplexProperties.page +++ /dev/null @@ -1,247 +0,0 @@ - -

Complex Properties

-

In a relational database, one table will often refer to another. Likewise, -some of your business objects may include another object or list of objects. -Types that nest other types are called "complex types". You may not want a -statement to return a simple type, but a fully-formed complex type.

- -

In the database, a related column is usually represented via a 1:1 -relationship, or a 1:M relationship where the class that holds the complex -property is from the "many side" of the relationship and the property itself -is from the "one side" of the relationship. The column returned from the -database will not be the property we want; it is a key to be used in another -query.

- -

From the framework's perspective, the problem is not so much loading a complex -type, but loading each "complex property". To solve this problem, you can -specify in the Result Map a statement to run to load a given property. In -the following example, the "category" property of the -"select-product-result" element is a complex property.

- - - - - - - - - - - - - - - - - - -

In the above example, the framework will use the "selectCategory" -statement to populate the "category" property. The value of each category is -passed to the "selectCategory" statement, and the object returned is set to -the category property. When the process completes, each Product instance will -have the the appropriate category object instance set.

- -

Avoiding N+1 Selects (1:1)

-

A problem with the above example may be that whenever you load a -Product, two statements execute: one for the Product and one for the Category. -For a single Product, this issue may seem trivial. But if you load 10 -products, then 11 statements execute. For 100 Products, instead of one -statement product statement executing, a total of 101 statements execute. The -number of statements executing for the above example will always be -N+1: 100+1=101.

- -

One way to mitigate the problem is to cache the "selectCategory" statement. -We might have a hundred products, but there might only be five categories. -Instead of running a SQL query or stored procedure, the framework will return -the category object from it cache. A 101 statements would still run, but they -would not be hitting the database. See Cache Models -more details about caches.

- -

Another solution is to use a standard SQL join to return the columns you need -from the another table. A join can bring all the columns we need over from the -database in a single query. When you have a nested object, you can reference -nested properties using a dotted notation, like "category.description".

- -

The following example solves the same problem as the previous -example, but uses a join instead of nested properties.

- - - - - - - - - - select * - from PRODUCT, CATEGORY - where PRD_CAT_ID=CAT_ID - and PRD_ID = #value# - - - -
Lazy Loading vs. Joins (1:1): -

It's important to note that using a join is not always better. If you are in a -situation where it is rare to access the related object (e.g. the category -property of the Product class) then it might actually be faster to avoid the -join and the unnecessary loading of all category properties. This is -especially true for database designs that involve outer joins or nullable -and/or non-indexed columns. In these situations it might be better to use the -sub-select solution with lazy loading enabled. The general rule of thumb is: -use the join if you're more likely going to access the associated properties -than not. Otherwise, only use it if lazy loading is not an option.

- -

If you're having trouble deciding which way to go, don't worry. No matter -which way you go, you can always change it without impacting your application -source code. The two examples above result in -exactly the same object graph and are loaded using the exact same method call -from the application. The only consideration is that if you were to enable -caching, then the using the separate select (not the join) solution could -result in a cached instance being returned. But more often than not, that -won't cause a problem (your application shouldn't be dependent on instance -level equality i.e. "===").

-
- -

Complex Collection Properties

-

It is also possible to load properties that represent lists of complex -objects. In the database the data would be represented by a M:M relationship, -or a 1:M relationship where the class containing the list is on the "one -side" of the relationship and the objects in the list are on the "many -side"". To load a TList of objects, there is no change to the statement -(see example above). The only difference required to cause the SQLMap -DataMapper framework to load the property as a TList is that the property -on the business object must be of type TList. For example, if a Category -has a TList of Product instances, the mapping would look like this -(assuming Category has a property called "ProductList" of TList.):

- - - - - - - - - - - - - - - select * from CATEGORY where CAT_ID = #value# - - - - select * from PRODUCT where PRD_CAT_ID = #value# - - - -

Avoiding N+1 Select Lists (1:M and M:N)

-

This is similar to the 1:1 situation above, but is of even greater concern due -to the potentially large amount of data involved. The problem with the -solution above is that whenever you load a Category, two SQL statements are -actually being run (one for the Category and one for the list of associated -Products). This problem seems trivial when loading a single Category, but if -you were to run a query that loaded ten (10) Categories, a separate query -would be run for each Category to load its associated list of Products. This -results in eleven (11) queries total: one for the list of Categories and one -for each Category returned to load each related list of Products (N+1 or in -this case 10+1=11). To make this situation worse, we're dealing with -potentially large lists of data.

- - - - - - - - - - - - - - - - select * from CATEGORY where CAT_ID = #value# - - - - - select * from PRODUCT where PRD_CAT_ID = #value# - - - -

1:N and M:N Solution?

-

-One way to avoid multiple SQL queries is to use Joins in the query -together with the groupBy attribute in <resultMap> and -the resultMapping attribute of <result>. -The following example selects all the categories and the corresponding -products using 1 query. -

- - - - - - - - - - - - - - - - select - CATEGORY.CAT_ID as CAT_ID, - CATEGORY.CAT_DESCRIPTION as CAT_DESCRIPTION, - PRODUCT.PRD_ID as PRD_ID, - PRODUCT.PRD_DESCRIPTION as PRD_DESCRIPTION - from CATEGORY - left join PRODUCT on - PRODUCT.PRD_CAT_ID = CATEGORY.CAT_ID - - - -

In the above example, the groupBy attribute is set -to the column of that specifies the Category ID. All the rows -with the same CAT_ID will be considered as a collection -for the ProductList property.

- -
Lazy Loading vs. Joins (1:M and M:N): -

As with the 1:1 situation described previously, it's important to note that -using a join is not always better. This is even more true for collection -properties than it was for individual value properties due to the greater -amount of data. If you are in a situation where it is rare to access the -related object (e.g. the ProductList property of the Category class) then it -might actually be faster to avoid the join and the unnecessary loading of the -list of products. This is especially true for database designs that involve -outer joins or nullable and/or non-indexed columns. In these situations it -might be better to use the sub-select solution with the lazy loading. The -general rule of thumb is: use the join if you're more likely going to access -the associated properties than not. Otherwise, only use it if lazy loading is -not an option.

- -

As mentioned earlier, if you're having trouble deciding which way to go, don't -worry. No matter which way you go, you can always change it without impacting -your PHP code. The two examples above would result in exactly the same object -graph and are loaded using the exact same method call. The only consideration -is that if you were to enable caching, then the using the separate select (not -the join) solution could result in a cached instance being returned. But more -often than not, that won't cause a problem (your application should not be -dependent on instance level equality i.e. "===").

-
- - \ No newline at end of file -- cgit v1.2.3