Arrays

SQL Arrays are supported both as input and output parameters.

Input

If a method parameter is either either Collection subtype or primitive or reference array (not java.sql.Array) we automatically map it to a SQL array.

In order to create the array we need to know the name of the element type (see Connection#createArrayOf). We use a default mapping of Java types to SQL standard types. For cases where this does not work for you you can either use the @TypeName annotation or implement the TypeNameResolver interface and register it using the #withTypeNameResolver method. For Collection subtype we recommend annotating the type parameter but annotating the method parameter works as well.

  void sampleArrayArgumentTypeParameter(List<@TypeName("name_of_array_element_type") Integer> ids);

  void sampleArrayArgumentArray(@TypeName("name_of_array_element_type") Integer[] ids);

ProcedureCallerFactory.of(inferfaceDeclaration, dataSource)
    .withTypeNameResolver(parameter -> { /* implementation */ })
    .build();

We will call Array#free before the method returns.

Output

Returning SQL arrays through out parameter or return values works much the same way, simply make your interface method return a Java primitive or reference array (not java.sql.Array). As the driver creates the array there is no need to pass in the name of the element type.

  @ReturnValue
  Integer[] referenceArrayAsReturnValue();

  @OutParameter
  int[] primitiveArrayAsOutParameter();

We will call Array#free before the method returns.

Oracle

Arrays in Oracle are special because Oracle does not support anonymous arrays, Oracle only supports arrays as user defined types with a name.

First you need to all #withOracleArrays on the factory in order to enable Oracle array support. In addition the Oracle driver classes need to be visible.

ProcedureCallerFactory.of(inferfaceDeclaration, dataSource)
    .withOracleArrays()
    .build();

Note that only top level types can be used as PL/SQL Package Types can only be used in SELECT statements.

CREATE OR REPLACE TYPE NAME_OF_ORACLE_ARRAY_TYPE IS TABLE OF NUMBER(8);

Input

For input arrays the @TypeName annotation or a TypeNameResolver need to be used. In this case the name will be the array type, not the element type. Therefore for Collection subtypes we recommend annotating the method parameter rather than the type parameter but annotating the type parameter works as well.

  @ReturnValue
  void sampleArrayArgumentTypeParameter(@TypeName("NAME_OF_ORACLE_ARRAY_TYPE") List<Integer> ids);

  @OutParameter
  void sampleArrayArgumentArray(@TypeName("NAME_OF_ORACLE_ARRAY_TYPE") Integer[] ids);

Output

For output arrays the typeName attribute on @OutParameter or @ReturnValue needs to be set.

  @ReturnValue(typeName = "NAME_OF_ORACLE_ARRAY_TYPE")
  Integer[] referenceArrayAsReturnValue();

  @OutParameter(typeName = "NAME_OF_ORACLE_ARRAY_TYPE")
  int[] primitiveArrayAsOutParameter();

PostgreS

Starting with version 42.2.0 of pgjdbc and version 0.11 of this library proprietary driver extensions can be used to bind primitive arrays more efficiently.

First you need to all #withPostgresArrays on the factory in order to enable PostgreS array support. In addition the PostgreS driver classes need to be visible.

ProcedureCallerFactory.of(inferfaceDeclaration, dataSource)
    .withPostgresArrays()
    .build();