Mybatis調用PostgreSQL預存程序實現數組入參傳遞_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會自動嘗試適配，所以直接寫測試代碼看看：

@Testpublic 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即可：

@Testpublic 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。

以上就是本文的全部內容，希望對大家的學習有所協助，也希望大家多多支援雲棲社區。