Call a Postgresql function passing an array of integer using mybatis

Question

It might be a direct question but I'm trying many options which is not working. I've given the below details for your reference. Mybatis xml configuration:-

<select id="updateComps" statementType="CALLABLE" parameterType="java.util.List">
  <![CDATA[ {call vivo.select_workout (#{list, mode=IN, jdbcType=ARRAY})}]]>        
</select>

Postgres Function:-

CREATE OR REPLACE FUNCTION vivo.delete_completed_activity_logged_workout(INT[])
    RETURNS integer
 LANGUAGE plpgsql
AS $function$
declare
ids ALIAS FOR $1;
dist BIGINT[];

Mapper config:-

void deleteCompletedActivityRecords(List<Integer> users);

Error facing:-

aused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType ARRAY . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.ClassCastException: java.util.Arrays$ArrayList cannot be cast to java.sql.Array at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:55) at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:87) ... 68 more Caused by: java.lang.ClassCastException: java.util.Arrays$ArrayList cannot be cast to java.sql.Array at org.apache.ibatis.type.ArrayTypeHandler.setNonNullParameter(ArrayTypeHandler.java:35) at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:53) ... 69 more [WARNING] Could not send response error 500: javax.servlet.ServletException: javax.servlet.ServletException: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='list', mode=IN, javaType=class java.lang.Object, jdbcType=ARRAY, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType ARRAY . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.lang.ClassCastException: java.util.Arrays$ArrayList cannot be cast to java.sql.Array

You need to write a custom type handler. It would look pretty much the same as this one except "integer" should be the first argument of createArrayOf instead of "text". — ave
Thank you. I added a custom typeHandler for integer. Based on the link you've suggested and the below one. — Jefila

Jefila Jefila · Accepted Answer · 2020-06-02T07:20:10

I created the below custom ArrayTypeHandler.

package com.lf.util;

import com.google.common.collect.Lists;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.JdbcType;

import java.sql.*;
import java.util.List;

@MappedJdbcTypes(JdbcType.ARRAY)
public class CustomArrayTypeHandler extends BaseTypeHandler<List<Long>> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<Long> parameter, JdbcType jdbcType) throws SQLException {
        Array array = ps.getConnection().createArrayOf("integer", parameter.toArray());
        ps.setArray(i, array);
    }

    @Override
    public List<Long> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return toList(rs.getArray(columnName));
    }

    @Override
    public List<Long> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return toList(rs.getArray(columnIndex));
    }

    @Override
    public List<Long> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return toList(cs.getArray(columnIndex));
    }

    private List<Long> toList(Array pgArray) throws SQLException {
        if (pgArray == null) return Lists.newArrayList();

        Long[] strings = (Long[]) pgArray.getArray();
        return containsOnlyNulls(strings) ? Lists.<Long>newArrayList() : Lists.newArrayList(strings);
    }

    private boolean containsOnlyNulls(Long[] strings) {
        for (Long s : strings) {
            if (s != null) {
                return false;
            }
        }
        return true;
    }
}

And in the xml file:

<select id="deleteRecords" statementType="CALLABLE" resultType="java.lang.Integer" parameterType="java.util.List">
        <![CDATA[ {call vivo.delete_records (#{list, mode=IN, typeHandler=com.lf.util.CustomArrayTypeHandler})}]]>
    </select>

Call a Postgresql function passing an array of integer using mybatis

2 Answers