2009-02-26

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);
...
Creative Commons License
Passing arrays to PostgreSQL database from java (JDBC) by Valentine Gogichashvili is licensed under a Creative Commons Attribution 3.0 Unported License.

4 comments:

Mike Traum said...

Nice post. The only issue I'm having is with the intArrayToPostgreSQLInt4ArrayString method and its null check. If I leave it the way it is (where it returns NULL), I get the following exception:
org.postgresql.util.PSQLException: ERROR: array value must start with "{" or dimension information

So, I can replace that with "{}", but I'd definitely prefer to set the column to null. Any ideas?

Mike Traum said...

For anyone else reading this, null should be returned if the array is null, not "NULL" (the value null, not the string "NULL).

Thanks again...

Andrew Rose said...

Valentine,

I'm hoping that, since you've posted this very helpful code on your blog, you're willing for other people to use it.

If that's the case, I wonder if you'd be willing to modify the post to add the following...

"I, Valentine Gogichashvili, being the copyright holder, hereby place this code in the public domain."

Obviously this is your code and you're free to do what you want with it, but sadly although I'd love to use it, I can't without some clarity on the license situation.

If you're unhappy putting it in the public domain but would publish it under some other license, that could be useful too.

Many thanks,

Andrew

Valentine Gogichashvili said...

Hallo Andrew,

I added a Creative Commons license to that post :) So you are free to use the code as you wish :)

https://github.com/valgog/pg-spring-type-mapper can be also interesting for you... though the code is not ready for production... and another project, bases on the ideas of the first one https://github.com/danielnowak/sproc-spring-mapper

So have fun and you are welcome to contribute! ;)