Passing arrays to PostgreSQL database from java (JDBC)

Normally JDBC driver needs to know, how to serialize some database type so, that the database can accept it. In case of PostgreSQL JDBC driver we use 2 implementations for passing integer and text arrays.

* to pass an integer array to PostgreSQL database the following java.sql.Array implementation can be used:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>int4</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLInt4Array implements java.sql.Array {

 private final int[] intArray;
 private final String stringValue;

 public PostgreSQLInt4Array(int[] intArray) {
  this.intArray = intArray;
  this.stringValue = intArrayToPostgreSQLInt4ArrayString(intArray);
 }

 public String toString() {
  return stringValue;
 }

 /**
  * This static method can be used to convert an integer array to string representation of PostgreSQL integer array.
  * @param a source integer array
  * @return string representation of a given integer array
  */
 public static String intArrayToPostgreSQLInt4ArrayString(int[] a) {
  if ( a == null ) {
   return "NULL";
  }
  final int al = a.length;
  if ( al == 0 ) {
   return "{}";
  }
  StringBuilder sb = new StringBuilder( 2 + al * 7 ); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
  sb.append('{');
  for (int i = 0; i < al; i++) {
   if ( i > 0 ) sb.append(',');
   sb.append(a[i]);
  }
  sb.append('}');
  return sb.toString();
 }


 public static String intArrayToCommaSeparatedString(int[] a) {
  if ( a == null ) {
   return "NULL";
  }
  final int al = a.length;
  if ( al == 0 ) {
   return "";
  }
  StringBuilder sb = new StringBuilder( al * 7 ); // as we usually operate with 6 digit numbers + 1 symbol for a delimiting comma
  for (int i = 0; i < al; i++) {
   if ( i > 0 ) sb.append(',');
   sb.append(a[i]);
  }
  return sb.toString();
 }

 public Object getArray() throws SQLException {
  return intArray == null ? null : Arrays.copyOf(intArray, intArray.length);
 }

 public Object getArray(Map<String, Class<?>> map) throws SQLException {
  return getArray();
 }

 public Object getArray(long index, int count) throws SQLException {
  return intArray == null ? null : Arrays.copyOfRange(intArray, (int)index, (int)index + count );
 }

 public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
  return getArray(index, count);
 }

 public int getBaseType() throws SQLException {
  return java.sql.Types.INTEGER;
 }

 public String getBaseTypeName() throws SQLException {
  return "int4";
 }

 public ResultSet getResultSet() throws SQLException {
  throw new UnsupportedOperationException();
 }

 public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
  throw new UnsupportedOperationException();
 }

 public ResultSet getResultSet(long index, int count) throws SQLException {
  throw new UnsupportedOperationException();
 }

 public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
  throw new UnsupportedOperationException();
 }

 public void free() throws SQLException {
 }

}

