You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Alex Park <yj...@paran.com> on 2010/04/22 16:50:06 UTC
How to get a result from output parameter(SYS_REFCURSOR) in iBATIS 3(by using annotation)
Hi there,
How to get a result from output parameter(oracle cursor).
Below is my code and that is not work.
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
}
}
Error Message:
### 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@13f210f'
Cause: org.apache.ibatis.reflection.ReflectionException:
There is no setter for property named 'rs' in 'class java.lang.Class'
Thanks,
Alex
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: How to get a result from output parameter(SYS_REFCURSOR) in iBATIS 3(by using annotation)
Posted by Alex Park <yj...@paran.com>.
Thanks for your kindness and recommendation Guy,
Actually, I didn't think that I had to use output parameter and ref cursor.
Because stored procedures don't need any output parameter to get result sets
in case of SQL Server. (SQL Server is major in-house DBMS)
But Oracle stored procedure seems to need output parameter as ref cursor
to get a result set.
That is why I'm trying to use it.
I got some questions from your answer.
Is it possible to get a result set from SP without ref cursor? And what is the
meaning of the simpler iBATIS functionality? Is it a map? or using query instead
of SP?
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: How to get a result from output parameter(SYS_REFCURSOR) in iBATIS
3(by using annotation)
Posted by Guy Rouillier <gu...@burntmail.com>.
Alex, my workplace is an Oracle shop, and I'm using iBATIS in a major
in-house app. So I may be able to help. Why are you trying to use a
ref cursor? That complicates your SQL, and if you are just using result
sets, that's a level of complexity you don't need. Unless you
specifically require some functionality provided by ref cursors, you'd
be better off using the simpler iBATIS functionality.
On 4/23/2010 12:43 PM, Alex Park wrote:
> Thank you Clinton, your answer was very helpful.
> I tried to fix my code but I couldn't make it work.
>
> I did something to this code that I thought right.
> But it was not good. :(
>
> Can you give me some example codes or additional information?
> I'm very newbie in iBATIS and even English :(
>
> I would appreciate if you can let me know the solution, Thanks!
>
> Alex
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
--
Guy Rouillier
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: How to get a result from output parameter(SYS_REFCURSOR) in
iBATIS 3(by using annotation)
Posted by Clinton Begin <cl...@gmail.com>.
Unfortunately I don't use Oracle and haven't used OUT params or ref cursors
for 10 years. So someone else will have to help you out with an example.
Clinton
On Fri, Apr 23, 2010 at 10:43 AM, Alex Park <yj...@paran.com> wrote:
> Thank you Clinton, your answer was very helpful.
> I tried to fix my code but I couldn't make it work.
>
> I did something to this code that I thought right.
> But it was not good. :(
>
> Can you give me some example codes or additional information?
> I'm very newbie in iBATIS and even English :(
>
> I would appreciate if you can let me know the solution, Thanks!
>
> Alex
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: How to get a result from output parameter(SYS_REFCURSOR) in iBATIS 3(by using annotation)
Posted by Alex Park <yj...@paran.com>.
Thank you Clinton, your answer was very helpful.
I tried to fix my code but I couldn't make it work.
I did something to this code that I thought right.
But it was not good. :(
Can you give me some example codes or additional information?
I'm very newbie in iBATIS and even English :(
I would appreciate if you can let me know the solution, Thanks!
Alex
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org
Re: How to get a result from output parameter(SYS_REFCURSOR) in
iBATIS 3(by using annotation)
Posted by Clinton Begin <cl...@gmail.com>.
To do what you're trying to do, you'll need to write a TypeHandler. iBATIS
doesn't allow ResultSets to leave the execution scope.
Clinton
On Fri, Apr 23, 2010 at 6:20 AM, Alex Park <yj...@paran.com> wrote:
> Alex Park <yjacket <at> paran.com> writes:
>
> >
> > Hi there,
> >
> > How to get a result from output parameter(oracle cursor).
> > Below is my code and that is not work.
> > 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
> > }
> > }
> >
> > Error Message:
> > ### 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 <at> 13f210f'
> > Cause: org.apache.ibatis.reflection.ReflectionException:
> > There is no setter for property named 'rs' in 'class java.lang.Class'
> >
> > Thanks,
> > Alex
> >
>
> 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.ProductFilter@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.
>
> Thanks,
> Alex
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
Re: How to get a result from output parameter(SYS_REFCURSOR) in iBATIS 3(by using annotation)
Posted by Alex Park <yj...@paran.com>.
Alex Park <yjacket <at> paran.com> writes:
>
> Hi there,
>
> How to get a result from output parameter(oracle cursor).
> Below is my code and that is not work.
> 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
> }
> }
>
> Error Message:
> ### 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 <at> 13f210f'
> Cause: org.apache.ibatis.reflection.ReflectionException:
> There is no setter for property named 'rs' in 'class java.lang.Class'
>
> Thanks,
> Alex
>
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.ProductFilter@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.
Thanks,
Alex
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org