前言
項(xiàng)目中用到了Mybatis調(diào)用PostgreSQL存儲(chǔ)過(guò)程（自定義函數(shù)）相關(guān)操作，由于PostgreSQL自帶數(shù)組類(lèi)型，所以有一個(gè)自定義函數(shù)的入?yún)⒕褪且粋€(gè)int數(shù)組，形如：
創(chuàng)建自定義函數(shù)
如圖，第一步首先是創(chuàng)建一個(gè)用于調(diào)用的自定義函數(shù)，功能也很簡(jiǎn)單，遍歷參數(shù)數(shù)組的每一個(gè)元素和t_student表的stuid做比較，若一致，則修改那條記錄的stuname（在其后拼接一段字符串），該自定義函數(shù)的DLL語(yǔ)句如下：
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";
很簡(jiǎn)單，獲取到參數(shù)數(shù)組的長(zhǎng)度后開(kāi)始循環(huán)，匹配stuid并更新stuname，直接在數(shù)據(jù)庫(kù)調(diào)用一下看看結(jié)果：
如上圖，可以看到成功修改了stuid為101，102和103的stuname，自定義函數(shù)已經(jīng)沒(méi)問(wèn)題了，接下來(lái)就具體看一下如何通過(guò)mybatis調(diào)用。
調(diào)用自定義函數(shù)
mybatis中調(diào)用自定義函數(shù)很簡(jiǎn)單，Mapper XML文件中的select元素直接提供了屬性支持——statementType，在官方文檔中可以看到：
如上圖，statementType的值默認(rèn)是PREPARED，也就是說(shuō)底層默認(rèn)會(huì)使用jdbc的PreparedStatement，而我們都知道jdbc調(diào)用存儲(chǔ)過(guò)程時(shí)需要用CallableStatement，所以在這里我們需要將statementType的值設(shè)置為CALLABLE。
mybatis默認(rèn)的ArrayTypeHandler
調(diào)用存儲(chǔ)過(guò)程很簡(jiǎn)單，那么接下來(lái)的問(wèn)題是如何在mybatis中傳一個(gè)數(shù)組參數(shù)到存儲(chǔ)過(guò)程中呢？這里就要用到另外一個(gè)概念——TypeHandler，這是mybatis提供的自定義類(lèi)型轉(zhuǎn)換器，mybatis在預(yù)編譯語(yǔ)句對(duì)象（PreparedStatement）設(shè)置參數(shù)時(shí)或是從結(jié)果集中取值時(shí)都會(huì)用類(lèi)型處理器將獲取的值以合適的方式轉(zhuǎn)換成Java類(lèi)型，mybatis默認(rèn)實(shí)現(xiàn)了一部分TypeHandler供我們使用，當(dāng)我們沒(méi)有指定TypeHandler時(shí)（大多數(shù)情況都不會(huì)指定），mybatis會(huì)根據(jù)參數(shù)或者返回結(jié)果的不同，默認(rèn)為我們選擇合適的TypeHandler處理，下面可以通過(guò)查看源碼大概看一下默認(rèn)的TypeHandler，導(dǎo)入源碼后可以在org.apache.ibatis.type包下找到一個(gè)TypeHandlerRegistry類(lèi)，typeHandler正是通過(guò)這個(gè)類(lèi)管理的，先看一下它的構(gòu)造方法：
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()); }如上所示，這就是全部默認(rèn)的typeHandler了，注意一下46，47行可以看到默認(rèn)有一個(gè)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(); }}那它能否識(shí)別PostgreSQL的數(shù)組類(lèi)型并將它自動(dòng)轉(zhuǎn)換成Java數(shù)組類(lèi)型呢？按官方的說(shuō)法，既然這是默認(rèn)的typeHandler，那么我們無(wú)需做任何配置mybatis會(huì)自動(dòng)嘗試適配，所以直接寫(xiě)測(cè)試代碼看看：
@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>如上所示，參數(shù)傳的是一個(gè)Integer[]，直接運(yùn)行一下junit看看測(cè)試結(jié)果：
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如上所示，在調(diào)用AbstractJdbc2Statement類(lèi)的setObject方法時(shí)拋出異常，那么再看看這個(gè)方法的源碼：
/* * 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); } }我們參數(shù)傳進(jìn)去的Integer[]數(shù)組是一個(gè)Object數(shù)組，而 setObject(int parameterIndex, Object x)方法的第二個(gè)參數(shù)是Object，所以這里這里自然無(wú)法匹配也就報(bào)錯(cuò)了，那么換成int[]可以嗎？在上面的else if語(yǔ)句中明顯沒(méi)有x instanceof int[]這行代碼，所以當(dāng)然也不行，說(shuō)到這里也就明確了mybatis默認(rèn)提供的ArrayTypeHandler是無(wú)法自動(dòng)識(shí)別PostgreSQL的數(shù)組類(lèi)型，我們必須自定義一個(gè)參數(shù)為Object[]的ArrayTypeHandler才能實(shí)現(xiàn)匹配。
自定義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; }}如上所示，我們指定了參數(shù)類(lèi)型為Object[]，這樣就可以接收Integer[]類(lèi)型的參數(shù)了，關(guān)鍵是44~46行，postgresql的驅(qū)動(dòng)類(lèi)AbstractJdbc4Connection實(shí)現(xiàn)了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()); }這樣通過(guò)自定義的ArrayTypeHandler就可以在Mybatis中方便的操作數(shù)組類(lèi)型數(shù)據(jù)了，最后再測(cè)試一下，測(cè)試類(lèi)代碼不變，僅需在調(diào)用存儲(chǔ)過(guò)程時(shí)指定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>再次運(yùn)行junit看一下測(cè)試結(jié)果：
如上所示，此時(shí)已經(jīng)可以成功調(diào)用參數(shù)為Integer[]數(shù)組的pg自定義函數(shù)了。
總結(jié)
簡(jiǎn)單記錄一下在mybatis中調(diào)用postgresql自定義函數(shù)時(shí)傳遞數(shù)組參數(shù)的解決方案，希望對(duì)遇到同樣問(wèn)題的朋友有所幫助，The End。
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持VEVB武林網(wǎng)。
新聞熱點(diǎn)
疑難解答
圖片精選
網(wǎng)友關(guān)注
