Mybatis调用PostgreSQL存储过程实现数组入参传递

共计 13309 个字符，预计需要花费 34 分钟才能阅读完成。

前言

项目中用到了 Mybatis 调用 PostgreSQL 存储过程（自定义函数）相关操作，由于 PostgreSQL 自带数组类型，所以有一个自定义函数的入参就是一个 int 数组，形如：

CREATE OR REPLACE FUNCTION "public"."func_arr_update"(ids _int4)...

如上所示，参数是一个 int 数组，Mybatis 提供了对调用存储过程的支持，那么 PostgreSQL 独有的数组类型作为存储过程的参数又将如何处理呢？其实很简单，mybatis 提供了 typeHandlers 可以创建一个数组类型的 类型处理器，具体做法为：实现 org.apache.ibatis.type.TypeHandler 接口，或继承一个很便利的类 org.apache.ibatis.type.BaseTypeHandler，然后可以选择性地将它映射到一个 JDBC 类型，先稍作了解，后面再做详细说明，接下来依旧结合一个示例来看看。

创建自定义函数

如图，第一步首先是创建一个用于调用的自定义函数，功能也很简单，遍历参数数组的每一个元素和 t_student 表的 stuid 做比较，若一致，则修改那条记录的 stuname（在其后拼接一段字符串），该自定义函数的 DLL 语句如下：

CREATE OR REPLACE FUNCTION "public"."func_arr_update"(ids _int4)
  RETURNS "pg_catalog"."void" AS $BODY$
DECLARE
     scount INTEGER;
     rownum integer := 1;
BEGIN
        scount:=array_length(ids,1);
        while rownum <= scount LOOP
            update t_student set stuname = stuname || 'has been modified.' where stuid = ids[rownum];
            rownum := rownum + 1;
    END LOOP;
    RETURN;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

ALTER FUNCTION "public"."func_arr_update"(ids _int4) OWNER TO "postgres";

很简单，获取到参数数组的长度后开始循环，匹配 stuid 并更新 stuname，直接在数据库调用一下看看结果：

如上图，可以看到成功修改了 stuid 为 101，102 和 103 的 stuname，自定义函数已经没问题了，接下来就具体看一下如何通过 mybatis 调用。

调用自定义函数

mybatis 中调用自定义函数很简单，Mapper XML 文件中的 select 元素直接提供了属性支持——statementType，在官方文档中可以看到：

如上图，statementType 的值默认是 PREPARED，也就是说底层默认会使用 jdbc 的 PreparedStatement，而我们都知道 jdbc 调用存储过程时需要用 CallableStatement，所以在这里我们需要将 statementType 的值设置为CALLABLE。

mybatis 默认的 ArrayTypeHandler