* the same way we can create a class to pass a string array to PostgreSQL database:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>text</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLTextArray implements java.sql.Array {

 private final String[] stringArray;
 private final String stringValue;

 /**
  * Initializing constructor
  * @param stringArray
  */
 public PostgreSQLTextArray(String[] stringArray) {
  this.stringArray = stringArray;
  this.stringValue = stringArrayToPostgreSQLTextArray(this.stringArray);

 }

 @Override
 public String toString() {
  return stringValue;
 }

 private static final String NULL = "NULL";

 /**
  * This static method can be used to convert an string array to string representation of PostgreSQL text array.
  * @param a source String array
  * @return string representation of a given text array
  */
 public static String stringArrayToPostgreSQLTextArray(String[] stringArray) {
  final int arrayLength;
  if ( stringArray == null ) {
   return NULL;
  } else if ( ( arrayLength = stringArray.length ) == 0 ) {
   return "{}";
  }
  // count the string length and if need to quote
  int neededBufferLentgh = 2; // count the beginning '{' and the ending '}' brackets
  boolean[] shouldQuoteArray = new boolean[stringArray.length];
  for (int si = 0; si < arrayLength; si++) {
   // count the comma after the first element
   if ( si > 0 )  neededBufferLentgh++;

   boolean shouldQuote;
   final String s = stringArray[si];
   if ( s == null ) {
    neededBufferLentgh += 4;
    shouldQuote = false;
   } else {
    final int l = s.length();
    neededBufferLentgh += l;
    if ( l == 0 || s.equalsIgnoreCase(NULL) ) {
     shouldQuote = true;
    } else {
     shouldQuote = false;
     // scan for commas and quotes
     for (int i = 0; i < l; i++) {
      final char ch = s.charAt(i);
      switch(ch) {
       case '"':
       case '\\':
        shouldQuote = true;
        // we will escape these characters
        neededBufferLentgh++;
        break;
       case ',':
       case '\'':
       case '{':
       case '}':
        shouldQuote = true;
        break;
       default:
        if ( Character.isWhitespace(ch) ) {
         shouldQuote = true;
        }
        break;
      }
     }
    }
    // count the quotes
    if ( shouldQuote ) neededBufferLentgh += 2;
   }
   shouldQuoteArray[si] = shouldQuote;
  }

  // construct the String
  final StringBuilder sb = new StringBuilder(neededBufferLentgh);
  sb.append('{');
  for (int si = 0; si < arrayLength; si++) {
   final String s = stringArray[si];
   if ( si > 0 ) sb.append(',');
   if ( s == null ) {
    sb.append(NULL);
   } else {
    final boolean shouldQuote = shouldQuoteArray[si];
    if ( shouldQuote ) sb.append('"');
    for (int i = 0, l = s.length(); i < l; i++) {
     final char ch = s.charAt(i);
     if ( ch == '"' || ch == '\\' ) sb.append('\\');
     sb.append(ch);
    }
    if ( shouldQuote ) sb.append('"');
   }
  }
  sb.append('}');
  assert sb.length() == neededBufferLentgh;
  return sb.toString();
 }


 @Override
 public Object getArray() throws SQLException {
  return stringArray == null ? null : Arrays.copyOf(stringArray, stringArray.length);
 }

 @Override
 public Object getArray(Map<String, Class<?>> map) throws SQLException {
  return getArray();
 }

 @Override
 public Object getArray(long index, int count) throws SQLException {
  return stringArray == null ? null : Arrays.copyOfRange(stringArray, (int)index, (int)index + count);
 }

 @Override
 public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
  return getArray(index, count);
 }

 @Override
 public int getBaseType() throws SQLException {
  return java.sql.Types.VARCHAR;
 }

 @Override
 public String getBaseTypeName() throws SQLException {
  return "text";
 }

 @Override
 public ResultSet getResultSet() throws SQLException {
  throw new UnsupportedOperationException();
 }

 @Override
 public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
  throw new UnsupportedOperationException();
 }

 @Override
 public ResultSet getResultSet(long index, int count) throws SQLException {
  throw new UnsupportedOperationException();
 }

 @Override
 public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
  throw new UnsupportedOperationException();
 }

 @Override
 public void free() throws SQLException {
 }

// public static void main(String[] args) {
//  // test the method
//  String[][] stringArrayArray = new String[][] {
//    { "ol\tlala", "", null, "bla" },
//    { "", "NULL", "NuLL", "\"tint\"", "tilt, cilt" },
//    { "", "NULL", "NuLL", "\"ku\\ku\"", "sylt, Co\tlogne", "Num\tremberg" },
//    { }
//  };
//
//  for( String[] stringArray : stringArrayArray ) {
//   PostgreSQLTextArray a = new PostgreSQLTextArray(stringArray);
//   String s = a.toString();
//   System.out.println(s);
//  }
// }

}

Definitely it is possible to merge these two classes so that one wrapper is used instead of two and more known database types can be added in such a wrapper. This implementation relies on the fact that PostgreSQL type names are fixed and the serialization technique does not change much from type to type. So actually all the numeric types can be serialized using an example shown in the first class.

In springframework database abstraction model these classes can be used like that:

...
String sql = "select * from test.array_accepting_procedure( :text_array_param, :int_array_param)";

MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("text_array_param", new PostgreSQLTextArray(dto.getTextArray()),    java.sql.Types.ARRAY );
namedParameters.addValue("int_array_param",  new PostgreSQLInt4Array(dto.getIntegerArray()), java.sql.Types.ARRAY );

resultList =  getSimpleJdbcTemplate().queryForList( sql, namedParameters, mapper);
...