tags:

views:

384

answers:

0

Hi there, How to get a result from output parameter of stored procedure(Oracle)?
Below is my code. Does anyone can help me?

Oracle Stored Procedure:

CREATE OR REPLACE PROCEDURE getProducts (
    rs OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN rs FOR
    SELECT * FROM Products;
END getProducts;

Interface:

public interface ProductMapper {
    @Select("call getProducts(#{rs,mode=OUT,jdbcType=CURSOR})")
    @Options(statementType = StatementType.CALLABLE)
    List<Product> getProducts();
}

DAO:

public class ProductDAO {
    public List<Product> getProducts() {
        return mapper.getProducts(); // mapper is ProductMapper
    }
}

Full Error Message:

Exception in thread "main" org.apache.ibatis.exceptions.IbatisException: 
### Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: Could not set property 'rs' of 'class org.apache.ibatis.reflection.MetaObject$NullObject' with value 'oracle.jdbc.driver.OracleResultSetImpl@1a001ff' Cause: org.apache.ibatis.reflection.ReflectionException: There is no setter for property named 'rs' in 'class java.lang.Class'
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: org.apache.ibatis.reflection.ReflectionException: Could not set property 'rs' of 'class org.apache.ibatis.reflection.MetaObject$NullObject' with value 'oracle.jdbc.driver.OracleResultSetImpl@1a001ff' Cause: org.apache.ibatis.reflection.ReflectionException: There is no setter for property named 'rs' in 'class java.lang.Class'
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:61)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:53)
    at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:82)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:35)
    at $Proxy8.getList(Unknown Source)
    at com.dao.ProductDAO.getList(ProductDAO.java:42)
    at com.Ibatis3Test.main(Ibatis3Test.java:30)
Caused by: org.apache.ibatis.reflection.ReflectionException: Could not set property 'rs' of 'class org.apache.ibatis.reflection.MetaObject$NullObject' with value 'oracle.jdbc.driver.OracleResultSetImpl@1a001ff' Cause: org.apache.ibatis.reflection.ReflectionException: There is no setter for property named 'rs' in 'class java.lang.Class'
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.setBeanProperty(BeanWrapper.java:154)
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.set(BeanWrapper.java:36)
    at org.apache.ibatis.reflection.MetaObject.setValue(MetaObject.java:120)
    at org.apache.ibatis.executor.resultset.FastResultSetHandler.handleOutputParameters(FastResultSetHandler.java:69)
    at org.apache.ibatis.executor.statement.CallableStatementHandler.query(CallableStatementHandler.java:44)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:55)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:94)
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:59)
    ... 7 more
Caused by: org.apache.ibatis.reflection.ReflectionException: There is no setter for property named 'rs' in 'class java.lang.Class'
    at org.apache.ibatis.reflection.Reflector.getSetInvoker(Reflector.java:300)
    at org.apache.ibatis.reflection.MetaClass.getSetInvoker(MetaClass.java:97)
    at org.apache.ibatis.reflection.wrapper.BeanWrapper.setBeanProperty(BeanWrapper.java:146)
    ... 16 more

[Update]
I try another way but it still not work.

I added @Results annotion into the interface file.
And set ResultMap ID to "getList-ProductFilter"(I found it via debugging).
And I added one parameter on getList() method.

Interface:

public interface ProductMapper {
    @Select("call GETPRODUCTS(#{rs,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=getList-ProductFilter})")
    @Results(value={
        @Result(property="productCode",column="productCode"),
        @Result(property="productName",column="productName"),
        @Result(property="publisherCode",column="publisherCode"),
        @Result(property="swGroupCode",column="swGroupCode"),
        @Result(property="swType",column="swType"),
        @Result(property="version",column="version"),
        @Result(property="licenseType",column="licenseType"),
        @Result(property="price",column="price")                        
    })
    @Options(statementType = StatementType.CALLABLE)
    List<Product> getList(ProductFilter filter);
}

ProductFilter:

public class ProductFilter {
    private ResultSet rs;    
    public ResultSet getRs() {
        return rs;
    }    
    public void setRs(ResultSet rs) {
        this.rs = rs;
    }
}

DAO:

public List<Product> getList() {
    return mapper.getList(new ProductFilter());
}

Finally, I got an new error message.

Error Message:

Exception in thread "main" org.apache.ibatis.exceptions.IbatisException:
Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: Could not set property 'rs' of 'com.domain.filter.productfil...@13f210f' with value '[productCode:1, productName:1, publisherCode:1, swGroupCode:1, swType:1, version:1]' Cause: java.lang.IllegalArgumentException: argument type mismatch

I would appreciate if anyone can let me know the corrections as this.