summaryrefslogtreecommitdiff
path: root/docs/sqlmap/latex/ch3.tex
blob: 79b2c42aec21d7c246c7a6e0533cb3685aa0975c (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
\chapter{Working with Data Maps}\label{section:3}
\section{Introduction}

If you want to know how to configure and install SQLMap, see the Developer
Guide section~\ref{section:4} . But if you want to know how SQLMap really
works, continue from here.

The Data Map definition file is where the interesting stuff happens. Here, you
define how your application interacts with your database. As mentioned, the
Data Map definition is an XML descriptor file. By using a service routine
provided by SQLMap, the XML descriptors are rendered into a client object (or
Mapper). To access your Data Maps, your application calls the client object
and passes in the name of the statement you need.

The real work of using SQLMap is not so much in the application code, but in
the XML descriptors that SQLMap renders. Instead of monkeying with application
source code, you monkey with XML descriptors instead. The benefit is that the
XML descriptors are much better suited to the task of mapping your object
properties to database entities. At least, that's our own experience with our
own applications. Of course, your mileage may vary.

\section{What's in a Data Map definition file, anyway?}

If you read the Tutorial, you've already seen some simple Data Map examples,
like the one shown in Example~\ref{example:2.1}.

\begin{example}\label{example:3.1}
 A simple Data Map (PHP)
 \begin{verbatim}
<?xml version="1.0" encoding="UTF-8" ?>
  <sqlMap namespace="LineItem">
    <insert id="InsertLineItem" parameterClass="LineItem">
      INSERT INTO [LinesItem]
        (Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice)
      VALUES
       (#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#)
    </insert>
</sqlMap>
 \end{verbatim}
\end{example}
This map takes some properties from a \tt{LineItem} instance and merges the
values into the SQL statement. The value-add is that our SQL in separated from
our program code, and we can pass our \tt{LineItem} instance directly to a
library method:
\begin{verbatim}
TMapper::instance()->insert("InsertLineItem",$lineItem);
\end{verbatim}
No fuss, no muss. Likewise, see Example\ref{example:3.2} for a simple select
statement.

\begin{mybox}{Info:}
\textbf{A Quick Glance at Inline Parameters}

Say we have a mapped statement element that looks like this:
\begin{verbatim}
 <statement id="InsertProduct">
  insert into Products (Product_Id, Product_Description)
  values (#Id#, #Description#);
</statement>
\end{verbatim}
The inline parameters here are \tt{\#Id\#} and \tt{\#Description\#}. Let's
also say that we have an object with the properties \tt{Id} and
\tt{Description}. If we set the object properties to $5$ and ``dog'',
respectively, and passed the object to the mapped statement, we'd end up with
a runtime query that looked like this:
\begin{verbatim}
insert into Products (Product_Id, Product_Description) values (5, 'dog');
\end{verbatim}
For more about inline parameters, see Chapter~\ref{section:3.4}.
\end{mybox}

But, what if you wanted some ice cream with that pie? And maybe a cherry on
top? What if we wanted to cache the result of the select? Or, what if we
didn't want to use SQL aliasing or named parameters. (Say, because we were
using pre-existing SQL that we didn't want to touch.)
Example~\ref{example:3.2} shows a Data Map that specifies a cache, and uses a
\tt{<parameterMap>} and a \tt{<resultMap>} to keep our SQL pristine.

\begin{example}\label{example:3.2}
A Data Map definition file with some bells and whistles
\begin{verbatim}
<?xml version="1.0" encoding="UTF-8" ?>
  <sqlMap namespace="Product">

    <cacheModel id="productCache" type="LRU">
      <flushInterval hours="24"/>
      <property name="CacheSize" value="1000" />
    </cacheModel>

    <resultMap id="productResult" class="Product">
      <result property="Id" column="Product_Id"/>
      <result property="Description" column="Product_Description"/>
    </resultMap>

    <select id="GetProduct" parameterMap="productParam" cacheModel="productCache">
      select * from Products where Product_Id = ?
    </select>

    <parameterMap id="productParam" class="Product">
      <parameter property="Id"/>
    </parameterMap>

</sqlMap>
\end{verbatim}
\end{example}
In Example~\ref{example:3.2}, \tt{<parameterMap>} maps the SQL ``?'' to the
product \tt{Id} property. The \tt{<resultMap>} maps the columns to our object
properties. The \tt{<cacheModel>} keeps the result of the last one thousand of
these queries in active memory for up to 24 hours.

Example~\ref{example:3.2} is longer and more complex than
Example~\ref{example:3.1}, but considering what you get in return, it seems
like a fair trade. (A bargain even.)

Many agile developers would start with something like
Example~\ref{example:3.1} and add features like caching later. If you changed
the Data Map from Example~\ref{example:3.1} to Example~\ref{example:3.2}, you
would not have to touch your application source code at all. You can start
simple and add complexity only when it is needed.

A single Data Map definition file can contain as many Cache Models, Type
Aliases, Result Maps, Parameter Maps, and Mapped Statements (including stored
procedures), as you like. Everything is loaded into the same configuration, so
you can define elements in one Data Map and then use them in another. Use
discretion and organize the statements and maps appropriately for your
application by finding some logical way to group them.

\section{Mapped Statements}
Mapped Statements can hold any SQL statement and can use Parameter Maps and
Result Maps for input and output. (A stored procedure is a specialized form of
a statement. See section~\ref{section:3.3.1} and \ref{section:3.3.2} for more
information.)

If the case is simple, the Mapped Statement can reference the parameter and
result classes directly. Mapped Statements support caching through reference
to a Cache Model element. The following example shows the syntax for a
statement element.

\begin{example}\label{example:3.3}
Statement element syntax
\begin{verbatim}
<statement id="statement.name"
  [parameterMap="parameterMap.name"]
  [parameterClass="class.name"]
  [resultMap="resultMap.name"]
  [resultClass="class.name"]
  [listClass="class.name"]
  [cacheModel="cache.name"]
>

  select * from Products where Product_Id = [?|#propertyName#]
  order by [$simpleDynamic$]

</statement>
\end{verbatim}
\end{example}
In Example~\ref{example:3.3}, the [bracketed] parts are optional, and some
options are mutually exclusive. It is perfectly legal to have a Mapped
Statement as simple as shown by Example~\ref{example:3.4}.

\begin{example}\label{example:3.4}
A simplistic Mapped Statement
\begin{verbatim}
<statement id="InsertTestProduct" >
  insert into Products (Product_Id, Product_Description) values (1, "Shih Tzu")
</statement>
\end{verbatim}
\end{example}

Example~\ref{example:3.4} is obviously unlikely, unless you are running a
test. But it does shows that you can use SQLMap to execute arbitrary SQL
statements. More likely, you will use the object mapping features with
Parameter Maps (Chapter~\ref{section:3.4}) and Result Maps
(Chapter~\ref{section:3.5}) since that's where the magic happens.

\subsection{Statement Types}\label{section:3.3.1}
The \tt{<statement>} element is a general ``catch all'' element that can be
used for any type of SQL statement. Generally it is a good idea to use one of
the more specific statement-type elements. The more specific elements provided
better error-checking and even more functionality. (For example, the insert
statement can return a database-generated key.) Table~\ref{table:3.1}
summarizes the statement-type elements and their supported attributes and
features. The various attributes used by statement-type elements are covered
in Section~\ref{section:3.3.4}.
\begin{table}[!hpt]
\caption{The six statement-type elements } \label{table:3.1}
 \centering
\begin{tabular}{|l|l|l|l|}
  \hline
  \textbf{Statement Element} &
  \textbf{Attribute} &
  \textbf{Child Elements} &
  \textbf{Methods} \\
  \hline
  % after \\: \hline or \cline{col1-col2} \cline{col3-col4} ...
  \tt{<statement>} &
  \begin{minipage}{0.17\textwidth}
  \vspace{3mm}
    id \\
    parameterClass \\
    resultClass \\
    listClass \\
    parameterMap \\
    resultMap\\
    cacheModel
    \vspace{3mm}
    \end{minipage}
 &
   \begin{minipage}{0.22\textwidth}
     None
   \end{minipage}
 &
    \begin{minipage}{0.2\textwidth}
      Insert \\ Update \\ Delete \\ All query methods
    \end{minipage}
 \\
  \hline
  \tt{<insert>} &
  \begin{minipage}{0.17\textwidth}
  \vspace{3mm}
    id \\
    parameterClass \\
    parameterMap
    \vspace{3mm}
    \end{minipage}
 &
   \begin{minipage}{0.22\textwidth}
     \tt{<selectKey>}\\
     \tt{<generate>}
   \end{minipage}
 &
    \begin{minipage}{0.2\textwidth}
      Insert \\ Update \\ Delete
    \end{minipage}
\\
\hline
  \tt{<update>} &
  \begin{minipage}{0.17\textwidth}
  \vspace{3mm}
    id \\
    parameterClass \\
    parameterMap \\
    extends
    \vspace{3mm}
    \end{minipage}
 &
   \begin{minipage}{0.22\textwidth}
     \tt{<generate>}
   \end{minipage}
 &
    \begin{minipage}{0.2\textwidth}
      Insert \\ Update \\ Delete
    \end{minipage}
 \\
 \hline
   \tt{<delete>} &
  \begin{minipage}{0.17\textwidth}
  \vspace{3mm}
    id \\
    parameterClass \\
    parameterMap \\
    extends
    \vspace{3mm}
    \end{minipage}
 &
   \begin{minipage}{0.22\textwidth}
     \tt{<generate>}
   \end{minipage}
 &
    \begin{minipage}{0.2\textwidth}
      Insert \\ Update \\ Delete
    \end{minipage}
 \\
 \hline
  \tt{<select>} &
  \begin{minipage}{0.17\textwidth}
  \vspace{3mm}
    id\\
    parameterClass\\
    resultClass\\
    listClass \\
    parameterMap \\
    resultMap \\
    cacheModel \\
    extends
    \vspace{3mm}
    \end{minipage}
 &
   \begin{minipage}{0.22\textwidth}
     \tt{<generate>}
   \end{minipage}
 &
    \begin{minipage}{0.2\textwidth}
      All query methods
    \end{minipage}
 \\
 \hline
   \tt{<procedure>} &
  \begin{minipage}{0.17\textwidth}
  \vspace{3mm}
    id\\
    parameterMap \\
    resultClass\\
    resultMap \\
    cacheModel
    \vspace{3mm}
    \end{minipage}
 &
   \begin{minipage}{0.22\textwidth}
     None
   \end{minipage}
 &
    \begin{minipage}{0.2\textwidth}
      Insert \\ Update \\ Delete \\ All query methods
    \end{minipage}
 \\
 \hline
\end{tabular}
\end{table}

\subsection{Stored Procedures}\label{section:3.3.2}

????

\section{The SQL} \label{section:3.3.3}
If you are not using stored procedures, the most important part of a
statement-type element is the SQL. You can use any SQL statement that is valid
for your database system. Since SQLMap passes the SQL through to a standard
libraries (Adodb for PHP), you can use any statement with SQLMap that you
could use without SQLMap. You can use whatever functions your database system
supports, and even send multiple statements, so long as your driver or
provider supports them.

%If standard, static SQL isn't enough, iBATIS can help you build a dynamic SQL
%statement. See Section 3.9 for more about Dynamic SQL.


\subsection{Escaping XML symbols} Because you are combining SQL and XML in a
single document, conflicts can occur. The most common conflict is the
greater-than and less-than symbols (><). SQL statements use these symbols as
operators, but they are reserved symbols in XML. A simple solution is to
escape the SQL statements that uses XML reserved symbols within a CDATA
element. Example~\ref{example:3.6} demonstrates this.

\begin{example}\label{example:3.6}
Using CDATA to ``escape'' SQL code
\begin{verbatim}
<statement id="SelectPersonsByAge" parameterClass="int" resultClass="Person">
  <![CDATA[
     SELECT * FROM PERSON WHERE AGE > #value#
  ]]>
</statement>
\end{verbatim}
\end{example}

\subsection{Auto-Generated Keys}
Many database systems support auto-generation of primary key fields, as a
vendor extension. Some vendors pre-generate keys (e.g. Oracle), some vendors
post-generate keys (e.g. MS-SQL Server and MySQL). In either case, you can
obtain a pre-generated key using a \tt{<selectKey>} stanza within an
\tt{<insert>} element. Example~\ref{example:3.7} shows an \tt{<insert>}
statement for either approach.

\begin{example}\label{example:3.7}
\normalfont \tt{<insert>} statements using \tt{<selectKey>} stanzas
\begin{verbatim}
<!��Oracle SEQUENCE Example using .NET 1.1 System.Data.OracleClient -->
<insert id="insertProduct-ORACLE" parameterClass="product">
  <selectKey resultClass="int" type="pre" property="Id" >
     SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL
  </selectKey>
  insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#)
</insert>

<!�� Microsoft SQL Server IDENTITY Column Example -->
<insert id="insertProduct-MS-SQL" parameterClass="product">
  insert into PRODUCT (PRD_DESCRIPTION)
  values (#description#)
 <selectKey resultClass="int" type="post" property="id" >
   select @@IDENTITY as value
 </selectKey>
</insert>

<!-- MySQL Example -->
<insert id="insertProduct-MYSQL" parameterClass="product">
  insert into PRODUCT (PRD_DESCRIPTION)
  values (#description#)
 <selectKey resultClass="int" type="post" property="id" >
   select LAST_INSERT_ID() as value
 </selectKey>
</insert>
\end{verbatim}
\end{example}

\subsection{\tt{<generate>} tag}
You can use SQLMap to execute any SQL statement your application requires.
When the requirements for a statement are simple and obvious, you may not even
need to write a SQL statement at all. The \tt{<generate>} tag can be used to
create simple SQL statements automatically, based on a \tt{<parameterMap>}
element. The four CRUD statement types (insert, select, update, and delete)
are supported. For a select, you can select all or select by a key (or keys).
Example~\ref{example:3.8} shows an example of generating the usual array of
CRUD statements.

\begin{mybox}{Important:}
The intended use of the \tt{<generate>} tag is to save developers the trouble
of coding mundane SQL statements (and only mundane statements). It is not
meant as a object-to-relational mapping tool. There are many frameworks that
provide extensive object-to-relational mapping features. The \tt{<generate>}
tag is not a replacement for any of those. When the \tt{<generate>} tag does
not suit your needs, use a conventional statement instead.
\end{mybox}

\begin{example}\label{example:3.8}
\normalfont Creating the ``usual suspects'' with the \tt{<generate>} tag
\begin{verbatim}
  <parameterMap id="insert-generate-params">
    <parameter property="Name" column="Category_Name"/>
    <parameter property="Guid" column="Category_Guid" dbType="UniqueIdentifier"/>
  </parameterMap>

  <parameterMap id="update-generate-params" extends="insert-generate-params">
    <parameter property="Id" column="Category_Id" />
  </parameterMap>

  <parameterMap id="delete-generate-params">
    <parameter property="Id" column="Category_Id" />
    <parameter property="Name" column="Category_Name"/>
  </parameterMap>

  <parameterMap id="select-generate-params">
    <parameter property="Id" column="Category_Id" />
    <parameter property="Name" column="Category_Name"/>
    <parameter property="Guid" column="Category_Guid" dbType="UniqueIdentifier"/>
  </parameterMap>

  <update id="UpdateCategoryGenerate" parameterMap="update-generate-params">
    <generate table="Categories" by="Category_Id"/>
  </update>

  <delete id="DeleteCategoryGenerate" parameterMap="delete-generate-params">
    <generate table="Categories" by="Category_Id, Category_Name"/>
  </delete>

  <select id="SelectByPKCategoryGenerate" resultClass="Category" parameterClass="Category"
          parameterMap="select-generate-params">
    <generate table="Categories" by="Category_Id"/>
  </select>

  <select id="SelectAllCategoryGenerate" resultClass="Category"
          parameterMap="select-generate-params">
    <generate table="Categories" />
  </select>

  <insert id="InsertCategoryGenerate" parameterMap="insert-generate-params">
    <selectKey property="Id" type="post" resultClass="int">
      select @@IDENTITY as value
    </selectKey>
    <generate table="Categories" />
  </insert>
\end{verbatim}
\end{example}
The tag generates ANSI SQL, which should work with any compliant database.
Special types, such as blobs, are not supported, and vendor-specific types are
also not supported. But, the generate tag does keep the simple things simple.

\begin{mybox}{Note:}
The SQL is generated when the DataMapper instance is built and can be cached
afterward, so there is no performance impact at execution time.
\end{mybox}

The generate tag supports two attributes :
\begin{table}[!htb]\centering\label{table:3.2}
\caption{\tt{<generate>} attributes}
\begin{tabular}{|l|l|l|}
  \hline
  % after \\: \hline or \cline{col1-col2} \cline{col3-col4} ...
  \textbf{Attribute} & \textbf{Description} & \textbf{Required} \\
  \hline
  table & specifies the table name to use in the SQL statement. & yes \\
  \hline
  by & specifies the columns to use in a WHERE clause & no \\
  \hline
\end{tabular}
\end{table}

\section{Statement-type Element Attributes}\label{section:3.3.4}
The six statement-type elements take various attributes. See
Section~\ref{section:3.3.1} for a table itemizing which attributes each
element-type accepts. The individual attributes are described in the sections
that follow.

\subsection{\tt{id} attribute}
The required \tt{id} attribute provides a name for this statement, which must
be unique within this \tt{<SqlMap>}.

\subsection{\tt{parameterMap} attribute}
A Parameter Map defines an ordered list of values that match up with the ``?''
placeholders of a standard, parameterized query statement.
Example~\ref{example:3.9} shows a \tt{<parameterMap>} and a corresponding
\tt{<statement>}.

\begin{example}\label{example:3.9}
A \tt{parameterMap} and corresponding statement
\begin{verbatim}
<parameterMap id="insert-product-param" class="Product">
  <parameter property="id"/>
  <parameter property="description"/>
</parameterMap>

<statement id="insertProduct" parameterMap="insert-product-param">
  insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?);
</statement>
\end{verbatim}
\end{example}

In Example~\ref{example:3.9}, the Parameter Map describes two parameters that
will match, in order, two placeholders in the SQL statement. The first ``?''
is replaced by the value of the \tt{id} property. The second is replaced with
the \tt{description} property.

SQLMap also supports named, inline parameters, which most developers seem to
prefer. However, Parameter Maps are useful when the SQL must be kept in a
standard form or when extra information needs to be provided. For more about
Parameter Maps see Chapter~\ref{section:3.4}.

\subsection{\tt{parameterClass} attribute }
If a \tt{parameterMap} attribute is not specified, you may specify a
\tt{parameterClass} instead and use inline parameters (see
Section~\ref{section:3.4.3}). The value of the \tt{parameterClass} attribute
can be any existing PHP class name. Example~\ref{example:3.10} shows a
statement using a PHP class named \tt{Product} in \tt{parameterClass}
attribute.

\begin{example}\label{example:3.10}
Specify the \tt{parameterClass} with a PHP class name.
\begin{verbatim}
<statement id="statementName" parameterClass="Product">
  insert into PRODUCT values (#id#, #description#, #price#)
</statement>
\end{verbatim}
\end{example}

\subsection{\tt{resultMap} attribute}
A Result Map lets you control how data is extracted from the result of a
query, and how the columns are mapped to object properties.
Example~\ref{example:3.11} shows a \tt{<resultMap>} element and a
corresponding \tt{<statement>} element.
\begin{example}\label{example:3.11}
A \tt{<resultMap>} and corresponding \tt{<statement>}
\begin{verbatim}
<resultMap id="select-product-result" class="product">
  <result property="id" column="PRD_ID"/>
  <result property="description" column="PRD_DESCRIPTION"/>
</resultMap>

<statement id="selectProduct" resultMap="select-product-result">
  select * from PRODUCT
</statement>
\end{verbatim}
\end{example}

In Example~\ref{example:3.11}, the result of the SQL query will be mapped to
an instance of the \tt{Product} class using the ``select-product-result''
\tt{<resultMap>}. The \tt{<resultMap>} says to populate the \tt{id} property
from the \tt{PRD\_ID} column, and to populate the \tt{description} property
from the \tt{PRD\_DESCRIPTION} column.

\begin{mybox}{Tip:}
In Example~\ref{example:3.11}, note that using `` select * '' is supported. If
you want all the columns, you don't need to map them all individually. (Though
many developers consider it a good practice to always specify the columns
expected.)
\end{mybox}

For more about Result Maps, see Chapter~\ref{section:3.5}.

\subsection{\tt{resultClass} attribute}
If a \tt{resultMap} is not specified, you may specify a \tt{resultClass}
instead. The value of the \tt{resultClass} attribute can be the name of a PHP
class or primitives like \tt{integer}, \tt{string}, or \tt{array}. The class
specified will be automatically mapped to the columns in the result, based on
the result metadata. The following example shows a \tt{<statement>} element
with a \tt{resultClass} attribute.

\begin{example}\label{example:3.12}
A \tt{<statement>} element with \tt{resultClass} attribute
\begin{verbatim}
<statement id="SelectPerson" parameterClass="int" resultClass="Person">
  SELECT
  PER_ID as Id,
  PER_FIRST_NAME as FirstName,
  PER_LAST_NAME as LastName,
  PER_BIRTH_DATE as BirthDate,
  PER_WEIGHT_KG as WeightInKilograms,
  PER_HEIGHT_M as HeightInMeters
  FROM PERSON
  WHERE PER_ID = #value#
</statement>
\end{verbatim}
\end{example}

In Example~\ref{example:3.12}, the \tt{Person} class has properties including:
\tt{Id}, \tt{FirstName}, \tt{LastName}, \tt{BirthDate},
\tt{WeightInKilograms}, and \tt{HeightInMeters}. Each of these corresponds
with the column aliases described by the SQL select statement using the ``as''
keyword �Ca standard SQL feature. When executed, a \tt{Person} object is
instantiated and populated by matching the object property names to the column
names from the query.

Using SQL aliases to map columns to properties saves defining a
\tt{<resultMap>} element, but there are limitations. There is no way to
specify the types of the output columns (if needed), there is no way to
automatically load related data such as complex properties.You can overcome
these limitations with an explicit Result Map (Chapter~\ref{section:3.5}).

\subsection{\tt{listClass} attribute}
In addition to providing the ability to return an \tt{TList} of objects, the
DataMapper supports the use of custom collection: a class that implements
\tt{ArrayAccess}. The following is an example of a TList (it implements
ArrayAccess) class that can be used with the DataMapper.

\begin{example}\label{example:3.13}
An \tt{ArrayAccess} implementation, by extending \tt{TList}.
\begin{verbatim}
class AccountCollection extends TList
{
    public function addRange($accounts)
    {
        foreach($accounts as $account)
            $this->add($account);
    }

    public function copyTo(TList $array)
    {
        $array->copyFrom($this);
    }
}
\end{verbatim}
\end{example}
An \tt{ArrayAccess} class can be specified for a select statement through the
\tt{listClass} attribute. The value of the \tt{listClass} attribute is the
full name of a PHP class that implements \tt{ArrayAccess}. The statement
should also indicate the \tt{resultClass} so that the DataMapper knows how to
handle the type of objects in the collection. The \tt{resultClass} specified
will be automatically mapped to the columns in the result, based on the result
metadata. The following example shows a \tt{<statement>} element with a
\tt{listClass} attribute.

\begin{example}\label{example:3.14}
A \tt{<statement>} element with \tt{listClass} attribute
\begin{verbatim}
<statement id="GetAllAccounts"
 listClass="AccountCollection"
 resultClass="Account">
   select
   Account_ID as Id,
   Account_FirstName as FirstName,
   Account_LastName as LastName,
   Account_Email as EmailAddress
   from Accounts
   order by Account_LastName, Account_FirstName
</statement>
\end{verbatim}
\end{example}

\subsection{\tt{cacheModel} attribute}
If you want to cache the result of a query, you can specify a Cache Model as
part of the \tt{<statement>} element. Example~\ref{example:3.15} shows a
\tt{<cacheModel>} element and a corresponding \tt{<statement>}.

\begin{example}\label{example:3.15}
A \tt{<cacheModel>} element with its corresponding \tt{<statement>}
\begin{verbatim}
<cacheModel id="product-cache" implementation="LRU">
  <flushInterval hours="24"/>
  <flushOnExecute statement="insertProduct"/>
  <flushOnExecute statement="updateProduct"/>
  <flushOnExecute statement="deleteProduct"/>
  <property name="size" value="1000" />
</cacheModel>

<statement id="selectProductList" parameterClass="int" cacheModel="product-cache">
  select * from PRODUCT where PRD_CAT_ID = #value#
</statement>
\end{verbatim}
\end{example}

In Example~\ref{example:3.15}, a cache is defined for products that uses a
Least Recently Used [LRU] type and flushes every 24 hours or whenever
associated update statements are executed. For more about Cache Models, see
Section~\ref{section:3.8}.

\subsection{\tt{extends} attribute}
When writing Sql, you often encounter duplicate fragments of SQL. SQLMap
offers a simple yet powerful attribute to reuse them.

\begin{verbatim}
<select id="GetAllAccounts"
  resultMap="indexed-account-result">
select
  Account_ID,
  Account_FirstName,
  Account_LastName,
  Account_Email
from Accounts
</select>

<select id="GetAllAccountsOrderByName"
  extends="GetAllAccounts"
  resultMap="indexed-account-result">
    order by Account_FirstName
</select>
\end{verbatim}