'Open Projects/iBATIS' 카테고리의 글 목록

'Open Projects/iBATIS'에 해당되는 글 1건

2005. 2. 12. 13:32

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:

Connection and transaction management.

Setting Java objects as query parameters.

Converting SQL ResultSets into Java objects.

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.

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:

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

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:
- <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.

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

}
```

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:
- <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.

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.

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.

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>
```

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>
```

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:

Add log4j.jar to your application classpath. For a web application, you will have to copy it to WEB-INF/lib.

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

Does not depend on what Dialects are supported by an OR mapping framework.

Very easy to use; supports many advanced features.

Doesn't require learning a new query language like EJBQL. Allows you to take advantage of your existing knowledge of SQL.

Disadvantages

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.