ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Object-Relational Mapping with SQLMaps

by Sunil Patil


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:
        <settings useStatementNamespaces="false" />
        <transactionManager type="JDBC">
            <dataSource type="SIMPLE" >
                <property name="JDBC.Driver"
                <property name="JDBC.ConnectionURL"
                <property name="JDBC.Username"
                <property name="JDBC.Password"
        <sqlMap resource="Contact.xml"/>
    SqlMapConfig.xml is the deployment descriptor for SQLMaps and contains the following elements:
    • <sqlMapConfig> is the root element of the file. The <settings> element is used for defining application-level settings; for instance, the useStatementNamespaces attribute is used to define whether you want to use the fully qualified name of the prepared statement. It can have a few more attributes for controlling caching and lazy initialization; please look into the documentation for further details.
    • The <transactionManager> element is used to define what kind of transaction management you want to use in your application. In our sample application, we want to use the Connection object's commit and rollback methods to manage transactions, so we are using JDBC as the transaction manager. It contains <dataSource> as a child element, which defines the type of Connection management you want to use. In our sample application, we want to use SQLMaps' own implementation of connection pooling, so we are using a datasource of type SIMPLE. SQLMaps requires information like the JDBC driver name, URL, and password in order to create the connection pool, so we are using <property> elements for passing that information. We will cover various available transaction and connection management options in more detail later.
    • The <sqlMap> element is used to declare sqlmap config files. These files, discussed earlier, list the SQL queries that you wish to execute.
  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"
        <select id="getContact"
            parameterClass="int" resultClass="contact"">
                select CONTACTID as contactId,
                       FIRSTNAME as firstName,
                       LASTNAME as lastName from
                       ADMINISTRATOR.CONTACT where CONTACTID = #id#
    The tags used in the file are as follows:
    • <sqlMap> is the root element of the file. Your application will normally have more than one table, and since you will want to separate queries related to different tables into different namespaces, the <namespace> element is used to specify the namespace in which all of the queries in this file should be placed.
    • <typeAlias> is used to declare a short name for the fully qualified name of the Contact class. After this declaration, the short name can be used instead of the fully qualified name.
    • The <select> element should be used for declaring a SELECT query in the SQLMaps framework. You can specify the query to be executed as the value of the element. The id attribute is used to specify the name that will be used to instruct SQLMaps to execute this particular query. parameterClass is used to specify which class is used for passing query parameters and resultClass provides the name of the class that should be used to return values from the ResultSet.
  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 =
        Reader configReader =
        SqlMapClient sqlMap =
        Contact contact = (Contact)
        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.

Pages: 1, 2

Next Pagearrow