ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ken Katsma <kenkat...@gmail.com>
Subject Oracle function result sets with iBatis
Date Sat, 15 Jan 2005 19:01:19 GMT
I think iBatis is a great product, my group was looking to build 
something that extracted our sql into xml files anyway, and when I found 
iBatis, I was pretty happy to see all that work has already been done.

One thing it can't seem to do right now though is support Oracle result 
sets from functions.  If there is a way to do so without modifying the 
code let me know.  I went in and modified SQLExecutor to support the 
functionality I need.  This is a really bad hack, and it would be great 
if we could specify our own SQLExecutor in the xml file to implement 
database specific code like this in a more elegant fashion.  But this 
works, and all you have to do is modify the SQLExecutor like this:

  public void executeQueryProcedure(RequestScope request, Connection 
conn, String sql, Object[] parameters,
                                    int skipResults, int maxResults, 
RowHandlerCallback callback)
      throws SQLException {
    ErrorContext errorContext = request.getErrorContext();
    errorContext.setActivity("executing query procedure");
    errorContext.setObjectId(sql);

    CallableStatement cs = null;
    ResultSet rs = null;
   
    try {
      errorContext.setMoreInfo("Check the SQL Statement (preparation 
failed).");
      cs = conn.prepareCall(sql);

      ParameterMap parameterMap = request.getParameterMap();

      ParameterMapping[] mappings = parameterMap.getParameterMappings();

      errorContext.setMoreInfo("Check the output parameters (register 
output parameters failed).");
      registerOutputParameters(cs, mappings);

      errorContext.setMoreInfo("Check the parameters (set parameters 
failed).");
      parameterMap.setParameters(request, cs, parameters);

      errorContext.setMoreInfo("Check the statement (update procedure 
failed).");
     
      if 
(conn.getMetaData().getDatabaseProductName().equalsIgnoreCase("Oracle"))
      {
          boolean b = cs.execute();
         
          errorContext.setMoreInfo("In Oracle query mode.");
          errorContext.setMoreInfo("Check the output parameters 
(retrieval of output parameters failed).");
          retrieveOutputParameters(cs, mappings, parameters);
    
          for (int i=0;i<parameters.length;i++)
          {
              if (parameters[i] instanceof ResultSet)
              {
                  rs = (ResultSet) parameters[i];
                  break;
              }
          }

          errorContext.setMoreInfo("Check the results (failed to 
retrieve results).");
          handleResults(request, rs, skipResults, maxResults, callback);
      }
      else
      {
          errorContext.setMoreInfo("In non-Oracle mode.");
          rs = cs.executeQuery();

        errorContext.setMoreInfo("Check the results (failed to retrieve 
results).");
        handleResults(request, rs, skipResults, maxResults, callback);

        errorContext.setMoreInfo("Check the output parameters (retrieval 
of output parameters failed).");
        retrieveOutputParameters(cs, mappings, parameters);
      }
      } finally {
      try {
        closeResultSet(rs);
      } finally {
        closeStatement(cs);
      }
    }

If you stick this above the base iBatis libraries, you can now get the 
results out.
No big deal in the xml file:

<typeAlias alias="Client" type="com.test.vo.ClientVO" />

    <resultMap id="clientResult" class="Client">
         <result property="clntSeq" columnIndex="1" />
         <result property="clntName" columnIndex="2" />
        <result property="clntSubType" columnIndex="3" />
        <result property="clntCode" columnIndex="4"/>
        <result property="clntMailName" columnIndex="5" />
         <result property="clntLOBCode" columnIndex="6" />
         <result property="clntCompanyID" columnIndex="7" />
         <result property="clntCostCenter" columnIndex="8" />
    </resultMap>

    <parameterMap id="clientParameters" class="map" >
        <parameter property="result" jdbcType="ORACLECURSOR" mode="OUT"/>
        <parameter property="maxRows" jdbcType="VARCHAR" 
javaType="java.lang.String" mode="IN"/>
    </parameterMap>

    <procedure id="getClientListProc" resultMap="clientResult" 
parameterMap="clientParameters">
        {?= call abc.CLIENT_VIEW_PKG.client_result_list_f(?)}
    </procedure>

Hope nobody get's offended by the ugliness of this hack, but we really 
need the functionality as all queries are sp's in my project (no choice).

Thanks,

Ken


Mime
View raw message