PyDev is finally fully open sourced!

PyDev is finally fully open sourced! These are really good news!

Getting arrays from PostgreSQL database using JDBC in Java

getArray() standard JDBC methods are working for basic types in PostgreSQL as documented in JDBC specifications.

As PostgreSQL 8.2 does not support arrays of types, and PostgreSQL JDBC driver does not support getting of the arrays of types even from PostgreSQL 8.3+, we can pass this kind of structures as text arrays, were text elements are postgres records (not types), serialized with textin(record_out(ROW(a, b, c)))::text approach (for PostgreSQL 8.2 this is the only way to serialize the record, in 8.3+ it is now possible to simply convert the record to text like ROW(a, b, c)::text) and the way of deserialization of the received text array data with java method that is shown below:

public class Utils {
...

/**
* Method parses a postgres Row into a List of Strings.
* <p>
* The postgres row is represented by a String and consists of one or more columns, that are separated by a comma.
* The row must begin with an open bracket and must end with a closing bracket.
* Each column must begin with a letter or a quote. If a column begins with a quote, the column must end with a quote.
* Inside quotation a quote is represented by a double quote or by backslash and quote, a backslash is represented by double backslash.
*
* @param value
* @return List of Strings
* @throws JBackendParserException
*/
public static List<String> postgresROW2StringList(String value) throws JBackendParserException
{
    return postgresROW2StringList(value, 128);
}

/**
* Method parses a postgres Row into a List of Strings.
* <p>
* The postgres row is represented by a String and consists of one or more columns, that are separated by a comma.
* The row must begin with an open bracket and must end with a closing bracket.
* Each column must begin with a letter or a quote. If a column begins with a quote, the column must end with a quote.
* Inside quotation a quote is represented by a double quote or by backslash and quote, a backslash is represented by double backslash.
* <p>
* The appendStringSize is the Size for StringBuilder.
*
* @param value, the postgres Row
* @param appendStringSize
* @return List of Strings
* @throws JBackendParserException
*/
public static List<String> postgresROW2StringList(String value, int appendStringSize)
throws JBackendParserException
{
    if (!(value.startsWith("(") && value.endsWith(")")))
    throw new ParseException("postgresROW2StringList() ROW must begin with '(' and end with ')': " + value);

    List<String> result = new ArrayList<String>();

    char[] c = value.toCharArray();

    StringBuilder element = new StringBuilder(appendStringSize);
    int i = 1;
    while (c[i] != ')')
    {
        if (c[i] == ',')
        {
            if (c[i+1] == ',')
            {
                result.add(new String());
            } else if (c[i+1] == ')')
            {
                result.add(new String());
            }
            i++;
        } else if (c[i] == '\"')
        {
            i++;
            boolean insideQuote = true;
            while(insideQuote)
            {
                char nextChar = c[i + 1];
                if(c[i] == '\"')
                {
                    if (nextChar == ',' || nextChar == ')')
                    {
                        result.add(element.toString());
                        element = new StringBuilder(appendStringSize);
                        insideQuote = false;
                    } else if(nextChar == '\"')
                    {
                        i++;
                        element.append(c[i]);
                    } else
                    {
                        throw new ParseException("postgresROW2StringList() char after \" is not valid");
                    }
                } else if (c[i] == '\\')
                {
                    if(nextChar == '\\' || nextChar == '\"')
                    {
                        i++;
                        element.append(c[i]);
                    } else
                    {
                        throw new ParseException("postgresROW2StringList() char after \\ is not valid");
                    }
                } else
                {
                    element.append(c[i]);
                }
                i++;
            }
        }else
        {
            while(!(c[i] == ',' || c[i] == ')'))
            {
                element.append(c[i]);
                i++;
            }
            result.add(element.toString());
            element = new StringBuilder(appendStringSize); // we aways loose the last object here, but its easier then checking for flag every time before append (definitely we loose some performance here)
        }
    }
return result;
}
...
}

We can use the following example SQL statement to demonstrate how to pack needed data structures into the serialized text arrays

select s.i as id,
'row ' || s.i as text_data,
ARRAY( select textin(record_out( ROW( 100 * s.i + a.i,
'element ' || 100 * s.i + a.i,
'constant text with some "quoting"' ) ))::text
from generate_series( 1, 5 ) as a(i) ) as serialized_row_array
from generate_series(1, 10) as s(i)

The result of the execution of this query is:



And then read these text arrays using the following java code in the springsframework row mapper

And then read these text arrays using the following java code in the
springframework row mapper (as this example uses ResultSet actually, you can see as well, how to read data directly from ResultSet in the same example):

public class ArrayRowMapper<ITEM> implements ParameterizedRowMapper<ITEM> {

...
private ITEM createEmptyItem() {
...
}
private Element createEmptyElement() {
...
}

public final ITEM mapRow(ResultSet rs, int rowNum) throws SQLException {
ITEM item = createEmptyItem();
item.setId( rs.getInt("id") );
item.setTextData( rs.getString("text_data") );
Array sqlArray = rs.getArray("serialized_row_array");
if ( sqlArray == null ) {
item.setElements(null);
} else {
String[] textArray = (String[])sqlArray.getArray();
List<Element> elements = new ArrayList<Element>(textArray.length);

for(int i = 0; i < textArray.length; i++)
{
try
{
List<String> stringResultList = Utils.postgresROW2StringList(textArray[i]);

Element element = createEmptyElement();
element.setId(Integer.parseInt(stringResultList.get(0)));
element.setTextData(stringResultList.get(1));
element.setConstantTextData(stringResultList.get(2));
elements.add(element);
}catch (JBackendParserException pe) {
logger.error("Problem parsing received ROW value [" + textArray[i] + "]: " + pe.getMessage(), pe);
}catch (Exception e) {
logger.error("Problem setting values to Element object from received ROW value [" + textArray[i] + "] : " + e.getMessage(), e);
}
}
item.setElements(elements);
}
}
}

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[][] {
//              { "shm\taliko", "", null, "kluku" },
//              { "", "NULL", "NuLL", "\"kuku\"", "valiko, shmaliko" },
//              { "", "NULL", "NuLL", "\"ku\\ku\"", "valiko, shm\taliko", "shm\taliko" },
//              { }
//      };
//
//      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);
...

Passing arrays to PostgreSQL database from java (JDBC) by Valentine Gogichashvili is licensed under a Creative Commons Attribution 3.0 Unported License.