Spring + iBatis // Oracle Ref Cursors

The previous iBatis example on this blog was for simple embedded SQL queries.  The following example is for using iBatis with Oracle stored procedures that have a REF CURSOR OUT parameter.  The code snippets below assume you have followed and understood the previous iBatis example, as they are build upon the same file structure.

The cursor is declared in the Oracle package specification as:

TYPE T_CURSOR IS REF CURSOR;

The stored procedure I am using in this example is as follows:

PROCEDURE GET_PICKUP_INSTRUCTIONS(IO_CURSOR OUT T_CURSOR) IS

 BEGIN

 OPEN IO_CURSOR FOR
 SELECT f.ID,
 f.ROOT_DIR,
 f.TARGET_DIR,
 f.INCLUDE_SUBDIRS,
 f.FILE_FILTER_LIST,
 f.PREFIX_TEXT,
 f.POSTFIX_TEXT,
 f.NEW_EXT,
 f.KEEP_UNIQUE,
 f.AUDIT_DB_PROPERTIES_FILE,
 f.DEL_SOURCE               
 FROM FILEPICKUP_CONFIG f;

 END GET_PICKUP_INSTRUCTIONS;

The sqlMap entries should be as follows.  If you are continuing on from the previous example, you will already have the resultMap below setup.  If not, you will need to refer back and also create the FilePickup class.

<!-- map for result set based on table -->
 <resultMap id="result" class="com.example.FilePickup">
 <result property="id" column="ID"/>
 <result property="root_dir" column="ROOT_DIR"/>
 <result property="target_dir" column="TARGET_DIR"/>
 <result property="include_subdirs" column="INCLUDE_SUBDIRS"/>    
 <result property="file_filter_list" column="FILE_FILTER_LIST"/>
 <result property="prefix_text" column="PREFIX_TEXT"/>
 <result property="postfix_text" column="POSTFIX_TEXT"/>
 <result property="new_ext" column="NEW_EXT"/>
 <result property="keep_unique" column="KEEP_UNIQUE"/>
 <result property="audit_db_properties_file" column="AUDIT_DB_PROPERTIES_FILE"/>
 <result property="del_source" column="DEL_SOURCE"/>    
 </resultMap>

 <parameterMap id="getPickupInstructionsMap" class="java.util.Map">
 <parameter property="IO_CURSOR" javaType="java.sql.ResultSet"
            jdbcType="ORACLECURSOR" mode="OUT" />
 </parameterMap>

 <procedure id="getPickupInstructions"
            parameterMap="getPickupInstructionsMap"
            resultMap="result">
    {call PKG_FILE_PICKUP.GET_PICKUP_INSTRUCTIONS(?)}
 </procedure>

Add this declaration to your DAO interface:

List<FilePickupRM> getPickupInstructions();

In the DAO implementation class add the following method:

public List<FilePickupRM> getPickupInstructions() {

 Map map = new HashMap();        
 return getSqlMapClientTemplate().queryForList("getPickupInstructions", map);

 }

To execute the method use the following snippet of code.  Again, refer to previous iBatis example for defining the applicationContext.xml file.

public static void main(String[] args) {

 ClassPathXmlApplicationContext ctx =
   new ClassPathXmlApplicationContext("applicationContext.xml");

 MyDAO dao = (MyDAO) ctx.getBean("myDAO");
 List<FilePickupRM> fp = dao.getPickupInstructions();

 for (FilePickupRM fps : fp) {
    System.out.println(fps);
 }

 ctx.close();

 }

Advertisement