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();

 }

Spring + iBatis // Part 7 // Executing a Method

Ok, so you now have everything setup.  You have the libraries added, the database.properties file pointed properly, the applicationContext.xml file configured, the sql mappings file in place with the classes to support it, and the DAO interface implemented.

Now you just want to execute it and see it working, right?  The snippet below will execute the query to pull back a java.util.List.

public static void main(String[] args) {

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

MyDAO dao = (MyDAO) ctx.getBean("myDAO");
List<FilePickup> fp = dao.selectFilepickupInstructions();

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

ctx.close();

}

If you spot any errors, or have any questions about these steps, please feel free to leave a comment.  I can’t promise I will reply or have time to look into it.

Spring + iBatis // Part 6 // The Result Map Class

You may have noticed when creating the sqlMap details that we referenced a class that we were mapping the table to.  This class is just a simple class that has read/write class properties for each of the table columns we will be using.

Spring + iBatis // Part 5 // The DAO

We declared a DAO in our applicationContext.xml file.  It’s time to create that DAO now by starting with an interface declaration.  I called this interface MyDAO.java.

package com.example;

import java.util.List;

public interface MyDAO {

 FilePickup selectFilepickupInstruction(long id);
 List<FilePickup> selectFilepickupInstructions();

}

Then we need to create the class that will implement this interface.  The name of this class should match that specified in the applicationContext.xml.  In this example it’s MyDAOImpl.java.

package com.example;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

public class MyDAOImpl extends SqlMapClientDaoSupport implements MyDAO {
  public MyDAOImpl() {
  }

  public FilePickup selectFilepickupInstruction(long id) {
    return  
      (FilePickup)getSqlMapClientTemplate().queryForObject(
          "selectFilepickupInstruction",id);
  }
  public List<FilePickup> selectFilepickupInstructions() {   
      return     
        getSqlMapClientTemplate().queryForList(
            "selectFilepickupInstructions",null);

  }

 }

Note that a java.util.list is used to handle results from a sql statement that returns multiple rows.  The strings used in the queryForList() and queryForObject() methods are those names declared in the mapping stage.

Spring + iBatis // Part 4 // sqlmap-config

The next file to create is “sqlmap-config.xml” that we declared in the applicationContext.xml file.  Create the file in the same location as before, and paste in the following XML.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
 PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
 "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
 <sqlMap resource="mySqlMap.xml"/>
</sqlMapConfig>

We could include all the mapping detail in this file, but instead we choose to link out to it should we need to extend this in future.

Create another file, again in the same location, called “mySqlMap.xml“.  This will contain your actual sql along with any parameter and resultset mappings.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
 PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
 "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="MySqlMap">

 <!-- 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="include_subdirs" column="INCLUDE_SUBDIRS"/>
 <result property="target_dir" column="TARGET_DIR"/>
 <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="del_source" column="DEL_SOURCE"/>
 <result property="audit_db_properties_file" column="AUDIT_DB_PROPERTIES_FILE"/>
 </resultMap>

 <!-- sql for retrieving from table based on one parameter -->
 <select id="selectFilepickupInstruction" resultMap="result">
 SELECT
 fpc.id,
 fpc.root_dir,
 fpc.include_subdirs,
 fpc.target_dir,
 fpc.file_filter_list,
 fpc.prefix_text,
 fpc.postfix_text,
 fpc.new_ext,
 fpc.keep_unique,
 fpc.del_source,
 fpc.audit_db_properties_file            
 FROM filepickup_config fpc        
 WHERE fpc.id = #value#
 </select>

 <!-- getting multiple values from table -->
 <select id="selectFilepickupInstructions" resultMap="result">
 SELECT
 id,
 root_dir,
 include_subdirs,
 target_dir,
 file_filter_list,
 prefix_text,
 postfix_text,
 new_ext,
 keep_unique,
 del_source,
 audit_db_properties_file            
 FROM filepickup_config
 </select>

</sqlMap>

 

The file selects from a table and offers two methods – one with a parameter and one without.  We will clearly define these methods in a later step.  Note that both methods use the same result map.  The result map is linked to a java class called FilePickup.

Spring + iBatis // Part 3 // applicationContext

In the same location (as part 2), create another file called “applicationContext.xml”.  Paste in the following content.  Some explanation will follow below.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans

http://www.springframework.org/schema/beans/spring-beans-2.5.xsd

