MyBatis-Spring-jsf: Error setting null parameters

[deivK]

Hello to everyone,
i'm using: Spring 3.1.1, MyBatis 3.0.6, jsf-myfaces 2.0.12.
I'm working on WebLogic 10.3.2 and Oracle DB.

This is the query that i'm trying to do:

@Select(SELECT_BY_ID_DATE)
        @Results(value= { @Result(property="idValidationEvent", column="ID_VALIDATION_EVENT"),
                                        @Result(property="id_flow", column="ID_FLOW"),
                                        @Result(property="inputArtifact", column="INPUTARTIFACT"),
                                        @Result(property="validationRuleDescription", column="VALIDATIONRULEDESCRIPTION"),
                                        @Result(property="contestDescription", column="CONTESTDESCRIPTION"),
                                        @Result(property="type", column="TYPE"),
                                        @Result(property="dataInserimento", column="DATAINSERIMENTO")})
public List<ValidationEvent> selectByIDDate(String id, java.sql.Date dataIns);


where SELECT_BY_ID_DATE is:


final String SELECT_BY_ID_DATE = "SELECT *"+
                                                " FROM VALIDATION_EVENT" +
                                                " WHERE ID_FLOW = #{id} AND DATAINSERIMENTO = #{dataIns}";


This is the form from wich I fire the query "()" = "<>":


(h:form)
    (h:outputText value="Seleziona la data di validazione:" /)
    (p:selectOneMenu value="#{UploadedFlowValidationEventsBean.selectedDate}" effect="fade")  
            (f:selectItem itemLabel="Seleziona la data" itemValue="null" /)
            (f:selectItem itemLabel="Tutte" itemValue="tutte" /)  
            (f:selectItems value="#{UploadedFlowValidationEventsBean.dateInserimento}" var="dataInserimento" itemLabel="#{dataInserimento}" itemValue="#{dataInserimento}"/)
        (/p:selectOneMenu)
        (p:commandButton value="Select Date" ajax="false" action="#{UploadedFlowValidationEventsBean.getUploadedFlowValidationEventsByDate}"/)
        (/h:form)


UploadedFlowValidationEventsBean is a jsf bean that, i'm sure, is in scope and has all the properties correctly initialized. This is the bean code:



private String id;
private List<ValidationEvent> validationEvents;
private ValidationEventDAO veDAO;
private String msg;
private List<String> dateInserimento;
private String selectedDate;

public String getUploadedFlowValidationEventsByDate(){
                try{
                        if(selectedDate.equals("null")){
                               
                        }
                        else{
                                if(!selectedDate.equals("tutte")){
                                       
                                        Date convertedDate = Date.valueOf(selectedDate);
                                        long time = convertedDate.getTime();
                                        convertedDate.setTime(time);
                                        validationEvents = veDAO.selectByIDDate(id, convertedDate);
                                }
                                return "getUploadedFlowValidationEventsByDate-ok";
                        }
                       
                }catch(Exception e){
                        e.printStackTrace();
                }
               
                return "getUploadedFlowValidationEventsByDate-ko";
        }


If a date value was selected in the xhtml form, the bean execute a select with that date value and id.
The problem is that i get the following exception:

