Thursday, August 27, 2009

iBATIS DataMapper - Oracle REF CURSOR Solutions

This whiteboard is for the collaborative documentation and design as a start toward improving or extending current support for Oracle REF CURSOR types.

Current Working Solutions

If you have a current solution that works, please put it here.

Solution 1

This is sample mapping for a Stored procedure which return Ref cursor in it's out parameter

<?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="KOMUNIKA_REPORT">
    <resultMap id="BaseResultMap" class="javaapplication4.StockAreaAndWarehouse" >
        <result column="PRODUCT_CODE" property="productCode"  />
        <result column="PRODUCT_NAME" property="productName" />
        <result column="INCOMING" property="incoming" />
        <result column="UNIT_SOLD" property="unitSold" />
        <result column="TOTAL_STOCK" property="totalStock" />
    </resultMap>
    <parameterMap id="resultMap" class="java.util.Map">
        <parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
    </parameterMap>
    <procedure id="selectStockAreaAndWarehouse" 
        parameterMap="resultMap"
        resultMap="BaseResultMap"
        >
        { call KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(?) }
    </procedure>
</sqlMap>

You should aware that javaType="java.sql.ResultSet" attribute must set to be able to map result to a Class, if it not set than your effort is a waste (like mine )

Oracle's PL/SQL Stored procedure

CREATE OR REPLACE PROCEDURE KOMUNIKA.LP_STOCK_AREA_WAREHOUSE( 
RESULT_CURSOR out TYPES.ref_cursor) as
BEGIN
   OPEN RESULT_CURSOR FOR select PRODUCT_CODE, PRODUCT_NAME, INCOMING, UNIT_SOLD, TOTAL_STOCK
    from KOMUNIKA.STOCK_AREA_AND_WAREHOUSE ;   
END LP_STOCK_AREA_WAREHOUSE;

To make that PL/SQL work you should declare a package so that TYPES.ref_cursor recognized by oracle 

CREATE OR REPLACE PACKAGE KOMUNIKA.Types
AS
    TYPE ref_cursor IS REF CURSOR;
END;

To test wether it works or not you need sqlMapConfig 

<?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>
    <settings useStatementNamespaces="true" classInfoCacheEnabled="true" />
    <transactionManager type="JDBC" >
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="oracle.jdbc.OracleDriver"/>
            <property name="JDBC.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
            <property name="JDBC.Username" value="YOUUSERHERE"/>
            <property name="JDBC.Password" value="YOURPASSWORDHERE"/>
            <property name="JDBC.DefaultAutoCommit" value="true" />
        </dataSource>
    </transactionManager>
    <sqlMap resource="javaapplication4/KOMUNIKA_REPORT_SqlMap.xml" />
</sqlMapConfig>

oh, one more setting which is realy important is useStatementNamespaces="true", i find it out in a hardway when i want to use namespace

At last here is Java code used to test the configuration. This java code derived from improvement solution below,

Main.java
package javaapplication4;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author ifnu<mailto:ifnubima@gmail.com>
 */
public class Main {
    public static void main(String[] args) throws Exception {
        String resource;
        Reader reader;
        List list;
        SqlMapClient sqlMap;
        resource = "javaapplication4/ibatis.xml";
        reader = Resources.getResourceAsReader (resource);
        sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
        Map map = new HashMap();
        // use queryForList because the procedure map defines a resultmap
        // for the statement
        list = sqlMap.queryForList("KOMUNIKA_REPORT.selectStockAreaAndWarehouse", map);
        System.out.println(list);
    }
}

you can download the code above : http://opensource.atlassian.com/confluence/oss/download/attachments/561/iBatisCallSpRefCursorOracle.zip

Solution 2

...

Improving the Solutions

If you have an idea to improve one of the solutions above, but it requires a change to the iBATIS framework, put it here.

Improvement 1

I have modified the framework to allow all oracle cursors to be mapped by a statement's resultMap so you can call the queryForList() method and have the oracle cursor results returned in the list. I also added the ability to specifiy a resultMap to use with each of the oracle cursor parameters to support the queryForObject() call. I have tested this code with IBATIS 2.1.6.598

