Segmentation Fault: Core dumped..;-)

iBatis SQLMaps and OSCache: Data Access and Manipulation 201

Posted : May 26, 2004 at 9:41 pm [America/Los_Angeles]

I am not a SQL developer by profession, but I know enough to know that it is an incredibly simple and yet extremely powerful data access and manipulation language. And while I must say that I have been fairly impressed with the plethora of QLs like EQL, HQL, myNeighbor’s-QL (just kidding) that have sprung up in the last few years, I continue to find Codd’s SQL (and the myriad flavors of it) far more stable, predictable and powerful for my data access and manipulation needs at this time. However, I am still holding out hope that some day soon I will have my “Eureka” moment vis-a-vis Hibernate and some of the other O/R tools and APIs out there

See, here’s the deal. Pure JDBC code (or the many customized homegrown wrappers) were exciting to use for accessing and manipulating relational data a few years back. However, thanks to SQLExecutor, Spring’s JDBCTemplate, iBatis SQLMaps and many such APIs, there are plenty of nice options out there for folks who want to make things somewhat simpler and cleaner. Not to mention add a bunch of functionalities besides data access and manipulation. For example, caching the Result Set, something that would ordinarily take some coding on the part of the programmer if he/she was working purely in the JDBC realm.

Enter iBatis SQLMaps. Here are some of the things that I have found so far that makes me like iBatis SQLMaps a whole lot:

• You seem to have the complete power of your favorite flavor of SQL at your finger tips, including Stored Procedures!
• The queries are nicely tucked into separate files (in this case, XML files). See the example configuration file below for a simple example.
• In the iBatis world, the way things are laid out is pretty logical, especially to those coming from a pure JDBC background:
  1. Actual SQL query
  2. Parameters that gets passed in to the SQL query for binding (if any)
  3. Results that gets returned from the execution of the Query (if any)
• Parameters can be passed as simple Java primitives, Map (like HashMap) or Java Beans
• Similarly, Results can be retrieved as a Java Bean, Map (like HashMap) or Java primitives or a collection of either of these.
• It integrates nicely with Jakarta’s DBCP connection pooling API
• You can configure caching of the results on a query-by-query basis without writing one line of code. As if this was not good enough, you can plug in other implementations of the caching algorithms like OSCache.

Just to give you a taste of just how incredibly trivial it is to take a query and cache it’s results, here’s the queries.xml file of that I am using in sample web application:

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="queries">

  <cacheModel id="cacheFullname" type="OSCACHE">
    <flushInterval hours="1"/>
    <flushOnExecute statement="updateFullname"/>
  </cacheModel>

  <select id="getFullname"
    parameterClass="java.lang.String"
    resultClass="java.lang.String"
    cacheModel="cacheFullname">
      SELECT fullname AS value
      FROM test
      WHERE userid=#value#
  </select>

  <update id="updateFullname"
    parameterClass="map">
      UPDATE test SET fullname = #fullname#
      WHERE userid=#userid#
  </update>

</sqlMap>

This file defines the two queries that I am using in my sample application: One which does a SELECT and the other which does an UPDATE. Notice how a cacheModel was defined (cacheFullname) and then assigned to the SELECT query (getFullname) by the use of cacheModel attribute in the <select> tag. So, what does this buy us? It ensures that when I invoke the ‘getFullname’ query (as part of a web request via Velocity pages, in my case), the result gets cached using OpenSymphony’s OSCache library. If you wanted to use the default LRU or FIFO caching algorithms that comes as part of iBatis, you would simply replace the type=OSCACHE with type=LRU or type=FIFO. It’s that easy.

So far so good. But how do I flush the cache, you ask? Well, in this example, there are two ways the cache will get flushed:

1. It will get automatically flushed in an hours time or
2. It will get flushed when someone invokes the update statement ‘updateFullname’.

Needless to say, you could have your own little custom way (providing a ‘Refresh’ button on the UI screen) of flushing the cache thereby giving your end users the ability to perform a flush, if they felt that the data was stale.

If you think about this for a second, I am sure you will see how simple and extremely powerful this implementation is.

This alone would warrant you to take iBatis for a ride if you haven’t done it already. If you’re convinced, download this sample web application, unzip it, read the README.txt and you should be ready in less than 5 mins to see iBatis SQLMaps and OSCache in action. Just for your benefit, here’s a slightly embellished version of what the README.txt file looks like:

README

======

Assumptions:

---------------

1. You've access to a database, preferably MySQL. Of course, PostgreSQL, Oracle or DB2

    would work just as well.

2. You've a Tomcat setup

3. You've some experience with JDBC programming
Steps:
                                
- Anand
                                
                                

                                Category: Application Development |
                                
                                
                                 
                                
                                
                                

                                

                                Viewed: 3301 times