调用存储过程很简单，那么接下来的问题是如何在 mybatis 中传一个数组参数到存储过程中呢？这里就要用到另外一个概念——TypeHandler，这是mybatis 提供的自定义类型转换器，mybatis 在预编译语句对象（PreparedStatement）设置参数时或是从结果集中取值时都会用类型处理器将获取的值以合适的方式转换成 Java 类型，mybatis 默认实现了一部分 TypeHandler 供我们使用，当我们没有指定 TypeHandler 时（大多数情况都不会指定），mybatis 会根据参数或者返回结果的不同，默认为我们选择合适的 TypeHandler 处理，下面可以通过查看源码大概看一下默认的 TypeHandler，导入源码后可以在 org.apache.ibatis.type 包下找到一个 TypeHandlerRegistry 类，typeHandler 正是通过这个类管理的，先看一下它的构造方法：

 public TypeHandlerRegistry() {register(Boolean.class, new BooleanTypeHandler());
    register(boolean.class, new BooleanTypeHandler());
    register(JdbcType.BOOLEAN, new BooleanTypeHandler());
    register(JdbcType.BIT, new BooleanTypeHandler());

    register(Byte.class, new ByteTypeHandler());
    register(byte.class, new ByteTypeHandler());
    register(JdbcType.TINYINT, new ByteTypeHandler());

    register(Short.class, new ShortTypeHandler());
    register(short.class, new ShortTypeHandler());
    register(JdbcType.SMALLINT, new ShortTypeHandler());

    register(Integer.class, new IntegerTypeHandler());
    register(int.class, new IntegerTypeHandler());
    register(JdbcType.INTEGER, new IntegerTypeHandler());

    register(Long.class, new LongTypeHandler());
    register(long.class, new LongTypeHandler());

    register(Float.class, new FloatTypeHandler());
    register(float.class, new FloatTypeHandler());
    register(JdbcType.FLOAT, new FloatTypeHandler());

    register(Double.class, new DoubleTypeHandler());
    register(double.class, new DoubleTypeHandler());
    register(JdbcType.DOUBLE, new DoubleTypeHandler());

    register(String.class, new StringTypeHandler());
    register(String.class, JdbcType.CHAR, new StringTypeHandler());
    register(String.class, JdbcType.CLOB, new ClobTypeHandler());
    register(String.class, JdbcType.VARCHAR, new StringTypeHandler());
    register(String.class, JdbcType.LONGVARCHAR, new ClobTypeHandler());
    register(String.class, JdbcType.NVARCHAR, new NStringTypeHandler());
    register(String.class, JdbcType.NCHAR, new NStringTypeHandler());
    register(String.class, JdbcType.NCLOB, new NClobTypeHandler());
    register(JdbcType.CHAR, new StringTypeHandler());
    register(JdbcType.VARCHAR, new StringTypeHandler());
    register(JdbcType.CLOB, new ClobTypeHandler());
    register(JdbcType.LONGVARCHAR, new ClobTypeHandler());
    register(JdbcType.NVARCHAR, new NStringTypeHandler());
    register(JdbcType.NCHAR, new NStringTypeHandler());
    register(JdbcType.NCLOB, new NClobTypeHandler());

    register(Object.class, JdbcType.ARRAY, new ArrayTypeHandler());
    register(JdbcType.ARRAY, new ArrayTypeHandler());

    register(BigInteger.class, new BigIntegerTypeHandler());
    register(JdbcType.BIGINT, new LongTypeHandler());

    register(BigDecimal.class, new BigDecimalTypeHandler());
    register(JdbcType.REAL, new BigDecimalTypeHandler());
    register(JdbcType.DECIMAL, new BigDecimalTypeHandler());
    register(JdbcType.NUMERIC, new BigDecimalTypeHandler());

    register(Byte[].class, new ByteObjectArrayTypeHandler());
    register(Byte[].class, JdbcType.BLOB, new BlobByteObjectArrayTypeHandler());
    register(Byte[].class, JdbcType.LONGVARBINARY, new BlobByteObjectArrayTypeHandler());
    register(byte[].class, new ByteArrayTypeHandler());
    register(byte[].class, JdbcType.BLOB, new BlobTypeHandler());
    register(byte[].class, JdbcType.LONGVARBINARY, new BlobTypeHandler());
    register(JdbcType.LONGVARBINARY, new BlobTypeHandler());
    register(JdbcType.BLOB, new BlobTypeHandler());

    register(Object.class, UNKNOWN_TYPE_HANDLER);
    register(Object.class, JdbcType.OTHER, UNKNOWN_TYPE_HANDLER);
    register(JdbcType.OTHER, UNKNOWN_TYPE_HANDLER);

    register(Date.class, new DateTypeHandler());
    register(Date.class, JdbcType.DATE, new DateOnlyTypeHandler());
    register(Date.class, JdbcType.TIME, new TimeOnlyTypeHandler());
    register(JdbcType.TIMESTAMP, new DateTypeHandler());
    register(JdbcType.DATE, new DateOnlyTypeHandler());
    register(JdbcType.TIME, new TimeOnlyTypeHandler());

    register(java.sql.Date.class, new SqlDateTypeHandler());
    register(java.sql.Time.class, new SqlTimeTypeHandler());
    register(java.sql.Timestamp.class, new SqlTimestampTypeHandler());

    // issue #273
    register(Character.class, new CharacterTypeHandler());
    register(char.class, new CharacterTypeHandler());
  }

如上所示，这就是全部默认的 typeHandler 了，注意一下 46，47 行可以看到默认有一个 ArrayTypeHandler，顺便看一下它的源码：

/*
 *    Copyright 2009-2012 The MyBatis Team
 *
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */
package org.apache.ibatis.type;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ArrayTypeHandler extends BaseTypeHandler<Object> {

  public ArrayTypeHandler() {super();
  }

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {ps.setArray(i, (Array) parameter);
  }

  @Override
  public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {Array array = rs.getArray(columnName);
    return array == null ? null : array.getArray();}

  @Override
  public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {Array array = rs.getArray(columnIndex);
    return array == null ? null : array.getArray();}

  @Override
  public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {Array array = cs.getArray(columnIndex);
    return array == null ? null : array.getArray();}

}

