ONJava.com    
 Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples


Object-Relational Mapping with SQLMaps

by Sunil Patil
02/02/2005

Introduction

Nowadays a lot of work is going on in the object-relational (OR) mapping field, with Hibernate having seemingly taken the lead over other frameworks. But there is one problem with object-relational mapping tools: most database administrators seem not to be very comfortable with the queries generated by these OR mapping tools. Sadly, these DBAs don't understand how brilliant your framework is in automatically generating queries for you, and how flexible it makes your application. They feel that with the database being your application's primary bottleneck, you should have complete control over SQL queries, so that they will be able to analyze and tune them for performance.

But the problem is that if you don't use an OR mapping tool, then you have to spend a lot of resources in writing and maintain low-level JDBC code. Every JDBC application will have repetitive code for:

  1. Connection and transaction management.
  2. Setting Java objects as query parameters.
  3. Converting SQL ResultSets into Java objects.
  4. Creating query strings.

iBatis' SQLMaps framework helps you to significantly reduce the amount of Java code that you normally need to access a relational database. It takes care of three of the above concerns, in that it allows an easy mapping of a JavaBean object to PreparedStatement parameters and ResultSet values. The philosophy behind SQLMaps is simple: provide a simple framework to provide 80 percent of JDBC's functionality.

Related Reading

SQL in a Nutshell
A Desktop Quick Reference
By Kevin Kline

This article is a step-by-step tutorial about how to use the SQLMaps framework. We will start by creating a sample Struts application and configure it to use SQLMaps. Then we will cover how to perform basic database operations like SELECT, INSERT, UPDATE, etc. Next, we will cover what options SQLMaps provides for connection and transaction management. And at the end, we will try to use some advanced features of SQLMaps like caching and paging.

The Basic Idea Behind SQLMaps

To use the SQLMaps framework, you create a XML file that lists all of the SQL queries that you wish to execute through your application. For each SQL query, you specify with which Java class the query will exchange parameters and ResultSets.

Inside of your Java code, when you want to execute a particular query, you will create an object to pass query parameters and necessary conditions, and then pass this object and name of the query to be executed to SQLMaps. Once the query is executed, SQLMaps will create an instance of the class you have specified to receive query results, and populate it with values from the ResultSet returned by the database.

A Simple Application Using SQLMaps (Hello World)

We will start by creating a sample Struts application to demonstrate what needs to change in your application to use SQLMaps. The code for this sample may be found in the Resources section below. In this sample, application we will create a JSP page that asks the user for a contactId. Once it is submitted, we use it to search for a contact in the CONTACT table, which is displayed to the user using another JSP. Follow these step-by-step instructions:

  1. Copy ibatis-sqlmap-2.jar and ibatis-common-2.jar to your web-inf/lib directory.

  2. Create a SqlMapConfig.xml file in your Java source folder, like this:
    
    <sqlMapConfig>
        <settings useStatementNamespaces="false" />
        <transactionManager type="JDBC">
            <dataSource type="SIMPLE" >
                <property name="JDBC.Driver"
                    value="COM.ibm.db2.jdbc.app.DB2Driver"/>
                <property name="JDBC.ConnectionURL"
                    value="jdbc:db2:SAMPLE"/>
                <property name="JDBC.Username"
                    value="db2admin"/>
                <property name="JDBC.Password"
                    value="admin2db"/>
            </dataSource>
        </transactionManager>
        <sqlMap resource="Contact.xml"/>
    </sqlMapConfig>
    
    SqlMapConfig.xml is the deployment descriptor for SQLMaps and contains the following elements:
  3. Create a JavaBean-type class, Contact.java, that has firstName, lastName, and contactId properties and corresponding getter and setter methods. This class will be used for passing query parameters and reading values from the ResultSet.
    
    public class Contact implements Serializable{
    	private String firstName;
    	private String lastName;
    	private int contactId;
    	//Getter setter methods for firstName,
    	//lastName and contactId property
    }
    
  4. Create a Contact.xml file like this, where we will list all Contact-table-related SQL queries that we want to execute:
    
    <sqlMap namespace="Contact"">
        <typeAlias alias="contact"
            type="com.sample.contact.Contact"/">
        <select id="getContact"
            parameterClass="int" resultClass="contact"">
                select CONTACTID as contactId,
                       FIRSTNAME as firstName,
                       LASTNAME as lastName from
                       ADMINISTRATOR.CONTACT where CONTACTID = #id#
        </select>
    </sqlMap>
    
    The tags used in the file are as follows:
  5. Inside of the execute() method of our Action class, we build an instance of SqlMapClient, which is used for interacting with SQLMaps. We have to pass the SqlMapConfig.xml file to SqlMapClientBuilder, which is used to read configuration settings.

    
        DynaActionForm contactForm =
            (DynaActionForm)form;
        Reader configReader =
            Resources.getResourceAsReader("SqlMapConfig.xml");
        SqlMapClient sqlMap =
            SqlMapClientBuilder.buildSqlMapClient(configReader);
        Contact contact = (Contact)
            sqlMap.queryForObject("getContact",
                        contactForm.get("contactId"));
        request.setAttribute("contactDetail", contact);
        return mapping.findForward("success");
    
    SQLMaps' queryForObject method should be used when you want to execute a SELECT query. In Contact.xml, we have specified int as parameterClass class, so we are passing contactId as an integer, along with the name of the query (i.e, getContact). SQLMaps will then return an object of the Contact class.

