Object-Relational Mapping with SQLMaps
Pages: 1, 2

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.

• SIMPLE: Use SQLMaps' own connection pool implementation. While using this implementation, you have to pass connection information (such as a JDBC driver name, username, and password) to SQLMaps.
• DBCP: Use Apache's DBCP connection pooling algorithm.
• JNDI: Use a container supplied datasource. If you want to use this method, then first configure the JDBC datasource in the container (in some container-specific way), and then specify the JNDI name of datasource like this:

  
  <transactionManager type="JDBC" >
      <dataSource type="JNDI">
          <property name="DataSource"
              value="java:comp/env/jdbc/testDB"/>
      </dataSource>
  </transactionManager>
  

  The value of DataSource property should point to the JNDI name of the datasource you want to use.

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:

• JDBC: In this case, transactions are controlled by calling begin() and commit() methods on the underlying Connection object. This option should be used in applications that run in an outside container and interact with a single database.
• JTA: In this case, a global JTA transaction is used. SQLMaps activities can be included as a part of a wider-scope transaction that possibly involves other databases and transaction resources.
• External: In this case, you have to manage the transaction on your own. A transaction will not be committed or rolled back as part of the framework lifecycle. This setting is useful for non-transactional (read-only) databases.

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.

• LRU: Removes the least recently used element from the cache when the cache is full.
• FIFO: Removes the oldest object from the cache once the cache is full.
• MEMORY: Uses Java reference types such as SOFT, WEAK, and STRONG to manage cache behavior. It allows the garbage collector to determine what stays in memory and what gets deleted. This implementation should be used in applications where memory is scarce.
• OSCACHE: A plugin for the OSCache2.0 caching engine. You need oscache.properties in your root folder to configure OSCache. This implementation can be used in distributed applications.

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

• Sample code for this article
• iBatis home page
• Struts home page

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.