ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Custom Type Handler question
Date Mon, 16 May 2005 02:00:28 GMT
I'm not really sure what the problem could be. It should work fine. 

Is there no way to just join the data and return a set of repeating groups 
and use the iBATIS N+1 Selects solution?

http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+get+around+the+N+Plus+1+selects+problem%3F

Cheers,
Clinton


On 5/15/05, Ken Katsma <kenkatsma@gmail.com> wrote:
> 
> Hi,
> 
> My group is currently using stored procedures in Oracle for all our
> database access. To get around the n+m problem we are considering
> several alternatives. One we know will work is to do a user function
> for each column that maps to a "m" table that gives us the data in
> string format (name,value pairs).
> 
> Another approach, (which we would prefer) is to return the results for
> each "m" column as a cursor. I can do this in java with code that looks
> something like this:
> 
> class RefCursor
> {
> 
> public static void main (String args [])
> throws SQLException, ClassNotFoundException
> {
> String query = "begin "+
> "open ? for "+
> "'select dname, CURSOR(select ename "+
> "from emp "+
> "where
> emp.deptno=dept.deptno) "+
> "from dept'; "+
> "end;";
> 
> DriverManager.registerDriver
> (new oracle.jdbc.driver.OracleDrive r());
> 
> Connection conn=
> DriverManager.getConnection
> ("jdbc:oracle:thin:@aria-dev:1521:ora817dev",
> "scott", "tiger");
> 
> Statement trace = conn.createStatement();
> 
> CallableStatement cstmt = conn.prepareCall(query);
> 
> cstmt.registerOutParameter(1,OracleTypes.CURSOR);
> cstmt.execute();
> 
> ResultSet rset= (ResultSet)cstmt.getObject(1); 
> 
> for(int i = 0; rset.next(); i++ )
> {
> System.out.println( rset.getString(1) );
> ResultSet rset2 = (ResultSet)rset.getObject(2);
> for( int j = 0; rset2.next(); j++ )
> System.out.println ( " " + rset2.getString(1) );
> rset2.close();
> 
> }
> 
> rset.close();
> cstmt.close();
> }
> }
> 
> To get the same functionality in iBatis we tried writing a custom type
> handler for the cursor column that looks like this: 
> 
> import java.sql.Ref;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> 
> import com.ibatis.sqlmap.client.extensions.ParameterSetter;
> import com.ibatis.sqlmap.client.extensions.ResultGetter;
> import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
> import com.ibatis.sqlmap.engine.type.ResultGetterImpl;
> 
> /**
> * @author S001027
> *
> * TODO To change the template for this generated type comment go to 
> * Window - Preferences - Java - Code Style - Code Templates
> */
> public class RefTypeHandler implements TypeHandlerCallback {
> 
> /* (non-Javadoc)
> * @see
> com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#setParameter (
> com.ibatis.sqlmap.client.extensions.ParameterSetter,
> java.lang.Object)
> */
> public void setParameter(ParameterSetter arg0, Object arg1)
> throws SQLException {
> if (arg1 != null)
> {
> arg0.setObject(arg1);
> }
> }
> 
> /* (non-Javadoc)
> * @see
> com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#getResult(
> com.ibatis.sqlmap.client.extensions.ResultGetter )
> */
> public Object getResult(ResultGetter arg0) throws SQLException {
> System.out.println("Object: " +
> arg0.getObject().getClass().getName());
> ResultSet result = (ResultSet) arg0.getObject();
> System.out.println("Iterating");
> while(result.next())
> System.out.println("test");
> result.close();
> 
> return result;
> } 
> 
> /* (non-Javadoc)
> * @see
> com.ibatis.sqlmap.client.extensions.TypeHandlerCallback#valueOf(
> java.lang.String)
> */
> public Object valueOf(String arg0) {
> // TODO Auto-generated method stub 
> return null;
> }
> 
> Unfortunately, when the statement:
> 
> ResultSet result = (ResultSet) arg0.getObject();
> 
> executes, we get an error from oracle that says "FETCH OUT OF
> SEQUENCE". Is there something with the cursor going on in iBatis that 
> prevents us from touching the nested cursor? I guess my next step is to
> start stepping through the iBatis code in debug to see whats happening,
> but I was hoping somebody may already have a solution or know the problem. 
> 
> 
> Thanks,
> 
> Ken
>

Mime
View raw message