Mybatis calls the PostgreSQL stored procedure to implement array input parameter transfer. mybatispostgresql

Mybatis calls the PostgreSQL stored procedure to implement array input parameter transfer. mybatispostgresql

Preface

Mybatis is used in the project to call operations related to the PostgreSQL Stored Procedure (custom function). Because PostgreSQL comes with an array type, the input parameter of a custom function is an int array, such:

Copy codeThe Code is as follows: create or replace function "public". "func_arr_update" (ids _ int4 )...
As shown above, the parameter is an int array, and Mybatis provides support for calling stored procedures. How can I deal with the unique array type of PostgreSQL as the parameter of stored procedures? Mybatis provides typeHandlers to create an array type processor. The specific method is to implement org. apache. ibatis. type. typeHandler interface, or inherit a very convenient class org. apache. ibatis. type. baseTypeHandler, which can be selectively mapped to a JDBC Type. First, let's take a look at it and then explain it in detail. Next, let's take a look at it with an example.

Create a UDF

The first step is to create a user-defined function for calling. The function is also very simple. Traverse each element of the parameter array and compare it with the stuid of the t_student table. If they are consistent, modify the stuname of the record (concatenate a string after it). The DLL statement of the UDF is as follows:

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";

It is easy to get the length of the parameter array and then start the loop. Match the stuid and update the stuname. You can call it directly in the database to see the result:

For example, you can see that the stuname with stuid 101,102 and 103 has been successfully modified, and the UDF is no longer a problem. Next, let's take a look at how to call it through mybatis.

Call a custom function

It is very easy to call udfs in mybatis. The select element in the Mapper XML file directly provides the attribute support-statementType, which can be seen in the official documentation:

For example, the default value of statementType is PREPARED. That is to say, the underlying layer uses the PreparedStatement of jdbc by default. We all know that CallableStatement is required when jdbc calls a stored procedure, so here we need to set the statementType value to CALLABLE.

Mybatis default ArrayTypeHandler

It is very easy to call the stored procedure. The next question is how to transfer an array parameter to the stored procedure in mybatis? Another concept-TypeHandler, which isCustom type converter provided by mybatisMybatis will use the type processor to convert the obtained value to the Java type in an appropriate way when the PreparedStatement parameter is set or when the value from the result set, mybatis implements some TypeHandler by default for our use. When TypeHandler is not specified (it is not specified in most cases), mybatis will vary depending on the parameters or returned results, the appropriate TypeHandler processing is selected by default. You can check the default TypeHandler by checking the source code. After importing the source code, you can go to org. apache. ibatis. A TypeHandlerRegistry class is found under the type package. typeHandler is managed through this class. Let's take a look at its constructor:

 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()); }

As shown above, this is all the default typeHandler. Note that lines and 47 have a default ArrayTypeHandler. By the way, let's take a look at its source code:

/* *  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(); }}

Can it identify the PostgreSQL array type and automatically convert it to the Java array type? According to the official statement, since this is the default typeHandler, we do not need to make any configuration, mybatis will automatically try to adapt, so directly write the test code to see:

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

As shown above, the parameter is passed to an Integer []. Run junit to check the test result:

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.

As shown in the exception log above, an exception is thrown when the setObject method of the AbstractJdbc2Statement class is called. Let's look at the source code of this method:

  /*   * 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);    }  }

The Integer [] array passed in by our parameters is an Object array, while the second parameter of the setObject (int parameterIndex, Object x) method is Object, so here it is not possible to match, so an error is returned. Can it be changed to int? In the preceding else if statement, the line of code x instanceof int [] is obviously not available, so it is clear that the ArrayTypeHandler provided by mybatis by default cannot automatically recognize the array type of PostgreSQL, we must customize an ArrayTypeHandler with the parameter Object [] to implement matching.

Custom ArrayTypeHandler

For example, paste the code first:

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;  }}

As shown above, the parameter type is specified as Object [], so that you can receive parameters of the Integer [] type. The key is 44 ~ Line 46: postgresql's driver class AbstractJdbc4Connection implements the createArrayOf method of the Connect interface. The source code is as follows:

  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());  }

In this way, you can use the custom ArrayTypeHandler to conveniently operate the array type data in Mybatis. Finally, you can test it again, and the test class code remains unchanged, you only need to specify the typeHandler Of The mapper file when calling the stored procedure:

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

Run junit again to check the test results:

As shown above, you can successfully call the PostgreSQL user-defined function with the parameter Integer [] array.

Summary

A simple record of The solution for passing array parameters when calling postgresql udfs in mybatis. I hope it will be helpful to those who encounter The same problem, The End.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.