那它能否识别 PostgreSQL 的数组类型并将它自动转换成 Java 数组类型呢？按官方的说法，既然这是默认的 typeHandler，那么我们无需做任何配置 mybatis 会自动尝试适配，所以直接写测试代码看看：

@Test
public void testFunc1() {SqlSession session = sqlSessionFactory.openSession();
    try {Map<String, Object> map = new HashMap<String, Object>();
        map.put("ids", new Integer[] { 101, 102, 103 });
        session.update("com.wl.entity.StudentMapper.testFuncUpdate2", map);
        session.commit();} catch (Exception e) {e.printStackTrace();
    } finally {session.close();
    }
}

<update id="testFuncUpdate2" statementType="CALLABLE">
    {call func_arr_update (#{ids,mode=IN})}  
</update>

如上所示，参数传的是一个 Integer[]，直接运行一下 junit 看看测试结果：

Can’t infer the SQL type to use for an instance of [Ljava.lang.Integer;. Use setObject() with an explicit Types value to specify the type to use.

异常 log 如上所示，在调用 AbstractJdbc2Statement 类的 setObject 方法时抛出异常，那么再看看这个方法的源码：

    /*
     * This stores an Object into a parameter.
     */
    public void setObject(int parameterIndex, Object x) throws SQLException
    {checkClosed();
        if (x == null)
            setNull(parameterIndex, Types.OTHER);
        else if (x instanceof String)
            setString(parameterIndex, (String)x);
        else if (x instanceof BigDecimal)
            setBigDecimal(parameterIndex, (BigDecimal)x);
        else if (x instanceof Short)
            setShort(parameterIndex, ((Short)x).shortValue());
        else if (x instanceof Integer)
            setInt(parameterIndex, ((Integer)x).intValue());
        else if (x instanceof Long)
            setLong(parameterIndex, ((Long)x).longValue());
        else if (x instanceof Float)
            setFloat(parameterIndex, ((Float)x).floatValue());
        else if (x instanceof Double)
            setDouble(parameterIndex, ((Double)x).doubleValue());
        else if (x instanceof byte[])
            setBytes(parameterIndex, (byte[])x);
        else if (x instanceof java.sql.Date)
            setDate(parameterIndex, (java.sql.Date)x);
        else if (x instanceof Time)
            setTime(parameterIndex, (Time)x);
        else if (x instanceof Timestamp)
            setTimestamp(parameterIndex, (Timestamp)x);
        else if (x instanceof Boolean)
            setBoolean(parameterIndex, ((Boolean)x).booleanValue());
        else if (x instanceof Byte)
            setByte(parameterIndex, ((Byte)x).byteValue());
        else if (x instanceof Blob)
            setBlob(parameterIndex, (Blob)x);
        else if (x instanceof Clob)
            setClob(parameterIndex, (Clob)x);
        else if (x instanceof Array)
            setArray(parameterIndex, (Array)x);
        else if (x instanceof PGobject)
            setPGobject(parameterIndex, (PGobject)x);
        else if (x instanceof Character)
            setString(parameterIndex, ((Character)x).toString());
        else if (x instanceof Map)
            setMap(parameterIndex, (Map)x);
        else
        {// Can't infer a type.
            throw new PSQLException(GT.tr("Can''t infer the SQL type to use for an instance of {0}. Use setObject() with an explicit Types value to specify the type to use.", x.getClass().getName()), PSQLState.INVALID_PARAMETER_TYPE);
        }
    }

我们参数传进去的 Integer[]数组是一个 Object 数组，而 setObject(int parameterIndex, Object x)方法的第二个参数是 Object，所以这里这里自然无法匹配也就报错了，那么换成 int[]可以吗？在上面的 else if 语句中明显没有 x instanceof int[]这行代码，所以当然也不行，说到这里也就明确了 mybatis 默认提供的 ArrayTypeHandler 是无法自动识别 PostgreSQL 的数组类型，我们必须自定义一个参数为 Object[]的 ArrayTypeHandler 才能实现匹配。

自定义 ArrayTypeHandler

如题，先贴上代码：

package com.wl.util;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.TypeException;

@MappedJdbcTypes(JdbcType.ARRAY)
public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {

    private static final String TYPE_NAME_VARCHAR = "varchar";
    private static final String TYPE_NAME_INTEGER = "integer";
    private static final String TYPE_NAME_BOOLEAN = "boolean";
    private static final String TYPE_NAME_NUMERIC = "numeric";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
            Object[] parameter, JdbcType jdbcType) throws SQLException {String typeName = null;
        if (parameter instanceof Integer[]) {typeName = TYPE_NAME_INTEGER;} else if (parameter instanceof String[]) {typeName = TYPE_NAME_VARCHAR;} else if (parameter instanceof Boolean[]) {typeName = TYPE_NAME_BOOLEAN;} else if (parameter instanceof Double[]) {typeName = TYPE_NAME_NUMERIC;}

        if (typeName == null) {throw new TypeException("ArrayTypeHandler parameter typeName error, your type is"
                            + parameter.getClass().getName());
        }

        Connection conn = ps.getConnection();
        Array array = conn.createArrayOf(typeName, parameter);
        ps.setArray(i, array);
    }

    @Override
    public Object[] getNullableResult(ResultSet rs, String columnName)
            throws SQLException {return getArray(rs.getArray(columnName));
    }

    @Override
    public Object[] getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {return getArray(rs.getArray(columnIndex));
    }

    @Override
    public Object[] getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {return getArray(cs.getArray(columnIndex));
    }

    private Object[] getArray(Array array) {if (array == null) {return null;
        }

        try {return (Object[]) array.getArray();} catch (Exception e) { }

        return null;
    }
}

如上所示，我们指定了参数类型为 Object[]，这样就可以接收 Integer[]类型的参数了，关键是 44~46 行，postgresql 的驱动类 AbstractJdbc4Connection 实现了 Connect 接口的 createArrayOf 方法，源码如下：

   public Array createArrayOf(String typeName, Object[] elements) throws SQLException
    {checkClosed();
        int oid = getTypeInfo().getPGArrayType(typeName);
        if (oid == Oid.UNSPECIFIED)
            throw new PSQLException(GT.tr("Unable to find server array type for provided name {0}.", typeName), PSQLState.INVALID_NAME);

        char delim = getTypeInfo().getArrayDelimiter(oid);
        StringBuffer sb = new StringBuffer();
        appendArray(sb, elements, delim);

        // This will not work once we have a JDBC 5,
        // but it'll do for now.
        return new Jdbc4Array(this, oid, sb.toString());
    }

这样通过自定义的 ArrayTypeHandler 就可以在 Mybatis 中方便的操作数组类型数据了，最后再测试一下，测试类代码不变，仅需在调用存储过程时指定 mapper 文件的 typeHandler 即可：

@Test
public void testFunc1() {SqlSession session = sqlSessionFactory.openSession();
    try {Map<String, Object> map = new HashMap<String, Object>();
        map.put("ids", new Integer[] { 101, 102, 103 });
        session.update("com.wl.entity.StudentMapper.testFuncUpdate2", map);
        session.commit();} catch (Exception e) {e.printStackTrace();
    } finally {session.close();
    }
}

<update id="testFuncUpdate2" statementType="CALLABLE">
    {call func_arr_update (#{ids,mode=IN,typeHandler=com.wl.util.ArrayTypeHandler})}  
</update>

再次运行 junit 看一下测试结果：

如上所示，此时已经可以成功调用参数为 Integer[]数组的 pg 自定义函数了。

总结

简单记录一下在 mybatis 中调用 postgresql 自定义函数时传递数组参数的解决方案，希望对遇到同样问题的朋友有所帮助，The End。

———————————— 华丽丽的分割线 ————————————

在 CentOS 6.5 上编译安装 PostgreSQL 9.3 数据库 http://www.linuxidc.com/Linux/2016-06/132272.htm

CentOS 6.3 环境下 yum 安装 PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htm

PostgreSQL 缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htm

Windows 平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htm

Ubuntu 下 LAPP（Linux+Apache+PostgreSQL+PHP)环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htm

Ubuntu 上的 phppgAdmin 安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htm

CentOS 平台下安装 PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htm

PostgreSQL 配置 Streaming Replication 集群 http://www.linuxidc.com/Linux/2014-05/101724.htm

———————————— 华丽丽的分割线 ————————————

PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135541.htm