MyBatis calls PostgreSQL stored procedures to implement array entry-parameter delivery _postgresql

Objective

The project uses the MyBatis call PostgreSQL stored procedure (custom function) related operations, because the PostgreSQL has an array type, so there is a custom function of the entry is an int array, in the form of:

code as follows:

The CREATE OR REPLACE FUNCTION "public." Func_arr_update "(IDs _int4) ...

As shown above, the argument is an array of int, and MyBatis provides support for invoking stored procedures, so what about PostgreSQL's unique array type as a parameter to the stored procedure? In fact, MyBatis provides a type processor that typehandlers can create an array type, either by implementing the Org.apache.ibatis.type.TypeHandler interface, or by inheriting a convenient class Org.apache.ibatis.type.BaseTypeHandler, then you can selectively map it to a JDBC type, take a moment to understand it, and then do a detailed description, followed by a combination of examples to see.

Create a custom function

The first step, as shown in the diagram, is to create a custom function for the call, which is simple enough to iterate over each element of the parameter array and the stuid of the T_student table, and, if it is consistent, modify the stuname of that record (and then concatenate a string). The DLL statement for the custom function is as follows:

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

ALTER FUNCTION "public". Func_arr_update "(IDs _int4) OWNER to" Postgres ";

Quite simply, get to the length of the parameter array and start the loop, match stuid and update stuname, and just call it in the database to see the results:

As shown above, you can see the successful modification of the Stuid 101,102 and 103 stuname, the custom function is no longer a problem, and then look at how to use the MyBatis call.

Call a custom function

It is simple to call a custom function in MyBatis, Mapper the Select element in the XML file directly provides attribute support--statementtype, as you can see in the official documentation:

As shown above, the StatementType value defaults to prepared, which means that the underlying default uses JDBC PreparedStatement, and we all know that JDBC calls the stored procedure with CallableStatement, So here we need to set the value of StatementType to callable.

MyBatis Default Arraytypehandler

Calling a stored procedure is simple, and the next question is how do you pass an array parameter to a stored procedure in MyBatis? Here's another concept--typehandler, which is a custom type converter provided by MyBatis , MyBatis when you set a parameter in a precompiled statement object (PreparedStatement) or when you take a value from the result set, you use the type processor to convert the obtained value to the Java type in the appropriate way, mybatis the default implementation of a part of the Typehandler for us to use, When we do not specify Typehandler (most cases will not be specified), MyBatis will be based on parameters or return results of different, default for us to choose the appropriate typehandler processing, the following can be viewed from the source to see the default Typehandler, Import source can be under the Org.apache.ibatis.type package to find a Typehandlerregistry class, typehandler It is through this class management, first look at its construction method:

 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 46,47 Line can see the default has a Arraytypehandler, by the way to see its source code:

* * Copyright 2009-2012 the MyBatis team * licensed under the Apache License, Version 2.0 (the "License");
 * You could not use this file, except in compliance with the License. * You may obtain a copy of the License in * * http://www.apache.org/licenses/LICENSE-2.0 * * unless required by a  Pplicable or agreed to in writing, software * Distributed under the License be distributed on ' as is ' basis, *
 Without warranties or CONDITIONS of any KIND, either express or implied.
 * 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.G
  Etarray (ColumnName); return array = = null?
 Null:array.getArray (); @Override public Object Getnullableresult (ResultSet rs, int columnindex) throws SQLException {array array = Rs.get
  Array (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 recognize the PostgreSQL array type and automatically convert it to a Java array type? According to the official, since this is the default Typehandler, then we do not have to do any configuration mybatis will automatically try to fit, so write the test code directly to see:

@Test public
void TestFunc1 () {
  sqlsession session = Sqlsessionfactory.opensession ();
  try {
    map<string, object> Map = new hashmap<string, object> ();
    Map.put ("IDs", new integer[] {A, 102, N);
    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 a integer[], run JUnit to see the test results directly:

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

The Exception log, as shown above, throws an exception when calling the Abstractjdbc2statement class's SetObject method, then look at the source of the 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 a instance of {0}.) Use SetObject () with a explicit Types value toSpecify the type to use. ", X.getclass (). GetName ()), psqlstate.invalid_parameter_type);
 }
  }

The integer[] array we're going to pass is an object array, and the second argument to the setobject (int parameterindex, Object x) method is object, so there's no matching error here, so replace it with a int[ ] can I? There is obviously no x instanceof int[] This line of code in the else if statement above, so of course not, it is also clear that the MyBatis provided by default Arraytypehandler is an array type that cannot automatically recognize PostgreSQL. We must customize a arraytypehandler with a parameter of object[] to achieve a match.

Custom Arraytypehandler

The title, first put the code:

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 Stati
  C 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) thro
    WS 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 t
    Ype 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 {retur
  N 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, we have specified the parameter type object[] so that we can receive the parameters of the integer[type, the key is 44~46 line, The PostgreSQL driver class Abstractjdbc4connection implements the Connect interface createarrayof method, 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}.", T Ypename), psqlstate.invalid_name);

    Char Delim = GetTypeInfo (). Getarraydelimiter (OID);
    StringBuffer sb = new StringBuffer ();
    Appendarray (SB, elements, Delim);

    This is not work once we have a JDBC 5,
    //But it ' ll does for now.
    Return to New Jdbc4array (this, OID, sb.tostring ());
  }

This allows you to easily manipulate the array type data in MyBatis by customizing the Arraytypehandler, and then test the code for the same, just specify the Typehandler of the mapper file when you call the stored procedure:

@Test public
void TestFunc1 () {
  sqlsession session = Sqlsessionfactory.opensession ();
  try {
    map<string, object> Map = new hashmap<string, object> ();
    Map.put ("IDs", new integer[] {A, 102, N);
    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 and look at the test results:

As shown above, a PG custom function with the parameter integer[] array can be successfully invoked at this time.

Summarize

Simply record a solution that passes the array parameter when calling PostgreSQL custom function in MyBatis, and hopefully it will help friends who have the same problem, the end.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.