http://www.springframework.org/schema/tx

http://www.springframework.org/schema/tx/spring-tx-2.5.xsd

http://www.springframework.org/schema/aop

http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">

<bean
 class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  <property name="location" value="classpath:database.properties"/>
</bean>

<bean
 id="dataSource"
 class="org.apache.commons.dbcp.BasicDataSource"
 destroy-method="close">
  <property name="driverClassName" value="${database.class}"/>
  <property name="url" value="${database.url}"/>
  <property name="username" value="${database.username}"/>
  <property name="password" value="${database.password}"/>
</bean>

<bean
 id="transactionManager"
 class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  <property name="dataSource" ref="dataSource"/>
</bean>

<tx:annotation-driven/>

<bean
 id="sqlMapClient"
 class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation" value="sqlmap-config.xml"/>
  <property name="dataSource" ref="dataSource"/>
  <property name="useTransactionAwareDataSource" value="true"/>
</bean>

<bean id="myDAO" class="com.example.dao.MyDAOImpl">
 <property name="sqlMapClient" ref="sqlMapClient"/>
</bean>

</beans>

Notes:

• classpath:database.properties points to the database.properties files we created earlier.  If you used a different location, you may need to update this value.
• sqlmap-config.xml is a file we will create later.  It will hold our sql and mapping information.
• myDAO is our data access interface.  This will be created later also.  You may need to change the name of this, and it’s class reference.

Spring + iBatis // Part 2 // Database Properties

In the JDeveloper IDE, right click on the resources folder in your project structure (applications navigator window).  Select “New…” from the window and “File” from the general category.  Name the new file “database.properties”.

database.username=username
database.password=password
database.class=oracle.jdbc.OracleDriver
database.url=jdbc:oracle:thin:@host:port:sid

Place the above in the file (substitute values in italics) and save your project.

Spring + iBatis // Part 1 // Libraries

I’ve just discovered the greatness of Spring and iBatis.  This will be the first in a series of posts that will help you configure the environment in JDeveloper 10.1.3.3.  First of all, here are the libraries you need to include in your project.

• Spring 2.5
• Commons Logging 1.0.3
• Commons Collections 2.1
• Oracle JDBC
• Commons-dbcp-1.2.2.jar
• Commons-pool-1.5.4.jar
• iBatis.2.3.4.726.jar
• Commons Beanutils 1.6.1
• Commons Digester 1.5

BPEL // ORABPEL-11814 Invalid ISO8601 datetime specification

I’m using BPEL to call out to a stored procedure in Oracle.  Some of the procedure parameters are of type DATE.  I was using the following date format for sending through to the procedure.

2009-07-02 12:18:02 PM

This returned the following bindingFault in the BPEL console:

The date or timestamp specification
2009-07-02 12:18:02 PM
is not ISO8601 compliant.
; nested exception is: ORABPEL-11814
Invalid ISO8601 datetime specification.

I searched for ISO8601 details and found a post that explained that the date should be in the following format:

YYYY-MM-DDTHH:mm:ss.SSS

The .SSS is milliseconds and is optional.  It is also possible to append the timezone from which the time belongs to using the format -HH:mm or +HH:mm.

Upon updating my code, BPEL was able to pass the date values through to the procedures without any issues.

BPEL // The result is empty for the XPath expression

Some of my xml elements are optional.  The customer need not populate them or even include them in the file.  BPEL does not like when I try to retrieve the value like this:

bpws:getVariableData(‘var_IncomingFile’,'ORDERS’,'/ns2:ORDERS/ns2:ORDER[bpws:getVariableData("var_LoopIterator")]/ns2:OPTIONAL_FIELD/text()’)

It spits out this error and throws a selectionFailure:

Error in evaluate <from> expression at line “347″. The result is empty for the XPath expression : “bpws:getVariableData(‘var_IncomingFile’,'ORDERS’,'/ns2:ORDERS/ns2:ORDER[bpws:getVariableData("var_LoopIterator")]/ns2:OPTIONAL_FIELD/text()’)”.

To get it to work I had to do this:

concat(bpws:getVariableData(‘var_IncomingFile’,'ORDERS’,'/ns2:ORDERS/ns2:ORDER[bpws:getVariableData("var_LoopIterator")]/ns2:OPTIONAL_FIELD/text()’),”)

Is there a more elegant way?