Working With Data Maps
If you want to know how to configure and install SQLMap,
see the Installation and
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.
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 below.
<?xml version="1.0" encoding="UTF-8" ?>
<insert id="InsertLineItem" parameterClass="LineItem">
INSERT INTO [LinesItem]
(Order_Id, LineItem_LineNum, Item_Id, LineItem_Quantity, LineItem_UnitPrice)
(#Order.Id#, #LineNumber#, #Item.Id#, #Quantity#, #Item.ListPrice#)
This map takes some properties from a 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 LineItem instance directly to a
No fuss, no muss.
A Quick Glance at Inline Parameters
Say we have a mapped statement element that looks like this:
insert into Products (Product_Id, Product_Description)
values (#Id#, #Description#);
The inline parameters here are #Id# and #Description#. Let's
also say that we have an object with the properties Id and
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:
insert into Products (Product_Id, Product_Description) values (5, 'dog');
See inline parameters
for further details.
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.)
The following example shows a Data Map that specifies a cache, and uses a
<parameterMap> and a <resultMap> to keep our SQL pristine.
<?xml version="1.0" encoding="UTF-8" ?>
<cacheModel id="productCache" type="LRU">
<property name="CacheSize" value="1000" />
<resultMap id="productResult" class="Product">
<result property="Id" column="Product_Id"/>
<result property="Description" column="Product_Description"/>
<select id="GetProduct" parameterMap="productParam" cacheModel="productCache">
select * from Products where Product_Id = ?
<parameterMap id="productParam" class="Product">
In the above example, <parameterMap> maps the SQL "?" to the
product Id property. The <resultMap> maps the columns to our object
properties. The <cacheModel> keeps the result of the last one thousand of
these queries in active memory for up to 24 hours.
The above example is longer and more complex than
the previous example, but considering what you get in return, it seems
like a fair trade. (A bargain even.)
Many agile developers would start with something like
the first example and add features like caching later. If you changed
the Data Map from the first example to the second example, 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, 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.