here are my sample sqlMap:

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

    <typeAlias alias="Employee" type="test.Employee" />
    
    <resultMap id="employee-map" class="Employee">
        <result property="name" column="ENAME" />
        <result property="employeeNumber" column="EMPNO" />
        <result property="departmentNumber" column="DEPTNO" />
    </resultMap>
    
    <parameterMap id="single-rs" class="map" >
        <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>      
    </parameterMap>
     
    <procedure id="GetSingleEmpRs" parameterMap="single-rs" resultMap="employee-map">
         { call scott.example.GetSingleEmpRS(?, ?) }
    </procedure>
    
    <parameterMap id="double-rs" class="map" >
        <parameter property="in1" jdbcType="int" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="output1" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="employee-map" />
        <parameter property="output2" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="employee-map" />        
    </parameterMap>
    
    <procedure id="GetDoubleEmpRs" parameterMap="double-rs" >
        { call scott.example.GetDoubleEmpRS(?, ?, ?) }
    </procedure>
</sqlMap>

Here is my sample code that makes use of the maps:

Main.java
package test;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class Main {

	
	    public static void main(String arg[]) throws Exception {
	        String resource;
	        Reader reader;
	        List list;
	        SqlMapClient sqlMap;
	        resource = "test/SqlMapConfig.xml";
	        reader = Resources.getResourceAsReader (resource);
	        sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
	        Map map = new HashMap();
	        map.put("in1", new Integer(10));
	        // use queryForList because the procedure map defines a resultmap
	        // for the statement
	        list = sqlMap.queryForList("GetSingleEmpRs", map); 

	        System.out.println("--------------------");
	        System.out.println( list );
	        System.out.println("--------------------");
	        
	        map = new HashMap();
	        map.put("in1", new Integer(10));
	        // use queryForObject because the procedure map does not define a 
	        // result map for the statement
	        sqlMap.queryForObject("GetDoubleEmpRs", map);
	        
	        System.out.println("--------------------");
	        System.out.println( map.get("output1"));
	        System.out.println( map.get("output2"));
	        System.out.println("--------------------");
	        
	           
	    }
}

Here is the output from Main.java

 
--------------------
[Employee[name=CLARK,id=7782,dept=10], Employee[name=KING,id=7839,dept=10], Employee[name=MILLER,id=7934,dept=10]]
--------------------
--------------------
[Employee[name=CLARK,id=7782,dept=10], Employee[name=KING,id=7839,dept=10], Employee[name=MILLER,id=7934,dept=10]]
[Employee[name=ADAMS,id=7876,dept=20], Employee[name=ALLEN,id=7499,dept=30], Employee[name=BLAKE,id=7698,dept=30], 
 Employee[name=FORD,id=7902,dept=20], Employee[name=JAMES,id=7900,dept=30], Employee[name=JONES,id=7566,dept=20], 
 Employee[name=MARTIN,id=7654,dept=30], Employee[name=SCOTT,id=7788,dept=20], Employee[name=SMITH,id=7369,dept=20],
 Employee[name=TURNER,id=7844,dept=30],Employee[name=WARD,id=7521,dept=30]]
--------------------

All test code and modified src are included in this page's attachments. Most of my PL/SQL code just returns a single ref cursor so it is nice just to define a resultMap for the statement and just have it work automatically with a queryForList() call. The second form handles the more complicated case of returning two or more cursors. I have tested this code with oracle 9i using the thin driver (ojdbc14.jar) with jdk 1.4

Warning

This implementation does not support

  • Paging functionality
  • Remappable results for parameter assigned resultMaps

Improvement 2

...

New Solutions

If you have a completely new solution that can only work by changing or extending the iBATIS framework, please put it here.

New 1

...

New 2

Posted by at No comments:
Labels: ,

iBATIS Procedure Call 샘플 예제

이 글은 IBatis in Action 한글판을 번역하신 이동국님께서 올리신 글입니다.
참조 URL : http://openframework.or.kr/JSPWiki/Wiki.jsp?page=ProcedureExample 

