Working With Data Maps

If you want to know how to configure and install SQLMap, see the Installation and Configuration. 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" ?>
  <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>

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 library method:

TMapper::instance()->insert("InsertLineItem",$lineItem);

No fuss, no muss.

<statement id="InsertProduct">
  insert into Products (Product_Id, Product_Description)
  values (#Id#, #Description#);
</statement>

insert into Products (Product_Id, Product_Description) values (5, 'dog');

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" ?>
  <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>

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.