org.springframework.jdbc.UncategorizedSQLException: Error setting null parameter.  Most JDBC drivers require that the JdbcType must be specified for all nullable parameters. Cause: java.sql.SQLException: Tipo di colonna non valido: 1111
; uncategorized SQLException for SQL []; SQL state [99999]; error code [17004]; Tipo di colonna non valido: 1111; nested exception is java.sql.SQLException: Tipo di colonna non valido: 1111
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)
        at $Proxy53.selectList(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:190)
        at org.apache.ibatis.binding.MapperMethod.executeForList(MapperMethod.java:100)
        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:70)
        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:25)
        at $Proxy240.selectByIDDate(Unknown Source)
        at com.reply.wmb.dao.implementations.ValidationEventMyBatisDAO.selectByIDDate(ValidationEventMyBatisDAO.java:32)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
        at $Proxy241.selectByIDDate(Unknown Source)
        at com.reply.wmb.web.UploadedFlowValidationEventsBean.getUploadedFlowValidationEventsByDate(UploadedFlowValidationEventsBean.java:60)
        at com.reply.wmb.web.UploadedFlowValidationEventsBean$$FastClassByCGLIB$$e04e80fd.invoke(<generated>)
        at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.apache.myfaces.orchestra.conversation.CurrentConversationAdvice.invoke(CurrentConversationAdvice.java:84)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
        at com.reply.wmb.web.UploadedFlowValidationEventsBean$$EnhancerByCGLIB$$ca4da194.getUploadedFlowValidationEventsByDate(<generated>)
        at com.reply.wmb.web.UploadedFlowValidationEventsBean$$FastClassByCGLIB$$e04e80fd.invoke(<generated>)
        at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
        at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
        at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
        at com.reply.wmb.web.UploadedFlowValidationEventsBean$$EnhancerByCGLIB$$4d12937b.getUploadedFlowValidationEventsByDate(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.sun.el.parser.AstValue.invoke(AstValue.java:157)
        at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:283)
        at org.apache.myfaces.view.facelets.el.ContextAwareTagMethodExpression.invoke(ContextAwareTagMethodExpression.java:94)
        at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:72)
        at javax.faces.component.UICommand.broadcast(UICommand.java:120)
        at javax.faces.component.UIViewRoot._broadcastAll(UIViewRoot.java:1015)
        at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:284)
        at javax.faces.component.UIViewRoot._process(UIViewRoot.java:1309)
        at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:746)
        at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:38)
        at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:171)
        at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
        at javax.faces.webapp.FacesServlet.service(FacesServlet.java:189)
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
        at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
        at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:79)
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
        at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3592)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2202)
        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2108)
        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1432)
        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
        at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
Caused by: java.sql.SQLException: Tipo di colonna non valido: 1111
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
        at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3526)
        at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4312)
        at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4298)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1281)
        at weblogic.jdbc.wrapper.PreparedStatement.setNull(PreparedStatement.java:525)
        at sun.reflect.GeneratedMethodAccessor1063.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:53)
        at $Proxy90.setNull(Unknown Source)
        at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:15)
        at org.apache.ibatis.executor.parameter.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:73)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:61)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:43)
        at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:56)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:40)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73)
        at sun.reflect.GeneratedMethodAccessor337.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:350)
        ... 70 more



Can you help me?

[Eduardo Macarron]

Hi deivK,

You have two options.

Specify an specific jdbcType that Oracle accepts
INSERT INTO TABLE ( COLUMN ) VALUES ( #{property,jdbcType=VARCHAR} )
(VARCHAR is just a sample!)

Or go 3.1 and do it as a default for all your queries:
http://code.google.com/p/mybatis/issues/detail?id=224

The doc, see jdbcTypeForNull:
http://www.mybatis.org/core/configuration.html

[Eduardo Macarron]

Sorry, I meant "for all your statements", not queries.

On 14 mar, 04:18, Eduardo <[hidden email]> wrote:

> Hi deivK,
>
> You have two options.
>
> Specify an specific jdbcType that Oracle accepts
> INSERT INTO TABLE ( COLUMN ) VALUES ( #{property,jdbcType=VARCHAR} )
> (VARCHAR is just a sample!)
>
> Or go 3.1 and do it as a default for all your queries:http://code.google.com/p/mybatis/issues/detail?id=224
>
> The doc, see jdbcTypeForNull:http://www.mybatis.org/core/configuration.html

[deivK]

Thank you very much, it works!

2012/3/14 Eduardo <[hidden email]>

Sorry, I meant "for all your statements", not queries.

On 14 mar, 04:18, Eduardo <[hidden email]> wrote:
> Hi deivK,
>
> You have two options.
>
> Specify an specific jdbcType that Oracle accepts
> INSERT INTO TABLE ( COLUMN ) VALUES ( #{property,jdbcType=VARCHAR} )
> (VARCHAR is just a sample!)
>
> Or go 3.1 and do it as a default for all your queries:http://code.google.com/p/mybatis/issues/detail?id=224
>
> The doc, see jdbcTypeForNull:http://www.mybatis.org/core/configuration.html

--
Davide Carrettin