Basic Database Operation

Now we will turn our focus on how to perform some basic database operations using SQMLaps.
  1. Insert

    We will start with how to execute an INSERT query.

    
    <insert id="insertContact" parameterClass="contact">
    INSERT INTO ADMINISTRATOR.CONTACT( CONTACTID,FIRSTNAME,LASTNAME)
            VALUES(#contactId#,#firstName#,#lastName#);
     </insert>
    

    The <insert> element is used to declare an INSERT SQL query. It will have a parameterClass attribute to indicate which JavaBean class should be used to pass request parameters. We want to use the value of the contactId attribute while inserting new records, so we have to use a #contactId# in our SQL query.

    
    public void contactInsert() throws SQLException, IOException {
        sqlMap.startTransaction();
        try {
        sqlMap.startTransaction();
        Contact contact = new Contact();
        contact.setContactId(3);
        contact.setFirstName("John");
        contact.setLastName("Doe");
        sqlMap.insert("insertContact",contact);
        sqlMap.commitTransaction();
        } finally{
            sqlMap.endTransaction();
        }
    }
    

    Inside of our Java code, we create a Contact object, populate its values, and then call sqlMap.insert(), passing the name of the query that we want to execute and the Contact. This method will insert the new contact and return the primary key of the newly inserted contact.

    By default, SQLMaps treats every DML method as a single unit of work. But you can use the startTransaction, commitTransaction, and endTransaction methods for transaction boundary demarcation. You can start a transaction by calling the startTransaction() method, which will also retrieve a connection from connection pool. This connection object will be used for executing queries in this transaction. If all of the queries in the transaction are executed successfully, you should call commitTransaction() to commit your changes. Irrespective of whether your transaction was successful or not, you should call the endTransaction method in the end, which will return the connection object back to the pool, and is thus necessary for proper cleanup.

  2. Update

    The <update> element is used to declare an update query. Its parameterClass element is used to declare the name of the JavaBean class used to pass query parameters. Inside of your Java code you can instruct SQLMaps to fire an update query with sqlMap.update("updateContact",contact). This method will return number of affected rows.

    
    <update id="updateContact" parameterClass="contact">
    update ADMINISTRATOR.CONTACT SET
    FIRSTNAME=#firstName# ,
    LASTNAME=#lastName#
    where contactid=#contactId#
    </update>
    
  3. Delete

    The <delete> element is used to declare a DELETE query. Inside of your Java class, you execute the statement like this: sqlMap.delete("deleteContact",new Integer(contactId)). The method returns the number of affected rows.

    
    <delete id="deleteContact" parameterClass="int">
    DELETE FROM ADMINISTRATOR.CONTACT WHERE CONTACTID=#contactId#
    </delete>
    
  4. Procedure

    Stored procedures are supported via theprocedureelement. Most of the stored procedures take some parameters, which can be of the types IN, INOUT, or OUT. So you create <parameterMap> elements and list the parameters that you want to pass to the stored procedure. The parameterMap object is changed only if the parameter type is either OUT or INOUT.

    
    <parameterMap id="swapParameters" class="map" >
        <parameter property="contactId" jdbcType="INTEGER"
            javaType="java.lang.Integer" mode="IN"/>
        <parameter property="firstName" jdbcType="VARCHAR"
            javaType="java.lang.String" mode="IN"/>
        <parameter property="lastName" jdbcType="VARCHAR"
            javaType="java.lang.String" mode="IN"/>
    </parameterMap>
    
    <procedure id="swapContactName" parameterMap="swapParameters" >
    {call swap_contact_name (?, ?,?)}
    </procedure>
    
    

    Inside of your Java code first, create a HashMap of parameters that you want to pass to the procedure, and then pass it to sqlMap along with name of the query that you want to execute.

    
    HashMap paramMap = new HashMap();
    paramMap.put("contactId", new Integer(1));
    paramMap.put("firstName", "Sunil");
    paramMap.put("lastName", "Patil");
    sqlMap.queryForObject("swapCustomerName", paramMap);
    

Connection and Transaction Management

The SQLMaps framework takes care of connection management for you. By default, it ships with three different implementations of connection management. You can specify which implementation you want to use by the value of the type attribute of the <dataSource> element.

SQLMaps uses DataSourceFactory implementations for connection management, so you can create your own class implementing this interface and instruct SQLMaps to use it, if you like.

For transaction management, the value of the <transactionManager> element in SqlMapConfig.xml indicates which class should be used for transaction management:

Advanced Features

Now we can spend some time talking about advanced features of the SQLMaps framework. The scope of this article does not allow me to cover all of them, so I will be talking about few that i think are commonly useful; you can look into the SQLMaps documentation (PDF) to find out what features are supported.

Caching

The <cacheModel> element is used to describe a cache for use with a query-mapped statement.

12345678901234567890123456789012345678901234567890
  <cacheModel id="contactCache" type="LRU">
  <flushOnExecute statement="insertContact"/>
  <flushOnExecute statement="updateContact"/>
  <flushOnExecute statement="deleteContact"/>
      <property name="size" value="1000"/>
  </cacheModel>

  <select id="getCachedContact" parameterClass="int"
    resultClass="contact" cacheModel="contactCache">
    select FIRSTNAME as firstName,LASTNAME as lastName
        from CONTACT where  CONTACTID = #contactId#
  </select>

Each query can have a different cache model, or more than one query can share the same cache. SQLMaps supports a pluggable framework for supporting different types of caches. Which implementation should be used is specified in the type attribute of the cacheModel element.

The cacheModel attribute of the <select> element defines which caching model should be used for caching its results. You can disable caching globally for SqlMapClient by setting the value of the cacheModelsEnabled attribute of <settings> to false.

How to Enable Logging

SQLMaps provides logging information through the use of the Jakarta Commons logging framework . Follow these steps to enable logging:

  1. Add log4j.jar to your application classpath. For a web application, you will have to copy it to WEB-INF/lib.
  2. Create a log4j.properties file like the following in your classpath root:
    
    log4j.rootLogger=ERROR, stdout
    # SqlMap logging configuration...
    log4j.logger.com.ibatis=DEBUG
    log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
    log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
    log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
    log4j.logger.java.sql.Connection=DEBUG
    log4j.logger.java.sql.Statement=DEBUG
    log4j.logger.java.sql.PreparedStatement=DEBUG
    log4j.logger.java.sql.ResultSet=DEBUG
    # Console output...
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
    

Paging

Assume that our CONTACT table has 1000 records and we want to display it in a spreadsheet to the user, but only 50 records at a time. In this situation, we don't want to query the CONTACT table to get a ResultSet containing 1000 contacts; we want to query the CONTACT table and get 50 records at time. SQLMaps provides the PaginatedList interface for handling this type of situation. It allows you to deal with a subset of data through which the user can navigate forwards and backwards.


PaginatedList list = sqlMap.queryForPaginatedList("getContacts", null, 2);
while (true) {
    Iterator listIterator = list.iterator();
    while (listIterator.hasNext()) {
        System.out.println(
            ((Contact)listIterator.next()).getContactId());
    }
    if( list.isNextPageAvailable())
        list.nextPage();
    else
        break;
}

Conclusion

SQLMaps is a very good option if your application has a small number of fixed queries. It is very easy to use and allows the developer to take advantage of his or her knowledge of SQL. It also helps you achieve separation of roles, since a developer can list out queries that he or she needs and then start working on his or her Java code, giving the SQLMaps XML file to a DBA who will try to analyze and tune SQL queries.

Advantages

  1. Does not depend on what Dialects are supported by an OR mapping framework.
  2. Very easy to use; supports many advanced features.
  3. Doesn't require learning a new query language like EJBQL. Allows you to take advantage of your existing knowledge of SQL.

Disadvantages

  1. Applications will not be portable if you use advanced features.

But if your application is going to work on more than multiple databases, or if it has a large number of queries, then you may want to look at several OR mapping frameworks before making a final decision.

Resources

Sunil Patil has worked on J2EE technologies for more than five years. His areas of interest include object relational mapping tools, UI frameworks, and portals.


Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.