Mybatis TypeHandler to convert Integer to String

Question

I’m using Mybatis ORM with Vertica DB. I have an object like below, which I can’t modify

Class Employee{

  Integer empId;

  Integer deptId;

 Integer salary;

  //get and set methods for above

}

And code for TypeHandler is like below

public class IntegerToStringTypeHandler  extends BaseTypeHandler<Integer> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Integer integer, JdbcType jdbcType) throws SQLException {
        System.out.println("i  "+i +" integer "+ integer + "   "+integer );
        preparedStatement.setString(i,integer.toString());
    }

    @Override
    public Integer getNullableResult(ResultSet resultSet, String columnName) throws SQLException {

        return Integer.valueOf(resultSet.getString(columnName));
    }

    @Override
    public Integer getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return Integer.valueOf(resultSet.getString(i));

    }

    @Override
    public Integer getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return Integer.valueOf(callableStatement.getString(i));
    }


}

And then table is like below

Desc Employee

Columns.        Type
empId           Integer

deptId          Varchar(10)

Salary          Integer

I have mybatis query like below

<select id=“getCount” parameter=“employee”>

Select count(1) from Employee
Where  empid = #{employee.empid}
AND.  deptId = #{employee. deptId,typeHandler=com.convert.type.IntegerToStringTypeHandler}
AND  salary= #{employee.salary}  
</select>

But it doesn’t work it fails with the following exception

Caused by: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='__frch_employee_0.deptid’, mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: [Vertica][JDBC](10940) Invalid parameter index: 3.
        at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:89) ~[mybatis-3.4.5.jar:3.4.5]


Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #3 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: [Vertica][JDBC](10940) Invalid parameter index: 3.
        at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55) ~[mybatis-3.4.5.jar:3.4.5]

Caused by: java.sql.SQLException: [Vertica][JDBC](10940) Invalid parameter index: 3.
        at com.vertica.exceptions.ExceptionConverter.toSQLException(Unknown Source) ~[vertica-jdbc-8.1.1-7.jar:?]
        at com.vertica.jdbc.common.SPreparedStatement.checkValidParameterIndex(Unknown Source) ~[vertica-jdbc-8.1.1-7.jar:?]
        at com.vertica.jdbc.common.SPreparedStatement.setString(Unknown Source) ~[vertica-jdbc-8.1.1-7.jar:?]
        at com.convert.type.IntegerToStringTypeHandler.setNonNullParameter(IntegerToStringTypeHandler.java:16) ~[classes/:?]
        at com.convert.type.IntegerToStringTypeHandler.setNonNullParameter(IntegerToStringTypeHandler.java:11) ~[classes/:?]
        at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:53) ~[mybatis-3.4.5.jar:3.4.5]
        at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87) ~[mybatis-3.4.5.jar:3.4.5]
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:93) ~[mybatis-3.4.5.jar:3.4.5]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:64) ~[mybatis-3.4.5.jar:3.4.5]
        at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86) ~[mybatis-3.4.5.jar:3.4.5]

I have spent a lot of time. Could someone please let know what I am missing?

the main problem is in this line

#{employee. deptId,typeHandler=com.convert.type.IntegerToStringTypeHandler}

Thanks

Answer 1

The code here is cannot be the one that is actually executed, I guess you have rewrote the code in the post because: - quoting characters “ / ”are not supported by the XML parser

• there is an unexpected dot . after the first AND that likely end up to SQL syntax exception

• the select does specify neither a Result Type nor a Result Map.

So please copy/paste the code actually executed, that will allow giving more accurate answer. your TypeHandler is working fine.