当前位置: 首页 > 编程笔记 >

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

明安阳
2023-03-14
本文向大家介绍Mybatis调用PostgreSQL存储过程实现数组入参传递,包括了Mybatis调用PostgreSQL存储过程实现数组入参传递的使用技巧和注意事项,需要的朋友参考一下

前言

项目中用到了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。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持小牛知识库。

 类似资料:
  • 问题内容: 我正在将MyBAtis-3与MyBAtis- Spring一起使用。当我尝试调用使用MyBatis批注返回多个参数的存储过程时。我什么也没得到,我可以看到输入参数已传递到日志中的SP,并且挂在那里,没有进度,也没有抛出异常。 PFB我试图从MyBAtis访问的Oracle存储过程, PFB映射器接口方法, Test对象包含在存储过程调用语句中作为输入传递的参数。 当我执行此操作时,它会

  • 我在HANA数据库中创建了这个存储过程,它使用两个参数,一个是表类型,另一个是。 现在我想在Java中调用这个过程,我写了这样的东西。 有人能告诉我在调用此存储过程时,如何将对象作为参数中的表实体传递吗?

  • 这可能是一个直接的问题,但我尝试了很多选择,但都不起作用。我给出了以下细节供大家参考。Mybatis xml配置:- Postgres功能:- 映射器配置:- 错误面对:- aused:org.apache.ibatis.type.TypeException:使用JdbcType数组为参数#1设置非空时出错。尝试为此参数设置不同的JdbcType或不同的配置属性。原因:java.lang.Clas

  • 我正在从Spring中调用一个存储过程,但是我需要向存储过程传递一个字符串数组。然而,到目前为止,我遇到了一个又一个错误。你知道哪里出了问题吗? 我尝试运行以下测试。 这是我得到的stacktrace。

  • 本文向大家介绍SpringMVC与Mybatis集合实现调用存储过程、事务控制实例,包括了SpringMVC与Mybatis集合实现调用存储过程、事务控制实例的使用技巧和注意事项,需要的朋友参考一下 在SSM框架中经常会用到调用数据库中的存储过程、以及事务控制,下面以保存某单据为例,介绍一下: 1、Oracle中存储过程代码如下(主要逻辑将单据编码自动加1,并将该单据编码返回): 2、Mybati

  • 问题内容: 我希望能够在PetaPoco中使用命名参数调用存储的proc。 为了调用执行搜索/获取的存储过程: 我可以做这样的事情: 另外,如何调用执行插入操作的存储过程? 谢谢,Nac 问题答案: 更新: 我尝试了以下操作来进行获取和插入,并且效果很好: 可以进一步改进以传递SQL参数。