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