ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dave Rodenbaugh" <drodenba...@WILDBLUECORP.COM>
Subject Using a Type Handler for SELECT stmt
Date Mon, 05 Feb 2007 23:59:09 GMT
Hi everyone,

I have a custom type handler I'm using for Java 1.5 enums to map to a
VARCHAR column in Oracle.  I've read the SqlMap doc about TypeHandlers
but there are some missing details...I'm sure my TypeHandler syntax
definition is off, but I'm having a hard time finding the right place
for it.
 
If I want to setup my sql-map.xml like this (to pull the enums for a
select), is this the right XML?  (the examples are all INSERT/stored
proc-based, so the syntax here isn't clear):
 
    <typeAlias alias='User' type='com.foo.UserVO'/>
    <!-- SQL fragment to be referenced by other selects -->
    <sql id='UserBase'>
        SELECT
            USER_ID AS userId,
            USER_TYPE_ID AS userTypeId,
            USER_TYPE AS
userType#userType,javaType=com.foo.UserTypeEnum,jdbcType=VARCHAR,handler
=com.foo.UserTypeTypeHandlerCallback#,
            SALES_AGENT_ID AS salesAgentId,
            USERNAME AS username,
            PASSWORD AS password,
            START_DATE AS startDate,
            END_DATE AS endDate,
            FIRST_NAME AS firstName,
            LAST_NAME AS lastName,
            CYG_SALES_ID AS cygSalesId,
            CREATE_DATE AS createDate,
            UPDATE_DATE AS updateDate,
            CREATED_BY AS createdBy,
            UPDATED_BY AS updatedBy
        FROM OWNER.USER_VW
    </sql>
    <select id='findUser' resultClass='User'>
        <include refid='UserBase'/>
    </select>
    <select id='findUserByUserId' parameterClass='java.lang.Long'
resultClass='User'><include refid='UserBase'/>WHERE User_ID =
#userId#</select>

Where UserVO has an Enum for the field/property UserType, the enum is
well-defined, and my callback handler looks like this:

public class UserTypeTypeHandlerCallback implements TypeHandlerCallback
{
    public Object getResult(ResultGetter getter) throws SQLException
    {
        //Convert the result to the Enum class
        String s = getter.getString();
        if (null == s) return null;
        try {
            return Enum.valueOf(UserTypeEnum.class, s);
        } catch (IllegalArgumentException e) {
            throw new SQLException("Invalid enum value encountered: " +
s + " for " + UserTypeEnum.class);
        }
    }

    public void setParameter(ParameterSetter setter, Object param)
throws SQLException
    {
        //Convert the Enum to the DB result
        UserTypeEnum e = null;
        if (param.getClass() == String.class) {
            e = (UserTypeEnum)param;
        } else if (param.getClass() == Long.class) {
            e = UserTypeEnum.valueOf((Long)param);
        }
        setter.setString(e.name());
    }

    public Object valueOf(String str)
    {
        //Convert the result to the Enum class
        if (null == str) return null;
        return Enum.valueOf(UserTypeEnum.class, str);
    }
}

Why do I see an Oracle error for this query:

com.ibatis.dao.client.DaoException: Failed to queryForList - id
[findWbUserByUserId], parameterObject [1].  Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in
com/wildblue/serviceimage/ibatis-view-sql-maps.xml.  
--- The error occurred while applying a parameter map.  
--- Check the findWbUserByUserId-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00923: FROM keyword not found
where expected

Caused by: java.sql.SQLException: ORA-00923: FROM keyword not found
where expected

	at
com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForList(SqlMapDaoT
emplate.java:284)
	at
com.wildblue.serviceimage.WbUserDaoImpl.findWbUserByUserId(WbUserDaoImpl
.java:41)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:39)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at com.ibatis.dao.engine.impl.DaoProxy.invoke(DaoProxy.java:72)
	at $Proxy15.findWbUserByUserId(Unknown Source)
	at
com.wildblue.serviceimage.ServiceImageService.getUser(ServiceImageServic
e.java:177)
	at
com.wildblue.serviceimage.ServiceImageService.insertAccount(ServiceImage
Service.java:655)
	at
com.wildblue.serviceimage.ServiceImageService.persistAccount(ServiceImag
eService.java:241)
	at
com.wildblue.serviceimage.ServiceImageServiceTest.testPaymentMethod(Serv
iceImageServiceTest.java:45)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:39)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at junit.framework.TestCase.runTest(TestCase.java:154)
	at junit.framework.TestCase.runBare(TestCase.java:127)
	at junit.framework.TestResult$1.protect(TestResult.java:106)
	at junit.framework.TestResult.runProtected(TestResult.java:124)
	at junit.framework.TestResult.run(TestResult.java:109)
	at junit.framework.TestCase.run(TestCase.java:118)
	at
org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUn
it3TestReference.java:128)
	at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.ja
va:38)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTe
stRunner.java:460)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTe
stRunner.java:673)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRun
ner.java:386)
	at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRu
nner.java:196)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in
com/wildblue/serviceimage/ibatis-view-sql-maps.xml.  
--- The error occurred while applying a parameter map.  
--- Check the findWbUserByUserId-InlineParameterMap.  
--- Check the statement (query failed).  
--- Cause: java.sql.SQLException: ORA-00923: FROM keyword not found
where expected

Caused by: java.sql.SQLException: ORA-00923: FROM keyword not found
where expected

	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery
WithCallback(GeneralStatement.java:185)
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery
ForList(GeneralStatement.java:123)
	at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap
ExecutorDelegate.java:614)
	at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMap
ExecutorDelegate.java:588)
	at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessi
onImpl.java:118)
	at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClient
Impl.java:96)
	at
com.ibatis.dao.client.template.SqlMapDaoTemplate.queryForList(SqlMapDaoT
emplate.java:282)
	... 27 more
Caused by: java.sql.SQLException: ORA-00923: FROM keyword not found
where expected

	at
oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
	at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:877
)
	at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2
513)
	at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.
java:2857)
	at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedS
tatement.java:608)
	at
oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStateme
nt.java:684)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.jav
a:39)
	at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at
com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(Prepared
StatementLogProxy.java:62)
	at $Proxy17.execute(Unknown Source)
	at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.
java:182)
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQu
ery(GeneralStatement.java:205)
	at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQuery
WithCallback(GeneralStatement.java:173)
	... 33 more

Thanks...
-Dave

Mime
View raw message