----------------------------------------------
사용된 개발환경
  • iBATIS : 2.3.0.677
  • Oracle : 10g
  • eclipse : wtp All-in-one R-1.5.4-200705021353

  • xml설정파일에서는 대소문자 구분이 의외로 굉장히 중요하다. 필자의 경우 jdbcType 의 값을 INTEGER로 줘야 하는데도 불구하고 Integer로 줘서 한참동안이나 고생을 했다.
  • Procedure에서는 Map을 사용할 경우 queryForObject 메소드의 반환 객체에 값이 셋팅되는것이 아니고 넘긴 객체에 값이 셋팅된다. 기본적으로 반환되는 Map객체는 null로 넘어온다. 프로시저에서 넘긴값을 인자로 넘긴 Map에 셋팅되니 이점에 유의해서 사용해야 한다.
  1. IN 타입 예제[1]
  2. OUT 타입 예제[2]
  3. INOUT 타입 예제[3]

IN타입[#1]

  • IN파라미터란! : 호출자에 의해 프로시저에 전달되는 파라미터이고 읽기전용의 값이다. 즉 프로시저는 이 값을 변경할수는 없다.
  • 테스트 케이스 : 여기서는 IN타입으로 값을 받아와서 특정 테이블에 값을 입력하는 케이스를 보여준다.

프로시저

CREATE OR REPLACE PROCEDURE procedurein (p_param IN NUMBER) IS
BEGIN
   DBMS_OUTPUT.put_line (p_param);
   INSERT INTO tablein VALUES (p_param);
   commit;
END;
/

테이블 생성

create table tablein(val number);

sqlMap파일

<?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="Account">
  <procedure id="inProcedure" parameterClass="java.lang.Integer">
    { call procedurein(#val#) }
  </procedure>
</sqlMap>

실행코드

package com.mydomain.data;

.......................

public class SimpleExample {

  private static SqlMapClient sqlMapper;
  static {
    try {
      Reader reader = Resources.getResourceAsReader("com/mydomain/data/SqlMapConfig.xml");
      sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
      reader.close();
    } catch (IOException e) {
      throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
    }
  }
 
  public static void callInTypeProcedure(int val) throws SQLException{
    sqlMapper.queryForObject("inProcedure", new Integer(val));
  }

  public static void main(String[] args) throws SQLException {
   
    try {
      callInTypeProcedure(123);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

결과

SQL> select * from tableIN;

       VAL
----------
       123

SQL>

여기서 보면 인자로 123이라는 숫자값을 넘겼으며 프로시저에 정의된 내용대로 tableIN이라는 테이블에 값이 정상적으로 입력이 된것을 볼수 있다.

OUT타입[#2]

  • OUT파라미터란! : 프로시저에서 값이 변경될수 있다. 이 파라미터는 프로시저가 다수의 정보를 호출자에게 반환시켜주어야 하는 경우에 주로 사용한다.
  • 테스트 케이스 : 여기서는 IN타입으로 입력된 숫자값에 3을 더한 값을 반환하는 것을 보여주는 예제이다.

프로시저

CREATE OR REPLACE PROCEDURE procedure_out(p_inval in integer, p_outval out integer) IS
BEGIN
   p_outval := p_inval+3;
END;
/

sqlMap파일

<?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="Account">
  <parameterMap id="outProcedureMap" class="java.util.Map">
    <parameter property="p_inval" javaType="java.lang.Integer" jdbcType="INTEGER" mode="IN" />
    <parameter property="p_outval" javaType="java.lang.Integer" jdbcType="INTEGER" mode="OUT" />
  </parameterMap>
 
  <procedure id="outProcedure" parameterMap="outProcedureMap">
    { call procedure_out (?,?) }
  </procedure>
</sqlMap>

실행코드

package com.mydomain.data;

.......................

public class SimpleExample {

  private static SqlMapClient sqlMapper;
  static {
    try {
      Reader reader = Resources.getResourceAsReader("com/mydomain/data/SqlMapConfig.xml");
      sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
      reader.close();
    } catch (IOException e) {
      throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
    }
  }
 
  public static Map callOutTypeProcedure(Map m) throws SQLException{
    sqlMapper.queryForObject("outProcedure", m);
    return m;
  }

  public static void main(String[] args) throws SQLException {    
    try {
      Map<String, Integer> m = new HashMap<String, Integer>(2);
      m.put("p_inval", new Integer(123));      
      Map result = SimpleExample.callOutTypeProcedure(m);
      System.out.println("result : " + Integer.parseInt(result.get("p_outval").toString()));    
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

결과

result : 126

인자로 넘겨진 값인 123에 3을 더해 126이라는 값이 반환되었다.

INOUT타입[#3]

  • INOUT파라미터란! : 프로시저가 읽고 쓰는 작업을 동시에 할수 있는 파라미터를 의미한다.
  • 테스트 케이스 : 여기서는 INOUT타입으로 값을 받아와서 값의 위치를 서로 바꿔서 값을 입력한 뒤 반환하는 케이스를 보여주는 예제이다.

프로시저

CREATE OR REPLACE PROCEDURE procedure_inout(p_inout1 in out integer, p_inout2 in out integer) IS
tmpVar NUMBER;
BEGIN
   tmpVar:=p_inout1;
   p_inout1:=p_inout2;
   p_inout2:=tmpVar;
END;
/

sqlMap파일

<?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="Account">
  <parameterMap id="inoutProcedureMap" class="java.util.Map">
    <parameter property="p_inout1" javaType="java.lang.Integer" jdbcType="INTEGER" mode="INOUT" />
    <parameter property="p_inout2" javaType="java.lang.Integer" jdbcType="INTEGER" mode="INOUT" />
  </parameterMap>  
 
  <procedure id="inoutProcedure" resultClass="java.util.Map" parameterMap="inoutProcedureMap">
    { call procedure_inout(?, ?) }
  </procedure>  
</sqlMap>

실행코드

package com.mydomain.data;

.......................

public class SimpleExample {

  private static SqlMapClient sqlMapper;
  static {
    try {
      Reader reader = Resources.getResourceAsReader("com/mydomain/data/SqlMapConfig.xml");
      sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
      reader.close();
    } catch (IOException e) {
      throw new RuntimeException("Something bad happened while building the SqlMapClient instance." + e, e);
    }
  }
 
  public static Map callProcedure(Map m) throws SQLException{
    sqlMapper.queryForObject("inoutProcedure", m);    
    return m;
  }

  public static void main(String[] args) throws SQLException {
   
    try {
      Map<String, Integer> m = new HashMap<String, Integer>(2);
      m.put("p_inout1", new Integer(7));
      m.put("p_inout2", new Integer(5));
      System.out.println("인자로 넘어가는 값 \n"+m);
      Map map = (HashMap)SimpleExample.callProcedure(m);
      System.out.println("결과로 넘어온 값 \n"+m);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

결과

인자로 넘어가는 값
{p_inout1=7, p_inout2=5}
결과로 넘어온 값
{p_inout1=5, p_inout2=7}

보면 처음에 넘긴값은 p_inout1값이 7이고 p_inout2값인데 반환값은 반대로 5, 7이다. 즉 프로시저의 처리고 값이 정상적으로 바껴서 넘어온것이다.

Posted by at No comments:
Labels: ,

Wednesday, August 26, 2009

하드디스크 불량~

이거는 정상적인 상태 디스크 사용율 80%정도됨

 

이번에 받은거 완전 느리고 먼가 이상함 -_-;;;

Posted by at No comments:
Labels:

Monday, August 10, 2009

[TIP] Gauce와 Windows XP SP2상의 데이터수신 에러건



윈도우 서비스팩2에서 가우스를 사용할때 특정 조건등을 GET방식의 파라미터로 처리할때
 
서비스가 정상적으로 처리되었는데 결과를 못받아오거나, 에러메시지와 코드가 없는 에러
 
다이얼로그가 뜨는 경우에 대한 처리.
 
자세한 해결법 및 원인은 아래 참고!!
 

 

펼쳐두기..



 

 

Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)